PL/SQL programming

SQL and PL/SQL

SQL:
• Native language of RDBMS
• Non- procedural language
PL/SQL(Procedural language extension of SQL):
• Conditional control statements support
Advantages:
• Better performance
• Portable
• Modularized program development
• Example: Bank project (saving account, current account,
personal loan, vehicle loan etc)
• Oracle forms, reports.
• IRCTC uses Oracle forms
 Some simple important concepts to
know
• Maximum width(No. of characters) of table
name?
• Table name always start with alphabet
• Symbols that can only be used in table name can
be _ , $ and #
• Maximum number of columns in single table?
0 to 255(In RDBMS)
1000 (In Oracle)
1024 (In MySql server)
 Some simple important concepts to
know
• How many triggers can be created on a single table?
12(for each row and each statement before and after
– for insert, update,delete)
• When using update or delete in PL/SQL, cursor is must
• To view all the tables and owner names,
• Select table_name,owner from ALL_TABLES;
• Displays count of tables from dict which start in
USER_
• select count(*) from dict where table_name like
'USER_'
 Types of variables
i) Scalar variables: Holds only one value at a time
SQL specific PL/SQL specific:
Data types: • Boolean
• Number,char,varchar2, date,Long,
Raw, LongRaw • Binary_Float
• Number => (precision,scalar) • Binary_integer
=>(7,2) => 52001.25 • Bianry_double
• Char => Max 2000bytes • PLSQL_integer
• Varchar2 => Max 4000 bytes
• Date => 7 bytes
• Long => Max 2 GB
• Raw => Max 2000 bytes
• LongRaw => Max 2 GB
 Types of variables – Continued
• Composite => hold more than 1 values
• Entire column info in 1 variable
• Entire table info in 1 variable(associative array)
• Entire record in 1 variable(PL/SQL record)
• LOB’s
– CLOB(Char LOB)
– BLOB(binary LOB)
– NCLOB(National char LOB) => store unicode info
(or) Multibyte info
 Binding of variables
• Variables declared in one environment and used in
another.
Outside the program
• Var name varchar2(20)
• Var empno int
PL/SQL block
Begin
Select eno,ename into :empno, :name from employee
where eno=1;
End;
/
• DDL and DCL(eg:grant) commands cannot be used in
PL/SQL.
• Only in Dynamic SQL, it can be used
• PL/SQL
• Syntax and other errors identified at compile time itself
• Multi-row functions(aggregate functions) cannot be
used in PL/SQL
• Dynamic SQL
• Syntax and other errors identified only at run time
• Rowid and rownum are pseudocolumns
• Rowid => returns a physical address of the row
• Rownum => returns the row number
 Select the row using rownum
Displays the 1st row:
select employee_id,last_name,salary,job_id
from employees
where rownum=1;
To display the 2nd row(Is it possible => ?? )
select employee_id,last_name,salary,job_id
from employees
where rownum=2;
 Selecting a particular row based on
rownum
Select the 5th row Select the 7th row
select select
employee_id,last_name,sal employee_id,last_name,sal
ary,job_id from employees ary,job_id from employees
where rownum<=5 where rownum<=5
minus minus
select select
employee_id,last_name,sal employee_id,last_name,sal
ary,job_id from employees ary,job_id from employees
where rownum<=4; where rownum<=4;
 Alternate way to Select the particular
row
• select employee_id,last_name,salary,job_id
from (select e.*,rownum x from employees e)
where x=5;
 Displays from 1 to 10 Displays from 10 to 1
begin begin
for a in 1..10 for a in reverse 1..10
loop loop
dbms_output.put_line(a); dbms_output.put_line(a);
end loop; end loop;
end; end;
declare declare
a number:=5; a number;
b number:=2; b number;
begin begin
if a>b then if a>b then
dbms_output.put_line(a || 'is dbms_output.put_line(a || 'is
greater than b'); greater than b');
else else
dbms_output.put_line(a || 'is less dbms_output.put_line(a || 'is less
than b'); than b');
end if; end if;
end; End;

Output: ? Output: ?
 Packages
• PL/SQL supports modularized Prog dev using
package
• To view all packages,
• Select distinct name from all_source where
type = ‘PACKAGE’;
• Each package have many procedure inside it
• dbms_output . put_line(‘hai’);

• Built-in Package Procedure inside that

package
• All procedures are put into one package
• Advantage:
• When any procedure in a package is called for
1st time, entire package => loaded to logical
memory.
• Further, if any procedure called from that
package, it will be loaded from logical
memory=> Response time increases
• Object oriented support
How long the package will be in Logical
memory?
• LRU can be used
• Admin can unpin the package from logical
memory
• Shut down of db, will unpin package from
logical to physical memory
 2 parts of a package
• Specification => visible to all users who got
priviledge
• Body => Visible only to the owner of the
package
• To view specification of package,
• Select text from all_source where
name=‘DBMS_OUTPUT’;
 To list all procedures inside the
package
• Syntax:
• desc packagename;
• Example:
• desc DBMS_OUTPUT
To connect to hr account
 Locking
• Row –level locking
• Table –level locking
• Row –level locking
• Syntax:
select columns from <tablename>
where <condition> for update <wait time
period>
• Table- level locking
• Syntax: Lock table <tablename> in <lockmode>
– Lockmode can be shared or exclusive
 Cursors
• To process multiple rows
• Huge amount of information need to be
updated or deleted
• Displaying all rows using PL/SQL without using
cursor will degrade performance of db?
Types
Implicit cursor(Managed by system)
Explicit cursor(Managed by programmer)
 Sub program
• Types
• Procedure => Performs an action[DML & TCL
commands can be used]
• Functions => compute and return a value
 Procedure - example
create or replace procedure proc1 is
cursor c1 is select * from employee;
begin
for i in c1 loop
dbms_output.put_line(i.eno||chr(9)||i.ename|
|chr(9)||i.salary);
end loop;
end;