Brief Introduction of Oracle Collections and Bulk Collect.

Collection and Bulk  Collect

A collection is same as an array in other programing language like,c,c++,java ect. Its an ordered group of elements of a particular types. It can hold simple as well as complex data types. DOWNLOAD

Name	No of Elements	Subscript type	Dense/Sparse	Where to create
Associative array or index by table or plsql table	Unbounded/No limit	String or integer	Either	Only in plsql
Nested table	Unbounded/No limit	Integer only	Starts Dense can become sparse	Plsql and schema level (sql)
Variable Array or Varray	Bounded/ limit	Integer only	Always Dense	Plsql and schema level (sql)

Index by table:-

1)       TYPE <TYPENAME> IS TABLE OF <DATA TYPE> INDEX BY VARCHAR2(20);

2)      Appropriate to use for relatively smaller collective values in which the collection can be initialized and used within the same subprograms.

3)      No need to initialize

4)      Bulk collect cannot be used.

5)      Can have negative subscript

 Nested Table:-

1)      TYPE <TYPENAME> IS TABLE OF <DATA TYPE>;

Since the upper size limit is not fixed, memory needs to be extended each time before we use .

2)      Use EXTEND Method

3)      4) Need to be initialized.

Varray:-

1)      TYPE <TYPENAME> IS VARRAY(<SIZE>) OF  <DATA TYPE>;

2)      Appropriates to use when the array size is known and to perform similar activities on all the array elements.

3)      Need to be initialized.

Bulk collect

Reduce loop overhead for DML statement and queries

Bulk Collect:- Select Statements that retrieve multiple rows with a single fetch, improves the data retrieval speed. (Avoid Context Switch).

FORALL:- Insert, Update, Delete collection to change multiple rows of data very quickly.

Limit:- %BULK_ROWCOUNT(n):- It Returns the number of rows affected in the nth DML statement of the FORALL statement.

%BULK_EXCEPTIONS(i).ERROR_INDEX

%BULK_EXCEPTIONS(i).ERROR_CODE

%BULK_EXCEPTIONS(i).COUNT

Returning Clause:- It specified the values return from delete,execute immediate,insert,update statements. We can retrieve the column values into individual variable or into collections

Example:- Returning id,description BULK COLLECT into l_tab;

Index by Table Example:-

DECLARE
TYPE ABC IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
TYPE  PQR IS TABLE OF NUMBER INDEX BY VARCHAR2(20);
TYPE KLM IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
V1 ABC;
V2 PQR;
V3 KLM;
BEGIN
V1(1):=1;
V1(10):=23;
V2('A'):=10;
V2('M'):=12;
V3(18):='NEW YORK';
V3(-83):='LONDON';
END;
/

--Methods in Index by Table


DECLARE
TYPE ABC IS TABLE OF DATE INDEX BY PLS_INTEGER;
V ABC;
BEGIN
      FOR I IN 1..10 LOOP
V(I):=SYSDATE+I;
   DBMS_OUTPUT.PUT_LINE('DATE VALUE FOR V('||I||')='||V(I));
END LOOP;
IF(V.EXISTS(11)) THEN
DBMS_OUTPUT.PUT_LINE('ELEMENT EXISTS');
ELSE
DBMS_OUTPUT.PUT_LINE('ELEMENT DOES NOT EXISTS');
END IF;
DBMS_OUTPUT.PUT_LINE('TOTAL NUMBER OF ELEMENTS '||V.COUNT);
V.DELETE(7);
DBMS_OUTPUT.PUT_LINE('TOTAL NUMBER OF ELEMENTS AFTER DELETE '||V.COUNT);
V.DELETE(8,10);
DBMS_OUTPUT.PUT_LINE('TOTAL NUMBER OF ELEMENTS AFTER DELETE '||V.COUNT);
--SHOWING FIRST AND LAST METHOD
FOR I IN V.FIRST..V.LAST LOOP
DBMS_OUTPUT.PUT_LINE(V(I));
END LOOP;
DBMS_OUTPUT.PUT_LINE('THE NEXT INDEX FOR 4 IS '||V.NEXT(4));
DBMS_OUTPUT.PUT_LINE('THE PRIOR INDEX FOR  4 IS '||V.PRIOR(4));
END;
/

--Varchar2 as Index

DECLARE
TYPE ABC IS TABLE OF VARCHAR2(20) INDEX BY VARCHAR2(1);
V ABC;
X VARCHAR2(1);
BEGIN
V('A'):='APPLE';
V('B'):='BOY';
V('C'):='CAT';
V('D'):='DOG';
X:='A';
LOOP
DBMS_OUTPUT.PUT_LINE(V(X));
X:=V.NEXT(X);
EXIT WHEN X IS NULL;
END LOOP;
END;
/

--Bulk Collect Example

DECLARE
TYPE ABC IS TABLE OF EMP%ROWTYPE INDEX BY PLS_INTEGER;
V ABC;
X NUMBER;
BEGIN
SELECT * BULK COLLECT INTO V FROM EMP;
FOR I IN 1..V.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(V(I).ENAME);
END LOOP;
END;
/
Nested Table Example:-

--Table of Numbers

DECLARE
TYPE ABC IS TABLE OF NUMBER;
V ABC;
CURSOR C1 IS SELECT  EMPNO FROM EMP;
V_EMP ABC;
COUNTER NUMBER:=1;
BEGIN
V_EMP:=ABC();
FOR REC IN C1 LOOP
V_EMP.EXTEND();
V_EMP(COUNTER):=REC.EMPNO;
DBMS_OUTPUT.PUT_LINE('EMPLOYEE NUMBER VALUE AT '||'V_EMP('||COUNTER||')='||V_EMP(COUNTER));
COUNTER:=COUNTER+1;
END LOOP;
END;
/
--Table of Varchar2 Data type

DECLARE
TYPE ABC IS TABLE OF NUMBER;
TYPE PQR IS TABLE OF VARCHAR2(20);
V1 ABC;
V2 PQR;
BEGIN
V1:=ABC(10,20,30,40);
V2:=PQR('A','B','C','D','E');
FOR I IN 1..V1.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('THE VALUE AT INDEX '||I||' IS '||V1(I));
END LOOP;
FOR I IN V2.FIRST..V2.LAST LOOP
DBMS_OUTPUT.PUT_LINE('THE VALUE AT INDEX '||I||' IS '||V2(I));
END LOOP;
END;
/

Example of Varray:-

declare
type abc is varray(10) of number;
v abc:=abc(1,2,3,4,5,6,7,8,9,10);
begin
for i in 1..10
loop
dbms_output.put_line(v(i));
end loop;
end;
/

--Example with cursor

declare
type abc is varray(10) of number;
v1 abc;
cursor c1 is select empno from emp where rownum<11;
v2 abc;
counter number:=1;
begin
v1:=abc();
for i in 1..10
loop
v1.extend();
v1(i):=i*10;
dbms_output.put_line('Value of v1'||'('||i||')'||' is '||v1(i));
end loop;
v2:=abc();
for rec in c1
     loop
 v2.extend();
 v2(counter):=rec.empno;
dbms_output.put_line('Value of empno at v2'||'('||counter||')'||' is '||v2(counter));
counter:=counter+1;
end loop;
end;
/

--Example with Collection Method

DECLARE
TYPE ABC IS VARRAY(10) OF NUMBER;
V ABC:=ABC(1,2,3,4,5,6);
BEGIN
--Using collection methods
DBMS_OUTPUT.PUT_LINE('V.COUNT='||V.COUNT);
DBMS_OUTPUT.PUT_LINE('V.LIMIT='||V.LIMIT);
DBMS_OUTPUT.PUT_LINE('V.FIRST='||V.FIRST);
DBMS_OUTPUT.PUT_LINE('V.LAST='||V.LAST);
--Ttrimming last two elements
V.TRIM(2);
FOR I IN V.FIRST..V.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(V(I));
END LOOP;
DBMS_OUTPUT.PUT_LINE('V.LAST='||V.LAST);
END;
/