Oracle PLSQL : Collections

A collection is an ordered group of elements, all of the same type. It is a general concept that encompasses lists, arrays, and other familiar datatypes. Each element has a unique subscript that determines its position in the collection.


PL/SQL offers these collection types:

Index-by tables, also known as associative arrays, let you look up elements using arbitrary numbers and strings for subscript values. (They are similar to hash tables in other programming languages.)

    TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY
    [BINARY_INTEGER PLS_INTEGER VARCHAR2(size_limit)];INDEX BY
    key_type;


 Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts. You can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.

     TYPE type_name IS TABLE OF element_type [NOT NULL];

Varrays (short for variable-size arrays) hold a fixed number of elements (although you can change the number of elements at runtime). They use sequential numbers as subscripts. You can define equivalent SQL types, allowing varrays to be stored in database tables. They can be stored and retrieved through SQL, but with less flexibility than nested tables


Following are the methods for use with collections. There are certain restrictions on the usage though.

• EXISTS
• COUNT
• LIMIT
• FIRST and LAST
• PRIOR and NEXT
• EXTEND
• TRIM
• DELETE

Examples:



TYPE EMP_NESTED_TABLE IS TABLE OF NUMBER;
empno emp_nested_table;
BEGIN
EMPNO := EMP_NESTED_TABLE(1, 2, 3, 4);
FOR I IN 1..4
LOOP
dbms_output.put_line('Emp No :'||empno(i));
end loop;
end;
/



declare
TYPE EMP_NESTED_TABLE IS TABLE OF NUMBER;
empno emp_nested_table;
BEGIN
EMPNO := EMP_NESTED_TABLE();
EMPNO.EXTEND(4);
EMPNO(1) := 11;
EMPNO(2) := 12;
EMPNO(3) := 13;
EMPNO(4) := 14;


FOR I IN 1..4
LOOP
dbms_output.put_line('Emp No :'||empno(i));
end loop;
END;
/


DECLARE
TYPE EMP_ROW_NEST_TABLE IS TABLE OF EMP%ROWTYPE;
EMPROW EMP_ROW_NEST_TABLE;
singlerow emp%rowtype;
BEGIN
SELECT * INTO singlerow
from emp where empno= 7369;


emprow := EMP_ROW_NEST_TABLE(singlerow);


FOR I IN 1..1
LOOP
IF EMPROW is not null THEN
DBMS_OUTPUT.PUT_LINE('Emp No: '||EMPROW(I).EMPNO||' Ename: '||EMPROW(I).ENAME);
end if;
end loop;


END;
/





DECLARE
TYPE V_ARRAY_NUMBER IS VARRAY(16) OF NUMBER;
VRAY V_ARRAY_NUMBER;
BEGIN


VRAY := V_ARRAY_NUMBER();
VRAY.EXTEND;
VRAY(1) := 23;
VRAY.EXTEND;
VRAY(2) := 34;
VRAY.EXTEND;
VRAY(3) := 14;
VRAY.EXTEND;
VRAY(4) := 4;
VRAY.EXTEND;
vray(5) := 1;
VRAY.EXTEND;
vray(6) := VRAY.count;


FOR I IN vray.first..vray.last
LOOP


DBMS_OUTPUT.PUT_LINE(VRAY(I));
end loop;
END;
/



--Reverse a String using varrays--

DECLARE

TYPE V_ARRAY_NUMBER IS VARRAY(15) OF char;

VRAY V_ARRAY_NUMBER;

STR VARCHAR2(20) := 'SAMPLE';

VRAY_REV V_ARRAY_NUMBER;

cnt number := 1;

BEGIN

VRAY := V_ARRAY_NUMBER();

VRAY.EXTEND(6);

--Store elements in varray

FOR I IN 1..6

LOOP

SELECT SUBSTR(str,I,1) into vray(i) from dual;

END LOOP;

DBMS_OUTPUT.PUT_LINE('Varray Count is :'||VRAY.COUNT);

FOR I IN VRAY.FIRST..VRAY.LAST

LOOP

if(vray.exists(i)) then

DBMS_OUTPUT.PUT_LINE(VRAY(I));

end if;

end loop;

--Reversing the string

VRAY_REV := V_ARRAY_NUMBER();

VRAY_REV.EXTEND(6);

FOR I IN REVERSE VRAY.FIRST..VRAY.LAST

LOOP

VRAY_REV(CNT) := VRAY(I);

cnt := cnt+1;

end loop;

--Print the reversed string

FOR I IN VRAY_REV.FIRST..VRAY_REV.LAST

LOOP

IF(VRAY_REV.EXISTS(I)) THEN

DBMS_OUTPUT.PUT_LINE(VRAY_rev(I));

END IF;

end loop;

END;
/
--Alternatively you can reverse the string using reverse() function in your sql


DECLARE

TYPE V_ARRAY_NUMBER IS VARRAY(16) OF NUMBER;
VRAY V_ARRAY_NUMBER;

BEGIN

VRAY := V_ARRAY_NUMBER();
VRAY.EXTEND(6);
VRAY(1) := 23;
VRAY(2) := 34;
VRAY(3) := 14;
VRAY(4) := 4;
vray(5) := 1;
vray(6) := VRAY.count;


FOR I IN vray.first..vray.last
LOOP
DBMS_OUTPUT.PUT_LINE(VRAY(I));
end loop;
END;
/

declare


TYPE V_ASS_ARRAY IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
V_RAY V_ASS_ARRAY;

BEGIN
V_RAY(1) := 120;
v_ray(2) := 100;

FOR I IN V_RAY.FIRST..V_RAY.LAST
LOOP
  IF V_RAY.EXISTS(I) THEN
  DBMS_OUTPUT.PUT_LINE(V_RAY(I));
  END IF;
end loop;
END;
/


Bulk Binding

Bulk Binding is useful when you want to transfer all the elements of a collection in single operation. This helps in improving the performance of the queries since there is no context switch between sql and plsql engines.



DECLARE

TYPE num_tab IS TABLE OF NUMBER;
deptnums num_tab;


BEGIN
SELECT deptno BULK COLLECT INTO deptnums FROM DEPT;


FORALL i IN 1..deptnums.COUNT
INSERT INTO emp_dept_table SELECT empno, deptno FROM emp WHERE deptno = deptnums(i);


end;
/