BULK COLLECT
Ø This is used for array fetches
Ø With this you can retrieve multiple rows of data with a single roundtrip.
Ø This reduces the number of context switches between the pl/sql and sql engines.
Ø Reduces the overhead of retrieving data.
Ø You can use bulk collect in both dynamic and static sql.
Ø You can use bulk collect in select, fetch into and returning into clauses.
Ø SQL engine automatically initializes and extends the collections you reference in the bulk collect clause.
Ø Bulk collect operation empties the collection referenced in the into clause before executing the query.
Ø You can use the limit clause of bulk collect to restrict the no of rows retrieved.
Ø You can fetch into multible collections with one column each.
Ø Using the returning clause we can return data to the another collection.
BULK COLLECT IN FETCH
Ex:
DECLARE
Type t is table of dept%rowtype;
nt t;
Cursor c is select *from dept;
BEGIN
Open c;
Fetch c bulk collect into nt;
Close c;
For i in nt.first..nt.last loop
dbms_output.put_line('Dname = ' || nt(i).dname || ' Loc = ' || nt(i).loc);
end loop;
END;
Output:
Dname = ACCOUNTING Loc = NEW YORK
Dname = RESEARCH Loc = DALLAS
Dname = SALES Loc = CHICAGO
Dname = OPERATIONS Loc = BOSTON
BULK COLLECT IN SELECT
Ex:
DECLARE
Type t is table of dept%rowtype;
Nt t;
BEGIN
Select * bulk collect into nt from dept;
for i in nt.first..nt.last loop
dbms_output.put_line('Dname = ' || nt(i).dname || ' Loc = ' || nt(i).loc);
end loop;
END;
Output:
Dname = ACCOUNTING Loc = NEW YORK
Dname = RESEARCH Loc = DALLAS
Dname = SALES Loc = CHICAGO
Dname = OPERATIONS Loc = BOSTON
LIMIT IN BULK COLLECT
Ex:
DECLARE
Type t is table of dept%rowtype;
nt t;
Cursor c is select *from dept;
BEGIN
Open c;
Fetch c bulk collect into nt;
Close c;
For i in nt.first..nt.last loop
dbms_output.put_line('Dname = ' || nt(i).dname || ' Loc = ' || nt(i).loc);
end loop;
END;
Output:
Dname = ACCOUNTING Loc = NEW YORK
Dname = RESEARCH Loc = DALLAS
MULTIPLE FETCHES IN INTO CLAUSE
Ex1:
DECLARE
Type t is table of dept.dname%type;
nt t;
Type t1 is table of dept.loc%type;
nt1 t;
Cursor c is select dname,loc from dept;
BEGIN
Open c;
Fetch c bulk collect into nt,nt1;
Close c;
For i in nt.first..nt.last loop
dbms_output.put_line('Dname = ' || nt(i));
end loop;
For i in nt1.first..nt1.last loop
dbms_output.put_line('Loc = ' || nt1(i));
end loop;
END;
Output:
Dname = ACCOUNTING
Dname = RESEARCH
Dname = SALES
Dname = OPERATIONS
Loc = NEW YORK
Loc = DALLAS
Loc = CHICAGO
Loc = BOSTON
Ex2:
DECLARE
type t is table of dept.dname%type;
type t1 is table of dept.loc%type;
nt t;
nt1 t1;
BEGIN
Select dname,loc bulk collect into nt,nt1 from dept;
for i in nt.first..nt.last loop
dbms_output.put_line('Dname = ' || nt(i));
end loop;
for i in nt1.first..nt1.last loop
dbms_output.put_line('Loc = ' || nt1(i));
end loop;
END;
Output:
Dname = ACCOUNTING
Dname = RESEARCH
Dname = SALES
Dname = OPERATIONS
Loc = NEW YORK
Loc = DALLAS
Loc = CHICAGO
Loc = BOSTON
RETURNING CLAUSE IN BULK COLLECT
declare
type t is table of number(2);
nt t := t(1,2,3,4);
type t1 is table of varchar(2);
nt1 t1;
type t2 is table of student%rowtype;
nt2 t2;
begin
select name bulk collect into nt1 from student;
forall v in nt1.first..nt1.last
update student set no = nt(v) where name = nt1(v) returning no,name,marks bulk collect into nt2;
for v in nt2.first..nt2.last loop
dbms_output.put_line('Marks = ' || nt2(v));
end loop;
end;
POINTS TO REMEMBER
Ø Cursor name can be up to 30 characters in length.
Ø Cursors declared in anonymous blocks or subprograms closes automatically when that block terminates execution.
Ø %bulk_rowcount and %bulk_exceptions can be used only with forall construct.
Ø Cursor declarations may have expressions with column aliases.
Ø These expressions are called virtual columns or calculated columns.
FETCH BULK COLLECT <cursor_name> BULK COLLECT INTO <collection_name> LIMIT <numeric_expression>; --or FETCH BULK COLLECT <cursor_name> BULK COLLECT INTO <array_name> LIMIT <numeric_expression>; SET timing ON DECLARE CURSOR a_cur IS SELECT program_id FROM airplanes; BEGIN FOR cur_rec IN a_cur LOOP NULL; END LOOP; END; / DECLARE CURSOR a_cur IS SELECT program_id FROM airplanes; TYPE myarray IS TABLE OF a_cur%ROWTYPE; cur_array myarray; BEGIN OPEN a_cur; LOOP FETCH a_cur BULK COLLECT INTO cur_array LIMIT 100; EXIT WHEN a_cur%NOTFOUND; END LOOP; CLOSE a_cur; END; / DECLARE CURSOR a_cur IS SELECT program_id FROM airplanes; TYPE myarray IS TABLE OF a_cur%ROWTYPE; cur_array myarray; BEGIN OPEN a_cur; LOOP FETCH a_cur BULK COLLECT INTO cur_array LIMIT 500; EXIT WHEN a_cur%NOTFOUND; END LOOP; CLOSE a_cur; END; / DECLARE CURSOR a_cur IS SELECT program_id FROM airplanes; TYPE myarray IS TABLE OF a_cur%ROWTYPE; cur_array myarray; BEGIN OPEN a_cur; LOOP FETCH a_cur BULK COLLECT INTO cur_array LIMIT 1000; EXIT WHEN a_cur%NOTFOUND; END LOOP; CLOSE a_cur; END; /
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
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
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
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
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;
Queries with execute immediate
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;
Bind variables
Declare
V number := 500;
Begin
Update student set marks = v where; -- here v is bind variable
End;
Variable Names
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
Declare
V1 number;
V2 varchar(2);
Begin
Select no,name into v1,v2 from student where marks = 100;
End;
DML and Records
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
declare
srow student%rowtype;
begin
srow.no := 7;
srow.name := 'cc';
srow.marks := 500;
insert into student values srow;
end;
Record based updates
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
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;
Forall with non-sequential arrays
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.
Usage of indices of to avoid the above error
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;
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;
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.
Returning 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