The content of the article

• Pl / sql Collections Overview
• Types of collections
• Associative array
• Varray
• Nested table
• Set operations with nested tables
• Logical operations with collections
• Collection methods
  • Delete
  • Trim
  • Extend
  • Exists
  • First and Last
  • Count
  • Limit
  • Prior and Next
• Bulk collect
• Forall cycle
  
  • Exceptions in forall
• Collection exceptions
• DBMS_SESSION.FREE_UNUSED_USER_MEMORY

Pl / sql Collections Overview

• Creating a collection takes place in two stages.
  1. First we declare the type (type) of the collection (the constructions assoc_array_type_def, varray_type_def and nested_table_type_def will be given later)
     
     
  2. Then we declare a variable of this type.
• The call to the collection item has the following syntax:

  variable_name(index) 

• Variables of the collection type can be NULL (and the elements of the collections themselves, too).
• Multidimensional collections are possible (collections of collections)

Types of collections

Associative array

• Key-value pair set
• Data is stored in sorted by key order.
• Does not support DML operations (cannot participate in selects, cannot be stored in tables)
• When declared as a constant, it must be immediately initialized by the function.
• The order of the elements in an associative array with a string index depends on the NLS_SORT and NLS_COMP parameters.
• You cannot declare a type at the schema level, but you can in the package
• Does not have a constructor
• The index can not be null (but allows an empty string - details and a link to the example in the first comment)
• Datatype is any data type except ref cursor

• For keeping small reference tables in memory
• To pass as a collection parameter

Varray

• The size is set when creating
• Indexed from 1
• Initialized by the constructor.
  

   collection_type ( [ value [, value ]... ] ) 

• If no parameters are passed to the constructor, an empty collection is returned.
• Datatype is any data type except ref cursor

• We know the maximum possible number of elements.
• Access to elements sequential

Nested table

• Collection size changes dynamically
• It may be in a discharged state, as shown in the picture.
  <
• Initialized by the constructor.
  

   collection_type ( [ value [, value ]... ] ) 

• If no parameters are passed to the constructor, an empty collection is returned.
• Datatype is any data type except ref cursor
• If it contains only one scalar value, then the column name is Column_Value
  

   SELECT column_value FROM TABLE(nested_table) 

  
  In the comments to this article they suggest a more preferable option - it is more universal, it allows not only a scalar value in the table:
  

   SELECT value(t) x FROM TABLE(nested_table) t 

• If no parameters are passed to the constructor, an empty collection is returned.

Set operations with nested tables

 DECLARE TYPE nested_typ IS TABLE OF NUMBER; nt1 nested_typ := nested_typ(1,2,3); nt2 nested_typ := nested_typ(3,2,1); nt3 nested_typ := nested_typ(2,3,1,3); nt4 nested_typ := nested_typ(1,2,4); answer nested_typ; BEGIN answer := nt1 MULTISET UNION nt4; answer := nt1 MULTISET UNION nt3; answer := nt1 MULTISET UNION DISTINCT nt3; answer := nt2 MULTISET INTERSECT nt3; answer := nt2 MULTISET INTERSECT DISTINCT nt3; answer := SET(nt3); answer := nt3 MULTISET EXCEPT nt2; answer := nt3 MULTISET EXCEPT DISTINCT nt2; END; 

 nt1 MULTISET UNION nt4: 1 2 3 1 2 4 nt1 MULTISET UNION nt3: 1 2 3 2 3 1 3 nt1 MULTISET UNION DISTINCT nt3: 1 2 3 nt2 MULTISET INTERSECT nt3: 3 2 1 nt2 MULTISET INTERSECT DISTINCT nt3: 3 2 1 SET(nt3): 2 3 1 nt3 MULTISET EXCEPT nt2: 3 nt3 MULTISET EXCEPT DISTINCT nt2: empty set 

Logical operations with collections

 DECLARE TYPE nested_typ IS TABLE OF NUMBER; nt1 nested_typ := nested_typ(1, 2, 3); nt2 nested_typ := nested_typ(3, 2, 1); nt3 nested_typ := nested_typ(2, 3, 1, 3); nt4 nested_typ := nested_typ(); BEGIN IF nt1 = nt2 THEN DBMS_OUTPUT.PUT_LINE('nt1 = nt2'); END IF; IF (nt1 IN (nt2, nt3, nt4)) THEN DBMS_OUTPUT.PUT_LINE('nt1 IN (nt2,nt3,nt4)'); END IF; IF (nt1 SUBMULTISET OF nt3) THEN DBMS_OUTPUT.PUT_LINE('nt1 SUBMULTISET OF nt3'); END IF; IF (3 MEMBER OF nt3) THEN DBMS_OUTPUT.PUT_LINE('3 MEMBER OF nt3'); END IF; IF (nt3 IS NOT A SET) THEN DBMS_OUTPUT.PUT_LINE('nt3 IS NOT A SET'); END IF; IF (nt4 IS EMPTY) THEN DBMS_OUTPUT.PUT_LINE('nt4 IS EMPTY'); END IF; END; 

 nt1 = nt2 nt1 IN (nt2,nt3,nt4) nt1 SUBMULTISET OF nt3 3 MEMBER OF nt3 nt3 IS NOT A SET nt4 IS EMPTY 

Collection methods

 collection_name.method 

Delete

• Delete deletes all items. Immediately clears the memory allocated to store these items.
• Delete (n) deletes the element with index n. Memory does not free. The element can be restored (ie, set a new one) and it will occupy the same memory as the previous one.
• Delete (n, m) deletes items with indices in the interval n..m
• If there is no item to delete in the collection, it does nothing.
• For varray collections, only the version of the method is available without parameters.

Usage example

 DECLARE TYPE nt_type IS TABLE OF NUMBER; nt nt_type := nt_type(11, 22, 33, 44, 55, 66); BEGIN nt.DELETE(2); --    nt(2) := 2222; --  2-  nt.DELETE(2, 4); --    2-  4- nt(3) := 3333; --  3-  nt.DELETE; --    END; 

Results:

 beginning: 11 22 33 44 55 66 after delete(2): 11 33 44 55 66 after nt(2) := 2222: 11 2222 33 44 55 66 after delete(2, 4): 11 55 66 after nt(3) := 3333: 11 3333 55 66 after delete: empty set 

Trim

• Trim () - removes one item at the end of the collection. If the item does not exist, throws an exception SUBSCRIPT_BEYOND_COUNT
• Trim (n) - removes n elements at the end of the collection. If the items are less than n, an exception is thrown SUBSCRIPT_BEYOND_COUNT
• Works with the internal size of the collection. Those. if the last element was deleted using Delete, the Trim () call will delete the element that was previously deleted.
• Immediately clears the memory allocated for storing these items.
• It is better not to use in combination with Delete ()

 DECLARE TYPE nt_type IS TABLE OF NUMBER; nt nt_type := nt_type(11, 22, 33, 44, 55, 66); BEGIN nt.TRIM; -- Trim last element nt.DELETE(4); -- Delete fourth element nt.TRIM(2); -- Trim last two elements END; 

 beginning: 11 22 33 44 55 66 after TRIM: 11 22 33 44 55 after DELETE(4): 11 22 33 55 after TRIM(2): 11 22 33 

Extend

• EXTEND adds a single item with a null value to the end of the collection.
• EXTEND (n) adds n items with a null value to the end of the collection.
• EXTEND (n, i) adds n copies of the item with index i to the end of the collection. If the collection has a NOT NULL constraint, only this form can be used.
• If the elements were previously deleted using the Delete method, Extend will not use the memory cells stored in the collection, but will add a new element (will allocate a new memory)

 DECLARE TYPE nt_type IS TABLE OF NUMBER; nt nt_type := nt_type(11, 22, 33); BEGIN nt.EXTEND(2, 1); -- Append two copies of first element nt.DELETE(5); -- Delete fifth element nt.EXTEND; -- Append one null element END; 

 beginning: 11 22 33 after EXTEND(2,1): 11 22 33 11 11 after DELETE(5): 11 22 33 11 after EXTEND: 11 22 33 11 

Exists

• For deleted items, returns false.
• Returns false when going beyond the boundaries

 DECLARE TYPE NumList IS TABLE OF INTEGER; n NumList := NumList(1, 3, 5, 7); BEGIN n.DELETE(2); -- Delete second element FOR i IN 1 .. 6 LOOP IF n.EXISTS(i) THEN DBMS_OUTPUT.PUT_LINE('n('||i||') = ' || n(i)); ELSE DBMS_OUTPUT.PUT_LINE('n('||i||') does not exist'); END IF; END LOOP; END; 

First and Last

• For varray, First always returns one, Last always returns the same value as Count.

 DECLARE TYPE aa_type_str IS TABLE OF INTEGER INDEX BY VARCHAR2(10); aa_str aa_type_str; BEGIN aa_str('Z') := 26; aa_str('A') := 1; aa_str('K') := 11; aa_str('R') := 18; DBMS_OUTPUT.PUT_LINE('Before deletions:'); DBMS_OUTPUT.PUT_LINE('FIRST = ' || aa_str.FIRST); DBMS_OUTPUT.PUT_LINE('LAST = ' || aa_str.LAST); aa_str.DELETE('A'); aa_str.DELETE('Z'); DBMS_OUTPUT.PUT_LINE('After deletions:'); DBMS_OUTPUT.PUT_LINE('FIRST = ' || aa_str.FIRST); DBMS_OUTPUT.PUT_LINE('LAST = ' || aa_str.LAST); END; 

 Before deletions: FIRST = A LAST = Z After deletions: FIRST = K LAST = R 

Count

 DECLARE TYPE NumList IS VARRAY(10) OF INTEGER; n NumList := NumList(1, 3, 5, 7); BEGIN DBMS_OUTPUT.PUT('n.COUNT = ' || n.COUNT || ', '); DBMS_OUTPUT.PUT_LINE('n.LAST = ' || n.LAST); n.EXTEND(3); DBMS_OUTPUT.PUT('n.COUNT = ' || n.COUNT || ', '); DBMS_OUTPUT.PUT_LINE('n.LAST = ' || n.LAST); n.TRIM(5); DBMS_OUTPUT.PUT('n.COUNT = ' || n.COUNT || ', '); DBMS_OUTPUT.PUT_LINE('n.LAST = ' || n.LAST); END; 

 n.COUNT = 4, n.LAST = 4 n.COUNT = 7, n.LAST = 7 n.COUNT = 2, n.LAST = 2 

Limit

• For varray, returns the maximum allowed number of items in the collection, for other collections, returns null

 DECLARE TYPE aa_type IS TABLE OF INTEGER INDEX BY PLS_INTEGER; aa aa_type; -- associative array TYPE va_type IS VARRAY(4) OF INTEGER; va va_type := va_type(2, 4); -- varray TYPE nt_type IS TABLE OF INTEGER; nt nt_type := nt_type(1, 3, 5); -- nested table BEGIN aa(1) := 3; aa(2) := 6; aa(3) := 9; aa(4) := 12; DBMS_OUTPUT.PUT_LINE('aa.COUNT = ' || aa.count); DBMS_OUTPUT.PUT_LINE('aa.LIMIT = ' || aa.limit); DBMS_OUTPUT.PUT_LINE('va.COUNT = ' || va.count); DBMS_OUTPUT.PUT_LINE('va.LIMIT = ' || va.limit); DBMS_OUTPUT.PUT_LINE('nt.COUNT = ' || nt.count); DBMS_OUTPUT.PUT_LINE('nt.LIMIT = ' || nt.limit); END; 

 aa.COUNT = 4 aa.LIMIT = va.COUNT = 2 va.LIMIT = 4 nt.COUNT = 3 nt.LIMIT = 

Prior and Next

• Allow you to navigate the collection
• Return the index of the previous / next element (or null if there is no element)

 DECLARE TYPE nt_type IS TABLE OF NUMBER; nt nt_type := nt_type(18, NULL, 36, 45, 54, 63); BEGIN nt.DELETE(4); DBMS_OUTPUT.PUT_LINE('nt(4) was deleted.'); FOR i IN 1 .. 7 LOOP DBMS_OUTPUT.PUT('nt.PRIOR(' || i || ') = '); print(nt.PRIOR(i)); DBMS_OUTPUT.PUT('nt.NEXT(' || i || ') = '); print(nt.NEXT(i)); END LOOP; END; 

 nt(4) was deleted. nt.PRIOR(1) = nt.NEXT(1) = 2 nt.PRIOR(2) = 1 nt.NEXT(2) = 3 nt.PRIOR(3) = 2 nt.NEXT(3) = 5 nt.PRIOR(4) = 3 nt.NEXT(4) = 5 nt.PRIOR(5) = 3 nt.NEXT(5) = 6 nt.PRIOR(6) = 5 nt.NEXT(6) = nt.PRIOR(7) = 6 nt.NEXT(7) = 

Bulk collect

• Returns the results of an sql operator in PL / SQL in batches, not one by one
• SELECT BULK COLLECT INTO
• FETCH BULK COLLECT INTO [LIMIT]
• RETURNING BULK COLLECT INTO
• Does not work with associative arrays (except those indexed by pls_integer)

 DECLARE TYPE NumTab IS TABLE OF employees.employee_id%TYPE; TYPE NameTab IS TABLE OF employees.last_name%TYPE; CURSOR c1 IS SELECT employee_id,last_name FROM employees WHERE salary > 10000 ORDER BY last_name; enums NumTab; names NameTab; BEGIN SELECT employee_id, last_name BULK COLLECT INTO enums, names FROM employees ORDER BY employee_id; OPEN c1; LOOP FETCH c1 BULK COLLECT INTO enums, names LIMIT 10; EXIT WHEN names.COUNT = 0; do_something(); END LOOP; CLOSE c1; DELETE FROM emp_temp WHERE department_id = 30 RETURNING employee_id, last_name BULK COLLECT INTO enums, names; END; 

Forall cycle

• sends DML statements from PL / SQL to SQL in batches, rather than one by one
• may contain only one DML statement
• For discharged collections the following form is used:
  

   FORALL i IN INDICES OF cust_tab 

  (the construction does not work for associative arrays indexed by strings)
• it is convenient to work with sparse collections (or with part of the collection) using index collections (of pls_integer). Usage example:
  

   FORALL i IN VALUES OF rejected_order_tab 

• Some details of the work of forall can be found in this article habrahabr.ru/post/125893

• SQL% BULK_ROWCOUNT - collection, contains the number of rows affected by each dml statement
• SQL% ROWCOUNT - the total number of rows affected by the dml statements in the forall loop

 DECLARE TYPE NumList IS TABLE OF NUMBER; depts NumList := NumList(10, 20, 30); TYPE enum_t IS TABLE OF employees.employee_id%TYPE; e_ids enum_t; TYPE dept_t IS TABLE OF employees.department_id%TYPE; d_ids dept_t; BEGIN FORALL j IN depts.FIRST .. depts.LAST DELETE FROM emp_temp WHERE department_id = depts(j) RETURNING employee_id, department_id BULK COLLECT INTO e_ids, d_ids; END; 

Exceptions in forall

• When an exception occurs in any of the dml statements in the loop, the transaction is completely rolled back.
• If you describe an error handler, you can fix successfully executed dml statements in it (these are the statements that were executed before the exception occurred).
• Design

   FORALL j IN collection.FIRST.. collection.LAST SAVE EXCEPTIONS 

  
  ORA-24381 is generated at the end if exceptions occurred in the loop
• SQL% BULK_EXCEPTIONS - a collection containing information about all exceptions generated during the execution of a loop
  .Count
  .ERROR_INDEX - index value of j at which the exception occurred (sql% bulk_exception (i) .error_index)
  .ERROR_CODE - error code. Error information can be retrieved using the sqlerrm function: SQLERRM (- (SQL% BULK_EXCEPTIONS (i) .ERROR_CODE))

Collection exceptions

• COLLECTION_IS_NULL - an attempt to work with an uninitialized collection
• NO_DATA_FOUND - attempt to read deleted item
• SUBSCRIPT_BEYOND_COUNT - going beyond the boundaries of the collection
• SUBSCRIPT_OUTSIDE_LIMIT - index out of range
• VALUE_ERROR - the index is null or not converted to an integer

 DECLARE TYPE NumList IS TABLE OF NUMBER; nums NumList; BEGIN nums(1) := 1; -- raises COLLECTION_IS_NULL nums := NumList(1, 2); nums(NULL) := 3; -- raises VALUE_ERROR nums(0) := 3; -- raises SUBSCRIPT_BEYOND_COUNT nums(3) := 3; --raises SUBSCRIPT_OUTSIDE_LIMIT nums.Delete(1); IF nums(1) = 1 THEN ... -- raises NO_DATA_FOUND END; 

DBMS_SESSION.FREE_UNUSED_USER_MEMORY

• The DBMS_SESSION.FREE_UNUSED_USER_MEMORY procedure returns unused more memory to the system.
• Oracle documentation recommends using the procedure “rarely and wisely”.
• In case of connection in Dedicated Server mode, a call to this procedure returns unused PGA memory to the operating system.
• If connected in Shared Server mode, calling this procedure returns unused memory to the Shared Pool.

• Large sorts when the entire sort_area_size area is used
• Compiling large PL / SQL packages, procedures, or functions
• Storing large amounts of data in PL / SQL index tables

 CREATE PACKAGE foobar type number_idx_tbl is table of number indexed by binary_integer; store1_table number_idx_tbl; -- PL/SQL indexed table store2_table number_idx_tbl; -- PL/SQL indexed table store3_table number_idx_tbl; -- PL/SQL indexed table ... END; -- end of foobar DECLARE ... empty_table number_idx_tbl; -- uninitialized ("empty") version BEGIN FOR i in 1..1000000 loop store1_table(i) := i; -- load data END LOOP; ... store1_table := empty_table; -- "truncate" the indexed table ... - dbms_session.free_unused_user_memory; -- give memory back to system store1_table(1) := 100; -- index tables still declared; store2_table(2) := 200; -- but truncated. ... END; 

Other Oracle Articles