Collections:- To store data in the form of arrays we use collections.
Eg: nested table.
Declare
Type nes is table of varchar2(10);
Vnes nes:=nes(‘a’,’b’,’c’,’d’);
// where ‘nes’ is datatype
and ‘vnes’ is collection variable.
If vnes.limit is null
then
DOPL(‘vnes limit is limitness’);
Else
DOPL(‘vnes limit ‘||vnes.limit’);
End if;
DOPL(‘vnes count’||vnes.count);
DOPL(‘vnes first’||vnes.first);
DOPL(‘vnes last’||vnes.last);
DOPL(‘vnes prior’||vnes.prior(2));
DOPL(‘vnes next’||vnes.next(2));
DOPL(‘vnes values’);
For I in vnes.first..vnes.last loop
DOPL(‘vnes values(‘||I||’)’||vnes(I));
End loop;
if vnes.exists(2) then
dopl(‘vnes second value exists’||vnes(2));
dopl(‘vnes second value doesnot exists’);
end if;
vnes.extend;
vnes.exted(2);
DOPL(‘vnes value after extension’);
For I in vnes.first..vnes.last loop
DOPL(‘vnes values(‘||I||’)’||vnes(I));
End loop;
Vnes(5):=’E’;
Vnes(6):=’F’;
Vnes(7):=’G’;
Vnes.extend(2,3);
DOPL(‘nes value after providing
element’);
For I in vnes.first..vnes.last loop
DOPL(‘vnes values(‘||I||’)|| vnes(I));
End loop;
vnes.trim;
vnes.trim(2);
DOPL(‘vnes values after trim’);
For I in vnes.first..vnes.last loop
DOPL(‘vnes values(‘||I||’)’||vnes(I));
End loop;
vnes.delete(1);
vnes.delete(2,5);
DOPL(‘vnes values after specific
deletion’);
For I in vnes.first..vnes.last loop
DOPL(‘vnes values(‘||I||’)’||vnes(I));
End loop;
Vnes.delete;
DOPL(‘vnes values after complete
deletion’);
End;
collection variable should not be ‘NULL’,
to avoid that in that case we will define elements.
The function should be empty
nes()(or)nes(‘A’,’B’,’C”,’D’);
Usage of collections:-
Loop
Deff: To store the data in
the form of arrays we use collections
Collections basically
are of two types:
1. non-persistance (index by table) (temporary)
2. persistance (varrays, nested
table) (permanent)
·
Collections are useful to store complex/homogenous data.
·
Collections defined in packages will persist for an entire
session.
·
Limit (is there any limit (or) not (upper boundary)) Extend(2,3);
Where 2
represents extensions will happen. And what ever the value is present in 3
element it will return that. After time only we will delete, reverse is not so
we delete at that time empty cell will populate.
Densely packed (with out any cops):
Sparse:
1
|
A
|
3
|
C
|
5
|
C
|
Densely:
1
|
A
|
2
|
B
|
3
|
C
|
4
|
C
|
5
|
D
|
·
Extend delete(n) (or) (m,n)
o Here we can
mention in three ways without paranthes is defining with number(n).
·
In index by table we can key values as var/char also.
·
Key values should start with 1 and always positive values.
·
Varray always densely.
Eg:
Declare
Type nes is table of
varchar2(10) index by pls_integer;
Vnes nes:=nes();
Begin
Null;
End;
Error: No function name ‘nes’
exists in this scope.
Bulk Bind:-
·
Concept of bulk bind is a mechanism which is used to enhance the performance drastically, by decreasing the context switches.
·
Bulk bind reduces the interaction between SQL and PL/SQL engine.
·
We will maintain the bulk bind data through collections.
These are Two Types:
Bulk Collect:- (clause)
·
select col bulk collect into variable (this variable must be collection
variable).
·
Fetch cursor bulk collect into variable.
·
Return col bulk collect into variable (we can call out bind
also).
·
We can use bulk collect only above three other than this we
can’t.
For all statement(DML):
syntax:
for all: in{
var1..var2|value of collection|indecies by collection}
[save
exception](10g)DML operation;
· In for all statement we can perform single DML operation
only.
·
By using bind we can perform.
·
In the presence of bulk bind ‘n’ no. of context switches will get
compressed (or) bineoled into one single swich.
Using bulk collect in select statement:-
Declare
Sdate number:=0;
Edate number:=0;
Type nes is table of pls_integer;
Vnes nes:=nes();
Begin
Sdate:=dbms_utility.get_times;
For I in (select * from multab) loop
Vnes.extend; (if you don’t write extend it throws an error)
Vnes(vnes.last)=i.sno;
Enol loop;
Edate:=dbms_utility.get_time;
DOPL(edate-sdate);
Sdate:=dbms_utility.get_time;
Select sno bulk collect into vnes from multabs;
Edate:=dbms_utility.get_time;
DOPL(edate-sdate);
End;
·
While using bulk collect the variable should be collection
variable.
Using bulk collect in fetch statement:-
Declare
Sdate number:=0;
Edate number:=0;
Type nes is table of pls_integer;
Vnes nes:=nes( );
Cursor c is select * from multab;
Begin
Open c;
Sdate:=dbms_utility.get_time;
Vnes.extend;
Fetch c into vnes(vnes.last);
Exit when c%not found;
End loop;
Close c;
Edate:=dbms_utility.get_time;
DOPL(edate-sdate);
Open c;
Sdate:=dbms_utility.get_time;
Fetch c bulk collect into vnes;
Edate:=dbms_utility.get_time;
Close c;
DOPL(edate-sdate);
End;
For the
same program
Fetch c
bulk collect into vnes limit 500000;
We can also mention
the limit so to specify the no.of records into a collection through fetch
statement limit only possible in fetch statement.
Collections:-
Note:- We won’t initialize the ‘index by table’ datatype variable with
a constructor (or) function trying to do so through an error.
Eg:-
Declare
Type nes is table of
varchar2(10);
Vnes
nes:=nes(‘a’,’b’,’c’);
Begin
Vnes.delete(2)
DOPL(‘vnes first’||vnes.first);
DOPL(‘vnes
last’||vnes.last);
DOPL(‘vnes
prior’||vnes.prior(3));
DOPL(‘vnes
next’||vnes.next(1));
DOPL(‘vnes
count’||vnes.count);
If vnes.exists(2) then
DOPL(‘vnes second
value exists’||vnes(2)); // not existed
End if;
Vnes.extend;
If vnes.existts(4)
then
DOPL(‘vnes fourth
value exists’||vnes(4));
Else
DOPL(‘vnes fourth does
not exists’);
End if;
End;
Using collections as program parameters:-
Declare
Type nes is table of
varchar2(10);
Vnes
nes:=nes(‘a’,’b’,’c’,’d’);
Procedure p(x nes) is
Begin
For I in
x.first..x.last loop
DOPL(x(i));
End loop;
End p;
Begin
p(vnes);
end;
using collections for
return datatype:-
declare
type nes is table of
varchar2(10);
vnes:=nes(‘a’,’b’,’c’,’d’);
Y nes:=nes();
Function fun(x nes)
return nes is
Begin
Return x; //here we are returning also
End fun;
Begin
Y:=fun(vnes);
For I in
Y.first..Y.last loop
DOPL(Y(i));
End loop;
End;
Note:- From 11g onwards for
all statement allows merge command
Declare
Type nes is table of
number;
Vnes nes:=nes( );
Sdate number:=0;
Edate number:=0;
Rdate number:=0;
Begin
Select sno bulk
collect into vnes from multab;
Execute immediate
‘truncate table multab’;
Sdate:=dbms_utility.get._time;
For iin
vnes.first..vnes.last loop
Insert into multab values(vnes(i));
End loop;
Edate:=dbms_utility.get_time;
Rdate:=edate_sdate;
DOPL(‘elapsed time
‘||rdate);
Execute immediate
‘truncate table multab;
Sdate:=dbms_utility.get_times;
For all i in
vnes.first..vnes.last
Insert into multab
values(vnes(i));
Edate:=dbms_utility.get_time;
Rdate:=edate-sdate;
DOPL(‘elapsed time
‘||rdate);
End;
Output:-
Elapsed time 198
5
Up to here we never
used DDL commands in executable section.
Another eg for all statement:-
Declare
Type nes is table of
number;
Vnes nes:=nes( );
Vnes1 nes:=nes( );
Begin
Select sno bulk
collect into vnes from multab;
For all I in
vnes.first..vnes.last
Delete from multab
where sno:=vnes(i);
Return sno bulk
collect into vnes1;
DOPL(vnes.count);
End;
Using save exception (10g):-
·
It filters outs the error records and allows the other records
to process, instead of cancelling all the records.
·
Save exception saves the errors and we can retrieve the error by
using ‘%bulk exception.’
Declare
Type nes is table of
varchar2(10);
Vnes nes:=(‘x’,’a’,’y’,’b’,’z’);
Begin
For all I in vnes.first..vnes.last
Save exception
Insert into contab values(values (i));
Exception
When others then
For I in sql%bulk_exceptions.count loop
DOPL(sql%bulk_exceptions(i).error_index||’
‘||sql%bulk_exceptions(i).error_code);
End loop;
End;
·
Error-code
displays the error code without ‘-sign’
Indeces (index/subscript) of values of:-
Eg for “values of”
clause:-
·
Here we are comparing one collection values with another
collection of indices values.
Declare
Type nes is table of varchar2(10);
Vnes nes:=nes(‘a’,’b’,’c’,’d’,’e’,’f’);
Type nes1 is table of pls_integer;
Vnes1 nes1:=nes1(1,3,5);
Begin
For all I in vnes values of vnes1
Insert into contab values of vnes(i);
End;
Output:
Loc
|
A
C
C
|
Eg for “indecies of”
clause:-
Declare
Type nes is table of varchar2(10);
Vnes nes:=nes(‘a’,’b’,’c’,’d’,’e’,’f’);
Vnes1 nes1:=nes1(1,3,5);
Begin
For all I in vnes indices of vnes1
Insert into contab values(vnes(i));
End;
Output:-
Loc
|
A
B
C
|
Table function:-
·
It is a function which acts as same to that of table to SQL
engine with the help of table operator called ‘table’.
·
We provide table function in from clause of a select statement.
·
SQL engine can’t identify it as a function.
Note:- Table functions have to always return data from collections
only.
·
But we can’t provide functions in from clause.
Eg:-
Create or replace
function colfun return nes is
Vnes
nes:=nes(‘a’,’b’,’c’,’d’,’e’,’f’)
Begin
Return vnes; // here vnes always collection.
End;
Select * from
table(col_fun);
Column_val // it internally
pseudo column generates, introduced from 10g
A
B
C
D
E
F
·
Table function data will be displayed under a column name called
column_val which is a pseudo column.
Dynamic SQl and Dynamic
PL /SQL:-
·
Dynamic SQL statements are the statements which are constructed
(or) get framed (or) build up at the time of execution.
·
When an SQL statement get framed before of compilation time
those are called ‘static SQL’.
·
Creation of PL/SQL program at run time is called as “dynamic
PL/SQL”.
·
To execute dynamic SQL and PL/SQL we use
1.
Execute immediate (statement).
2. DBMS_SQL (packages)
Note:- Dynamic SQL statement falls pray, to SQL injections. We can also
execute immediate to execute DDL statements in the program which is normally
not possible.
Syntax for execute immediate:-
Execute immediate
‘string’ [into {var1,var2..}]
[using[in/out/inout]]
bind_variable,..];
Eg for execute immediate:-
Declare
V_create
varchar2(100):= ‘create table etab(sno number);
V number(5):=10;
Vsno number(5);
Begin
Execute immediate
v_create;
Execute immediate
‘insert into etab values(:1)’ usingv;
Execute immediate
‘select sno from etab where sno:=2’ into vsno using v;
Execute immediate
‘begin null; end;’;
Execute immediate
‘drop table etab;
DOPL(vsno);
End;
·
At run time if you want to drop the database object, we prefer
following program.
SQL> create or
replace procedure p(X varchar2) is
Begin
Execute immediate
‘drop table’||x;
End p;
> exec p(‘stab’);
·
The above program is sensitive for SQL injection but by using
bind arguments we’ll safeguard the above program.
Using bind arguments will also enhance the performance:-
Create or replace
procedure p(X varchar2) is
V varchar2(10);’begin
V:=x;
Execute immediate
‘DROP table :a’ using v;
End p;
No comments:
Post a Comment