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.
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