Oracle Collections Examples
--1) Simple Nested Table
DECLARE
TYPE TYP_NT_NUM IS TABLE OF NUMBER;
Nt_tab TYP_NT_NUM;
BEGIN
Nt_tab :=
TYP_NT_NUM (5,
10,
15,
20);
FOR i IN 1 .. nt_tab.COUNT
LOOP
DBMS_OUTPUT.put_line ('The Value Nested Table ' || nt_tab (i));
END LOOP;
END;
--2) Simple VARRAY
DECLARE
TYPE TYP_V_DAY IS VARRAY (7) OF VARCHAR2 (15);
v_tab TYP_V_DAY;
BEGIN
v_tab :=
TYP_V_DAY ('Sunday',
'Monday',
'Tuesday',
'Wedneday',
'Thursday',
'Friday',
'Saturday');
FOR i IN 1 .. v_tab.COUNT
LOOP
DBMS_OUTPUT.put_line ('The Value from VARRAY ' || v_tab (i));
END LOOP;
END;
--3) Simple PL/SQL Table
DECLARE
TYPE TYP_TAB IS TABLE OF NUMBER
INDEX BY PLS_INTEGER;
my_tab TYP_TAB;
BEGIN
my_tab (1) := 5;
my_tab (2) := 10;
my_tab (3) := 15;
FOR i IN 1 .. my_tab.COUNT
LOOP
DBMS_OUTPUT.put_line ('The Value from Index By Table ' || my_tab (i));
END LOOP;
END;
DECLARE
TYPE TYP_TAB IS TABLE OF NUMBER
INDEX BY PLS_INTEGER;
my_tab TYP_TAB;
i NUMBER := 1;
BEGIN
my_tab (1) := 5;
my_tab (2) := 10;
my_tab (3) := 15;
WHILE my_tab.EXISTS (i)
LOOP
DBMS_OUTPUT.put_line ('The Value from Index By Table ' || my_tab (i));
i := i + 1;
END LOOP;
END;
--Character Type PL/SQL Table
DECLARE
TYPE TYPE_TAB_DAYS IS TABLE OF PLS_INTEGER
INDEX BY VARCHAR2 (20);
day_tab TYPE_TAB_DAYS;
x_index VARCHAR2 (20);
p VARCHAR2 (20) := 'sdf';
BEGIN
day_tab ('Monday') := 10;
day_tab ('Tuesday') := 20;
day_tab ('Wednesday') := 30;
day_tab (p) := 40;
x_index := day_tab.FIRST;
LOOP
IF x_index IS NULL
THEN
EXIT;
END IF;
DBMS_OUTPUT.put_line (
'The Value from Index By Table ' || day_tab (x_index));
x_index := day_tab.NEXT (x_index);
END LOOP;
END;
--4) Check if the Collection is NULL
DECLARE
TYPE TYP_VAR_TAB IS VARRAY (30) OF VARCHAR2 (100);
tab1 TYP_VAR_TAB; -- declared but not initialized
BEGIN
IF Tab1 IS NULL
THEN
-- NULL collection, have to initialize it --
tab1 :=
TYP_VAR_TAB ('Sunday',
'Monday',
'Tuesday',
'Wedneday',
'Thursday',
'Friday',
'Saturday');
END IF; -- Now, we can handle the collection -
FOR i IN 1 .. tab1.COUNT
LOOP
DBMS_OUTPUT.put_line ('The Value from VARRAY ' || tab1 (i));
END LOOP;
END;
--5) We can assign values of one collection to the another collections if they are of the same type
DECLARE
TYPE TYPE_TAB_EMP IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
TYPE TYPE_TAB_EMP2 IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
tab1 TYPE_TAB_EMP;
tab2 TYPE_TAB_EMP;
tab3 TYPE_TAB_EMP2;
BEGIN
tab1 (1) := 5;
tab1 (2) := 10;
tab1 (3) := 15;
tab2 := tab1;
-- OK tab3 := tab1 ; -- Error : types not similar
FOR i IN 1 .. tab2.COUNT
LOOP
DBMS_OUTPUT.put_line ('The Value from Index By Table ' || tab2 (i));
END LOOP;
END;
--Collection Methods
--EXITS(n): Returns TRUE if the index element exists in the collection, else it returns FALSE.
DECLARE
TYPE TYP_V_DAY IS VARRAY (7) OF VARCHAR2 (15);
v_tab TYP_V_DAY;
BEGIN
v_tab :=
TYP_V_DAY ('Sunday',
'Monday',
'Tuesday',
'Wedneday',
'Thursday',
'Friday',
'Saturday');
IF v_tab.EXISTS (2)
THEN
DBMS_OUTPUT.put_line ('The Value from VARRAY ' || v_tab (2));
END IF;
END;
DECLARE
TYPE TYP_V_DAY IS VARRAY (7) OF VARCHAR2 (15);
v_tab TYP_V_DAY;
BEGIN
v_tab :=
TYP_V_DAY ('Sunday',
'Monday',
'Tuesday',
'Wedneday',
'Thursday',
'Friday',
'Saturday');
IF v_tab.EXISTS (8)
THEN
DBMS_OUTPUT.put_line ('The Value from VARRAY ' || v_tab (2));
ELSE
DBMS_OUTPUT.put_line ('No Element Found in the collections');
END IF;
END;
--COUNT: Returns the number of elements in a collection.
DECLARE
TYPE TYP_TAB IS TABLE OF NUMBER;
my_tab TYP_TAB
:= TYP_TAB (1,
2,
3,
4,
5);
BEGIN
DBMS_OUTPUT.Put_line ('COUNT = ' || TO_CHAR (my_tab.COUNT));
my_tab.DELETE (2);
DBMS_OUTPUT.Put_line ('COUNT = ' || TO_CHAR (my_tab.COUNT));
END;
--LIMIT : Returns the maximum number of elements that a varray can contain. Return NULL for Nested tables and Index-by table.
DECLARE
TYPE TYP_ARRAY IS ARRAY (30) OF NUMBER;
my_array TYP_ARRAY := TYP_ARRAY (1, 2, 3);
BEGIN
DBMS_OUTPUT.put_line ('Max array size is ' || my_array.LIMIT);
END;
--FIRST & LAST :
--Returns the first or last subscript of a collection. If the collection is empty, FIRST and LAST return NULL.
DECLARE
TYPE TYP_TAB IS TABLE OF NUMBER;
my_tab TYP_TAB
:= TYP_TAB (1,
2,
3,
4,
5);
BEGIN
FOR i IN my_tab.FIRST .. my_tab.LAST
LOOP
DBMS_OUTPUT.Put_line (
'my_tab(' || LTRIM (TO_CHAR (i)) || ') = ' || TO_CHAR (my_tab (i)));
END LOOP;
END;
DECLARE
TYPE TYP_TAB IS TABLE OF NUMBER;
my_tab TYP_TAB
:= TYP_TAB (1,
2,
3,
4,
5);
BEGIN
DBMS_OUTPUT.Put_line (my_tab.FIRST);
DBMS_OUTPUT.Put_line (my_tab.LAST);
FOR i IN my_tab.FIRST .. my_tab.LAST
LOOP
DBMS_OUTPUT.Put_line (
'my_tab(' || LTRIM (TO_CHAR (i)) || ') = ' || TO_CHAR (my_tab (i)));
END LOOP;
END;
DECLARE
TYPE TYP_TAB IS TABLE OF PLS_INTEGER
INDEX BY VARCHAR2 (1);
my_tab TYP_TAB;
BEGIN
FOR i IN 65 .. 69
LOOP
my_tab (CHR (i)) := i;
END LOOP;
DBMS_OUTPUT.Put_Line (
'First= ' || my_tab.FIRST || ' Last= ' || my_tab.LAST);
END;
--PRIOR(index) and NEXT(index) :
--Returns the previous or next subscript of the index element.
--If the index element has no predecessor, PRIOR (index) returns NULL. Likewise, if index has no successor, NEXT (index) returns NULL.
DECLARE
TYPE TYP_TAB IS TABLE OF PLS_INTEGER
INDEX BY VARCHAR2 (1);
my_tab TYP_TAB;
c VARCHAR2 (1);
BEGIN
FOR i IN 65 .. 69
LOOP
my_tab (CHR (i)) := i;
END LOOP;
c := my_tab.FIRST; -- first element
LOOP
DBMS_OUTPUT.Put_Line ('my_tab(' || c || ') = ' || my_tab (c));
c := my_tab.NEXT (c); -- get the successor element
EXIT WHEN c IS NULL; -- end of collection
END LOOP;
END;
--Use the PRIOR() or NEXT() method to be sure that you do not access an invalid element:
DECLARE
TYPE TYP_TAB IS TABLE OF PLS_INTEGER;
my_tab TYP_TAB
:= TYP_TAB (1,
2,
3,
4,
5);
BEGIN
my_tab.DELETE (2); --delete an element of the collection
FOR i IN my_tab.FIRST .. my_tab.LAST
LOOP
DBMS_OUTPUT.Put_Line (
'my_tab(' || LTRIM (TO_CHAR (i)) || ') = ' || my_tab (i));
END LOOP;
END;
--“NO DATA FOUND” Error
--In this example, we get an error because one element of the collection was deleted.
--One solution is to use the PRIOR()/NEXT() method:
DECLARE
TYPE TYP_TAB IS TABLE OF PLS_INTEGER;
my_tab TYP_TAB
:= TYP_TAB (1,
2,
3,
4,
5);
v PLS_INTEGER;
BEGIN
my_tab.DELETE (2);
v := my_tab.FIRST;
LOOP
DBMS_OUTPUT.Put_Line (
'my_tab(' || LTRIM (TO_CHAR (v)) || ') = ' || my_tab (v));
v := my_tab.NEXT (v); -- get the next valid subscript
EXIT WHEN v IS NULL;
END LOOP;
END;
--Another solution is to test if the index exists before use it:
DECLARE
TYPE TYP_TAB IS TABLE OF PLS_INTEGER;
my_tab TYP_TAB
:= TYP_TAB (1,
2,
3,
4,
5);
BEGIN
my_tab.DELETE (2);
FOR i IN my_tab.FIRST .. my_tab.LAST
LOOP
IF my_tab.EXISTS (i)
THEN
DBMS_OUTPUT.Put_Line (
'my_tab(' || LTRIM (TO_CHAR (i)) || ') = ' || my_tab (i));
END IF;
END LOOP;
END;
--EXTEND[(n[,i])] :
--Used to extend a collection (add new elements)
• EXTEND appends one null element to a collection.
• EXTEND(n) appends n null elements to a collection.
• EXTEND(n,i) appends n copies of the ith element to a collection.
DECLARE
TYPE typ_nes_tab IS TABLE OF VARCHAR2 (20);
tab1 typ_nes_tab;
i PLS_INTEGER;
PROCEDURE PRINT (i IN PLS_INTEGER)
IS
BEGIN
DBMS_OUTPUT.put_line ('tab1(' || LTRIM (TO_CHAR (i)) || ') = ' || tab1 (
i
));
END;
PROCEDURE printall
IS
BEGIN
DBMS_OUTPUT.put_line ('* Print all collection *');
FOR i IN tab1.FIRST .. tab1.LAST
LOOP
IF tab1.EXISTS (i)
THEN
DBMS_OUTPUT.put_line (
'tab1(' || LTRIM (TO_CHAR (i)) || ') = ' || tab1 (i)
);
END IF;
END LOOP;
END;
BEGIN
tab1 := typ_nes_tab ('One');
i := tab1.COUNT;
DBMS_OUTPUT.put_line ('tab1.COUNT = ' || i);
PRINT (i);
tab1.EXTEND;
i := tab1.COUNT;
tab1 (i) := 'Two';
printall;
--Add two Empty elements --
tab1.EXTEND (2);
i := i + 1;
tab1 (i) := 'Three';
i := i + 1;
tab1 (i) := 'Four';
printall;
--Add three elements with the same value as element 4 -- -
tab1.EXTEND (3, 1);
i := i + 3;
printall;
END;
--TRIM[(n)]
--Used to decrease the size of a collection
• TRIM removes one element from the end of a collection.
• TRIM(n) removes n elements from the end of a collection.
DECLARE
TYPE TYP_TAB IS TABLE OF VARCHAR2 (100);
tab TYP_TAB;
BEGIN
tab := TYP_TAB ('One', 'Two', 'Three');
FOR i IN tab.FIRST .. tab.LAST
LOOP
DBMS_OUTPUT.put_line (
'tab(' || LTRIM (TO_CHAR (i)) || ') = ' || tab (i));
END LOOP;
-- add 3 element with second element value --
DBMS_OUTPUT.put_line ('* add 3 elements *');
tab.EXTEND (3, 2);
FOR i IN tab.FIRST .. tab.LAST
LOOP
DBMS_OUTPUT.put_line (
'tab(' || LTRIM (TO_CHAR (i)) || ') = ' || tab (i));
END LOOP;
-- suppress the last element --
DBMS_OUTPUT.put_line ('* suppress the last element *');
tab.TRIM;
FOR i IN tab.FIRST .. tab.LAST
LOOP
DBMS_OUTPUT.put_line (
'tab(' || LTRIM (TO_CHAR (i)) || ') = ' || tab (i));
END LOOP;
END;
--If you try to suppress more elements than the collection contents, you get a SUBSCRIPT_BEYOND_COUNT exception.
DELETE[(n[,m])]
• DELETE removes all elements from a collection.
• DELETE(n) removes the nth element from an associative array with a numeric key or a nested table. If the associative array has astring key, the element corresponding to the key value is deleted. If n is null, DELETE(n) does nothing.
• DELETE(n,m) removes all elements in the range m..n from an associative array or nested table. If m is larger than n or if m or nis null, DELETE(n,m) does nothing
Caution : LAST returns the greatest subscript of a collection and COUNT returns the number of elements of a collection. If youdelete some elements, LAST != COUNT.
--Suppression of all the elements
DECLARE
TYPE TYP_TAB IS TABLE OF VARCHAR2 (100);
tab TYP_TAB;
BEGIN
tab := TYP_TAB ('One', 'Two', 'Three');
DBMS_OUTPUT.put_line ('Suppression of all elements');
tab.DELETE;
DBMS_OUTPUT.put_line ('tab.COUNT = ' || tab.COUNT);
END;
--Suppression of the second element
DECLARE
TYPE TYP_TAB IS TABLE OF VARCHAR2 (100);
tab TYP_TAB;
BEGIN
tab := TYP_TAB ('One', 'Two', 'Three');
DBMS_OUTPUT.put_line ('Suppression of the 2nd element');
tab.DELETE (2);
DBMS_OUTPUT.put_line ('tab.COUNT = ' || tab.COUNT);
DBMS_OUTPUT.put_line ('tab.LAST = ' || tab.LAST);
FOR i IN tab.FIRST .. tab.LAST
LOOP
IF tab.EXISTS (i)
THEN
DBMS_OUTPUT.put_line (tab (i));
END IF;
END LOOP;
END;
--Caution: For Varrays, you can suppress only the last element. If the element does not exists, no exception is raised.
--Main collection exceptions
DECLARE
TYPE NumList IS TABLE OF NUMBER;
nums NumList; -- atomically null
BEGIN
/* Assume execution continues despite the raised exceptions. */
nums (1) := 1; -- raises COLLECTION_IS_NULL (1)
nums := NumList (1, 2); -- initialize table
nums (NULL) := 3; -- raises VALUE_ERROR (2)
nums (0) := 3; -- raises SUBSCRIPT_OUTSIDE_LIMIT (3)
nums (3) := 3; -- raises SUBSCRIPT_BEYOND_COUNT (4)
nums.DELETE (1); -- delete element 1
IF nums (1) = 1
THEN ... -- raises NO_DATA_FOUND (5)
--Multi-level Collections
DECLARE
TYPE TYP_TAB IS TABLE OF NUMBER
INDEX BY PLS_INTEGER;
TYPE TYP_TAB_TAB IS TABLE OF TYP_TAB
INDEX BY PLS_INTEGER;
tab1 TYP_TAB_TAB;
BEGIN
FOR i IN 1 .. 3
LOOP
FOR j IN 1 .. 2
LOOP
tab1 (i) (j) := i + j;
DBMS_OUTPUT.put_line (
'tab1('
|| LTRIM (TO_CHAR (i))
|| ')('
|| LTRIM (TO_CHAR (j))
|| ') = '
|| tab1 (i) (j));
END LOOP;
END LOOP;
END;
--Collections of records
DECLARE
TYPE TYP_TAB IS TABLE OF DEPT%ROWTYPE
INDEX BY PLS_INTEGER;
tb_dept TYP_TAB;
rec DEPT%ROWTYPE;
CURSOR CDEPT
IS
SELECT * FROM DEPT;
BEGIN
OPEN CDEPT;
LOOP
FETCH CDEPT INTO rec;
EXIT WHEN CDEPT%NOTFOUND;
tb_dept (CDEPT%ROWCOUNT) := rec;
END LOOP;
FOR i IN tb_dept.FIRST .. tb_dept.LAST
LOOP
DBMS_OUTPUT.put_line (
tb_dept (i).DNAME || ' - ' || tb_dept (i).deptno || ' - ' || F||tb_dept(i).LOC );
End loop;
End;