PL/SQL Collections and Records

最新推荐文章于 2025-05-09 16:51:40 发布

数据架构

最新推荐文章于 2025-05-09 16:51:40 发布

阅读量531

点赞数

分类专栏： PL/SQL 文章标签： dba oracle PL/SQL

本文链接：https://blog.youkuaiyun.com/u011868279/article/details/129650952

版权

PL/SQL 专栏收录该内容

8 篇文章

订阅专栏

In a collection, the internal components always have the same data type, and are called elements. You can access each element of a collection variable by its unique index, with this syntax: variable_name(index). To create a collection variable, you either define a collection type and then create a variable of that type or use %TYPE.

In a record, the internal components can have different data types, and are called fields. You can access each field of a record variable by its name, with this syntax: variable_name.field_name. To create a record variable, you either define a RECORD type and then create a variable of that type or use %ROWTYPE or %TYPE.

You can create a collection of records, and a record that contains collections.

Collection Topics

Record Topics

Collection Types

Number of Elements

If the number of elements is specified, it is the maximum number of elements in the collection. If the number of elements is unspecified, the maximum number of elements in the collection is the upper limit of the index type

Dense or Sparse

A dense collection has no gaps between elements—every element between the first and last element is defined and has a value (the value can be NULL unless the element has a NOT NULL constraint). A sparse collection has gaps between elements.

Uninitialized Status

An empty collection exists but has no elements. To add elements to an empty collection, invoke the EXTEND method

A null collection (also called an atomically null collection) does not exist. To change a null collection to an existing collection, you must initialize it, either by making it empty or by assigning a non-NULL

Where Defined

A collection type defined in a PL/SQL block is a local type. It is available only in the block, and is stored in the database only if the block is in a standalone or package subprogram.

A collection type defined in a package specification is a public item. You can reference it from outside the package by qualifying it with the package name (package_name.type_name). It is stored in the database until you drop the package.

A collection type defined at schema level is a standalone type.

Can Be ADT Attribute Data Type

To be an ADT attribute data type, a collection type must be a standalone collection type.

Translating Non-PL/SQL Composite Types to PL/SQL Composite Types

Associative Arrays

An associative array (formerly called PL/SQL table or index-by table) is a set of key-value pairs. Each key is a unique index, used to locate the associated value with the syntax variable_name(index).

Example 5-1 Associative Array Indexed by String

SQL> set serveroutput on
SQL> 
SQL> DECLARE
  2    -- Associative array indexed by string:
  3    
  4    TYPE population IS TABLE OF NUMBER  -- Associative array type
  5      INDEX BY VARCHAR2(64);            --  indexed by string
  6    
  7    city_population  population;        -- Associative array variable
  8    i  VARCHAR2(64);                    -- Scalar variable
  9    
 10  BEGIN
 11    -- Add elements (key-value pairs) to associative array:
 12   
 13    city_population('Smallville')  := 2000;
 14    city_population('Midland')     := 750000;
 15    city_population('Megalopolis') := 1000000;
 16   
 17    -- Change value associated with key 'Smallville':
 18   
 19    city_population('Smallville') := 2001;
 20   
 21    -- Print associative array:
 22   
 23    i := city_population.FIRST;  -- Get first element of array
 24   
 25    WHILE i IS NOT NULL LOOP
 26      DBMS_Output.PUT_LINE
 27        ('Population of ' || i || ' is ' || city_population(i));
 28      i := city_population.NEXT(i);  -- Get next element of array
 29    END LOOP;
 30  END;
 31  /
Population of Megalopolis is 1000000
Population of Midland is 750000
Population of Smallville is 2001

PL/SQL procedure successfully completed.

SQL>

Example 5-2 Function Returns Associative Array Indexed by PLS_INTEGER

SQL> 
SQL> DECLARE
  2    TYPE sum_multiples IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
  3    n  PLS_INTEGER := 5;   -- number of multiples to sum for display
  4    sn PLS_INTEGER := 10;  -- number of multiples to sum
  5    m  PLS_INTEGER := 3;   -- multiple
  6  
  7    FUNCTION get_sum_multiples (
  8      multiple IN PLS_INTEGER,
  9      num      IN PLS_INTEGER
 10    ) RETURN sum_multiples
 11    IS
 12      s sum_multiples;
 13    BEGIN
 14      FOR i IN 1..num LOOP
 15        s(i) := multiple * ((i * (i + 1)) / 2);  -- sum of multiples
 16      END LOOP;
 17      RETURN s;
 18    END get_sum_multiples;
 19  
 20  BEGIN
 21    DBMS_OUTPUT.PUT_LINE (
 22      'Sum of the first ' || TO_CHAR(n) || ' multiples of ' ||
 23      TO_CHAR(m) || ' is ' || TO_CHAR(get_sum_multiples (m, sn)(n))
 24    );
 25  END;
 26  /
Sum of the first 5 multiples of 3 is 45

PL/SQL procedure successfully completed.

SQL>

Declaring Associative Array Constants

When declaring an associative array constant, you must create a function that populates the associative array with its initial value and then invoke the function in the constant declaration

Example 5-3 Declaring Associative Array Constant

SQL> 
SQL> CREATE OR REPLACE PACKAGE My_Types AUTHID DEFINER IS
  2    TYPE My_AA IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
  3    FUNCTION Init_My_AA RETURN My_AA;
  4  END My_Types;
  5  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY My_Types IS
  2    FUNCTION Init_My_AA RETURN My_AA IS
  3      Ret My_AA;
  4    BEGIN
  5      Ret(-10) := '-ten';
  6      Ret(0) := 'zero';
  7      Ret(1) := 'one';
  8      Ret(2) := 'two';
  9      Ret(3) := 'three';
 10      Ret(4) := 'four';
 11      Ret(9) := 'nine';
 12      RETURN Ret;
 13    END Init_My_AA;
 14  END My_Types;
 15  /

Package body created.

SQL> 
SQL> DECLARE
  2    v CONSTANT My_Types.My_AA := My_Types.Init_My_AA();
  3  BEGIN
  4    DECLARE
  5      Idx PLS_INTEGER := v.FIRST();
  6    BEGIN
  7      WHILE Idx IS NOT NULL LOOP
  8        DBMS_OUTPUT.PUT_LINE(TO_CHAR(Idx, '999')||LPAD(v(Idx), 7));
  9        Idx := v.NEXT(Idx);
 10      END LOOP;
 11    END;
 12  END;
 13  /
-10   -ten
0   zero
1    one
2    two
3  three
4   four
9   nine

PL/SQL procedure successfully completed.

SQL> 
SQL>

NLS Parameter Values Affect Associative Arrays Indexed by String

National Language Support (NLS) parameters such as NLS_SORT, NLS_COMP, and NLS_DATE_FORMAT affect associative arrays indexed by string.

Changing NLS Parameter Values After Populating Associative Arrays

The initialization parameters NLS_SORT and NLS_COMP determine the storage order of string indexes of an associative array. If you change the value of either parameter after populating an associative array indexed by string, then the collection methods FIRST, LAST, NEXT, and PRIOR might return unexpected values or raise exceptions. If you must change these parameter values during your session, restore their original values before operating on associative arrays indexed by string.

Indexes of Data Types Other Than VARCHAR2

In the declaration of an associative array indexed by string, the string type must be VARCHAR2 or one of its subtypes. However, you can populate the associative array with indexes of any data type that the TO_CHAR function can convert to VARCHAR2.

If your indexes have data types other than VARCHAR2 and its subtypes, ensure that these indexes remain consistent and unique if the values of initialization parameters change. For example:

Do not use TO_CHAR(SYSDATE) as an index.

If the value of NLS_DATE_FORMAT changes, then the value of (TO_CHAR(SYSDATE)) might also change.

Do not use different NVARCHAR2 indexes that might be converted to the same VARCHAR2 value.

Do not use CHAR or VARCHAR2 indexes that differ only in case, accented characters, or punctuation characters.

If the value of NLS_SORT ends in _CI (case-insensitive comparisons) or _AI (accent- and case-insensitive comparisons), then indexes that differ only in case, accented characters, or punctuation characters might be converted to the same value.

Passing Associative Arrays to Remote Databases

If you pass an associative array as a parameter to a remote database, and the local and the remote databases have different NLS_SORT or NLS_COMP values, then:

The collection method FIRST, LAST, NEXT or PRIOR might return unexpected values or raise exceptions.

Indexes that are unique on the local database might not be unique on the remote database, raising the predefined exception VALUE_ERROR.

Appropriate Uses for Associative Arrays

An associative array is appropriate for:

A relatively small lookup table, which can be constructed in memory each time you invoke the subprogram or initialize the package that declares it

Passing collections to and from the database server

Declare formal subprogram parameters of associative array types. With Oracle Call Interface (OCI) or an Oracle precompiler, bind the host arrays to the corresponding actual parameters. PL/SQL automatically converts between host arrays and associative arrays indexed by PLS_INTEGER.

An associative array is intended for temporary data storage. To make an associative array persistent for the life of a database session, declare it in a package specification and populate it in the package body.

Varrays (Variable-Size Arrays)

A varray (variable-size array) is an array whose number of elements can vary from zero (empty) to the declared maximum size. To access an element of a varray variable, use the syntax variable_name(index). The lower bound of index is 1; the upper bound is the current number of elements. The upper bound changes as you add or delete elements, but it cannot exceed the maximum size. When you store and retrieve a varray from the database, its indexes and element order remain stable.

The database stores a varray variable as a single object. If a varray variable is less than 4 KB, it resides inside the table of which it is a column; otherwise, it resides outside the table but in the same tablespace.

An uninitialized varray variable is a null collection. You must initialize it, either by making it empty or by assigning a non-NULL value to it.

Example 5-4 Varray (Variable-Size Array)

SQL> 
SQL> DECLARE
  2    TYPE Foursome IS VARRAY(4) OF VARCHAR2(15);  -- VARRAY type
  3   
  4    -- varray variable initialized with constructor:
  5   
  6    team Foursome := Foursome('John', 'Mary', 'Alberto', 'Juanita');
  7   
  8    PROCEDURE print_team (heading VARCHAR2) IS
  9    BEGIN
 10      DBMS_OUTPUT.PUT_LINE(heading);
 11   
 12      FOR i IN 1..4 LOOP
 13        DBMS_OUTPUT.PUT_LINE(i || '.' || team(i));
 14      END LOOP;
 15   
 16      DBMS_OUTPUT.PUT_LINE('---'); 
 17    END;
 18    
 19  BEGIN 
 20    print_team('2001 Team:');
 21   
 22    team(3) := 'Pierre';  -- Change values of two elements
 23    team(4) := 'Yvonne';
 24    print_team('2005 Team:');
 25   
 26    -- Invoke constructor to assign new values to varray variable:
 27   
 28    team := Foursome('Arun', 'Amitha', 'Allan', 'Mae');
 29    print_team('2009 Team:');
 30  END;
 31  /
2001 Team:
1.John
2.Mary
3.Alberto
4.Juanita
---
2005 Team:
1.John
2.Mary
3.Pierre
4.Yvonne
---
2009 Team:
1.Arun
2.Amitha
3.Allan
4.Mae
---

PL/SQL procedure successfully completed.

SQL>

Appropriate Uses for Varrays

A varray is appropriate when:

You know the maximum number of elements.

You usually access the elements sequentially.

Because you must store or retrieve all elements at the same time, a varray might be impractical for large numbers of elements.

Nested Tables

In the database, a nested table is a column type that stores an unspecified number of rows in no particular order. When you retrieve a nested table value from the database into a PL/SQL nested table variable, PL/SQL gives the rows consecutive indexes, starting at 1. Using these indexes, you can access the individual rows of the nested table variable. The syntax is variable_name(index). The indexes and row order of a nested table might not remain stable as you store and retrieve the nested table from the database.

The amount of memory that a nested table variable occupies can increase or decrease dynamically, as you add or delete elements.

An uninitialized nested table variable is a null collection. You must initialize it, either by making it empty or by assigning a non-NULL value to it.

Example 5-5 Nested Table of Local Type

SQL> 
SQL> DECLARE
  2    TYPE Roster IS TABLE OF VARCHAR2(15);  -- nested table type
  3   
  4    -- nested table variable initialized with constructor:
  5   
  6    names Roster := Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh');
  7   
  8    PROCEDURE print_names (heading VARCHAR2) IS
  9    BEGIN
 10      DBMS_OUTPUT.PUT_LINE(heading);
 11   
 12      FOR i IN names.FIRST .. names.LAST LOOP  -- For first to last element
 13        DBMS_OUTPUT.PUT_LINE(names(i));
 14      END LOOP;
 15   
 16      DBMS_OUTPUT.PUT_LINE('---');
 17    END;
 18    
 19  BEGIN 
 20    print_names('Initial Values:');
 21   
 22    names(3) := 'P Perez';  -- Change value of one element
 23    print_names('Current Values:');
 24   
 25    names := Roster('A Jansen', 'B Gupta');  -- Change entire table
 26    print_names('Current Values:');
 27  END;
 28  /
Initial Values:
D Caruso
J Hamil
D Piro
R Singh
---
Current Values:
D Caruso
J Hamil
P Perez
R Singh
---
Current Values:
A Jansen
B Gupta
---

PL/SQL procedure successfully completed.

SQL>

Example 5-6 Nested Table of Standalone Type

SQL> 
SQL> CREATE OR REPLACE TYPE nt_type IS TABLE OF NUMBER;
  2  /

Type created.

SQL> CREATE OR REPLACE PROCEDURE print_nt (nt nt_type) AUTHID DEFINER IS
  2    i  NUMBER;
  3  BEGIN
  4    i := nt.FIRST;
  5   
  6    IF i IS NULL THEN
  7      DBMS_OUTPUT.PUT_LINE('nt is empty');
  8    ELSE
  9      WHILE i IS NOT NULL LOOP
 10        DBMS_OUTPUT.PUT('nt.(' || i || ') = ');
 11        DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(nt(i)), 'NULL'));
 12        i := nt.NEXT(i);
 13      END LOOP;
 14    END IF;
 15   
 16    DBMS_OUTPUT.PUT_LINE('---');
 17  END print_nt;
 18  /

Procedure created.

SQL> DECLARE
  2    nt nt_type := nt_type();  -- nested table variable initialized to empty
  3  BEGIN
  4    print_nt(nt);
  5    nt := nt_type(90, 9, 29, 58);
  6    print_nt(nt);
  7  END;
  8  /
nt is empty
---
nt.(1) = 90
nt.(2) = 9
nt.(3) = 29
nt.(4) = 58
---

PL/SQL procedure successfully completed.

SQL>

Important Differences Between Nested Tables and Arrays

Conceptually, a nested table is like a one-dimensional array with an arbitrary number of elements. However, a nested table differs from an array in these important ways:

An array has a declared number of elements, but a nested table does not. The size of a nested table can increase dynamically.

An array is always dense. A nested array is dense initially, but it can become sparse, because you can delete elements from it.

Appropriate Uses for Nested Tables

A nested table is appropriate when:

The number of elements is not set.

Index values are not consecutive.

You must delete or update some elements, but not all elements simultaneously.

You would create a separate lookup table, with multiple entries for each row of the main table, and access it through join queries.

Collection Constructors

A collection constructor (constructor) is a system-defined function with the same name as a collection type, which returns a collection of that type. The syntax of a constructor invocation is:

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

You can assign the returned collection to a collection variable (of the same type) in the variable declaration and in the executable part of a block.

Example 5-7 Initializing Collection (Varray) Variable to Empty

SQL> 
SQL> DECLARE
  2    TYPE Foursome IS VARRAY(4) OF VARCHAR2(15);
  3    team Foursome := Foursome();  -- initialize to empty
  4   
  5    PROCEDURE print_team (heading VARCHAR2)
  6    IS
  7    BEGIN
  8      DBMS_OUTPUT.PUT_LINE(heading);
  9   
 10      IF team.COUNT = 0 THEN
 11        DBMS_OUTPUT.PUT_LINE('Empty');
 12      ELSE 
 13        FOR i IN 1..4 LOOP
 14          DBMS_OUTPUT.PUT_LINE(i || '.' || team(i));
 15        END LOOP;
 16      END IF;
 17   
 18      DBMS_OUTPUT.PUT_LINE('---'); 
 19    END;
 20   
 21  BEGIN
 22    print_team('Team:');
 23    team := Foursome('John', 'Mary', 'Alberto', 'Juanita');
 24    print_team('Team:');
 25  END;
 26  /
Team:
Empty
---
Team:
1.John
2.Mary
3.Alberto
4.Juanita
---

PL/SQL procedure successfully completed.

SQL>

Assigning Values to Collection Variables

You can assign a value to a collection variable in these ways:

Invoke a constructor to create a collection and assign it to the collection variable.

Use the assignment statement to assign it the value of another existing collection variable.

Pass it to a subprogram as an OUT or IN OUT parameter, and then assign the value inside the subprogram.

To assign a value to a scalar element of a collection variable, reference the element as collection_variable_name(index) and assign it a value as instructed

Data Type Compatibility

You can assign a collection to a collection variable only if they have the same data type. Having the same element type is not enough.

Example 5-8 Data Type Compatibility for Collection Assignment

SQL> 
SQL> DECLARE
  2    TYPE triplet IS VARRAY(3) OF VARCHAR2(15);
  3    TYPE trio    IS VARRAY(3) OF VARCHAR2(15);
  4   
  5    group1 triplet := triplet('Jones', 'Wong', 'Marceau');
  6    group2 triplet;
  7    group3 trio;
  8  BEGIN
  9    group2 := group1;  -- succeeds
 10    group3 := group1;  -- fails
 11  END;
 12  /
  group3 := group1;  -- fails
            *
ERROR at line 10:
ORA-06550: line 10, column 13:
PLS-00382: expression is of wrong type
ORA-06550: line 10, column 3:
PL/SQL: Statement ignored


SQL>

Assigning Null Values to Varray or Nested Table Variables

To a varray or nested table variable, you can assign the value NULL or a null collection of the same data type. Either assignment makes the variable null.

Example 5-9 Assigning Null Value to Nested Table Variable

SQL> 
SQL> DECLARE
  2    TYPE dnames_tab IS TABLE OF VARCHAR2(30);
  3   
  4    dept_names dnames_tab := dnames_tab(
  5      'Shipping','Sales','Finance','Payroll');  -- Initialized to non-null value
  6   
  7    empty_set dnames_tab;  -- Not initialized, therefore null
  8   
  9    PROCEDURE print_dept_names_status IS
 10    BEGIN
 11      IF dept_names IS NULL THEN
 12        DBMS_OUTPUT.PUT_LINE('dept_names is null.');
 13      ELSE
 14        DBMS_OUTPUT.PUT_LINE('dept_names is not null.');
 15      END IF;
 16    END  print_dept_names_status;
 17   
 18  BEGIN
 19    print_dept_names_status;
 20    dept_names := empty_set;  -- Assign null collection to dept_names.
 21    print_dept_names_status;
 22    dept_names := dnames_tab (
 23      'Shipping','Sales','Finance','Payroll');  -- Re-initialize dept_names
 24    print_dept_names_status;
 25  END;
 26  /
dept_names is not null.
dept_names is null.
dept_names is not null.

PL/SQL procedure successfully completed.

SQL>

Assigning Set Operation Results to Nested Table Variables

To a nested table variable, you can assign the result of a SQL MULTISET operation or SQL SET function invocation.

The SQL MULTISET operators combine two nested tables into a single nested table. The elements of the two nested tables must have comparable data types.

The SQL SET function takes a nested table argument and returns a nested table of the same data type whose elements are distinct (the function eliminates duplicate elements).

Example 5-10 Assigning Set Operation Results to Nested Table Variable

SQL> 
SQL> DECLARE
  2    TYPE nested_typ IS TABLE OF NUMBER;
  3   
  4    nt1    nested_typ := nested_typ(1,2,3);
  5    nt2    nested_typ := nested_typ(3,2,1);
  6    nt3    nested_typ := nested_typ(2,3,1,3);
  7    nt4    nested_typ := nested_typ(1,2,4);
  8    answer nested_typ;
  9   
 10    PROCEDURE print_nested_table (nt nested_typ) IS
 11      output VARCHAR2(128);
 12    BEGIN
 13      IF nt IS NULL THEN
 14        DBMS_OUTPUT.PUT_LINE('Result: null set');
 15      ELSIF nt.COUNT = 0 THEN
 16        DBMS_OUTPUT.PUT_LINE('Result: empty set');
 17      ELSE
 18        FOR i IN nt.FIRST .. nt.LAST LOOP  -- For first to last element
 19          output := output || nt(i) || ' ';
 20        END LOOP;
 21        DBMS_OUTPUT.PUT_LINE('Result: ' || output);
 22      END IF;
 23    END print_nested_table;
 24   
 25  BEGIN
 26    answer := nt1 MULTISET UNION nt4;
 27    print_nested_table(answer);
 28    answer := nt1 MULTISET UNION nt3;
 29    print_nested_table(answer);
 30    answer := nt1 MULTISET UNION DISTINCT nt3;
 31    print_nested_table(answer);
 32    answer := nt2 MULTISET INTERSECT nt3;
 33    print_nested_table(answer);
 34    answer := nt2 MULTISET INTERSECT DISTINCT nt3;
 35    print_nested_table(answer);
 36    answer := SET(nt3);
 37    print_nested_table(answer);
 38    answer := nt3 MULTISET EXCEPT nt2;
 39    print_nested_table(answer);
 40    answer := nt3 MULTISET EXCEPT DISTINCT nt2;
 41    print_nested_table(answer);
 42  END;
 43  /
Result: 1 2 3 1 2 4
Result: 1 2 3 2 3 1 3
Result: 1 2 3
Result: 3 2 1
Result: 3 2 1
Result: 2 3 1
Result: 3
Result: empty set

PL/SQL procedure successfully completed.

SQL>

Multidimensional Collections

Although a collection has only one dimension, you can model a multidimensional collection with a collection whose elements are collections.

Example 5-11 Two-Dimensional Varray (Varray of Varrays)

SQL> 
SQL> DECLARE
  2    TYPE t1 IS VARRAY(10) OF INTEGER;  -- varray of integer
  3    va t1 := t1(2,3,5);
  4  
  5    TYPE nt1 IS VARRAY(10) OF t1;      -- varray of varray of integer
  6    nva nt1 := nt1(va, t1(55,6,73), t1(2,4), va);
  7  
  8    i INTEGER;
  9    va1 t1;
 10  BEGIN
 11    i := nva(2)(3);
 12    DBMS_OUTPUT.PUT_LINE('i = ' || i);
 13  
 14    nva.EXTEND;
 15    nva(5) := t1(56, 32);          -- replace inner varray elements
 16    nva(4) := t1(45,43,67,43345);  -- replace an inner integer element
 17    nva(4)(4) := 1;                -- replace 43345 with 1
 18  
 19    nva(4).EXTEND;    -- add element to 4th varray element
 20    nva(4)(5) := 89;  -- store integer 89 there
 21  END;
 22  /
i = 73

PL/SQL procedure successfully completed.

SQL>

Example 5-12 Nested Tables of Nested Tables and Varrays of Integers

SQL> 
SQL> DECLARE
  2    TYPE tb1 IS TABLE OF VARCHAR2(20);  -- nested table of strings
  3    vtb1 tb1 := tb1('one', 'three');
  4  
  5    TYPE ntb1 IS TABLE OF tb1; -- nested table of nested tables of strings
  6    vntb1 ntb1 := ntb1(vtb1);
  7  
  8    TYPE tv1 IS VARRAY(10) OF INTEGER;  -- varray of integers
  9    TYPE ntb2 IS TABLE OF tv1;          -- nested table of varrays of integers
 10    vntb2 ntb2 := ntb2(tv1(3,5), tv1(5,7,3));
 11  
 12  BEGIN
 13    vntb1.EXTEND;
 14    vntb1(2) := vntb1(1);
 15    vntb1.DELETE(1);     -- delete first element of vntb1
 16    vntb1(2).DELETE(1);  -- delete first string from second table in nested table
 17  END;
 18  /

PL/SQL procedure successfully completed.

SQL>

Example 5-13 Nested Tables of Associative Arrays and Varrays of Strings

SQL> DECLARE
  2    TYPE tb1 IS TABLE OF INTEGER INDEX BY PLS_INTEGER;  -- associative arrays
  3    v4 tb1;
  4    v5 tb1;
  5  
  6    TYPE aa1 IS TABLE OF tb1 INDEX BY PLS_INTEGER;  -- associative array of
  7    v2 aa1;                                         --  associative arrays
  8  
  9    TYPE va1 IS VARRAY(10) OF VARCHAR2(20);  -- varray of strings
 10    v1 va1 := va1('hello', 'world');
 11  
 12    TYPE ntb2 IS TABLE OF va1 INDEX BY PLS_INTEGER;  -- associative array of varrays
 13    v3 ntb2;
 14  
 15  BEGIN
 16    v4(1)   := 34;     -- populate associative array
 17    v4(2)   := 46456;
 18    v4(456) := 343;
 19  
 20    v2(23) := v4;  -- populate associative array of associative arrays
 21  
 22    v3(34) := va1(33, 456, 656, 343);  -- populate associative array varrays
 23  
 24    v2(35) := v5;      -- assign empty associative array to v2(35)
 25    v2(35)(2) := 78;
 26  END;
 27  /

PL/SQL procedure successfully completed.

SQL>

Collection Comparisons

You cannot compare associative array variables to the value NULL or to each other.

Comparing Varray and Nested Table Variables to NULL

You can compare varray and nested table variables to the value NULL with the "IS [NOT] NULL Operator", but not with the relational operators equal (=) and not equal (<>, !=, ~=, or ^=).

Example 5-14 Comparing Varray and Nested Table Variables to NULL

SQL> 
SQL> DECLARE  
  2    TYPE Foursome IS VARRAY(4) OF VARCHAR2(15);  -- VARRAY type
  3    team Foursome;                               -- varray variable
  4    
  5    TYPE Roster IS TABLE OF VARCHAR2(15);        -- nested table type
  6    names Roster := Roster('Adams', 'Patel');    -- nested table variable
  7    
  8  BEGIN
  9    IF team IS NULL THEN
 10      DBMS_OUTPUT.PUT_LINE('team IS NULL');
 11    ELSE
 12      DBMS_OUTPUT.PUT_LINE('team IS NOT NULL');
 13    END IF;
 14   
 15    IF names IS NOT NULL THEN
 16      DBMS_OUTPUT.PUT_LINE('names IS NOT NULL');
 17    ELSE
 18      DBMS_OUTPUT.PUT_LINE('names IS NULL');
 19    END IF;
 20  END;
 21  /
team IS NULL
names IS NOT NULL

PL/SQL procedure successfully completed.

SQL>

Comparing Nested Tables for Equality and Inequality

Example 5-15 Comparing Nested Tables for Equality and Inequality

SQL> DECLARE
  2    TYPE dnames_tab IS TABLE OF VARCHAR2(30); -- element type is not record type
  3  
  4    dept_names1 dnames_tab :=
  5      dnames_tab('Shipping','Sales','Finance','Payroll');
  6  
  7    dept_names2 dnames_tab :=
  8      dnames_tab('Sales','Finance','Shipping','Payroll');
  9  
 10    dept_names3 dnames_tab :=
 11      dnames_tab('Sales','Finance','Payroll');
 12  
 13  BEGIN
 14    IF dept_names1 = dept_names2 THEN
 15      DBMS_OUTPUT.PUT_LINE('dept_names1 = dept_names2');
 16    END IF;
 17  
 18    IF dept_names2 != dept_names3 THEN
 19      DBMS_OUTPUT.PUT_LINE('dept_names2 != dept_names3');
 20    END IF;
 21  END;
 22  /
dept_names1 = dept_names2
dept_names2 != dept_names3

PL/SQL procedure successfully completed.

SQL>

Comparing Nested Tables with SQL Multiset Conditions

Example 5-16 Comparing Nested Tables with SQL Multiset Conditions

SQL> 
SQL> DECLARE
  2    TYPE nested_typ IS TABLE OF NUMBER;
  3    nt1 nested_typ := nested_typ(1,2,3);
  4    nt2 nested_typ := nested_typ(3,2,1);
  5    nt3 nested_typ := nested_typ(2,3,1,3);
  6    nt4 nested_typ := nested_typ(1,2,4);
  7   
  8    PROCEDURE testify (
  9      truth BOOLEAN := NULL,
 10      quantity NUMBER := NULL
 11    ) IS
 12    BEGIN
 13      IF truth IS NOT NULL THEN
 14        DBMS_OUTPUT.PUT_LINE (
 15          CASE truth
 16             WHEN TRUE THEN 'True'
 17             WHEN FALSE THEN 'False'
 18          END
 19        );
 20      END IF;
 21      IF quantity IS NOT NULL THEN
 22          DBMS_OUTPUT.PUT_LINE(quantity);
 23      END IF;
 24    END;
 25  BEGIN
 26    testify(truth => (nt1 IN (nt2,nt3,nt4)));        -- condition
 27    testify(truth => (nt1 SUBMULTISET OF nt3));      -- condition
 28    testify(truth => (nt1 NOT SUBMULTISET OF nt4));  -- condition
 29    testify(truth => (4 MEMBER OF nt1));             -- condition
 30    testify(truth => (nt3 IS A SET));                -- condition
 31    testify(truth => (nt3 IS NOT A SET));            -- condition
 32    testify(truth => (nt1 IS EMPTY));                -- condition
 33    testify(quantity => (CARDINALITY(nt3)));         -- function
 34    testify(quantity => (CARDINALITY(SET(nt3))));    -- 2 functions
 35  END;
 36  /
True
True
True
False
False
True
False
4
3

PL/SQL procedure successfully completed.

SQL>

Collection Methods

The basic syntax of a collection method invocation is:

collection_name.method

DELETE Collection Method

DELETE is a procedure that deletes elements from a collection. This method has these forms:

DELETE deletes all elements from a collection of any type.

This operation immediately frees the memory allocated to the deleted elements.

From an associative array or nested table (but not a varray):

DELETE(n) deletes the element whose index is n, if that element exists; otherwise, it does nothing.

DELETE(m,n) deletes all elements whose indexes are in the range m..n, if both m and n exist and m <= n; otherwise, it does nothing.

For these two forms of DELETE, PL/SQL keeps placeholders for the deleted elements. Therefore, the deleted elements are included in the internal size of the collection, and you can restore a deleted element by assigning a valid value to it.

Example 5-17 DELETE Method with Nested Table

SQL> 
SQL> DECLARE
  2    nt nt_type := nt_type(11, 22, 33, 44, 55, 66);
  3  BEGIN
  4    print_nt(nt);
  5   
  6    nt.DELETE(2);     -- Delete second element
  7    print_nt(nt);
  8   
  9    nt(2) := 2222;    -- Restore second element
 10    print_nt(nt);
 11   
 12    nt.DELETE(2, 4);  -- Delete range of elements
 13    print_nt(nt);
 14   
 15    nt(3) := 3333;    -- Restore third element
 16    print_nt(nt);
 17   
 18    nt.DELETE;        -- Delete all elements
 19    print_nt(nt);
 20  END;
 21  /
nt.(1) = 11
nt.(2) = 22
nt.(3) = 33
nt.(4) = 44
nt.(5) = 55
nt.(6) = 66
---
nt.(1) = 11
nt.(3) = 33
nt.(4) = 44
nt.(5) = 55
nt.(6) = 66
---
nt.(1) = 11
nt.(2) = 2222
nt.(3) = 33
nt.(4) = 44
nt.(5) = 55
nt.(6) = 66
---
nt.(1) = 11
nt.(5) = 55
nt.(6) = 66
---
nt.(1) = 11
nt.(3) = 3333
nt.(5) = 55
nt.(6) = 66
---
nt is empty
---

PL/SQL procedure successfully completed.

SQL>

Example 5-18 DELETE Method with Associative Array Indexed by String

SQL> 
SQL> DECLARE
  2    TYPE aa_type_str IS TABLE OF INTEGER INDEX BY VARCHAR2(10);
  3    aa_str  aa_type_str;
  4   
  5    PROCEDURE print_aa_str IS
  6      i  VARCHAR2(10);
  7    BEGIN
  8      i := aa_str.FIRST;
  9   
 10      IF i IS NULL THEN
 11        DBMS_OUTPUT.PUT_LINE('aa_str is empty');
 12      ELSE
 13        WHILE i IS NOT NULL LOOP
 14          DBMS_OUTPUT.PUT('aa_str.(' || i || ') = ');
 15          DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(aa_str(i)), 'NULL'));
 16          i := aa_str.NEXT(i);
 17        END LOOP;
 18      END IF;
 19   
 20      DBMS_OUTPUT.PUT_LINE('---');
 21    END print_aa_str;
 22   
 23  BEGIN
 24    aa_str('M') := 13;
 25    aa_str('Z') := 26;
 26    aa_str('C') := 3;
 27    print_aa_str;
 28   
 29    aa_str.DELETE;  -- Delete all elements
 30    print_aa_str;
 31   
 32    aa_str('M') := 13;   -- Replace deleted element with same value
 33    aa_str('Z') := 260;  -- Replace deleted element with new value
 34    aa_str('C') := 30;   -- Replace deleted element with new value
 35    aa_str('W') := 23;   -- Add new element
 36    aa_str('J') := 10;   -- Add new element
 37    aa_str('N') := 14;   -- Add new element
 38    aa_str('P') := 16;   -- Add new element
 39    aa_str('W') := 23;   -- Add new element
 40    aa_str('J') := 10;   -- Add new element
 41    print_aa_str;
 42   
 43    aa_str.DELETE('C');      -- Delete one element
 44    print_aa_str;
 45   
 46    aa_str.DELETE('N','W');  -- Delete range of elements
 47    print_aa_str;
 48   
 49    aa_str.DELETE('Z','M');  -- Does nothing
 50    print_aa_str;
 51  END;
 52  /
aa_str.(C) = 3
aa_str.(M) = 13
aa_str.(Z) = 26
---
aa_str is empty
---
aa_str.(C) = 30
aa_str.(J) = 10
aa_str.(M) = 13
aa_str.(N) = 14
aa_str.(P) = 16
aa_str.(W) = 23
aa_str.(Z) = 260
---
aa_str.(J) = 10
aa_str.(M) = 13
aa_str.(N) = 14
aa_str.(P) = 16
aa_str.(W) = 23
aa_str.(Z) = 260
---
aa_str.(J) = 10
aa_str.(M) = 13
aa_str.(Z) = 260
---
aa_str.(J) = 10
aa_str.(M) = 13
aa_str.(Z) = 260
---

PL/SQL procedure successfully completed.

SQL>

Example 5-19 TRIM Method with Nested Table

SQL> DECLARE
  2    nt nt_type := nt_type(11, 22, 33, 44, 55, 66);
  3  BEGIN
  4    print_nt(nt);
  5  
  6    nt.TRIM;       -- Trim last element
  7    print_nt(nt);
  8  
  9    nt.DELETE(4);  -- Delete fourth element
 10    print_nt(nt);
 11  
 12    nt.TRIM(2);    -- Trim last two elements
 13    print_nt(nt);
 14  END;
 15  /
nt.(1) = 11
nt.(2) = 22
nt.(3) = 33
nt.(4) = 44
nt.(5) = 55
nt.(6) = 66
---
nt.(1) = 11
nt.(2) = 22
nt.(3) = 33
nt.(4) = 44
nt.(5) = 55
---
nt.(1) = 11
nt.(2) = 22
nt.(3) = 33
nt.(5) = 55
---
nt.(1) = 11
nt.(2) = 22
nt.(3) = 33
---

PL/SQL procedure successfully completed.

SQL>

TRIM Collection Method

TRIM is a procedure that deletes elements from the end of a varray or nested table.

This method has these forms:

TRIM removes one element from the end of the collection, if the collection has at least one element; otherwise, it raises the predefined exception SUBSCRIPT_BEYOND_COUNT.

TRIM(n) removes n elements from the end of the collection, if there are at least n elements at the end; otherwise, it raises the predefined exception SUBSCRIPT_BEYOND_COUNT.

TRIM operates on the internal size of a collection. That is, if DELETE deletes an element but keeps a placeholder for it, then TRIM considers the element to exist. Therefore, TRIM can delete a deleted element.

Example 6-25 TRIM Method with Nested Table

SQL> 
SQL> DECLARE
  2    nt nt_type := nt_type(11, 22, 33, 44, 55, 66);
  3  BEGIN
  4    print_nt(nt);
  5  
  6    nt.TRIM;       -- Trim last element
  7    print_nt(nt);
  8  
  9    nt.DELETE(4);  -- Delete fourth element
 10    print_nt(nt);
 11  
 12    nt.TRIM(2);    -- Trim last two elements
 13    print_nt(nt);
 14  END;
 15  /
nt.(1) = 11
nt.(2) = 22
nt.(3) = 33
nt.(4) = 44
nt.(5) = 55
nt.(6) = 66
---
nt.(1) = 11
nt.(2) = 22
nt.(3) = 33
nt.(4) = 44
nt.(5) = 55
---
nt.(1) = 11
nt.(2) = 22
nt.(3) = 33
nt.(5) = 55
---
nt.(1) = 11
nt.(2) = 22
nt.(3) = 33
---

PL/SQL procedure successfully completed.

SQL>

EXTEND Collection Method

EXTEND is a procedure that adds elements to the end of a varray or nested table.

The EXTEND method has these forms:

EXTEND appends one null element to the collection.

EXTEND(n) appends n null elements to the collection.

EXTEND(n,i) appends n copies of the ith element to the collection.

Example 6-26 EXTEND Method with Nested Table

SQL> 
SQL> DECLARE
  2    nt nt_type := nt_type(11, 22, 33);
  3  BEGIN
  4    print_nt(nt);
  5   
  6    nt.EXTEND(2,1);  -- Append two copies of first element
  7    print_nt(nt);
  8   
  9    nt.DELETE(5);    -- Delete fifth element
 10    print_nt(nt);
 11   
 12    nt.EXTEND;       -- Append one null element
 13    print_nt(nt);
 14  END;
 15  /
nt.(1) = 11
nt.(2) = 22
nt.(3) = 33
---
nt.(1) = 11
nt.(2) = 22
nt.(3) = 33
nt.(4) = 11
nt.(5) = 11
---
nt.(1) = 11
nt.(2) = 22
nt.(3) = 33
nt.(4) = 11
---
nt.(1) = 11
nt.(2) = 22
nt.(3) = 33
nt.(4) = 11
nt.(6) = NULL
---

PL/SQL procedure successfully completed.

SQL>

EXISTS Collection Method

EXISTS is a function that tells you whether the specified element of a varray or nested table exists.

EXISTS(n) returns TRUE if the nth element of the collection exists and FALSE otherwise. If n is out of range, EXISTS returns FALSE instead of raising the predefined exception SUBSCRIPT_OUTSIDE_LIMIT.

For a deleted element, EXISTS(n) returns FALSE, even if DELETE kept a placeholder for it.

Example 6-27 EXISTS Method with Nested Table

SQL> 
SQL> DECLARE
  2    TYPE NumList IS TABLE OF INTEGER;
  3    n NumList := NumList(1,3,5,7);
  4  BEGIN
  5    n.DELETE(2); -- Delete second element
  6   
  7    FOR i IN 1..6 LOOP
  8      IF n.EXISTS(i) THEN
  9        DBMS_OUTPUT.PUT_LINE('n(' || i || ') = ' || n(i));
 10      ELSE
 11        DBMS_OUTPUT.PUT_LINE('n(' || i || ') does not exist');
 12      END IF;
 13    END LOOP;
 14  END;
 15  /
n(1) = 1
n(2) does not exist
n(3) = 5
n(4) = 7
n(5) does not exist
n(6) does not exist

PL/SQL procedure successfully completed.

SQL>

FIRST and LAST Collection Methods

FIRST and LAST Methods for Associative Array

Example 6-28 FIRST and LAST Values for Associative Array Indexed by PLS_INTEGER

SQL> 
SQL> DECLARE
  2    TYPE aa_type_int IS TABLE OF INTEGER INDEX BY PLS_INTEGER;
  3    aa_int  aa_type_int;
  4   
  5    PROCEDURE print_first_and_last IS
  6    BEGIN
  7      DBMS_OUTPUT.PUT_LINE('FIRST = ' || aa_int.FIRST);
  8      DBMS_OUTPUT.PUT_LINE('LAST = ' || aa_int.LAST);
  9    END print_first_and_last;
 10   
 11  BEGIN
 12    aa_int(1) := 3;
 13    aa_int(2) := 6;
 14    aa_int(3) := 9;
 15    aa_int(4) := 12;
 16   
 17    DBMS_OUTPUT.PUT_LINE('Before deletions:');
 18    print_first_and_last;
 19   
 20    aa_int.DELETE(1);
 21    aa_int.DELETE(4);
 22   
 23    DBMS_OUTPUT.PUT_LINE('After deletions:');
 24    print_first_and_last;
 25  END;
 26  /
Before deletions:
FIRST = 1
LAST = 4
After deletions:
FIRST = 2
LAST = 3

PL/SQL procedure successfully completed.

SQL>

Example 6-29 FIRST and LAST Values for Associative Array Indexed by String

SQL> DECLARE
  2    TYPE aa_type_str IS TABLE OF INTEGER INDEX BY VARCHAR2(10);
  3    aa_str  aa_type_str;
  4   
  5    PROCEDURE print_first_and_last IS
  6    BEGIN
  7      DBMS_OUTPUT.PUT_LINE('FIRST = ' || aa_str.FIRST);
  8      DBMS_OUTPUT.PUT_LINE('LAST = ' || aa_str.LAST);
  9    END print_first_and_last;
 10   
 11  BEGIN
 12    aa_str('Z') := 26;
 13    aa_str('A') := 1;
 14    aa_str('K') := 11;
 15    aa_str('R') := 18;
 16   
 17    DBMS_OUTPUT.PUT_LINE('Before deletions:');
 18    print_first_and_last;
 19   
 20    aa_str.DELETE('A');
 21    aa_str.DELETE('Z');
 22   
 23    DBMS_OUTPUT.PUT_LINE('After deletions:');
 24    print_first_and_last;
 25  END;
 26  /
Before deletions:
FIRST = A
LAST = Z
After deletions:
FIRST = K
LAST = R

PL/SQL procedure successfully completed.

SQL>

FIRST and LAST Methods for Varray

Example 6-30 Printing Varray with FIRST and LAST in FOR LOOP

SQL> 
SQL> DECLARE
  2    TYPE team_type IS VARRAY(4) OF VARCHAR2(15);
  3    team team_type;
  4   
  5    PROCEDURE print_team (heading VARCHAR2)
  6    IS
  7    BEGIN
  8      DBMS_OUTPUT.PUT_LINE(heading);
  9   
 10      IF team IS NULL THEN
 11        DBMS_OUTPUT.PUT_LINE('Does not exist');
 12      ELSIF team.FIRST IS NULL THEN
 13        DBMS_OUTPUT.PUT_LINE('Has no members');
 14      ELSE
 15        FOR i IN team.FIRST..team.LAST LOOP
 16          DBMS_OUTPUT.PUT_LINE(i || '. ' || team(i));
 17        END LOOP;
 18      END IF;
 19   
 20      DBMS_OUTPUT.PUT_LINE('---'); 
 21    END;
 22    
 23  BEGIN 
 24    print_team('Team Status:');
 25   
 26    team := team_type();  -- Team is funded, but nobody is on it.
 27    print_team('Team Status:');
 28   
 29    team := team_type('John', 'Mary');  -- Put 2 members on team.
 30    print_team('Initial Team:');
 31   
 32    team := team_type('Arun', 'Amitha', 'Allan', 'Mae');  -- Change team.
 33    print_team('New Team:');
 34  END;
 35  /
Team Status:
Does not exist
---
Team Status:
Has no members
---
Initial Team:
1. John
2. Mary
---
New Team:
1. Arun
2. Amitha
3. Allan
4. Mae
---

PL/SQL procedure successfully completed.

SQL>

FIRST and LAST Methods for Nested Table

Example 6-31 Printing Nested Table with FIRST and LAST in FOR LOOP

SQL> 
SQL> DECLARE
  2    TYPE team_type IS TABLE OF VARCHAR2(15);
  3    team team_type;
  4   
  5    PROCEDURE print_team (heading VARCHAR2) IS
  6    BEGIN
  7      DBMS_OUTPUT.PUT_LINE(heading);
  8   
  9      IF team IS NULL THEN
 10        DBMS_OUTPUT.PUT_LINE('Does not exist');
 11      ELSIF team.FIRST IS NULL THEN
 12        DBMS_OUTPUT.PUT_LINE('Has no members');
 13      ELSE
 14        FOR i IN team.FIRST..team.LAST LOOP
 15          DBMS_OUTPUT.PUT(i || '. ');
 16          IF team.EXISTS(i) THEN
 17            DBMS_OUTPUT.PUT_LINE(team(i));
 18          ELSE
 19            DBMS_OUTPUT.PUT_LINE('(to be hired)');
 20          END IF;
 21        END LOOP;
 22      END IF;
 23   
 24      DBMS_OUTPUT.PUT_LINE('---'); 
 25    END;
 26    
 27  BEGIN 
 28    print_team('Team Status:');
 29   
 30    team := team_type();  -- Team is funded, but nobody is on it.
 31    print_team('Team Status:');
 32   
 33    team := team_type('Arun', 'Amitha', 'Allan', 'Mae');  -- Add members.
 34    print_team('Initial Team:');
 35   
 36    team.DELETE(2,3);  -- Remove 2nd and 3rd members.
 37    print_team('Current Team:');
 38  END;
 39  /
Team Status:
Does not exist
---
Team Status:
Has no members
---
Initial Team:
1. Arun
2. Amitha
3. Allan
4. Mae
---
Current Team:
1. Arun
2. (to be hired)
3. (to be hired)
4. Mae
---

PL/SQL procedure successfully completed.

SQL>

COUNT Collection Method

COUNT is a function that returns the number of elements in the collection (ignoring deleted elements, even if DELETE kept placeholders for them).

COUNT Method for Varray

For a varray, COUNT always equals LAST. If you increase or decrease the size of a varray (with the EXTEND or TRIM method), the value of COUNT changes.

Example 6-32 COUNT and LAST Values for Varray

SQL> 
SQL> DECLARE
  2    TYPE NumList IS VARRAY(10) OF INTEGER;
  3    n NumList := NumList(1,3,5,7);
  4   
  5    PROCEDURE print_count_and_last IS
  6    BEGIN
  7      DBMS_OUTPUT.PUT('n.COUNT = ' || n.COUNT || ', ');
  8      DBMS_OUTPUT.PUT_LINE('n.LAST = ' || n.LAST);
  9    END  print_count_and_last;
 10   
 11  BEGIN
 12    print_count_and_last;
 13   
 14    n.EXTEND(3);
 15    print_count_and_last;
 16   
 17    n.TRIM(5);
 18    print_count_and_last;
 19  END;
 20  /
n.COUNT = 4, n.LAST = 4
n.COUNT = 7, n.LAST = 7
n.COUNT = 2, n.LAST = 2

PL/SQL procedure successfully completed.

SQL>

COUNT Method for Nested Table

For a nested table, COUNT equals LAST unless you delete elements from the middle of the nested table, in which case COUNT is smaller than LAST.

Example 6-33 COUNT and LAST Values for Nested Table

SQL> 
SQL> DECLARE
  2    TYPE NumList IS TABLE OF INTEGER;
  3    n NumList := NumList(1,3,5,7);
  4   
  5    PROCEDURE print_count_and_last IS
  6    BEGIN
  7      DBMS_OUTPUT.PUT('n.COUNT = ' || n.COUNT || ', ');
  8      DBMS_OUTPUT.PUT_LINE('n.LAST = ' || n.LAST);
  9    END  print_count_and_last;
 10   
 11  BEGIN
 12    print_count_and_last;
 13   
 14    n.DELETE(3);  -- Delete third element
 15    print_count_and_last;
 16   
 17    n.EXTEND(2);  -- Add two null elements to end
 18    print_count_and_last;
 19   
 20    FOR i IN 1..8 LOOP
 21      IF n.EXISTS(i) THEN
 22        IF n(i) IS NOT NULL THEN
 23          DBMS_OUTPUT.PUT_LINE('n(' || i || ') = ' || n(i));
 24        ELSE
 25          DBMS_OUTPUT.PUT_LINE('n(' || i || ') = NULL');
 26        END IF;
 27      ELSE
 28        DBMS_OUTPUT.PUT_LINE('n(' || i || ') does not exist');
 29      END IF;
 30    END LOOP;
 31  END;
 32  /
n.COUNT = 4, n.LAST = 4
n.COUNT = 3, n.LAST = 4
n.COUNT = 5, n.LAST = 6
n(1) = 1
n(2) = 3
n(3) does not exist
n(4) = 7
n(5) = NULL
n(6) = NULL
n(7) does not exist
n(8) does not exist

PL/SQL procedure successfully completed.

SQL>

LIMIT Collection Method

LIMIT is a function that returns the maximum number of elements that the collection can have. If the collection has no maximum number of elements, LIMIT returns NULL. Only a varray has a maximum size.

Example 6-34 LIMIT and COUNT Values for Different Collection Types

SQL> 
SQL> DECLARE
  2    TYPE aa_type IS TABLE OF INTEGER INDEX BY PLS_INTEGER;
  3    aa aa_type;                          -- associative array
  4   
  5    TYPE va_type IS VARRAY(4) OF INTEGER;
  6    va  va_type := va_type(2,4);   -- varray
  7   
  8    TYPE nt_type IS TABLE OF INTEGER;
  9    nt  nt_type := nt_type(1,3,5);  -- nested table
 10   
 11  BEGIN
 12    aa(1):=3; aa(2):=6; aa(3):=9; aa(4):= 12;
 13   
 14    DBMS_OUTPUT.PUT('aa.COUNT = ');
 15    DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(aa.COUNT), 'NULL'));
 16   
 17    DBMS_OUTPUT.PUT('aa.LIMIT = ');
 18    DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(aa.LIMIT), 'NULL'));
 19   
 20    DBMS_OUTPUT.PUT('va.COUNT = ');
 21    DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(va.COUNT), 'NULL'));
 22   
 23    DBMS_OUTPUT.PUT('va.LIMIT = ');
 24    DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(va.LIMIT), 'NULL'));
 25   
 26    DBMS_OUTPUT.PUT('nt.COUNT = ');
 27    DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(nt.COUNT), 'NULL'));
 28   
 29    DBMS_OUTPUT.PUT('nt.LIMIT = ');
 30    DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(nt.LIMIT), 'NULL'));
 31  END;
 32  /
aa.COUNT = 4
aa.LIMIT = NULL
va.COUNT = 2
va.LIMIT = 4
nt.COUNT = 3
nt.LIMIT = NULL

PL/SQL procedure successfully completed.

SQL>

PRIOR and NEXT Collection Methods

PRIOR and NEXT are functions that let you move backward and forward in the collection (ignoring deleted elements, even if DELETE kept placeholders for them). These methods are useful for traversing sparse collections.

Given an index:

PRIOR returns the index of the preceding existing element of the collection, if one exists. Otherwise, PRIOR returns NULL.

For any collection c, c.PRIOR(c.FIRST) returns NULL.

NEXT returns the index of the succeeding existing element of the collection, if one exists. Otherwise, NEXT returns NULL.

For any collection c, c.NEXT(c.LAST) returns NULL.

The given index need not exist. However, if the collection c is a varray, and the index exceeds c.LIMIT, then:

c.PRIOR(index) returns c.LAST.

c.NEXT(index) returns NULL.

SQL> DECLARE
  2    TYPE Arr_Type IS VARRAY(10) OF NUMBER;
  3    v_Numbers Arr_Type := Arr_Type();
  4  BEGIN
  5    v_Numbers.EXTEND(4);
  6   
  7    v_Numbers (1) := 10;
  8    v_Numbers (2) := 20;
  9    v_Numbers (3) := 30;
 10    v_Numbers (4) := 40;
 11   
 12    DBMS_OUTPUT.PUT_LINE(NVL(v_Numbers.prior (3400), -1));
 13    DBMS_OUTPUT.PUT_LINE(NVL(v_Numbers.next (3400), -1));
 14  END;
 15  /
4
-1

PL/SQL procedure successfully completed.

SQL>

Example 6-35 PRIOR and NEXT Methods

SQL> 
SQL> DECLARE
  2    TYPE nt_type IS TABLE OF NUMBER;
  3    nt nt_type := nt_type(18, NULL, 36, 45, 54, 63);
  4   
  5  BEGIN
  6    nt.DELETE(4);
  7    DBMS_OUTPUT.PUT_LINE('nt(4) was deleted.');
  8   
  9    FOR i IN 1..7 LOOP
 10      DBMS_OUTPUT.PUT('nt.PRIOR(' || i || ') = ');
 11      DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(nt.PRIOR(i)), 'NULL'));
 12  
 13      DBMS_OUTPUT.PUT('nt.NEXT(' || i || ')  = ');
 14      DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(nt.NEXT(i)), 'NULL'));
 15    END LOOP;
 16  END;
 17  /
nt(4) was deleted.
nt.PRIOR(1) = NULL
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)  = NULL
nt.PRIOR(7) = 6
nt.NEXT(7)  = NULL

PL/SQL procedure successfully completed.

SQL>

Example 6-36 Printing Elements of Sparse Nested Table

SQL> 
SQL> DECLARE
  2    TYPE NumList IS TABLE OF NUMBER;
  3    n NumList := NumList(1, 2, NULL, NULL, 5, NULL, 7, 8, 9, NULL);
  4    idx INTEGER;
  5   
  6  BEGIN
  7    DBMS_OUTPUT.PUT_LINE('First to last:');
  8    idx := n.FIRST;
  9    WHILE idx IS NOT NULL LOOP
 10      DBMS_OUTPUT.PUT('n(' || idx || ') = ');
 11      DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(n(idx)), 'NULL'));
 12      idx := n.NEXT(idx);
 13    END LOOP;
 14      
 15    DBMS_OUTPUT.PUT_LINE('--------------');
 16   
 17    DBMS_OUTPUT.PUT_LINE('Last to first:');
 18    idx := n.LAST;
 19    WHILE idx IS NOT NULL LOOP
 20      DBMS_OUTPUT.PUT('n(' || idx || ') = ');
 21      DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(n(idx)), 'NULL'));
 22      idx := n.PRIOR(idx);
 23    END LOOP;
 24  END;
 25  /
First to last:
n(1) = 1
n(2) = 2
n(3) = NULL
n(4) = NULL
n(5) = 5
n(6) = NULL
n(7) = 7
n(8) = 8
n(9) = 9
n(10) = NULL
--------------
Last to first:
n(10) = NULL
n(9) = 9
n(8) = 8
n(7) = 7
n(6) = NULL
n(5) = 5
n(4) = NULL
n(3) = NULL
n(2) = 2
n(1) = 1

PL/SQL procedure successfully completed.

SQL>

Collection Types Defined in Package Specifications

Example 6-37 Identically Defined Package and Local Collection Types

SQL> CREATE OR REPLACE PACKAGE pkg AS
  2    TYPE NumList IS TABLE OF NUMBER;
  3    PROCEDURE print_numlist (nums NumList);
  4  END pkg;
  5  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY pkg AS
  2    PROCEDURE print_numlist (nums NumList) IS
  3    BEGIN
  4      FOR i IN nums.FIRST..nums.LAST LOOP
  5        DBMS_OUTPUT.PUT_LINE(nums(i));
  6      END LOOP;
  7    END;
  8  END pkg;
  9  /

Package body created.

SQL> DECLARE
  2    TYPE NumList IS TABLE OF NUMBER;  -- local type identical to package type
  3    n1 pkg.NumList := pkg.NumList(2,4);  -- package type
  4    n2     NumList :=     NumList(6,8);  -- local type
  5  BEGIN
  6    pkg.print_numlist(n1);  -- succeeds
  7    pkg.print_numlist(n2);  -- fails
  8  END;
  9  /
  pkg.print_numlist(n2);  -- fails
  *
ERROR at line 7:
ORA-06550: line 7, column 3:
PLS-00306: wrong number or types of arguments in call to 'PRINT_NUMLIST'
ORA-06550: line 7, column 3:
PL/SQL: Statement ignored


SQL>

Example 6-38 Identically Defined Package and Standalone Collection Types

SQL> CREATE OR REPLACE TYPE NumList IS TABLE OF NUMBER;
  2    -- standalone collection type identical to package type
  3  /

Type created.

SQL> DECLARE
  2    n1 pkg.NumList := pkg.NumList(2,4);  -- package type
  3    n2     NumList :=     NumList(6,8);  -- standalone type
  4   
  5  BEGIN
  6    pkg.print_numlist(n1);  -- succeeds
  7    pkg.print_numlist(n2);  -- fails
  8  END;
  9  /
  pkg.print_numlist(n2);  -- fails
  *
ERROR at line 7:
ORA-06550: line 7, column 3:
PLS-00306: wrong number or types of arguments in call to 'PRINT_NUMLIST'
ORA-06550: line 7, column 3:
PL/SQL: Statement ignored


SQL>