41. COLLECTIONS

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

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;
Loop
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