Oracle Collections

COLLECTIONS: 

Oracle server supports following types
          1) PL/SQL Record (or) Record Type
          2) Index By Table  (or) PL/SQL Table (or) Associative Arrays
          3) Nested Tables
          4) V arrays
          5) Ref Cursors

 1) PL/SQL Record (or) Record Type:

•  Is is one of the user defined temporary data type which is used to store more than one  table data (or) to assign more than one column datatypes.
•  They must at least contain one element.
•  Pinpoint of data is not possible.

      Syntax : Type Typename is Record ( Val-1 Datatype, Val-2 Datatype,…..);
                     Var Typename
      Ex : 
            Declare
            Type Rec is record ( vname emp.ename%type,
                                             Vsal emp.sal%type,
                                             VLoc dept.loc%type);
            Vrec Rec;
            Vno emp.empno%type:=&n;
            Begin
                     Select ename,sal,loc into vrec from emp,dept where emp.deptno=dept.deptno and
                     emp.empno=vno;
                     Dbms_output.put_line(vrec.vname||’,’||vrec.vsal||’,’||vrec.vloc);
            End;

2) Index By Table  (or) PL/SQL Table (or) Associative Arrays:

• This is an user defined type which is used to store multiple data items in to a single unit.Basically this is an unconstrained table 
• Generally these tables are used to improve performance of applications because these tables are stored in memory area that's why these tables are also called as memory tables.
• Basically these table contains key value pairs i.e value field is stored in actual data and key field stored in indexes.
• Key field values are either integer or character and also these values are either -ve or +ve.
• These indexes key behaves like a primary key i.e does not accept duplicate and null values.basically this key datatype is binary_integer.
• Index by table having following collection methods.

                                   Exists
                                   first
                                   last
                                   prior
                                   next
                                   count
                                   delete(range of indexes)
Example1:
declare
type t1 is table of number(10)
index by binary_integer;
v_t t1;
begin
v_t(1):=10;
v_t(2):=20;
v_t(3):=30;
v_t(4):=40;
v_t(5):=50;
dbms_output.put_line(v_t(3));
dbms_output.put_line(v_t.first);
dbms_output.put_line(v_t.last);
dbms_output.put_line(v_t.prior(3));
dbms_output.put_line(v_t.next(4));
dbms_output.put_line(v_t.count);
dbms_output.put_line(v_t(5));
end;

Example2:
declare
type t1 is table of number(10)
index by binary_integer;
v_t t1;
begin
v_t(1):=10;
v_t(2):=20;
v_t(3):=30;
v_t(4):=40;
v_t(5):=50;
dbms_output.put_line(v_t.count);
v_t.delete(2,3);
dbms_output.put_line(v_t.count);
v_t.delete;
dbms_output.put_line(v_t.count);
end;

Question: Write a PLSQL program to get all employee names from emp table and store it into index by table and display data from index by table?

Program:
declare
type t1 is table of varchar2(10)
index by binary_integer;
v_t t1;
cursor c1 is select ename from emp;
n number(5):=1;
begin
open c1;
loop
fetch c1 into v_t(n);
exit when c1%notfound;
n:=n+1;
end loop;
close c1;
for i in v_t.first..v_t.last
loop
dbms_output.put_line(v_t(i));
end loop;
end;

Program:
declare
type t1 is table of varchar2(10)
index by binary_integer;
v_t t1;
begin
select ename bulk collect into v_t from emp;
for i in v_t.first..v_t.last
loop
dbms_output.put_line(v_t(i));
end loop;
end;

Program:
declare
type t1 is table of date
index by binary_integer;
v_t t1;
begin
for i in 1..10
loop
v_t(i):=sysdate+i;
end loop;
for i in v_t.first..v_t.last
loop
dbms_output.put_line(v_t(i));
end loop;
end;

Question: Write a PLSQL Program to retrieve all joining dates from emp table and store it into index by table and display content from index by table?

program:
declare
type t1 is table of date
index by binary_integer;
v_t t1;
begin
select hiredate bulk collect into v_t from emp;
for i in v_t.first..v_t.last
loop
dbms_output.put_line(v_t(i));
end loop;
end;

Example3:
declare
type t1 is table of varchar2(10)
index by varchar2(10);
v_t t1;
x varchar2(10);
begin
v_t('a'):= 'ARUN';
v_t('b'):= 'AJAY';
v_t('c'):= 'ABHI';
x :='a';
loop
dbms_output.put_line(v_t(x));
x := v_t.next(x);
exit when x is null;
end loop;
end;

Example4: 
declare
type t1 is table of emp%rowtype
index by binary_integer;
v_t t1;
x number(5);
begin
select * bulk collect into v_t from emp;
x:=1;
loop
dbms_output.put_line(v_t(x).empno||','||v_t(x).ename);
x:=v_t.next(x);
exit when x is null;
end loop;
end;

( OR )

Example5:
declare
type t1 is table of emp%rowtype
index by binary_integer;
v_t t1;
begin
select * bulk collect into v_t from emp;
for i in v_t.first..v_t.last
loop
dbms_output.put_line(v_t(i).empno||','||v_t(i).ename);
endloop;
end;

 3) Nested Tables:

• This is also user defined type which is used to store multiple data items in a single unit but        before we are storing actual data we must initialize the data while using constructor.            Here constructor name is same as type name. 
• Generally we are not allow to store index by tables permanently into database, to overcome this  problem they are introduce Nested Tables to extension of the index by tables.
• These user defined types stored permanently into database using sql.
• In Index by tables we can not add or remove the indexes. where as in Nested tables we can add or remove the indexes using Extend, Trim collection methods.
• In Nested tables we can allocate the memory explicitly while using Extend method.

     Syntax : Type type_name is Table of datatype( size );

                   variable_name type_name( ); => Constructor_Name

Example1: 

Declare

type t1 is table of number(10);

v t1:=t1();

begin

v.extend(100);

v(100):=10;

dbms_output.put_line(v(100));

end;

Example2:

Declare

type t1 is table of number(10);

v1 t1:=t1(10,20,30,40,50);

begin

dbms_output.put_line(v1.first);

dbms_output.put_line(v1.last);

dbms_output.put_line(v1.prior(3));

dbms_output.put_line(v1.next(3));

dbms_output.put_line(v1.count);

dbms_output.put_line(v1(3));

for i in v1.first..v1.last

loop

dbms_output.put_line(v1(i));

end loop;

end;

Example3: 

Declare

type t1 is table of number(10);

v1 t1;

v2 t1:=t1();

begin

if v1 is null then

dbms_output.put_line('v1 is null');

else

dbms_output.put_line('v1 is not null');

end if;

if v2 is null then

dbms_output.put_line('v2 is null');

else

dbms_output.put_line('v2 is not null');

end if;

end;

Example4:

declare

type t1 is table of number(10);

v t1:=t1();

begin

v.extend;

v(1):=5;

dbms_output.put_line(v(1));

end;

Question:Write a PLSQL program to get all employee names from emp table and store it into Nested Table and display data from Nested Table?

Program:

declare

type t1 is table of varchar2(10);

v t1:=t1();

cursor c1 is select ename from emp;

n number(10):=1;

begin

for i in c1

loop

v.extend();

v(n):=i.ename;

n:=n+1;

end loop;

for i in v.first..v.last

loop

dbms_output.put_line(v(i));

end loop;

end;

( OR )

declare

type t1 is table of varchar2(10);

v t1:=t1();

begin

select ename bulk collect into v from emp;

for i in v.first..v.last

loop

dbms_output.put_line(v(i));

end loop;

end;

( OR )  

declare

type t1 is table of emp%rowtype;

v t1:=t1();

begin

select * bulk collect into v from emp;

for i in v.first..v.last

loop

dbms_output.put_line(v(i).empno||','||v(i).ename||','||v(i).job);

end loop;

end;

4) V arrays:

         This is also user defined type which is used to store multiple data items in a single unit but                  before we are storing actual data we must initialize the data while using constructor.

         These user defined types stored permanently into database using sql.

         Basically we are using the Varrays for retrieving the huge data.

         Syntax : Type type_name is varray( maxsize ) of datatype( size );

                       Variable_name Type_name := Type_name( );

example1:

Declare

type t1 is varray(50) of emp%rowtype;

v t1:=t1();

begin

select * bulk collect into v from emp;

for i in v.first..v.last

loop

dbms_output.put_line(v(i).empno||','||v(i).ename||','||v(i).job);

end loop;

end;

5) Ref Cursors:

• Ref Cursors are user define types which is used to process multiple records and also this is record by record process
• Generally through the static cursors we are using only one select statement at a time for single active set area where as in ref cursors we are executing no of select statements dynamically for single active set area.
• That's why these type of cursors are also called as dynamic cursors.
• By using ref cursors we return large amount of data from oracle database into client applications.
• There are 2 Types > Strong Ref Cursor  > Weak Ref Cursor 

                 Strong Ref Cursor:It is one of the ref cursor which is having return type.
                Weak Ref Cursor:It is one of the ref cursor which does not have a return type.

        Note : In ref cursor we are executing select statements using open .... for statement.

example1:

Declare

Type t1 is ref cursor;

v_t t1;

i emp%rowtype;

begin

open v_t for select * from emp where sal>2000;

loop

fetch v_t into i;

exit when v_t%notfound;

dbms_output.put_line(i.ename||' '||i.sal);

end loop;

close v_t;

end;

example2:

declare

type t1 is ref cursor;

v_t t1;

i emp%rowtype;

j dept%rowtype;

v_no number(5):=&no;

begin

if v_no=1 then

open v_t for select * from emp;

loop

fetch v_t into i;

exit when v_t%notfound;

dbms_output.put_line(i.ename||' '||i.deptno);

end loop;

close v_t;

elsif v_no=2 then

open v_t for select * from dept;

loop

fetch v_t into j;

exit when v_t%notfound;

dbms_output.put_line(j.deptno||' '||j.dname);

end loop;

close v_t;

end if;

end;

example3:

Package Specification:

create or replace package pg1

is

type t1 is ref cursor return emp%rowtype;

type t2 is ref cursor return dept%rowtype;

procedure p1 (p_t1 out t1);

procedure p2 (p_t2 out t2);

end;

Package Body:

create or replace package body pg1 is

procedure p1 (p_t1 out t1)

is

begin

open p_t1 for select * from emp;

end p1;

procedure p2 (p_t2 out t2)

is

begin

open p_t2 for select * from dept;

end p2;

end;

Execution of Package:

variable a refcursor

variable b refcursor

exec pg1.p1(:a);

exec pg1.p2(:b);

print a b;

BULK MECHANISM:

• Bulk is one of the method which is used to improve the performance of the applications.
• Oracle introduce bulk bind process using collection i.e in this process we are putting all sql statement related values into collection and in this collection we are performing insert, update, delete at a time using for all statement.
• In this bulk we have two actions

1) Bulk Collect

2) Bulk Bind

1 ) Bulk Collect:

• In this clause we are used to fetch the data from resource into collection
• This clauses used in

          1) Bulk Collect used in Select ...........into............clause

          2) Bulk Collect used in Cursor...........Fetch...........Statement

          3) Bulk Collect used in Dml............Returning.........Clauses

          1) Bulk Collect used in select .....into .....clause:

              Syntax : select * bulk collect into collection_name from table_name.

              Example1: 

                              Declare

                              type t1 is table of emp%rowtype

                              index by binary_integer;

                              v t1;

                              begin

                              select * bulk collect into v from emp;

                              for i in v.first..v.last loop

                              dbms_output.put_line(v(i).empno||','||v(i).ename||','||v(i).job);

                              end loop;

                              end;

           2) Bulk Collect used in cursor......fetch.......statement

               Syntax : fetch cursor_name bulk collect into collection_variable.

               Example1:

                              Declare

                              type t1 is table of varchar2(10)

                              index by binary_integer;

                              v1 t1;

                              v2 t1;

                              cursor c1 is select ename,job from emp;

                              begin

                              open c1;

                              fetch c1 bulk collect into v1,v2;

                              close c1;

                              for i in v1.first..v1.last

                              loop

                              dbms_output.put_line(v1(i)||','||v2(i));

                              end loop;

                              end;

                Time Program with out BULK:

                              Declare

                              vrow varchar2(50);

                              cursor c1 is select object_name from all_objects;

                              z1 number(10);

                              z2 number(10);

                              begin

                              z1:=dbms_utility.get_time;

                              open c1;

                              loop

                              fetch c1 into vrow;

                              exit when c1%notfound;

                              end loop;

                              close c1;

                              z2:=dbms_utility.get_time;

                              dbms_output.put_line(z1);

                              dbms_output.put_line(z2);

                              dbms_output.put_line(z2-z1);

                              end;

                 Time Program with BULK:

                               Declare

                               type t1 is table of varchar2(50) index by binary_integer;

                               v1 t1;

                               cursor c1 is select object_name from all_objects;

                               z1 number(10);

                               z2 number(10);

                               begin

                               z1:=dbms_utility.get_time;

                               open c1;

                               loop

                               fetch c1 bulk collect into v1;

                               exit when c1%notfound;

                               end loop;

                               close c1;

                               z2:=dbms_utility.get_time;

                               dbms_output.put_line(z1);

                               dbms_output.put_line(z2);

                               dbms_output.put_line(z2-z1);

                               end;

             3) Bulk Collect used in DML..........Returning clauses.

                  Syntax : dml statement returning column_name into variable_name;

                  Example1:

                  Variable a varchar2(10);

                  Update emp set sal=sal+100 where ename ='KING' returning job into :a;

                  Print a;

               Question:Write a PLSQL Stored Procedure modify salaries of the clerk from emp table and                                  also these modified value immediately stored into index by table by using dml                                      ...returning clause and also display content from index by table?

               Program:

                             Create or replace procedure p1 is

                             type t1 is table of emp%rowtype

                             index by binary_integer;

                             v1 t1;

                             begin

                             update emp set sal=sal+100 where job='CLERK'

                             returning empno,ename,job,mgr,hiredate,sal,comm,deptno

                             bulk collect into v1;

                             dbms_output.put_line('updated no:of clerks are:'||sql%rowcount);

                             for i in v1.first..v1.last

                             loop

                            dbms_output.put_line(v1(i).ename||','||v1(i).job||','||v1(i).sal);

                            end loop;

                            end;

2 ) Bulk Bind:

• In bulk bind process we are performing bulk of operations using collection i.e in this process we are using bulk update, bulk delete, bulk insert using forall statement.
• Before we are using bulk bind process we are fetching data from database into collections using bulk collect clause.

          Syntax : forall indexvar in collectionvar.frist..collectionvar.last

          Example1:

                        Declare

                        type t1 is varray(10) of number(10);

                        v1 t1:=t1(10,20);

                        begin

                        forall i in v1.first..v1.last

                        update emp set sal=sal+100 where deptno=v1(i);

                        end;

      Bulk Update:

          Example1:

                       Declare

                       type t1 is table of number(5) index by binary_integer;

                      v1 t1;

                      begin

                      select empno bulk collect into v1 from emp;

                      forall i in v1.first..v1.last

                      update emp set sal=sal+111 where empno=v1(i);

                      end;

      Bulk Delete:

          Example1:

                       Declare

                       type t1 is varray(10) of number(10);

                       v1 t1:=t1(20,30,40);

                       begin

                       forall i in v1.first..v1.last

                       delete from emp where empno=v1(i);

                       end;

      Bulk Insert:

          Example1:

                       Declare

                       type t1 is table of number(10) index by binary_integer;

                       v1 t1;

                       begin

                       for i in 1..100

                       loop

                       v1(i):=i;

                      end loop;

                      forall i in v1.first..v1.last

                      insert into bt values (v1(i));

                      end;