11 Apr 2016

Collections in Oracle PL/SQL

Collections are one of the efficient implementations of persistent composite types in Oracle programming language. A collection is a group of elements which are similar in type and property. The tutorial covers the basics of collections, its types and collection methods.

Collections: Broad Classification

Collections can be abstractly categorized based on their ability to be stored physically as-
  • Persistent collection – The collections which are physically stored in database are known as Persistent Collections. They are created as schema objects and are valid user defined types.
  • Non persistent collection – The collections which are not stored in database are known as Non Persistent Collections. They are visible only within a PL/SQL block.

Collections: Behavioral Classification

Based on the behavior in a program, collections can exist in three types.
  • Associative Arrays alias Index By tables. They are non persistent form of collection and maintain the index-value structure. They have sequential numeric or non numeric subscript.
  • Nested Tables – Persistent form of collections, which hold index-value structure. Index is a hidden attribute and internally maintained by oracle.
  • VARRAY – Similar to Nested tables with the difference that the number of element is fixed in VARRAY.

Associative arrays

Associative arrays were introduced in Oracle 7 release, with the name PL/SQL tables. Due to their structure, they were recalled as Index by tables in Oracle 8 and above releases. Owing to its similarity with the conventional arrays, Oracle 10g called it as Associative array.
Associative arrays are simplest unit of ordered set of elements in a form of an array or list which can hold any number of elements. They are physically not stored in the database; instead they are declared within a PL/SQL block and are persistent within the scope of an anonymous block execution (in which they are declared).
As stated earlier, associative array exist in index-value structure. In an associative array, an element value can be located based on the cell index (also known as subscript). The index has to be unique and can be of number or string type.

Syntax [1]

TYPE [COLL NAME] IS TABLE OF [ELEMENT DATA TYPE] NOT NULL INDEX BY [INDEX DATA TYPE (BINARY_INTEGER | PLS_INTEGER | VARCHAR2)]
In the above syntax, RAW, LONG RAW, ROWID, and CHAR are the unsupported index data types.

Example [1]

The Associative array EX_MONTH_TAB holds the days in each month of the first half of the year. Note the string subscript and number value.
DECLARE
 TYPE EX_MONTH_TAB IS TABLE OF NUMBER INDEX BY VARCHAR2(10);
 L_ARRAY EX_MONTH_TAB;
BEGIN
 L_ARRAY('JAN') := 31 ;
 L_ARRAY('FEB') := 28 ;
 L_ARRAY('MAR') := 31 ;
 L_ARRAY('APR') := 30 ;
 L_ARRAY('MAY') := 31 ;
 L_ARRAY('JUN') := 30 ;
END;
/
 
PL/SQL PROCEDURE successfully completed.

Associative array usage notes:

  1. Associative array do not require initialization in the DECLARE section, but exists as an empty collection. This implies that associative array constant cannot exist.
  2. Being a non persistent composite type, associative array value cannot be modified using DML operation.
  3. Associative arrays can be passed as subprogram parameters or can be return type of a function.

Example [2]:

  1. The PL/SQL block below uses a local procedure P_ARRAY to print the associative array passed as input.
    DECLARE
   TYPE EX_MONTH_TAB IS TABLE OF NUMBER INDEX BY VARCHAR2(10);
   L_ARRAY EX_MONTH_TAB;
   PROCEDURE PRINT_ARRAY (P_ARRAY EX_MONTH_TAB) IS
I VARCHAR2(10);
   BEGIN
I := P_ARRAY.FIRST;
LOOP
DBMS_OUTPUT.PUT_LINE('Month '||I||' has '||P_ARRAY(I)||’ days’);
I := P_ARRAY.NEXT(I);
EXIT WHEN I IS NULL;
END LOOP;
   END;
BEGIN
 L_ARRAY('JAN') := 31 ;
 L_ARRAY('FEB') := 28 ;
 L_ARRAY('MAR') := 31 ;
 L_ARRAY('APR') := 30 ;
 L_ARRAY('MAY') := 31 ;
 L_ARRAY('JUN') := 30 ;
 PRINT_ARRAY (L_ARRAY);
END;
  2. Sort order of an associative array is based on NLS_SORT initialization parameter.
  3. If an associative array has to be passed to another remote database, keep note of NLS_SORT and NLS_COMP parameter values. If the values at target database are different from that of source, exceptions may occur.
  4. Associative array declared in Package specification behaves as Persistent collection.
  5. Associative array applications: Can be used as a small temporary data table, which can be reused every time the program is invoked.

Nested Tables

Nested tables are persistent form of collections which are physically stored in database (disk space consumption based on number of elements they hold). They are unbounded and distributed set of elements in key-value structure.
As per the Oracle internal architecture, a nested table type is stored as a table structure. If a column of nested table type is included in a table, oracle generates a separate relational table to hold the data and actual column referring to it. This system generated table is maintained by the server and it is named in ‘SYS_NCXXXXX$’ format. The actual table column is marked as a Virtual Column by Oracle.
During retrieval of a nested table data, the nested table rows are fetched in sequence of the subscript. Note that the subscript is not stored along with the nested table but Oracle implicitly maintains the index to identify each row in a nested table.

Syntax [2a]

In PL/SQL
DECLARE
TYPE type_name IS TABLE OF element_type [NOT NULL];
Element type can be a User defined type or primitive data type, but cannot be REF CURSOR.

Syntax [2b]

In SQL
CREATE [OR REPLACE] TYPE type_name IS TABLE OF element_type;
Element types cannot be any of BOOLEAN, LONG, LONG RAW, NATURAL, NATURALN, POSITIVE, POSITIVEN, REF CURSOR, SIGNTYPE, or STRING.

Example [3a]

The example below demonstrates the creation of a table with a column of nested table type.
SQL> CREATE TYPE EX_NEST_TAB AS TABLE OF NUMBER;
/
 
TYPE created.
Note the syntax to create the table with a nested table column. The table definition must specify an arbitrary name of separate storage table, which would be used by the server to hold column data.

Example [3b]

SQL> CREATE TABLE EX_COLL_TABLE 
     (COL1 NUMBER,
      COL2 EX_NEST_TAB)
 NESTED TABLE COL2 STORE AS NESTED_COL2;
 
TABLE created.
Amazingly, when the column was queried in USER_TAB_COLS view, it was discovered as a virtual column in the USER_TAB_COLS dictionary view. Oracle internally identifies a nested table collection type column as a virtual column.

Example [3c]

SQL> SELECT TABLE_NAME, COLUMN_NAME, VIRTUAL_COLUMN FROM USER_TAB_COLS WHERE TABLE_NAME=’EX_COLL_TABLE’;
 
TABLE_NAME                     COLUMN_NAME                    VIR
------------------------------ ------------------------------ ---
EX_COLL_TABLE                  COL1                           NO
EX_COLL_TABLE                  COL2                           YES
EX_COLL_TABLE                  SYS_NC0000200003$              NO
USER_NESTED_TABLES is another dictionary view which maintains the metadata of nested tables which are owned by the current user.
The additional storage table (NESTED_COL2 in this case) is also maintained in USER_TABLES dictionary view, with NESTED column value as YES.

Handling Nested tables: Initialization, DML operations, and Selection

Nested table in SQL as standalone type stored in database: In SQL, nested table is physically stored in the database. Later it can be used in an anonymous PL/SQL block or during table creation. We shall see the DML operations on nested tables and their Selection.
Insertion: INSERT statement can be prepared using the nested table collection type.

Example [4a]

SQL> INSERT INTO EX_COLL_TABLE VALUES (1, EX_NEST_TAB(100,200,300));
 
1 ROW inserted.
 
SQL> INSERT INTO EX_COLL_TABLE VALUES (11, EX_NEST_TAB(123,694,212,341));
 
1 ROW inserted.
 
SQL> INSERT INTO EX_COLL_TABLE VALUES (23, EX_NEST_TAB(42,20));
 
1 ROW inserted.
 
SQL> COMMIT;
 
Commit complete.
Selection: Conventional way of data selection from a table would get the data in complex and instance format.

Example [4b]

SQL> SELECT * FROM EX_COLL_TABLE;
 
COL1 COL2
---- ----
   1 EX_NEST_TAB(100,200,300)
  11 EX_NEST_TAB(123,694,212,341)
  23 EX_NEST_TAB(42,20)
To get the data in the readable format, TABLE function can be used. It brings the nested table column data in relational format, thereby making it easy inferable.

Example [4c]

SQL> SELECT T1.COL1 COL1, T2.* 
FROM EX_COLL_TABLE T1, TABLE(T1.COL2) T2;
 
COL1 COLUMN_VALUE
---- ------------
1      100 
1      200
   1       300
  11      123
  11      694
  11      212
  11      341
  23        42
  23        20
Update the collection attribute value

Example [4d]

The update statement below updates the collection column in the table. Alternatively, it can be done using a PL/SQL block too.
SQL> UPDATE EX_COLL_TABLE
SET COL2 = EX_NEST_TAB (93,81)
WHERE COL1 = 23;
 
1 ROW updated.
 
SQL> SELECT T1.COL1 COL1, T2.* 
FROM EX_COLL_TABLE T1, TABLE(T1.COL2) T2;
 
COL1 COLUMN_VALUE
---- ------------
1      100 
1      200
   1       300
  11      123
  11      694
  11      212
  11      341
  23        93
  23        81
As a subprogram parameter: Nested table column can be passed as a parameter too. Additionally, it can be the return type of a stored function.

Example [4e]

The procedure P_CHECK_ARRAY counts of even and odd numbers in the input array. The PL/SQL block below uses the above procedure to verify the objective.
SQL> CREATE OR REPLACE PROCEDURE P_CHECK_ARRAY (P_NEST EX_NEST_TAB)
IS
L_EVE NUMBER := 0;
L_ODD NUMBER := 0;
BEGIN
IF P_NEST.FIRST IS NULL THEN
   DBMS_OUTPUT.PUT_LINE ('Empty Collection');
ELSE
   FOR I IN 1..P_NEST.COUNT
   LOOP
 IF MOD(P_NEST(I),2) = 0 THEN
    L_EVE := L_EVE+1;
      ELSE   
       L_ODD := L_ODD+1;
  END IF;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE ('Array contains '|| L_EVE||' even numbers');
   DBMS_OUTPUT.PUT_LINE ('Array contains '|| L_ODD||' odd numbers');
END IF;
END;
/
 
PROCEDURE created.
 
SQL> DECLARE
        L_ARRAY EX_NEST_TAB := EX_NEST_TAB();
     BEGIN
      P_CHECK_ARRAY (L_ARRAY);
  DBMS_OUTPUT.PUT_LINE (‘----Populating array----’);
  L_ARRAY := EX_NEST_TAB (24,164,256,17,82,93,52);
  P_CHECK_ARRAY (L_ARRAY);
     END;
     /
 
Empty collection
----Populating array----
Array contains 5 even numbers
Array contains 2 odd numbers
 
PL/SQL PROCEDURE successfully completed.
Nested tables as PL/SQL construct: Initialization and usage: When defined in a PL/SQL block, nested table variable remains uninitialized and NULL collection. It is mandatory to initialize it to make it empty, a non null collection; else it raises ORA-06531 exception.

Example [5a]

A PL/SQL block declares initializes and prints two nested table collection arrays. Collection methods used in the blocks are discussed later in this tutorial.
DECLARE
   TYPE EX_NEST_TAB IS TABLE OF NUMBER;
   L_INI_ARRAY EX_NEST_TAB := EX_NEST_TAB();
   L_VAL_ARRAY EX_NEST_TAB := EX_NEST_TAB (1,2,34);
   N NUMBER := 0;
BEGIN
   WHILE (N <10) LOOP
L_INI_ARRAY.EXTEND;
L_VAL_ARRAY.EXTEND;
N := N+1;
L_INI_ARRAY (L_INI_ARRAY.LAST) := N;
L_VAL_ARRAY (L_VAL_ARRAY.LAST) := N;
   END LOOP;
 
   DBMS_OUTPUT.PUT_LINE('----Display L_INI_ARRAY----');
   FOR I IN 1..L_INI_ARRAY.COUNT
   LOOP
DBMS_OUTPUT.PUT_LINE('Cell: '||I||' holds element '||L_INI_ARRAY(I));
   END LOOP;
 
   DBMS_OUTPUT.PUT_LINE('----Display L_VAL_ARRAY----');
   FOR I IN 1..L_VAL_ARRAY.COUNT
   LOOP
DBMS_OUTPUT.PUT_LINE('Cell: '||I||' holds element '||L_VAL_ARRAY(I));
   END LOOP;
END;
/

Nested Tables Usage Notes

  1. Size of the nested table increases dynamically. It is capable of accommodating arbitrary number of elements
  2. In case of deletion of any element from a nested table, it is rendered sparse.
  3. Applications of Nested tables: They fit into almost major scenarios in real time applications. Mostly, when the number of elements is variable or not known, nested tables are the best choice.
{mospagebreak title=Oracle 11g Collections – VARRAY}

VARRAY

VARRAYs are enhanced version of nested tables, which were introduced in Oracle 8i. They contain limited and defined number of densely populated elements, whose index is sequential and numeric. The minimum bound of the index is 1, current bound is the total number of resident elements and maximum bound is the varray size. At any moment, current bound cannot exceed the maximum bound.
In terms of handling and usage, they are similar to nested tables in major situations. VARRAYs can exist as schema objects, referenced from PL/SQL blocks, and can be stored in database tables.
Unlike nested tables, which are always stored as separate storage table, varrays exist as single object in the same table. Only in exceptional cases when size of varray exceeds 4KB, it is relocated outside the table but in the same tablespace.

Syntax [3a]

In SQL
CREATE  [OR  REPLACE]  TYPE  type_name  IS   {VARRAY  |  VARYING  ARRAY}  (size_limit) OF  element_type

Syntax [3b]

In PL/SQL
DECLARE
TYPE  type_name  IS  {VARRAY  |  VARYING  ARRAY}  (size_limit) OF  element_type  [NOT  NULL];
Size_limit represents the maximum count of elements in the array. Being a persistent form of collection, varrays require initialization.
Element_type can be one of the data type available till Oracle 11g.

VARRAYS in PL/SQL

Example [6a]

VARRAYs can be locally declared in a PL/SQL block and can be referenced within the scope of the block.
DECLARE
   TYPE EX_VRR_TAB IS VARRAY(5) OF VARCHAR2(100);
   L_BATSMAN EX_VRR_TAB := EX_VRR_TAB();
   L_WICKET EX_VRR_TAB := EX_VRR_TAB();
   L_BOWLER EX_VRR_TAB := EX_VRR_TAB();
BEGIN
   L_BATSMAN := EX_VRR_TAB(‘Sachin’,’Sehwag’,’Gambhir’,’Kohli’,’Yuvraj’,);
   L_WICKET := EX_VRR_TAB(‘Dhoni’,);
   L_BOWLER := EX_VRR_TAB(‘Bhajji’,’Nehra’,’Zaheer’,’Praveen’,’Munaf’);
END;

VARRAYs in SQL

Example [6b]

The example below shows the creation of a VARRAY as schema object
SQL> CREATE OR REPLACE TYPE NUM_VARRAY AS VARRAY(10) OF INTEGER;
/
TYPE created.

Example [6c]

The VARRAY created in the schema can be referenced in a PL/SQL block
DECLARE
   L_INTEGER NUM_VARRAY := NUM_VARRAY();
BEGIN
   FOR I IN 1..5
   LOOP
 L_INTEGER.EXTEND;
      L_INTEGER(I) := i*2;
   END LOOP;
END;

Example [6d]

The VARRAY created can be used as a column type. The example demonstrates the Insert and Update process.
SQL> CREATE OR REPLACE TYPE SALES_TYPE AS OBJECT ( SALES_DATE DATE,
  SALES_QTY NUMBER);
 
TYPE created.
 
SQL> CREATE OR REPLACE TYPE SALES_VARRAY AS VARRAY(10) OF SALES_TYPE;
 
TYPE created.
 
SQL> CREATE TABLE ORDERS (ORD_ID NUMBER,
                          ITEM_CODE VARCHAR2(100),
                 ITEM_SALES SALES_VARRAY);
 
TABLE created.   
 
SQL> INSERT INTO ORDERS (SEQ_ORD.NEXTVAL,
    ‘ELEC’,
     SALES_VARRAY(
        SALES_TYPE(SYSDATE,130),
        SALES_TYPE(SYSDATE-5,57),
        SALES_TYPE(SYSDATE-10,130)
       )
    );
 
1 ROW inserted.
 
SQL>DECLARE
      L_SALES SALES_VARRAY ;
    BEGIN
       SELECT ITEM_SALES
       INTO L_SALES
       FROM ORDERS
       WHERE ORD_ID=100
       FOR UPDATE OF ITEM_SALES;
     L_SALES(2).SALES_QTY := 140 ; -- update the second element
       UPDATE ORDERS 
  SET ITEM_SALES = L_SALES 
  WHERE ORD_ID=100;
   END ;
   /
 
PL/SQL block successfully completed.
 
SQL> COMMIT;
 
Commit complete.

Collection methods

Oracle provides set of inbuilt methods, which can be used with a non null collection (except EXISTS), to yield some information and perform basic operations on the collection subscript and element. The list below shows the collections available and their applicability with the different collection types. If the collection is NULL, system raises COLLECTION_IS_NULL exception.
The collection information includes their null behavior of the collection, and element count. The basic utilities provided by Oracle are collection extension, deletion, and trimming.
All collection methods are compiled along with their behavior, in the below table.

Syntax [4]

collection_name.method_name[(parameters)]
These collection methods can be used as PL/SQL construct. Oracle does not support the collection methods in SQL.
Now, we shall study the usage of collection methods.

EXISTS

The method checks a collection for the NULL property. It returns FALSE for null collections while TRUE for initialized ones. It is the only collection method which is capable to work with NULL collections.
Note that if the input index value is out of range of the collection, system raises SUBSCRIPT_OUTSIDE_LIMIT exception.
Check the below illustration to demonstrate its use.

Example [7a]

IF EX_NEST_TAB.EXISTS (5) THEN
...
ELSE
…
END IF;

COUNT

The method counts the number of elements in a collection. Check the below illustration.
Check the below illustration to demonstrate the use of COUNT method.

Example [7b]

DECLARE
  TYPE T IS TABLE OF NUMBER;
  L T := T();
BEGIN
  FOR I IN 1..100
  LOOP
    L.EXTEND;
    L(L.LAST):=NULL;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('The Collection has '||to_char(L.COUNT)||' elements');
END;
/
 
The Collection has 100 elements
PL/SQL block successfully completed.

LIMIT

The method returns the maximum count of elements in a collection array. It can only be used with VARRAYs.
Note the difference between COUNT and LIMIT methods in the below example.

Example [7c]

DECLARE
  TYPE v IS VARRAY(10) OF NUMBER;
  M V := V();
BEGIN
  M := V(1,3,2,3,2,3);
  DBMS_OUTPUT.PUT_LINE('The Collection has '||to_char(M.COUNT)||' elements');
  DBMS_OUTPUT.PUT_LINE('The Collection can accommodate '||to_char(M.limit)||' elements');
END;
/
 
The Collection has 6 elements
The Collection can accommodate 10 elements
 
PL/SQL PROCEDURE successfully completed.

FIRST and LAST

The methods return the first and last subscript of a collection. For an uninitialized or empty collection, the method values are NULL. Check the below illustration to demonstrate its use of the methods.

Example [7d]

DECLARE
  TYPE T IS TABLE OF NUMBER;
  M T := T();
BEGIN
   DBMS_OUTPUT.PUT_LINE('FIRST and LAST subscripts for Empty collection are '||M.FIRST||' and '||M.LAST);
   M := T(272,4720,482,183,481,372,482,127,5943);
   DBMS_OUTPUT.PUT_LINE('FIRST and LAST subscripts for populated collection are '||M.FIRST||' and '||M.LAST);   
END;
/
 
FIRST AND LAST subscripts FOR Empty collection are  AND
FIRST AND LAST subscripts FOR populated collection are 1 AND 9
 
PL/SQL PROCEDURE successfully completed.

PRIOR and NEXT

The method give the subscript which is before and after the current subscript. These methods are used as to avoid NO_DATA_FOUND exception in sparse collections.

Example [7e]

DECLARE
TYPE T IS TABLE OF NUMBER;
M T := T(12,15,73,1,87,22,89);
   BEGIN
DBMS_OUTPUT.PUT_LINE('Subscript before the 5th element is:'||TO_CHAR(M.PRIOR(5)));
DBMS_OUTPUT.PUT_LINE('Subscript after the 5th element is:'||TO_CHAR(M.NEXT(5)));
   END;
   /
 
   Subscript BEFORE the 5th element IS:4
   Subscript after the 5th element IS:6
 
   PL/SQL PROCEDURE successfully completed.

EXTEND

The method allows addition of new elements to a persistent collection. By default, it appends one element to a collection. Upon specification, it can add N elements to a collection. EXTEND(m,n) make ‘m’ copies of nth element to a collection.
Note that is cannot be used for Associative arrays.

Example [7f]

DECLARE
  TYPE T IS TABLE OF NUMBER;
  M T := T(12,15,73,1,87,22,89);
BEGIN
 DBMS_OUTPUT.PUT_LINE('No of elements in Collection:'|| TO_CHAR(M.COUNT));
 M.EXTEND;
 DBMS_OUTPUT.PUT_LINE('No of elements in Collection after first extension:'|| TO_CHAR(M.COUNT));
 M.EXTEND(5);
 DBMS_OUTPUT.PUT_LINE('No of elements in Collection after second extension:'|| TO_CHAR(M.COUNT));
 M.EXTEND(2,3);
 DBMS_OUTPUT.PUT_LINE('No of elements in Collection after third extension:'|| TO_CHAR(M.COUNT));
END;
/
 
No OF elements IN Collection:7
No OF elements IN Collection after FIRST extension:8
No OF elements IN Collection after SECOND extension:13
No OF elements IN Collection after third extension:15
 
PL/SQL PROCEDURE successfully completed.

TRIM

As the name suggests, the method trims a persistent collection. By default, it trims single element in a collection. It can also trim N elements, if the count is specified with the method/
Note that is cannot be used for Associative arrays.

Example [7g]

DECLARE
  TYPE T IS TABLE OF NUMBER;
  M T := T(12,15,73,1,87,22,89);
BEGIN
 DBMS_OUTPUT.PUT_LINE('Elements in Collection:'|| TO_CHAR(M.COUNT));
 M.TRIM;
 DBMS_OUTPUT.PUT_LINE('Elements in Collection after first trim:'|| TO_CHAR(M.COUNT));
 M.TRIM(5);
 DBMS_OUTPUT.PUT_LINE('Elements in Collection after second trim:'|| TO_CHAR(M.COUNT));
END;
/
 
Elements IN Collection:7
Elements IN Collection after FIRST TRIM:6
Elements IN Collection after SECOND TRIM:1
 
PL/SQL PROCEDURE successfully completed.

DELETE

The method drops a cell from a collection. It removes a single element by default, but can remove N elements and even by range, if specified with the method.

Example [7h]

DECLARE
  TYPE T IS TABLE OF NUMBER;
  M T := T(12,15,73,1,87,22,89);
BEGIN
   DBMS_OUTPUT.PUT_LINE ('Count of elements before deletion:'||TO_CHAR(M.COUNT));
   DBMS_OUTPUT.PUT_LINE ('Last index of elements before deletion:'||TO_CHAR(M.LAST));
   M.DELETE(2);
   DBMS_OUTPUT.PUT_LINE ('Count of elements after deletion:'||TO_CHAR(M.COUNT));
   DBMS_OUTPUT.PUT_LINE ('Last subscript of elements after deletion:'||TO_CHAR(M.LAST));
END;
/
 
COUNT OF elements BEFORE deletion:7
LAST INDEX OF elements BEFORE deletion:7
COUNT OF elements after deletion:6
LAST INDEX OF elements after deletion:7
 
PL/SQL PROCEDURE successfully completed.

Posted by
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)