Collections in Oracle PL/SQL

Oracle uses collections in PL/SQL the same way other languages use arrays. Oracle provides three basic collections, each with an assortment of methods.

1. Index-By Tables (Associative Arrays)

The first type of collection is known as index-by tables. These behave in the same way as arrays except that have no upper bounds, allowing them to constantly extend. As the name implies, the collection is indexed using BINARY_INTEGER values, which do not need to be consecutive. The collection is extended by assigning values to an element using an index value that does not currently exist.

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  TYPE table_type IS TABLE OF NUMBER(10)
    INDEX BY BINARY_INTEGER;
  
  v_tab  table_type;
  v_idx  NUMBER;
BEGIN
  -- Initialise the collection.
  << load_loop >>
  FOR i IN 1 .. 5 LOOP
    v_tab(i) := i;
  END LOOP load_loop;
  
  -- Delete the third item of the collection.
  v_tab.DELETE(3);
  
  -- Traverse sparse collection
  v_idx := v_tab.FIRST;
  << display_loop >>
  WHILE v_idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE('The number ' || v_tab(v_idx));
    v_idx := v_tab.NEXT(v_idx);
  END LOOP display_loop;
END;
/

The number 1
The number 2
The number 4
The number 5

PL/SQL procedure successfully completed.

In Oracle 9i Release 2 we are able to index-by a string value.

SET SERVEROUTPUT ON
DECLARE
  TYPE country_tab IS TABLE OF VARCHAR2(50)
    INDEX BY VARCHAR2(5);

  t_country country_tab;
BEGIN

  -- Populate lookup
  t_country('UK') := 'United Kingdom';
  t_country('US') := 'United States of America';
  t_country('FR') := 'France';
  t_country('DE') := 'Germany';
  
  -- Find country name for ISO code "DE"
  DBMS_OUTPUT.PUT_LINE('ISO code "DE" = ' || t_country('DE'));

END;
/

In Oracle 9i Release 2 these have been renamed to Associative Arrays and can be indexed by BINARY INTEGER or VARCHAR2. In addition to the rename Oracle have added the ability to index-by string values making them significantly more flexible. To show this lets assume we need to hold an array of country names and ISO codes. Prior to Oracle9i Release 2 if we wanted to lookup the country names based on their ISO codes we would do the following.

SET SERVEROUTPUT ON
DECLARE
  TYPE country_type IS RECORD (
    iso_code  VARCHAR2(5),
    name      VARCHAR2(50)
  );
  
  TYPE country_tab IS TABLE OF country_type
    INDEX BY BINARY_INTEGER;

  t_country country_tab;
BEGIN

  -- Populate lookup
  t_country(1).iso_code := 'UK';
  t_country(1).name     := 'United Kingdom';
  t_country(2).iso_code := 'US';
  t_country(2).name     := 'United States of America';
  t_country(3).iso_code := 'FR';
  t_country(3).name     := 'France';
  t_country(4).iso_code := 'DE';
  t_country(4).name     := 'Germany';
  
  -- Find country name for ISO code "DE"
  << lookup >>
  FOR i IN 1 .. 4 LOOP
    IF t_country(i).iso_code = 'DE' THEN
      DBMS_OUTPUT.PUT_LINE('ISO code "DE" = ' || t_country(i).name);
      EXIT lookup;
    END IF;
  END LOOP;

END;
/

In Oracle 9i Release 2 we are able to index-by a string value.

SET SERVEROUTPUT ON
DECLARE
  TYPE country_tab IS TABLE OF VARCHAR2(50)
    INDEX BY VARCHAR2(5);

  t_country country_tab;
BEGIN

  -- Populate lookup
  t_country('UK') := 'United Kingdom';
  t_country('US') := 'United States of America';
  t_country('FR') := 'France';
  t_country('DE') := 'Germany';
  
  -- Find country name for ISO code "DE"
  DBMS_OUTPUT.PUT_LINE('ISO code "DE" = ' || t_country('DE'));

END;
/

The index values for associative arrays must be unique. If a new value is assigned using an existing key the existing value of that key is updated. They are particularly useful as quick lookups for reference data where the index value is the primary key of the original table. Associative arrays cannot be stored in tables like nested tables or varrays. Session level persistance can be achieved by defining the types and variables in a package.

2. Nested Table Collections

Nested table collections are an extension of the index-by tables. The main difference between the two is that nested tables can be stored in a database column but index-by tables cannot. In addition some DML operations are possible on nested tables when they are stored in the database. During creation the collection must be dense, having consecutive subscripts for the elements. Once created elements can be deleted using the DELETE method to make the collection sparse. The NEXT method overcomes the problems of traversing sparse collections.

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  TYPE table_type IS TABLE OF NUMBER(10);
  v_tab  table_type;
  v_idx  NUMBER;
BEGIN

  -- Initialise the collection with two values.
  v_tab := table_type(1, 2);

  -- Extend the collection with extra values.
  << load_loop >>
  FOR i IN 3 .. 5 LOOP
    v_tab.extend;
    v_tab(v_tab.last) := i;
  END LOOP load_loop;
  
  -- Delete the third item of the collection.
  v_tab.DELETE(3);
  
  -- Traverse sparse collection
  v_idx := v_tab.FIRST;
  << display_loop >>
  WHILE v_idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE('The number ' || v_tab(v_idx));
    v_idx := v_tab.NEXT(v_idx);
  END LOOP display_loop;
END;
/

The number 1
The number 2
The number 4
The number 5

PL/SQL procedure successfully completed.

3. Varray Collections

A VARRAY is similar to a nested table except you must specifiy an upper bound in the declaration. Like nested tables they can be stored in the database, but unlike nested tables individual elements cannot be deleted so they remain dense.

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  TYPE table_type IS VARRAY(5) OF NUMBER(10);
  v_tab  table_type;
  v_idx  NUMBER;
BEGIN
  -- Initialise the collection with two values.
  v_tab := table_type(1, 2);

  -- Extend the collection with extra values.
  << load_loop >>
  FOR i IN 3 .. 5 LOOP
    v_tab.extend;
    v_tab(v_tab.last) := i;
  END LOOP load_loop;
  
  -- Can't delete from a VARRAY.
  -- v_tab.DELETE(3);

  -- Traverse collection
  v_idx := v_tab.FIRST;
  << display_loop >>
  WHILE v_idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE('The number ' || v_tab(v_idx));
    v_idx := v_tab.NEXT(v_idx);
  END LOOP display_loop;
END;
/

The number 1
The number 2
The number 3
The number 4
The number 5

PL/SQL procedure successfully completed.

Extending the load_loop to 3..6 attempts to extend the VARRAY beyond it's limit of 5 elements resulting in the following error.

DECLARE
*
ERROR at line 1:
ORA-06532: Subscript outside of limit
ORA-06512: at line 12

4. Assignments and Equality Tests

Assignments can only be made between collections of the same type. Not types of similar structures, or with the same name in different packages, but literally the same type.

The following example shows a successful assignment between two collections of the same type.

DECLARE
  TYPE table_type IS TABLE OF NUMBER(10);
  v_tab_1  table_type;
  v_tab_2  table_type;
BEGIN
  -- Initialise the collection with two values.
  v_tab_1 := table_type(1, 2);

  -- Assignment works.
  v_tab_2 := v_tab_1;
END;
/

PL/SQL procedure successfully completed.

If we repeat that, but this time use two separate types with similar definitions, we can see the code fails to compile due to the illegal assignment.

DECLARE
  TYPE table_type_1 IS TABLE OF NUMBER(10);
  TYPE table_type_2 IS TABLE OF NUMBER(10);
  v_tab_1  table_type_1;
  v_tab_2  table_type_2;
BEGIN
  -- Initialise the collection with two values.
  v_tab_1 := table_type_1(1, 2);

  -- Assignment causes compilation error.
  v_tab_2 := v_tab_1;
END;
/

  v_tab_2 := v_tab_1;
             *
ERROR at line 11:
ORA-06550: line 11, column 14:
PLS-00382: expression is of wrong type
ORA-06550: line 11, column 3:
PL/SQL: Statement ignored

Collections of the same type can be tested for equality, as shown in the example below.

SET SERVEROUTPUT ON
DECLARE
  TYPE table_type IS TABLE OF NUMBER(10);
  v_tab_1  table_type;
  v_tab_2  table_type;
BEGIN
  -- Initialise the collection with two values.
  v_tab_1 := table_type(1, 2);
  v_tab_2 := v_tab_1;

  IF v_tab_1 = v_tab_2 THEN
    DBMS_OUTPUT.put_line('1: v_tab_1 = v_tab_2');
  END IF;

  v_tab_1 := table_type(1, 2, 3);

  IF v_tab_1 != v_tab_2 THEN
    DBMS_OUTPUT.put_line('2: v_tab_1 != v_tab_2');
  END IF;
END;
/
1: v_tab_1 = v_tab_2
2: v_tab_1 != v_tab_2

PL/SQL procedure successfully completed.

5. Collection Methods

A variety of methods exist for collections, but not all are relevant for every collection type.

  • EXISTS(n) - Returns TRUE if the specified element exists.

  • COUNT - Returns the number of elements in the collection.

  • LIMIT - Returns the maximum number of elements for a VARRAY, or NULL for nested tables.

  • FIRST - Returns the index of the first element in the collection.

  • LAST - Returns the index of the last element in the collection.

  • PRIOR(n) - Returns the index of the element prior to the specified element.

  • NEXT(n) - Returns the index of the next element after the specified element.

  • EXTEND - Appends a single null element to the collection.

  • EXTEND(n) - Appends n null elements to the collection.

  • EXTEND(n1,n2) - Appends n1 copies of the n2th element to the collection.

  • TRIM - Removes a single element from the end of the collection.

  • TRIM(n) - Removes n elements from the end of the collection.

  • DELETE - Removes all elements from the collection.

  • DELETE(n) - Removes element n from the collection.

  • DELETE(n1,n2) - Removes all elements from n1 to n2 from the collection.

6. MULTISET Conditions

Oracle provides MULTISET conditions against collections, including the following. MULTISET UNION joins the two collections together, doing the equivalent of a UNION ALL between the two sets.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF NUMBER;
  l_tab1 t_tab := t_tab(1,2,3,4,5,6);
  l_tab2 t_tab := t_tab(5,6,7,8,9,10);
BEGIN
  l_tab1 := l_tab1 MULTISET UNION l_tab2;
  
  FOR i IN l_tab1.first .. l_tab1.last LOOP
    DBMS_OUTPUT.put_line(l_tab1(i));
  END LOOP;
END;
/
1
2
3
4
5
6
5
6
7
8
9
10

PL/SQL procedure successfully completed.

The DISTINCT keyword can be added to any of the multiset operations to removes the duplicates. Adding it to the MULTISET UNION makes it the equivalent of a UNION between the two sets.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF NUMBER;
  l_tab1 t_tab := t_tab(1,2,3,4,5,6);
  l_tab2 t_tab := t_tab(5,6,7,8,9,10);
BEGIN
  l_tab1 := l_tab1 MULTISET UNION DISTINCT l_tab2;
  
  FOR i IN l_tab1.first .. l_tab1.last LOOP
    DBMS_OUTPUT.put_line(l_tab1(i));
  END LOOP;
END;
/
1
2
3
4
5
6
7
8
9
10

PL/SQL procedure successfully completed.

MULTISET EXCEPT returns the elements of the first set that are not present in the second set.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF NUMBER;
  l_tab1 t_tab := t_tab(1,2,3,4,5,6,7,8,9,10);
  l_tab2 t_tab := t_tab(6,7,8,9,10);
BEGIN
  l_tab1 := l_tab1 MULTISET EXCEPT l_tab2;
  
  FOR i IN l_tab1.first .. l_tab1.last LOOP
    DBMS_OUTPUT.put_line(l_tab1(i));
  END LOOP;
END;
/
1
2
3
4
5

PL/SQL procedure successfully completed.

MULTISET INTERSECT returns the elements that are present in both sets.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF NUMBER;
  l_tab1 t_tab := t_tab(1,2,3,4,5,6,7,8,9,10);
  l_tab2 t_tab := t_tab(6,7,8,9,10);
BEGIN
  l_tab1 := l_tab1 MULTISET INTERSECT l_tab2;
  
  FOR i IN l_tab1.first .. l_tab1.last LOOP
    DBMS_OUTPUT.put_line(l_tab1(i));
  END LOOP;
END;
/
6
7
8
9
10

PL/SQL procedure successfully completed.

The NOT keyword can be included to get the inverse. For example NOT MULTISET UNION.

7. SUBMULTISET Condition

The SUBMULTISET condition returns true if the first collection is a subset of the second.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF NUMBER;
  l_tab1 t_tab := t_tab(1,2,3,4,5);
  l_tab2 t_tab := t_tab(1,2,3);
  l_tab3 t_tab := t_tab(1,2,3,7);
BEGIN
  DBMS_OUTPUT.put('Is l_tab2 SUBMULTISET OF l_tab1? ');
  IF l_tab2 SUBMULTISET OF l_tab1 THEN
    DBMS_OUTPUT.put_line('TRUE');
  ELSE
    DBMS_OUTPUT.put_line('FALSE');  
  END IF;

  DBMS_OUTPUT.put('Is l_tab3 SUBMULTISET OF l_tab1? ');
  IF l_tab3 SUBMULTISET OF l_tab1 THEN
    DBMS_OUTPUT.put_line('TRUE');
  ELSE
    DBMS_OUTPUT.put_line('FALSE');  
  END IF;
END;
/
Is l_tab2 SUBMULTISET OF l_tab1? TRUE
Is l_tab3 SUBMULTISET OF l_tab1? FALSE

PL/SQL procedure successfully completed.

Having duplicate values in the main set is fine.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF NUMBER;
  l_tab1 t_tab := t_tab(1,2,3,3,4,5);
  l_tab2 t_tab := t_tab(1,2,3);
BEGIN
  DBMS_OUTPUT.put('Is l_tab2 SUBMULTISET OF l_tab1? ');
  IF l_tab2 SUBMULTISET OF l_tab1 THEN
    DBMS_OUTPUT.put_line('TRUE');
  ELSE
    DBMS_OUTPUT.put_line('FALSE');  
  END IF;
END;
/
Is l_tab2 SUBMULTISET OF l_tab1? TRUE

PL/SQL procedure successfully completed.

Having duplicate values in the subset results in false, if those duplicates are not present in the main set.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF NUMBER;
  l_tab1 t_tab := t_tab(1,2,3,4,5);
  l_tab2 t_tab := t_tab(1,2,3,3);
BEGIN
  DBMS_OUTPUT.put('Is l_tab2 SUBMULTISET OF l_tab1? ');
  IF l_tab2 SUBMULTISET OF l_tab1 THEN
    DBMS_OUTPUT.put_line('TRUE');
  ELSE
    DBMS_OUTPUT.put_line('FALSE');  
  END IF;
END;
/
Is l_tab2 SUBMULTISET OF l_tab1? FALSE

PL/SQL procedure successfully completed.

If we add the duplicates into both the main set and the subset, it returns true.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF NUMBER;
  l_tab1 t_tab := t_tab(1,2,3,4,5,3);
  l_tab2 t_tab := t_tab(1,2,3,3);
BEGIN
  DBMS_OUTPUT.put('Is l_tab2 SUBMULTISET OF l_tab1? ');
  IF l_tab2 SUBMULTISET OF l_tab1 THEN
    DBMS_OUTPUT.put_line('TRUE');
  ELSE
    DBMS_OUTPUT.put_line('FALSE');  
  END IF;
END;
/
Is l_tab2 SUBMULTISET OF l_tab1? TRUE

PL/SQL procedure successfully completed.

An initialised, but empty subset will always return true.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF NUMBER;
  l_tab1 t_tab := t_tab(1,2,3,4,5);
  l_tab2 t_tab := t_tab();
BEGIN
  DBMS_OUTPUT.put('Is l_tab2 SUBMULTISET OF l_tab1? ');
  IF l_tab2 SUBMULTISET OF l_tab1 THEN
    DBMS_OUTPUT.put_line('TRUE');
  ELSE
    DBMS_OUTPUT.put_line('FALSE');  
  END IF;
END;
/
Is l_tab2 SUBMULTISET OF l_tab1? TRUE

PL/SQL procedure successfully completed.

The result is also true if both sets are empty.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF NUMBER;
  l_tab1 t_tab := t_tab();
  l_tab2 t_tab := t_tab();
BEGIN
  DBMS_OUTPUT.put('Is l_tab2 SUBMULTISET OF l_tab1? ');
  IF l_tab2 SUBMULTISET OF l_tab1 THEN
    DBMS_OUTPUT.put_line('TRUE');
  ELSE
    DBMS_OUTPUT.put_line('FALSE');  
  END IF;
END;
/
Is l_tab2 SUBMULTISET OF l_tab1? TRUE

PL/SQL procedure successfully completed.

The NOT keyword can be included to get the inverse. For example NOT SUBMULTISET. The OF keyword is optional, but makes the code more readable.

8. MEMBER Condition

The MEMBER condition allows you to test if an element is member of a collection.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF NUMBER;
  l_tab1 t_tab := t_tab(1,2,3,4,5);
BEGIN
  DBMS_OUTPUT.put('Is 3 MEMBER OF l_tab1? ');
  IF 3 MEMBER OF l_tab1 THEN
    DBMS_OUTPUT.put_line('TRUE');
  ELSE
    DBMS_OUTPUT.put_line('FALSE');  
  END IF;
END;
/
Is 3 MEMBER OF l_tab1? TRUE

PL/SQL procedure successfully completed.

The NOT keyword can be included to get the inverse. For example NOT MEMBER. The OF keyword is optional, but makes the code more readable.

9. Multidimensional Collections

In addition to regular data types, collections can be based on record types, allowing the creation of two-dimensional collections.

SET SERVEROUTPUT ON

-- Collection of records.
DECLARE
  TYPE t_row IS RECORD (
    id  NUMBER,
    description VARCHAR2(50)
  );

  TYPE t_tab IS TABLE OF t_row;
  l_tab t_tab := t_tab();
BEGIN
  FOR i IN 1 .. 10 LOOP
    l_tab.extend();
    l_tab(l_tab.last).id := i;
    l_tab(l_tab.last).description := 'Description for ' || i;
  END LOOP;
END;
/

-- Collection of records based on ROWTYPE.
CREATE TABLE t1 (
  id  NUMBER,
  description VARCHAR2(50)
);

SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF t1%ROWTYPE;
  l_tab t_tab := t_tab();
BEGIN
  FOR i IN 1 .. 10 LOOP
    l_tab.extend();
    l_tab(l_tab.last).id := i;
    l_tab(l_tab.last).description := 'Description for ' || i;
  END LOOP;
END;
/

For multidimentional arrays you can build collections of collections.

DECLARE
  TYPE t_tab1 IS TABLE OF NUMBER;
  TYPE t_tab2 IS TABLE OF t_tab1;
  l_tab1 t_tab1 := t_tab1(1,2,3,4,5);
  l_tab2 t_tab2 := t_tab2();
BEGIN
  FOR i IN 1 .. 10 LOOP
    l_tab2.extend();
    l_tab2(l_tab2.last) := l_tab1;
  END LOOP;
END;
/

参考资料:

本文标题:Collections in Oracle PL/SQL

本文链接:http://yedward.net/?id=37

本文版权归作者所有,欢迎转载,转载请以文字链接的形式注明文章出处。

相关文章