SQL in PL/SQL
The only statements allowed directly in pl/sql
are DML and TCL.
BINDING
Binding a variable is
the process of identifying the storage location associated with an identifier
in the program.
Types of binding
Ø Early binding
Ø Late binding
Ø Binding during the
compiled phase is early binding.
Ø Binding during the
runtime phase is late binding.
Ø In early binding compile
phase will take longer because of binding work but the execution
is faster.
Ø In late binding it will
shorten the compile phase but lengthens the execution time.
Ø PL/SQL by default uses early
binding.
Ø Binding also involves
checking the database for permissions to access the object
Referenced.
DYNAMIC SQL
Ø If you use DDL in pl/sql it validates
the permissions and existence if requires during compile time which makes
invalid.
Ø We can avoid this by
using Dynamic SQL.
Ø Dynamic SQL allows you
to create a SQL statement dynamically at runtime.
Two techniques are available for Dynamic SQL.
Ø Native Dynamic SQL
Ø DBMS_SQL package
USING NATIVE
DYNAMIC SQL
USING EXECUTE
IMMEDIATE
Ex:
BEGIN
Execute immediate ‘create table
student(no number(2),name varchar(10))’;
or
Execute immediate (‘create table
student(no number(2),name varchar(10))’);
END;
USING EXECUTE
IMMEDIATE WITH PL/SQL VARIABLES
Ex:
DECLARE
v varchar(100);
BEGIN
v := 'create table student(no
number(2),name varchar(10))';
execute immediate v;
END;
USING EXECUTE
IMMEDIATE WITH BIND VARIABLES AND USING CLAUSE
Ex:
DECLARE
v varchar(100);
BEGIN
v := 'insert into student
values(:v1,:v2,:v3)';
execute immediate v using 6,'f',600;
END;
EXECUTING
QUERIES WITH OPEN FOR AND USING CLAUSE
Ex:
CREATE OR REPLACE
PROCEDURE P(smarks in number) IS
s varchar(100) := 'select *from student
where marks > :m';
type t is ref cursor;
c t;
v student%rowtype;
BEGIN
open c for s using smarks;
loop
fetch c into v;
exit when c%notfound;
dbms_output.put_line('Student Marks
= ' || v.marks);
end loop;
close c;
END;
Output:
SQL> exec p(100)
Student Marks = 200
Student Marks = 300
Student Marks = 400
QUERIES WITH EXECUTE
IMMEDIATE
Ex:
DECLARE
d_name dept.dname%type;
lc dept.loc%type;
v varchar(100);
BEGIN
v := 'select dname from dept where
deptno = 10';
execute immediate v into d_name;
dbms_output.put_line('Dname = '||
d_name);
v := 'select loc from dept where dname
= :dn';
execute immediate v into lc using
d_name;
dbms_output.put_line('Loc = ' || lc);
END;
Output:
Dname = ACCOUNTING
Loc = NEW YORK
VARIABLE NAMES
Ex:
DECLARE
Marks number(3) := 100;
BEGIN
Delete student where marks = marks; -- this will delete all the rows in the student
table
END;
This can be
avoided by using the labeled blocks.
<<my_block>>
DECLARE
Marks number(3) := 100;
BEGIN
Delete student where marks =
my_block.marks; -- delete rows which has a
marks of 100
END;
GETTING DATA
INTO PL/SQL VARIABLES
Ex:
DECLARE
V1 number;
V2 varchar(2);
BEGIN
Select no,name into v1,v2 from student
where marks = 100;
END;
DML AND RECORDS
Ex:
CREATE OR REPLACE
PROCEDURE P(srow in student%rowtype) IS
BEGIN
insert into student
values srow;
END P;
DECLARE
s student%rowtype;
BEGIN
s.no := 11;
s.name := 'aa';
s.marks := 100;
p(s);
END;
RECORD BASED
INSERTS
Ex:
DECLARE
srow student%rowtype;
BEGIN
srow.no := 7;
srow.name := 'cc';
srow.marks := 500;
insert into student values srow;
END;
RECORD BASED
UPDATES
Ex:
DECLARE
srow student%rowtype;
BEGIN
srow.no := 6;
srow.name := 'cc';
srow.marks := 500;
update student set row=srow where no =
srow.no;
END;
USING RECORDS
WITH RETURNING CLAUSE
Ex:
DECLARE
srow student%rowtype;
sreturn student%rowtype;
BEGIN
srow.no := 8;
srow.name := 'dd';
srow.marks := 500;
insert into student values srow returning
no,name,marks into sreturn;
dbms_output.put_line('No = ' ||
sreturn.no);
dbms_output.put_line('No = ' ||
sreturn.name);
dbms_output.put_line('No = ' ||
sreturn.marks);
END;
Output:
No = 8
No = dd
No = 500
FORALL STATEMENT
This can be used to get the data from the database
at once by reducting the number of context switches which is a transfer of
control between PL/SQL and SQL engine.
Syntax:
Forall index_var in
[ Lower_bound..upper_bound |
Indices of indexing_collection |
Values of indexing_collection ]
SQL statement;
FORALL WITH
NON-SEQUENTIAL ARRAYS
Ex:
DECLARE
type t is table of student.no%type index
by binary_integer;
ibt t;
BEGIN
ibt(1) := 1;
ibt(10) := 2;
forall i in ibt.first..ibt.last
update student set marks = 900
where no = ibt(i);
END;
The above
program will give error like ‘element at index [2] does not exists.
You can
rectify it in one of the two following ways.
USGAGE OF
INDICES OF TO AVOID THE ABOVE BEHAVIOUR
Ex:
DECLARE
type t is table of student.no%type index
by binary_integer;
ibt t;
type t1 is table of boolean index by
binary_integer;
ibt1 t1;
BEGIN
ibt(1) := 1;
ibt(10) := 2;
ibt(100) := 3;
ibt1(1) := true;
ibt1(10) := true;
ibt1(100) := true;
forall i in indices of ibt1
update student set marks = 900
where no = ibt(i);
END;
USGAGE OF
INDICES OF TO AVOID THE ABOVE BEHAVIOUR
Ex:
DECLARE
type t is table of student.no%type index
by binary_integer;
ibt t;
type t1 is table of pls_integer index by
binary_integer;
ibt1 t1;
BEGIN
ibt(1) := 1;
ibt(10) := 2;
ibt(100) := 3;
ibt1(11) := 1;
ibt1(15) := 10;
ibt1(18) := 100;
forall i in values of ibt1
update student set marks = 567
where no = ibt(i);
END;
POINTS ABOUT BULK BINDS
Ø Passing the entire PL/SQL table to the SQL engine in one step is
known as bulk bind.
Ø Bulk binds are done
using the forall statement.
Ø If there is an error
processing one of the rows in bulk DML operation, only that row is rolled back.
POINTS ABOUT RETURING CLAUSE
Ø This will be used only
with DML statements to return
data into PL/SQL variables.
Ø This will be useful in
situations like , when performing insert or update or delete if you want to
know the data of the table which has been effected by the DML.
Ø With out going for
another SELECT using RETURNING clause we will get the data which will avoid a call to RDBMS kernel.
No comments:
Post a Comment