Skip Headers
Oracle® Database PL/SQL Language Reference
11g Release 2 (11.2)

Part Number E10472-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

5 PL/SQL Collections and Records

This chapter explains how to create and use PL/SQL collection and record variables. These composite variables have internal components that you can treat as individual variables. You can pass composite variables to subprograms as parameters.

To create a collection or record variable, you first define a collection or record type, and then you declare a variable of that type. In this book, collection or record means both the type and the variables of that type, unless otherwise noted.

In a collection, the internal components are always of the same data type, and are called elements. You access each element by its unique subscript. Lists and arrays are classic examples of collections.

In a record, the internal components can be of different data types, and are called fields. You access each field with this syntax: record_name.field_name. A record variable can hold a table row, or some columns from a table row. Each record field corresponds to a table column.

Collection Topics

Record Topics

PL/SQL Collection Type Characteristics

PL/SQL has three collection types, whose characteristics are summarized in Table 5-1.

Table 5-1 Characteristics of PL/SQL Collection Types

Collection Type Number of Elements Subscript Type Dense or Sparse Where Created Can Be ADT Attribute

Associative array (or index-by table)

Unbounded

String or integer

Either

Only in PL/SQL block

No

Nested table

Unbounded

Integer

Starts dense, can become sparse

Either in PL/SQL block or at schema level

Yes

Variable-size array (varray)

Bounded

Integer

Always dense

Either in PL/SQL block or at schema level

Yes


Unbounded means that, theoretically, there is no limit to the number of elements in the collection. Actually, there is a limit, but it is very high—for details, see "Collection Element References".

Dense means that the collection has no gaps between elements—every element between the first and last element is defined and has a value (which can be NULL).

A collection that is created in a PL/SQL block (with the syntax in "Collection") is available only in that block. A nested table type or varray type that is created at schema level (with the "CREATE TYPE Statement") is stored in the database, and you can manipulate it with SQL statements.

A collection has only one dimension, but you can model a multidimensional collection by creating a collection whose elements are also collections. For examples, see "Multidimensional Collections".

If you have code or business logic that uses another language, you can usually translate the array and set types of that language directly to PL/SQL collection types. For example:

Topics:

See Also:

Oracle Database SQL Language Reference for information about the CAST function, which converts one SQL data type or collection-typed value into another SQL data type or collection-typed value.

Associative Arrays

An associative array (formerly called an index-by table) is a set of key-value pairs. Each key is unique, and is used to locate the corresponding value. The key can be either an integer or a string.

Using a key-value pair for the first time adds that pair to the associative array. Using the same key with a different value changes the value.

Example 5-1 declares an associative array that is indexed by a string, populates it, and prints it.

Example 5-1 Declaring and Populating Associative Array Indexed by String

DECLARE
  -- Associative array indexed by string:
  
  TYPE population IS TABLE OF NUMBER  -- Associative array type
    INDEX BY VARCHAR2(64);
  
  city_population  population;        -- Associative array variable
  i                VARCHAR2(64);
  
BEGIN
  -- Add elements to associative array:
 
  city_population('Smallville')  := 2000;
  city_population('Midland')     := 750000;
  city_population('Megalopolis') := 1000000;
 
  -- Change value associated with key 'Smallville':
 
  city_population('Smallville') := 2001;
 
  -- Print associative array:
 
  i := city_population.FIRST;
 
  WHILE i IS NOT NULL LOOP
    DBMS_Output.PUT_LINE
      ('Population of ' || i || ' is ' || TO_CHAR(city_population(i)));
    i := city_population.NEXT(i);
   END LOOP;
END;
/

Result:

Population of Megalopolis is 1000000
Population of Midland is 750000
Population of Smallville is 2001

Like a database table, an associative array holds a data set of arbitrary size, and you can access its elements without knowing their positions in the array. An associative array does not need the disk space or network operations of a database table, but an associative array cannot be manipulated by SQL statements (such as INSERT and DELETE).

An associative array is intended for temporary data storage. To make an associative array persistent for the life of a database session, declare the associative array (the type and the variable of that type) in a package, and assign values to its elements in the package body.

An associative array is appropriate for:

  • A relatively small lookup table, where the collection can be constructed in memory each time a subprogram is invoked or a package is initialized

  • Passing collections to and from the database server

    PL/SQL automatically converts between host arrays and associative arrays that use numeric key values. The most efficient way to pass collections to and from the database server is to set up data values in associative arrays, and then use those associative arrays with bulk constructs (the FORALL statement or BULK COLLECT clause).

With the Oracle Call Interface (OCI) or the Oracle Precompilers, you can bind host arrays to associative arrays declared as the formal parameters of a subprogram. That lets you pass host arrays to stored functions and procedures.

NLS Parameter Values Can Affect String Keys of Associative Arrays

Associative arrays that are indexed by strings can be affected by National Language Support (NLS) parameters such as NLS_SORT, NLS_COMP, and NLS_DATE_FORMAT.

As Example 5-1 shows, string keys of an associative array are not stored in creation order, but in sorted order. Sorted order is determined by the initialization parameters NLS_SORT and NLS_COMP. If you change the setting of either of these parameters after populating an associated array, and then try to traverse the array, you might get an error when using a collection method such as NEXT or PRIOR. If you must change these settings during your session, set them back to their original values before performing further operations on associative arrays that are indexed by strings.

When you declare an associative array that is indexed by strings, the string type in the declaration must be VARCHAR2 or one of its subtypes. However, the key values with which you populate the array can be of any data type that can be converted to VARCHAR2 by the TO_CHAR function.

If you use key values of data types other than VARCHAR2 and its subtypes, ensure thatthese key values will be consistent and unique even if the settings of initialization parameters change. For example:

  • Do not use TO_CHAR(SYSDATE) as a key value. If the NLS_DATE_FORMAT initialization parameter setting changes, array_element(TO_CHAR(SYSDATE)) might return a different result.

  • Two different NVARCHAR2 values might be converted to the same VARCHAR2 value (containing question marks instead of certain national characters), in which case array_element(national_string1) and array_element(national_string2) would refer to the same element.

  • Two CHAR or VARCHAR2 values that differ only in case, accented characters, or punctuation characters might also be considered the same if the value of the NLS_SORT initialization parameter ends in _CI (case-insensitive comparisons) or _AI (accent- and case-insensitive comparisons).

When you pass an associative array as a parameter to a remote database using a database link, the two databases can have different globalization settings. When the remote database uses a collection method such as FIRST or NEXT, it uses its own character order, which might be different from the order where the collection originated. If character set differences mean that two keys that were unique are not unique on the remote database, the program raises a VALUE_ERROR exception.

See Also:

Oracle Database Globalization Support Guide for information about linguistic sort parameters

Nested Tables

Conceptually, a nested table is like a one-dimensional array with an arbitrary number of elements.

In the database, a nested table is a column type that holds a set of values. The database stores the rows of a nested table in no particular order. When you retrieve a nested table from the database into a PL/SQL variable, the rows are given consecutive subscripts starting at 1. These subscripts give you array-like access to individual rows.

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 (however, a maximum limit is imposed—see "Collection Element References").

  • An array is always dense (that is, it always has consecutive subscripts). A nested array is dense initially, but it can become sparse, because you can delete elements from it.

Figure 5-1 shows the important differences between a nested table and an array.

Figure 5-1 Array and Nested Table

Array and Nested Table
Description of "Figure 5-1 Array and Nested Table"

A nested table can be stored in a database column; therefore, you can use a nested table to simplify SQL operations in which you join a single-column table with a larger table.

You cannot rely on the order and subscripts of a nested table remaining stable as the nested table is stored in and retrieved from the database, because the order and subscripts are not preserved in the database.

A nested table is appropriate when:

  • Index values are not consecutive.

  • There is no set number of index values.

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

    Nested table data is stored in a separate store table, a system-generated database table. When you access a nested table, the database joins the nested table with its store table. This makes nested tables suitable for queries and updates that affect only some elements of the collection.

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

The amount of memory allocated to a nested table can increase or decrease dynamically. As you delete elements, memory is freed page by page. If you delete the entire table, all the memory is freed.

Variable-Size Arrays (Varrays)

A variable-size array (varray) is an item of the data type VARRAY. A varray has a maximum size, which you specify in its type definition. A varray can contain a varying number of elements, from zero (when empty) to the maximum size. A varray index has a fixed lower bound of 1 and an extensible upper bound. To access an element of a varray, you use standard subscripting syntax.

Figure 5-2 shows a varray named Grades, which has maximum size 10 and contains seven elements. The current upper bound for Grades is 7, but you can increase it to the maximum of 10. Grades(n) references the nth element of Grades.

Figure 5-2 Varray of Size 10

Varray of Size 10
Description of "Figure 5-2 Varray of Size 10"

When stored in the database, a varray keeps its ordering and subscripts.

A varray is stored as a single object. If a varray is less than 4 KB, it is stored inside the table of which it is a column; otherwise, it is stored outside the table but in the same tablespace.

You must store or retrieve all elements of a varray at the same time, which is appropriate when operating on all the elements simultaneously, but might be impractical for large numbers of elements.

A varray is appropriate when:

  • You know the number of elements in advance.

  • The elements are usually accessed sequentially.

Collection Type Definitions

To create a collection, you define a collection type and then declare variables of that type.

You can define a collection type either at schema level, inside a package, or inside a PL/SQL block. A collection type created at schema level is a standalone stored type. You create it with the CREATE TYPE statement. It is stored in the database until you drop it with the DROP TYPE statement.

A collection type created inside a package is a packaged type. It is stored in the database until you drop the package with the DROP PACKAGE statement.

A type created inside a PL/SQL block is available only inside that block, and is stored in the database only if that block is nested in a standalone or packaged subprogram.

Collections follow the same scoping and instantiation rules as other types and variables. Collections are instantiated when you enter a block or subprogram, and cease to exist when you exit. In a package, collections are instantiated when you first reference the package and cease to exist when you end the database session.

You can define TABLE and VARRAY types in the declarative part of any PL/SQL block, subprogram, or package using a TYPE definition.

For nested tables and varrays declared in PL/SQL, the element type of the table or varray can be any PL/SQL data type except REF CURSOR.

When defining a VARRAY type, you must specify its maximum size with a positive integer. In this example, you define a type that stores up to 366 dates:

DECLARE
   TYPE Calendar IS VARRAY(366) OF DATE;

Associative arrays let you insert elements using arbitrary key values. The keys need not be consecutive.

The key data type can be PLS_INTEGER, VARCHAR2, or one of VARCHAR2 subtypes VARCHAR, STRING, or LONG.

You must specify the length of a VARCHAR2-based key, except for LONG which is equivalent to declaring a key type of VARCHAR2(32760). The types RAW, LONG RAW, ROWID, CHAR, and CHARACTER are not allowed as keys for an associative array. The LONG and LONG RAW data types are supported only for backward compatibility; see "LONG and LONG RAW Data Types" for more information.

An initialization clause is not allowed. There is no constructor notation for associative arrays. When you reference an element of an associative array that uses a VARCHAR2-based key, you can use other types, such as DATE or TIMESTAMP, if they can be converted to VARCHAR2 with the TO_CHAR function.

Associative arrays can store data using a primary key value as the index, where the key values are not sequential. Example 5-2 creates a single element in an associative array, with a subscript of 100 rather than 1.

Example 5-2 Declaring an Associative Array

DECLARE
   TYPE EmpTabTyp IS TABLE OF employees%ROWTYPE
      INDEX BY PLS_INTEGER;
   emp_tab EmpTabTyp;
BEGIN
   /* Retrieve employee record. */
   SELECT * INTO emp_tab(100) FROM employees
     WHERE employee_id = 100;
END;
/

Collection Variable Declarations

After defining a collection type, you declare variables of that type. You use the type name in the declaration, the same as with predefined types such as NUMBER.

Example 5-3 Declaring Nested Tables, Varrays, and Associative Arrays

DECLARE
   TYPE nested_type IS TABLE OF VARCHAR2(30);
   TYPE varray_type IS VARRAY(5) OF INTEGER;
   TYPE assoc_array_num_type
     IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
   TYPE assoc_array_str_type
     IS TABLE OF VARCHAR2(32) INDEX BY PLS_INTEGER;
   TYPE assoc_array_str_type2
     IS TABLE OF VARCHAR2(32) INDEX BY VARCHAR2(64);
   v1 nested_type;
   v2 varray_type;
   v3 assoc_array_num_type;
   v4 assoc_array_str_type;
   v5 assoc_array_str_type2;
BEGIN
-- an arbitrary number of strings can be inserted v1
   v1 := nested_type('Shipping','Sales','Finance','Payroll'); 
   v2 := varray_type(1, 2, 3, 4, 5); -- Up to 5 integers
   v3(99) := 10; -- Just start assigning to elements
   v3(7) := 100; -- Subscripts can be any integer values
   v4(42) := 'Smith'; -- Just start assigning to elements
   v4(54) := 'Jones'; -- Subscripts can be any integer values
   v5('Canada') := 'North America';
     -- Just start assigning to elements
   v5('Greece') := 'Europe';
     -- Subscripts can be string values
END;
/

As shown in Example 5-4, you can use %TYPE to specify the data type of a previously declared collection, so that changing the definition of the collection automatically updates other variables that depend on the number of elements or the element type.

Example 5-4 Declaring Collections with %TYPE

DECLARE
  TYPE few_depts  IS VARRAY(10)  OF VARCHAR2(30);
  TYPE many_depts IS VARRAY(100) OF VARCHAR2(64);
  some_depts few_depts;

  /* If the type of some_depts changes from few_depts to many_depts,
     local_depts and global_depts will use the same type 
     when this block is recompiled */

  local_depts  some_depts%TYPE;
  global_depts some_depts%TYPE;
BEGIN
  NULL;
END;
/

You can declare collections as the formal parameters of subprograms. That way, you can pass collections to stored subprograms and from one subprogram to another. Example 5-5 declares a nested table as a parameter of a packaged subprogram.

Example 5-5 Declaring a Procedure Parameter as a Nested Table

CREATE PACKAGE personnel AS
   TYPE staff_list IS TABLE OF employees.employee_id%TYPE;
   PROCEDURE award_bonuses (empleos_buenos IN staff_list);
END personnel;
/

CREATE PACKAGE BODY personnel AS
 PROCEDURE award_bonuses (empleos_buenos staff_list) IS
  BEGIN
    FOR i IN empleos_buenos.FIRST..empleos_buenos.LAST
    LOOP
     UPDATE employees SET salary = salary + 100 
         WHERE employees.employee_id = empleos_buenos(i);
   END LOOP;
  END;
 END;
/

To invoke personnel.award_bonuses from outside the package, you declare a variable of type personnel.staff_list and pass that variable as the parameter.

Example 5-6 Invoking a Procedure with a Nested Table Parameter

DECLARE
  good_employees personnel.staff_list;
BEGIN
  good_employees :=  personnel.staff_list(100, 103, 107);
  personnel.award_bonuses (good_employees);
END;
/

You can also specify a collection type in the RETURN clause of a function specification.

To specify the element type, you can use %TYPE, which provides the data type of a variable or database column. Also, you can use %ROWTYPE, which provides the rowtype of a cursor or database table. See Example 5-7 and Example 5-8.

Example 5-7 Specifying Collection Element Types with %TYPE and %ROWTYPE

DECLARE
-- Nested table type that can hold an arbitrary number
--   of employee IDs.
-- The element type is based on a column from the EMPLOYEES table. 
-- You need not know whether the ID is a number or a string.
   TYPE EmpList IS TABLE OF employees.employee_id%TYPE;
-- Declare a cursor to select a subset of columns.
   CURSOR c1 IS SELECT employee_id FROM employees;
-- Declare an Array type that can hold information
--   about 10 employees.
-- The element type is a record that contains all the same
-- fields as the EMPLOYEES table.
   TYPE Senior_Salespeople IS VARRAY(10) OF employees%ROWTYPE;
-- Declare a cursor to select a subset of columns.
   CURSOR c2 IS SELECT first_name, last_name FROM employees;
-- Array type that can hold a list of names. The element type
-- is a record that contains the same fields as the cursor
-- (that is, first_name and last_name).
   TYPE NameList IS VARRAY(20) OF c2%ROWTYPE;
BEGIN
   NULL;
END;
/

Example 5-8 uses a RECORD type to specify the element type. See "Record Definitions and Declarations".

Example 5-8 VARRAY of Records

DECLARE TYPE name_rec
  IS RECORD ( first_name VARCHAR2(20), last_name VARCHAR2(25));
   TYPE names IS VARRAY(250) OF name_rec;
BEGIN
   NULL;
END;
/

You can also impose a NOT NULL constraint on the element type, as shown in Example 5-9.

Example 5-9 NOT NULL Constraint on Collection Elements

DECLARE TYPE EmpList
  IS TABLE OF employees.employee_id%TYPE NOT NULL;
  v_employees EmpList := EmpList(100, 150, 160, 200);
BEGIN
   v_employees(3) := NULL; -- assigning NULL raises an exception
END;
/

Result:

v_employees(3) := NULL; -- assigning NULL raises an exception
                     *
ERROR at line 5:
ORA-06550: line 5, column 22:
PLS-00382: expression is of wrong type
ORA-06550: line 5, column 4:
PL/SQL: Statement ignored

Collection Initialization

Until you initialize it, a nested table or varray is atomically null; the collection itself is null, not its elements. To initialize a nested table or varray, you use a constructor, a system-defined function with the same name as the collection type. This function constructs collections from the elements passed to it.

You must explicitly call a constructor for each varray and nested table variable. Associative arrays, the third kind of collection, do not use constructors. Constructor calls are allowed wherever function calls are allowed.

Example 5-10 initializes a nested table using a constructor, which looks like a function with the same name as the collection type.

Example 5-10 Constructor for a Nested Table

DECLARE
   TYPE dnames_tab IS TABLE OF VARCHAR2(30);
   dept_names dnames_tab;
BEGIN
   dept_names := dnames_tab('Shipping','Sales','Finance','Payroll');
END;
/

Because a nested table does not have a declared size, you can put as many elements in the constructor as necessary.

Example 5-11 initializes a varray using a constructor, which looks like a function with the same name as the collection type.

Example 5-11 Constructor for a Varray

DECLARE
-- In the varray, put an upper limit on the number of elements
   TYPE dnames_var IS VARRAY(20) OF VARCHAR2(30);
   dept_names dnames_var;
BEGIN
-- Because dnames is declared as VARRAY(20),
-- you can put up to 20 elements in the constructor
   dept_names := dnames_var('Shipping','Sales','Finance','Payroll');
END;
/

Unless you impose the NOT NULL constraint in the type declaration, you can pass null elements to a constructor as in Example 5-12.

Example 5-12 Collection Constructor Including Null Elements

DECLARE
   TYPE dnames_tab IS TABLE OF VARCHAR2(30);
   dept_names dnames_tab;
   TYPE dnamesNoNulls_type IS TABLE OF VARCHAR2(30) NOT NULL;
BEGIN
   dept_names := dnames_tab('Shipping', NULL,'Finance', NULL);
-- If dept_names were of type dnamesNoNulls_type,
--  you could not include null values in the constructor
END;
/

You can initialize a collection in its declaration, which is a good programming practice, as shown in Example 5-13. In this case, you can invoke the collection's EXTEND method to add elements later.

Example 5-13 Combining Collection Declaration and Constructor

DECLARE
   TYPE dnames_tab IS TABLE OF VARCHAR2(30);
   dept_names dnames_tab :=
     dnames_tab('Shipping','Sales','Finance','Payroll');
BEGIN
   NULL;
END;
/

If you call a constructor without arguments, you get an empty but non-null collection as shown in Example 5-14.

Example 5-14 Empty Varray Constructor

DECLARE
   TYPE dnames_var IS VARRAY(20) OF VARCHAR2(30);
   dept_names dnames_var;
BEGIN
   IF dept_names IS NULL THEN
      DBMS_OUTPUT.PUT_LINE
        ('Before initialization, the varray is null.');
-- While the varray is null, you cannot check its COUNT attribute.
--   DBMS_OUTPUT.PUT_LINE
--     ('It has ' || dept_names.COUNT || ' elements.');
   ELSE
      DBMS_OUTPUT.PUT_LINE
        ('Before initialization, the varray is not null.');
   END IF;
   dept_names := dnames_var(); -- initialize empty varray 
   IF dept_names IS NULL THEN
      DBMS_OUTPUT.PUT_LINE
        ('After initialization, the varray is null.');
   ELSE
      DBMS_OUTPUT.PUT_LINE
        ('After initialization, the varray is not null.');
      DBMS_OUTPUT.PUT_LINE
        ('It has ' || dept_names.COUNT || ' elements.');
   END IF;
END;
/

Collection Element References

Every reference to an element includes a collection name and a subscript enclosed in parentheses. The subscript determines which element is processed. To reference an element, you specify its subscript using this syntax:

collection_name (subscript)

where subscript is an expression that usually yields an integer, or a VARCHAR2 for associative arrays declared with strings as keys.

The allowed subscript ranges are:

Example 5-15 shows how to reference an element in a nested table.

Example 5-15 Referencing a Nested Table Element

DECLARE
  TYPE Roster IS TABLE OF VARCHAR2(15);
  names Roster := 
    Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh');
  PROCEDURE verify_name(the_name VARCHAR2) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(the_name);
  END;
BEGIN
  FOR i IN names.FIRST .. names.LAST
  LOOP
      IF names(i) = 'J Hamil' THEN
        DBMS_OUTPUT.PUT_LINE(names(i));
          -- reference to nested table element
      END IF;
  END LOOP;
  verify_name(names(3));
    -- procedure call that references element
END;
/

Example 5-16 shows how you can reference the elements of an associative array in a function call.

Example 5-16 Referencing an Element of an Associative Array

DECLARE
  TYPE sum_multiples IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
  n  PLS_INTEGER := 5;   -- number of multiples to sum for display
  sn PLS_INTEGER := 10;  -- number of multiples to sum
  m  PLS_INTEGER := 3;   -- multiple
FUNCTION get_sum_multiples
  (multiple IN PLS_INTEGER, num IN PLS_INTEGER)
  RETURN sum_multiples IS
  s sum_multiples;
  BEGIN
      FOR i IN 1..num LOOP
        s(i) := multiple * ((i * (i + 1)) / 2);
           -- sum of multiples
      END LOOP;
    RETURN s;
  END get_sum_multiples;
BEGIN
-- invoke function to retrieve
-- element identified by subscript (key)
  DBMS_OUTPUT.PUT_LINE
    ('Sum of the first ' || TO_CHAR(n) || ' multiples of ' ||
     TO_CHAR(m) || ' is ' || TO_CHAR(get_sum_multiples (m, sn)(n)));
END;
/

Assignments to Collections

One collection can be assigned to another by an INSERT, UPDATE, FETCH, or SELECT statement, an assignment statement, or a subprogram call. You can assign the value of an expression to a specific element in a collection using the syntax:

collection_name (subscript) := expression;

where expression yields a value of the type specified for elements in the collection type definition.

You can use operators such as SET, MULTISET UNION, MULTISET INTERSECT, and MULTISET EXCEPT to transform nested tables as part of an assignment statement.

Assigning a value to a collection element can raise exceptions, for example:

For more information about collection exceptions, see "Collection Exceptions", Example 5-38, and "Predefined Exceptions".

Example 5-17 shows that collections must have the same data type for an assignment to work. Having the same element type is not enough.

Example 5-17 Data Type Compatibility for Collection Assignment

DECLARE
  TYPE last_name_typ IS VARRAY(3) OF VARCHAR2(64);
  TYPE surname_typ IS VARRAY(3) OF VARCHAR2(64);

  -- These first two variables have the same data type.
  group1 last_name_typ := last_name_typ('Jones','Wong','Marceau');
  group2 last_name_typ := last_name_typ('Klein','Patsos','Singh');

  -- This third variable has a similar declaration,
  -- but is a different type.
  group3 surname_typ := surname_typ('Trevisi','Macleod','Marquez');
BEGIN
  -- Allowed because they have the same data type
  group1 := group2;
  -- Not allowed because they have different data types
  --   group3 := group2; -- raises an exception
END;
/

If you assign an atomically null nested table or varray to a second nested table or varray, the second collection must be reinitialized, as shown in Example 5-18. In the same way, assigning a NULL value to a collection makes it atomically null.

Example 5-18 Assigning a Null Value to a Nested Table

DECLARE
  TYPE dnames_tab IS TABLE OF VARCHAR2(30);

  -- This nested table has some values
  dept_names dnames_tab :=
    dnames_tab('Shipping','Sales','Finance','Payroll');

  -- This nested table is not initialized ("atomically null").
  empty_set dnames_tab;
BEGIN
  -- At first, the initialized variable is not null.
  IF dept_names IS NOT NULL THEN
    DBMS_OUTPUT.PUT_LINE('OK, at first dept_names is not null.');
  END IF;

  -- Then assign a null nested table to it.
  dept_names := empty_set;

  -- Now it is null.
  IF dept_names IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('OK, now dept_names has become null.');
  END IF;

  -- Use another constructor to give it some values.
  dept_names := dnames_tab('Shipping','Sales','Finance','Payroll');
END;
/

Result:

OK, at first dept_names is not null.
OK, now dept_names has become null.

Example 5-19 shows some ANSI-standard operators that you can apply to nested tables.

Example 5-19 Assigning Nested Tables with Set Operators

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;

  -- The results might be in a different order than you expect.
  -- Do not rely on the order of elements in nested tables.

  PROCEDURE print_nested_table(the_nt nested_typ) IS
    output VARCHAR2(128);
  BEGIN
    IF the_nt IS NULL THEN
      DBMS_OUTPUT.PUT_LINE('Result: <NULL>');
      RETURN;
    END IF;
    IF the_nt.COUNT = 0 THEN
      DBMS_OUTPUT.PUT_LINE('Result: empty set');
      RETURN;
    END IF;
    FOR i IN the_nt.FIRST .. the_nt.LAST
    LOOP
      output := output || the_nt(i) || ' ';
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Result: ' || output);
  END;
BEGIN
  answer := nt1 MULTISET UNION nt4; -- (1,2,3,1,2,4)
  print_nested_table(answer);
  answer := nt1 MULTISET UNION nt3; -- (1,2,3,2,3,1,3)
  print_nested_table(answer);
  answer := nt1 MULTISET UNION DISTINCT nt3; -- (1,2,3)
  print_nested_table(answer);
  answer := nt2 MULTISET INTERSECT nt3; -- (3,2,1)
  print_nested_table(answer);
  answer := nt2 MULTISET INTERSECT DISTINCT nt3; -- (3,2,1)
  print_nested_table(answer);
  answer := SET(nt3); -- (2,3,1)
  print_nested_table(answer);
  answer := nt3 MULTISET EXCEPT nt2; -- (3)
  print_nested_table(answer);
  answer := nt3 MULTISET EXCEPT DISTINCT nt2; -- ()
  print_nested_table(answer);
END;
/

Result:

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

Example 5-20 shows an assignment to a VARRAY of records with an assignment statement.

Example 5-20 Assigning Values to VARRAYs with Complex Data Types

DECLARE
  TYPE emp_name_rec is RECORD (
    firstname    employees.first_name%TYPE,
    lastname     employees.last_name%TYPE,
    hiredate     employees.hire_date%TYPE
  );
    
  -- Array type that can hold information 10 employees
  TYPE EmpList_arr IS VARRAY(10) OF emp_name_rec;
  SeniorSalespeople EmpList_arr;
   
  -- Declare a cursor to select a subset of columns.
  CURSOR c1 IS
    SELECT first_name, last_name, hire_date FROM employees;
  Type NameSet IS TABLE OF c1%ROWTYPE;
  SeniorTen NameSet;
  EndCounter NUMBER := 10;
   
BEGIN
  SeniorSalespeople := EmpList_arr();
  SELECT first_name, last_name, hire_date
    BULK COLLECT INTO SeniorTen
    FROM employees
    WHERE job_id = 'SA_REP'
    ORDER BY hire_date;
  IF SeniorTen.LAST > 0 THEN
    IF SeniorTen.LAST < 10 THEN EndCounter := SeniorTen.LAST; 
    END IF;
    FOR i in 1..EndCounter LOOP
      SeniorSalespeople.EXTEND(1);
      SeniorSalespeople(i) := SeniorTen(i);
      DBMS_OUTPUT.PUT_LINE(SeniorSalespeople(i).lastname || ', ' 
       || SeniorSalespeople(i).firstname || ', ' ||
       SeniorSalespeople(i).hiredate);
    END LOOP;
  END IF;
END;
/

Result:

King, Janette, 30-JAN-04
Sully, Patrick, 04-MAR-04
Abel, Ellen, 11-MAY-04
McEwen, Allan, 01-AUG-04
Tucker, Peter, 30-JAN-05
Smith, Lindsey, 10-MAR-05
Ozer, Lisa, 11-MAR-05
Hutton, Alyssa, 19-MAR-05
Bernstein, David, 24-MAR-05
Hall, Peter, 20-AUG-05

Example 5-21 shows an assignment to a nested table of records with a FETCH statement.

Example 5-21 Assigning Values to Tables with Complex Data Types

DECLARE
  TYPE emp_name_rec is RECORD (
    firstname    employees.first_name%TYPE,
    lastname     employees.last_name%TYPE,
    hiredate     employees.hire_date%TYPE
  );
    
  -- Table type that can hold information about employees
  TYPE EmpList_tab IS TABLE OF emp_name_rec;
  SeniorSalespeople EmpList_tab;   
   
  -- Declare a cursor to select a subset of columns.
  CURSOR c1 IS 
    SELECT first_name, last_name, hire_date FROM employees;
  EndCounter NUMBER := 10;
  TYPE EmpCurTyp IS REF CURSOR;
  emp_cv EmpCurTyp; 
   
BEGIN
  OPEN emp_cv FOR SELECT first_name, last_name, hire_date
   FROM employees 
   WHERE job_id = 'SA_REP' ORDER BY hire_date;

  FETCH emp_cv BULK COLLECT INTO SeniorSalespeople;
  CLOSE emp_cv;

  -- for this example, display a maximum of ten employees
  IF SeniorSalespeople.LAST > 0 THEN
    IF SeniorSalespeople.LAST < 10 THEN
      EndCounter := SeniorSalespeople.LAST; 
    END IF;
    FOR i in 1..EndCounter LOOP
      DBMS_OUTPUT.PUT_LINE (
        SeniorSalespeople(i).lastname || ', '  || 
        SeniorSalespeople(i).firstname || ', ' || 
        SeniorSalespeople(i).hiredate);
    END LOOP;
  END IF;
END;
/

Result:

King, Janette, 30-JAN-04
Sully, Patrick, 04-MAR-04
Abel, Ellen, 11-MAY-04
McEwen, Allan, 01-AUG-04
Tucker, Peter, 30-JAN-05
Smith, Lindsey, 10-MAR-05
Ozer, Lisa, 11-MAR-05
Hutton, Alyssa, 19-MAR-05
Bernstein, David, 24-MAR-05
Hall, Peter, 20-AUG-05

Collection Comparisons

You can check whether a collection is null. Comparisons such as greater than, less than, and so on are not allowed. This restriction also applies to implicit comparisons. For example, collections cannot appear in a DISTINCT, GROUP BY, or ORDER BY list.

If you want to do such comparison operations, you must define your own notion of what it means for collections to be greater than, less than, and so on, and write one or more functions to examine the collections and their elements and return a true or false value.

For nested tables, you can check whether two nested table of the same declared type are equal or not equal, as shown in Example 5-23. You can also apply set operators to check certain conditions in a nested table or between two nested tables, as shown in Example 5-24.

Because nested tables and varrays can be atomically null, they can be tested for nullity, as shown in Example 5-22.

Example 5-22 Checking if a Collection Is Null

DECLARE
  TYPE emp_name_rec is RECORD (
    firstname    employees.first_name%TYPE,
    lastname     employees.last_name%TYPE,
    hiredate     employees.hire_date%TYPE
    );
   TYPE staff IS TABLE OF emp_name_rec;
   members staff;
BEGIN
  -- Condition yields TRUE because you have not used a constructor.
   IF members IS NULL THEN
     DBMS_OUTPUT.PUT_LINE('NULL');
   ELSE
     DBMS_OUTPUT.PUT_LINE('Not NULL');
   END IF;
END;
/

Result:

NULL

Example 5-23 shows that nested tables can be compared for equality or inequality. They cannot be ordered, because there is no greater than or less than comparison.

Example 5-23 Comparing Two Nested Tables

DECLARE
   TYPE dnames_tab IS TABLE OF VARCHAR2(30);
   dept_names1 dnames_tab :=
     dnames_tab('Shipping','Sales','Finance','Payroll');
   dept_names2 dnames_tab :=
     dnames_tab('Sales','Finance','Shipping','Payroll');
   dept_names3 dnames_tab :=
     dnames_tab('Sales','Finance','Payroll');
BEGIN
-- You can use = or !=, but not < or >.
-- These 2 are equal even though members are in different order.
   IF dept_names1 = dept_names2 THEN
     DBMS_OUTPUT.PUT_LINE
      ('dept_names1 and dept_names2 have the same members.');
   END IF;
   IF dept_names2 != dept_names3 THEN
      DBMS_OUTPUT.PUT_LINE
        ('dept_names2 and dept_names3 have different members.');
   END IF;
END;
/

Result:

dept_names1 and dept_names2 have the same members.
dept_names2 and dept_names3 have different members.

You can test certain properties of a nested table, or compare two nested tables, using ANSI-standard set operations, as shown in Example 5-24.

Example 5-24 Comparing Nested Tables with Set Operators

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 BOOLEAN;
  howmany NUMBER;
  PROCEDURE testify
    (truth BOOLEAN := NULL,
     quantity NUMBER := NULL) IS
  BEGIN
    IF truth IS NOT NULL THEN
      DBMS_OUTPUT.PUT_LINE
        (CASE truth WHEN TRUE THEN 'True' WHEN FALSE THEN 'False' END);
    END IF;
    IF quantity IS NOT NULL THEN
        DBMS_OUTPUT.PUT_LINE(quantity);
    END IF;
  END;
BEGIN
  answer := nt1 IN (nt2,nt3,nt4); -- true, nt1 matches nt2
  testify(truth => answer);
  answer := nt1 SUBMULTISET OF nt3; -- true, all elements match
  testify(truth => answer);
  answer := nt1 NOT SUBMULTISET OF nt4; -- also true
  testify(truth => answer);
  howmany := CARDINALITY(nt3); -- number of elements in nt3
  testify(quantity => howmany);
  howmany := CARDINALITY(SET(nt3)); -- number of distinct elements
  testify(quantity => howmany);
  answer := 4 MEMBER OF nt1; -- false, no element matches
  testify(truth => answer);
  answer := nt3 IS A SET; -- false, nt3 has duplicates
  testify(truth => answer);
  answer := nt3 IS NOT A SET; -- true, nt3 has duplicates
  testify(truth => answer);
  answer := nt1 IS EMPTY; -- false, nt1 has some members
  testify(truth => answer);
END;
/

Result:

True
True
True
4
3
False
False
True
False

Multidimensional Collections

Although a collection has only one dimension, you can model a multidimensional collection by creating a collection whose elements are also collections. For example, you can create a nested table of varrays, a varray of varrays, a varray of nested tables, and so on.

When creating a nested table of nested tables as a column in SQL, check the syntax of the CREATE TABLE statement to see how to define the storage table.

Example 5-25, Example 5-26, and Example 5-27 are some examples showing the syntax and possibilities for multilevel collections.

Example 5-25 Multilevel VARRAY

DECLARE
  TYPE t1 IS VARRAY(10) OF INTEGER;
  TYPE nt1 IS VARRAY(10) OF t1; -- multilevel varray type
  va t1 := t1(2,3,5);
  -- initialize multilevel varray
  nva nt1 := nt1(va, t1(55,6,73), t1(2,4), va);
  i INTEGER;
  va1 t1;
BEGIN
  -- multilevel access
  i := nva(2)(3); -- i will get value 73
  DBMS_OUTPUT.PUT_LINE('I = ' || i);
  -- add a varray element to nva
  nva.EXTEND;
  -- replace inner varray elements
  nva(5) := t1(56, 32);
  nva(4) := t1(45,43,67,43345);
  -- replace an inner integer element
  nva(4)(4) := 1; -- replaces 43345 with 1
  -- add an element to the 4th varray element
  -- and store integer 89 into it.
  nva(4).EXTEND;
  nva(4)(5) := 89;
END;
/

Result:

I = 73

Example 5-26 Multilevel Nested Table

DECLARE
  TYPE tb1 IS TABLE OF VARCHAR2(20);
  TYPE Ntb1 IS TABLE OF tb1; -- table of table elements
  TYPE Tv1 IS VARRAY(10) OF INTEGER;
  TYPE ntb2 IS TABLE OF tv1; -- table of varray elements
  vtb1 tb1 := tb1('one', 'three');
  vntb1 ntb1 := ntb1(vtb1);
  vntb2 ntb2 := ntb2(tv1(3,5), tv1(5,7,3));
  -- table of varray elements
BEGIN
  vntb1.EXTEND;
  vntb1(2) := vntb1(1);
  -- delete the first element in vntb1
  vntb1.DELETE(1);
  -- delete the first string
  -- from the second table in the nested table
  vntb1(2).DELETE(1);
END;
/

Example 5-27 Multilevel Associative Array

DECLARE
  TYPE tb1 IS TABLE OF INTEGER INDEX BY PLS_INTEGER;
  -- Index-by table of index-by tables:
  TYPE ntb1 IS TABLE OF tb1 INDEX BY PLS_INTEGER;
  TYPE va1 IS VARRAY(10) OF VARCHAR2(20);
  -- Index-by table of varray elements:
  TYPE ntb2 IS TABLE OF va1 INDEX BY PLS_INTEGER;
  v1 va1 := va1('hello', 'world');
  v2 ntb1;
  v3 ntb2;
  v4 tb1;
  v5 tb1; -- empty table
BEGIN
  v4(1) := 34;
  v4(2) := 46456;
  v4(456) := 343;
  v2(23) := v4;
  v3(34) := va1(33, 456, 656, 343);
  -- assign an empty table to v2(35) and try again
  v2(35) := v5;
  v2(35)(2) := 78; -- it works now
END;
/

Collection Methods

A collection method is a built-in PL/SQL subprogram—either a function that returns information about a collection or a procedure that operates on a collection. Collection methods make collections easier to use, and make your applications easier to maintain. Table 5-2 summarizes the collection methods.

Table 5-2 Collection Methods

Method Type Description

COUNT

Function

Returns number of elements in collection.

DELETE

Procedure

Deletes elements from collection.

EXISTS

Function

Returns TRUE if and only if a specified element of a nested table or varray exists.

EXTEND

Procedure

Increases size of nested table or varray.

FIRST

Function

Returns first subscript or key value in collection.

LAST

Function

Returns last subscript or key value in collection.

LIMIT

Function

Returns maximum number of elements that collection can have.

NEXT

Function

Returns subscript that succeeds specified index.

PRIOR

Function

Returns subscript that precedes specified index.

TRIM

Procedure

Deletes elements from the end of a nested table or varray.


The basic syntax of a collection method call is:

collection_name.method

For detailed syntax, see "Collection Method Call".

A collection method call can appear anywhere that a call to a PL/SQL subprogram of its type (function or procedure) can appear, except in a SQL statement.

The only collection method that you can use with an empty collection is EXISTS; all others raise the exception COLLECTION_IS_NULL.

In a subprogram, a collection parameter assumes the properties of the argument bound to it. You can apply collection methods to such parameters. For varray parameters, the value of LIMIT is always derived from the parameter type definition, regardless of the parameter mode.

Topics:

EXISTS Method

The EXISTS method is a function that you can use to check if a specified element of a nested table or varray 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 SUBSCRIPT_OUTSIDE_LIMIT.

Use EXISTS to avoid referencing a nonexistent element, which raises an exception.

Example 5-28 Checking Whether a Collection Element EXISTS

DECLARE
   TYPE NumList IS TABLE OF INTEGER;
   n NumList := NumList(1,3,5,7);
BEGIN
   n.DELETE(2); -- Delete the second element
   IF n.EXISTS(1) THEN
      DBMS_OUTPUT.PUT_LINE('OK, element #1 exists.');
   END IF;
   IF n.EXISTS(2) = FALSE THEN
      DBMS_OUTPUT.PUT_LINE('OK, element #2 was deleted.');
   END IF;
   IF n.EXISTS(99) = FALSE THEN
      DBMS_OUTPUT.PUT_LINE('OK, element #99 does not exist at all.');
   END IF;
END;
/

Result:

OK, element #1 exists.
OK, element #2 was deleted.
OK, element #99 does not exist at all.

COUNT Method

The COUNT method is a function that returns the number of elements in the collection (ignoring deleted elements).

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

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

Using DELETE with no parameters sets COUNT to 0.

Example 5-29 Counting Collection Elements with COUNT

DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   n NumList := NumList(2,4,6,8);
     -- Collection starts with 4 elements.
BEGIN
   DBMS_OUTPUT.PUT_LINE
     ('There are ' || n.COUNT || ' elements in N.');
   n.EXTEND(3); -- Add 3 elements at the end.
   DBMS_OUTPUT.PUT_LINE
     ('Now there are ' || n.COUNT || ' elements in N.');
   n := NumList(86,99); -- Assign a value with 2 elements.
   DBMS_OUTPUT.PUT_LINE
     ('Now there are ' || n.COUNT || ' elements in N.');
   n.TRIM(2); -- Remove the last 2 elements, leaving none.
   DBMS_OUTPUT.PUT_LINE
     ('Now there are ' || n.COUNT || ' elements in N.');
END;
/

Result:

There are 4 elements in N.
Now there are 7 elements in N.
Now there are 2 elements in N.
Now there are 0 elements in N.

LIMIT Method

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

Example 5-30 Checking the Maximum Size of a Collection with LIMIT

DECLARE
   TYPE dnames_var IS VARRAY(7) OF VARCHAR2(30);
   dept_names dnames_var :=
     dnames_var('Shipping','Sales','Finance','Payroll');
BEGIN
   DBMS_OUTPUT.PUT_LINE
     ('dept_names has ' || dept_names.COUNT || ' elements now');
   DBMS_OUTPUT.PUT_LINE
     ('dept_names''s type can hold a maximum of '
      || dept_names.LIMIT || ' elements');
   DBMS_OUTPUT.PUT_LINE
    ('The maximum number you can use with '
     || 'dept_names.EXTEND() is '
     || (dept_names.LIMIT - dept_names.COUNT));
END;
/

Result:

dept_names has 4 elements now
dept_names's type can hold a maximum of 7 elements
The maximum number you can use with dept_names.EXTEND() is 3

FIRST and LAST Methods

The FIRST and LAST methods are functions.

For a collection indexed by integers, FIRST and LAST return the first and last (smallest and largest) index numbers (ignoring deleted elements).

For an associative array indexed by strings, FIRST and LAST return the lowest and highest key values. If the NLS_COMP initialization parameter is set to ANSI, the order is based on the sort order specified by the NLS_SORT initialization parameter.

If the collection is empty, FIRST and LAST return NULL. If the collection contains only one element, FIRST and LAST return the same value. If the collection is a varray that is not empty, FIRST always returns 1.

For a varray, LAST always equals COUNT. For a nested table, LAST equals COUNT unless you delete elements from its middle, in which case LAST is larger than COUNT.

Example 5-31 uses FIRST and LAST to traverse a collection that has consecutive subscripts.

Example 5-31 FIRST and LAST Methods

DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   n NumList := NumList(1,3,5,7);
   counter INTEGER;
BEGIN
   DBMS_OUTPUT.PUT_LINE('N''s first subscript is ' || n.FIRST);
   DBMS_OUTPUT.PUT_LINE('N''s last subscript is ' || n.LAST);
-- When the subscripts are consecutive starting at 1, 
-- it's simple to loop through them.
   FOR i IN n.FIRST .. n.LAST
   LOOP
      DBMS_OUTPUT.PUT_LINE('Element #' || i || ' = ' || n(i));
   END LOOP;
   n.DELETE(2); -- Delete second element.
-- When the subscripts have gaps
-- or the collection might be uninitialized,
-- the loop logic is more extensive.
-- Start at the first element
-- and look for the next element until there are no more.
   IF n IS NOT NULL THEN
      counter := n.FIRST;
      WHILE counter IS NOT NULL
      LOOP
         DBMS_OUTPUT.PUT_LINE
           ('Element #' || counter || ' = ' || n(counter));
         counter := n.NEXT(counter);
      END LOOP;
   ELSE
      DBMS_OUTPUT.PUT_LINE('N is null, nothing to do.');
   END IF;
END;
/

Result:

N's first subscript is 1
N's last subscript is 4
Element #1 = 1
Element #2 = 3
Element #3 = 5
Element #4 = 7
Element #1 = 1
Element #3 = 5
Element #4 = 7

PRIOR and NEXT Methods

The PRIOR and NEXT methods are functions that let you move backward and forward in the collection.

PRIOR(n) returns the index number that precedes index n in the collection. If n has no predecessor, PRIOR(n) returns NULL.

NEXT(n) returns the index number that succeeds index n in the collection. If n has no successor, NEXT(n)returns NULL.

For an associative array with VARCHAR2 keys, these methods return the appropriate key values. Ordering is based on the binary values of the characters in the string, unless the NLS_COMP initialization parameter is set to ANSI, in which case the ordering is based on the locale-specific sort order specified by the NLS_SORT initialization parameter.

These methods are more reliable than looping through a fixed set of subscript values, because elements might be inserted or deleted from the collection during the loop. This is especially true for associative arrays, where subscripts might not be in consecutive order.

Example 5-32 PRIOR and NEXT Methods

DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   n NumList := NumList(1966,1971,1984,1989,1999);
BEGIN
   DBMS_OUTPUT.PUT_LINE('The element after #2 is #' || n.NEXT(2));
   DBMS_OUTPUT.PUT_LINE('The element before #2 is #' || n.PRIOR(2));
   n.DELETE(3);
     -- Delete an element to show how NEXT can handle gaps.
   DBMS_OUTPUT.PUT_LINE
     ('Now the element after #2 is #' || n.NEXT(2));
   IF n.PRIOR(n.FIRST) IS NULL THEN
      DBMS_OUTPUT.PUT_LINE
        ('Can''t get PRIOR of the first element or NEXT of the last.');
   END IF;
END;
/

Result:

The element after #2 is #3
The element before #2 is #1
Now the element after #2 is #4
Can't get PRIOR of the first element or NEXT of the last.

You can use PRIOR or NEXT to traverse collections indexed by any series of subscripts. Example 5-33 uses NEXT to traverse a nested table from which some elements were deleted.

Example 5-33 NEXT Method Accesses Elements of Nested Table

DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   n NumList := NumList(1,3,5,7);
   counter INTEGER;
BEGIN
   n.DELETE(2); -- Delete second element.
-- When the subscripts have gaps,
-- loop logic is more extensive.
-- Start at first element and look for next element
-- until there are no more.
   counter := n.FIRST;
   WHILE counter IS NOT NULL
   LOOP
      DBMS_OUTPUT.PUT_LINE
        ('Counting up: Element #' || counter || ' = ' || n(counter));
      counter := n.NEXT(counter);
   END LOOP;
-- Run the same loop in reverse order.
   counter := n.LAST;
   WHILE counter IS NOT NULL
   LOOP
      DBMS_OUTPUT.PUT_LINE
        ('Counting down: Element #' || counter || ' = ' || n(counter));
      counter := n.PRIOR(counter);
   END LOOP;
END;
/

Result:

Counting up: Element #1 = 1
Counting up: Element #3 = 5
Counting up: Element #4 = 7
Counting down: Element #4 = 7
Counting down: Element #3 = 5
Counting down: Element #1 = 1

When traversing elements, PRIOR and NEXT skip over deleted elements.

EXTEND Method

The EXTEND method is a procedure that increases the size of a nested table or varray. This method has these forms:

  • EXTEND appends one null element to a collection.

  • EXTEND(n) appends n null elements to a collection.

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

    Note:

    This is the only form of EXTEND that you can use on a collection whose elements have the NOT NULL constraint.

You cannot use EXTEND to initialize an atomically null collection. For instructions for initializing a collection, see "Collection Initialization".

EXTEND operates on the internal size of a collection, which includes elements deleted with DELETE(n) but not elements deleted with DELETE (see "DELETE Method").

Example 5-34 EXTEND Method

DECLARE
   TYPE NumList IS TABLE OF INTEGER;
   n NumList := NumList(2,4,6,8);
   x NumList := NumList(1,3);
   PROCEDURE print_numlist(the_list NumList) IS
      output VARCHAR2(128);
   BEGIN
      FOR i IN the_list.FIRST .. the_list.LAST
      LOOP
         output :=
           output || NVL(TO_CHAR(the_list(i)),'NULL') || ' ';
      END LOOP;
      DBMS_OUTPUT.PUT_LINE(output);
   END;
BEGIN
   DBMS_OUTPUT.PUT_LINE
     ('At first, N has ' || n.COUNT || ' elements.');
   n.EXTEND(5); -- Add 5 elements at the end.
   DBMS_OUTPUT.PUT_LINE
     ('Now N has ' || n.COUNT || ' elements.');
-- Elements 5, 6, 7, 8, and 9 are all NULL.
   print_numlist(n);
   DBMS_OUTPUT.PUT_LINE
     ('At first, X has ' || x.COUNT || ' elements.');
   x.EXTEND(4,2); -- Add 4 elements at the end.
   DBMS_OUTPUT.PUT_LINE
     ('Now X has ' || x.COUNT || ' elements.');
-- Elements 3, 4, 5, and 6 are copies of element #2.
   print_numlist(x);
END;
/

Result:

At first, N has 4 elements.
Now N has 9 elements.
2 4 6 8 NULL NULL NULL NULL NULL
At first, X has 2 elements.
Now X has 6 elements.
1 3 3 3 3 3

When it includes deleted elements, the internal size of a nested table differs from the values returned by COUNT and LAST. This refers to deleted elements after using DELETE(n), but not DELETE without parameters which completely removes all elements. For example, if you initialize a nested table with five elements, then delete elements 2 and 5, the internal size is 5, COUNT returns 3, and LAST returns 4. All deleted elements, regardless of position, are treated alike.

TRIM Method

The TRIM method is a procedure that deletes elements from the end of a nested table or varray. This method has these forms:

  • TRIM removes one element from the end of a collection.

  • TRIM(n) removes n elements from the end of a collection.

TRIM(n) removes n elements from the end of a collection. If n is greater than COUNT, TRIM(n) raises SUBSCRIPT_BEYOND_COUNT. TRIM operates on the internal size of a collection. If TRIM encounters deleted elements, it includes them in its tally.

If n is too large, TRIM(n) raises SUBSCRIPT_BEYOND_COUNT.

To remove all elements, use DELETE without parameters. See "DELETE Method".

Caution:

Do not depend on interaction between TRIM and DELETE. Either treat nested tables like fixed-size arrays and use only DELETE, or to treat them like stacks and use only TRIM and EXTEND.

In Example 5-35, the TRIM statement removes the last three elements from nested table courses.

Example 5-35 TRIM Method

DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   n NumList := NumList(1,2,3,5,7,11);
   PROCEDURE print_numlist(the_list NumList) IS
      output VARCHAR2(128);
   BEGIN
      IF n.COUNT = 0 THEN
         DBMS_OUTPUT.PUT_LINE('No elements in collection.');
      ELSE
         FOR i IN the_list.FIRST .. the_list.LAST
         LOOP
            output :=
              output || NVL(TO_CHAR(the_list(i)),'NULL') || ' ';
         END LOOP;
         DBMS_OUTPUT.PUT_LINE(output);
      END IF;
   END;
BEGIN
   print_numlist(n);
   n.TRIM(2); -- Remove last 2 elements.
   print_numlist(n);
   n.TRIM; -- Remove last element.
   print_numlist(n);
   n.TRIM(n.COUNT); -- Remove all remaining elements.
   print_numlist(n);
-- If too many elements are specified, 
-- TRIM raises the exception SUBSCRIPT_BEYOND_COUNT.
   BEGIN
      n := NumList(1,2,3);
      n.TRIM(100);
      EXCEPTION
        WHEN SUBSCRIPT_BEYOND_COUNT THEN
          DBMS_OUTPUT.PUT_LINE
            ('There weren''t 100 elements to be trimmed.');
   END;
-- When elements are removed by DELETE,
-- placeholders are left behind.
--  TRIM counts these placeholders
--  as it removes elements from the end.
   n := NumList(1,2,3,4);
   n.DELETE(3);  -- delete element 3
-- At this point, n contains elements (1,2,4).
-- TRIMming the last 2 elements
-- removes the 4 and the placeholder, not 4 and 2.
   n.TRIM(2);
   print_numlist(n);
END;
/

Result:

1 2 3 5 7 11
1 2 3 5
1 2 3
No elements in collection.
There weren't 100 elements to be trimmed.
1 2

TRIM operates on the internal size of a collection. If TRIM encounters deleted elements, it includes them in its tally. This refers to deleted elements after using DELETE(n), but not DELETE without parameters which completely removes all elements.

Example 5-36 TRIM Method on Deleted Elements

DECLARE
  TYPE CourseList IS TABLE OF VARCHAR2(10);
  courses CourseList;
BEGIN
  courses := CourseList('Biol 4412', 'Psyc 3112', 'Anth 3001');
  courses.DELETE(courses.LAST);  -- delete element 3
  /* At this point, COUNT equals 2, the number of valid
     elements remaining. So, you might expect the next 
     statement to empty the nested table by trimming 
     elements 1 and 2. Instead, it trims valid element 2 
     and deleted element 3 because TRIM includes deleted 
     elements in its tally. */
  courses.TRIM(courses.COUNT);
  DBMS_OUTPUT.PUT_LINE(courses(1));  -- prints 'Biol 4412'
END;
/

Result:

Biol 4412

In general, do not depend on the interaction between TRIM and DELETE. It is better to treat nested tables like fixed-size arrays and use only DELETE, or to treat them like stacks and use only TRIM and EXTEND.

Because PL/SQL does not keep placeholders for trimmed elements, you cannot replace a trimmed element simply by assigning it a value.

DELETE Method

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

  • DELETE deletes all elements from the collection.

    Note:

    This is the only form of DELETE that you can use with a varray (which always has consecutive subscripts).
  • DELETE(n) deletes the nth element from an associative array or nested table. If the nth element is null, DELETE(n) does nothing.

  • DELETE(m,n) deletes all elements in the range m..n from an associative array or nested table. If m is larger than n or if m or n is null, DELETE(m,n) does nothing.

If an element to be deleted does not exist, DELETE skips it and no exception is raised.

PL/SQL keeps placeholders for deleted elements, so that you can re-create them by assigning values.

Example 5-37 DELETE Method

DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   n NumList := NumList(10,20,30,40,50,60,70,80,90,100);
   TYPE NickList IS TABLE OF VARCHAR2(64) INDEX BY VARCHAR2(32);
   nicknames NickList;
BEGIN
   n.DELETE(2);    -- deletes element 2 
   n.DELETE(3,6);  -- deletes elements 3 through 6 
   n.DELETE(7,7);  -- deletes element 7 
   n.DELETE(6,3);  -- does nothing since 6 > 3
   n.DELETE;      -- deletes all elements
   nicknames('Bob') := 'Robert';
   nicknames('Buffy') := 'Esmerelda';
   nicknames('Chip') := 'Charles';
   nicknames('Dan') := 'Daniel';
   nicknames('Fluffy') := 'Ernestina';
   nicknames('Rob') := 'Robert';
-- following deletes element denoted by this key
   nicknames.DELETE('Chip');
-- following deletes elements with keys in this alphabetic range
   nicknames.DELETE('Buffy','Fluffy'); 
END;
/

PL/SQL keeps placeholders for deleted elements, so you can replace a deleted element by assigning it a value. This refers to deleted elements after using DELETE(n), but not DELETE without parameters which completely removes all elements.

DELETE lets you maintain sparse nested tables. You can store sparse nested tables in the database, just like any other nested tables.

The amount of memory allocated to a collection increases as the number of elements in the collection increases. If you delete the entire collection, or delete all elements individually, all of the memory used to store elements of that collection is freed.

Collection Exceptions

Example 5-38 shows various collection exceptions that are predefined in PL/SQL. The example also includes notes on how to avoid the problems.

Example 5-38 Collection Exceptions

DECLARE
  TYPE WordList IS TABLE OF VARCHAR2(5);
  words WordList;
  err_msg VARCHAR2(100);
  PROCEDURE display_error IS
  BEGIN
    err_msg := SUBSTR(SQLERRM, 1, 100);
    DBMS_OUTPUT.PUT_LINE('Error message = ' || err_msg);
  END;
BEGIN
  BEGIN
    words(1) := 10; -- Raises COLLECTION_IS_NULL
--  A constructor has not been used yet.
--  Note: This exception applies to varrays and nested tables,
--  but not to associative arrays which do not need a constructor.
    EXCEPTION
      WHEN OTHERS THEN display_error;
  END;
--  After using a constructor, you can assign values to the elements.
    words := WordList('1st', '2nd', '3rd'); -- 3 elements created
--  Any expression that returns a VARCHAR2(5) is valid.
    words(3) := words(1) || '+2';
  BEGIN
    words(3) := 'longer than 5 characters'; -- Raises VALUE_ERROR
--  The assigned value is too long.
    EXCEPTION
      WHEN OTHERS THEN display_error;
  END;
  BEGIN
    words('B') := 'dunno'; -- Raises VALUE_ERROR
--  The subscript (B) of a nested table must be an integer. 
--  Note: Also, NULL is not allowed as a subscript.
    EXCEPTION
      WHEN OTHERS THEN display_error;
  END;
  BEGIN
    words(0) := 'zero'; -- Raises SUBSCRIPT_OUTSIDE_LIMIT 
--  Subscript 0 is outside the allowed subscript range.
    EXCEPTION
      WHEN OTHERS THEN display_error;
  END;
  BEGIN
    words(4) := 'maybe'; -- Raises SUBSCRIPT_BEYOND_COUNT
--  The subscript (4) exceeds the number of elements in the table.
--  To add elements, invoke the EXTEND method first.
    EXCEPTION
      WHEN OTHERS THEN display_error;
  END;
  BEGIN
    words.DELETE(1);
    IF words(1) = 'First' THEN NULL; END IF;
      -- Raises NO_DATA_FOUND
--  The element with subcript (1) was deleted.
    EXCEPTION
      WHEN OTHERS THEN display_error;
  END;
END;
/

Result:

Error message = ORA-06531: Reference to uninitialized collection
Error message = ORA-06502: PL/SQL: numeric or value error: character string
buffer too small
Error message = ORA-06502: PL/SQL: numeric or value error: character to number
conversion error
Error message = ORA-06532: Subscript outside of limit
Error message = ORA-06533: Subscript beyond count
Error message = ORA-01403: no data found

Execution continues in Example 5-38 because the raised exceptions are handled in sub-blocks. See "Continuing Execution After an Exception Is Raised". For information about the use of SQLERRM with exception handling, see "Error Code and Error Message Retrieval".

This table summarizes when a given exception is raised:

Collection Exception Raised when...
COLLECTION_IS_NULL you try to operate on an atomically null collection.
NO_DATA_FOUND a subscript designates an element that was deleted, or a nonexistent element of an associative array.
SUBSCRIPT_BEYOND_COUNT a subscript exceeds the number of elements in a collection.
SUBSCRIPT_OUTSIDE_LIMIT a subscript is outside the allowed range.
VALUE_ERROR a subscript is null or not convertible to the key type. This exception might occur if the key is defined as a PLS_INTEGER range, and the subscript is outside this range.

In some cases, you can pass invalid subscripts to a method without raising an exception. For example, when you pass a null subscript to DELETE(n), it does nothing. You can replace deleted elements by assigning values to them, without raising NO_DATA_FOUND. This refers to deleted elements after using DELETE(n), but not DELETE without parameters which completely removes all elements.

Example 5-39 How Invalid Subscripts are Handled with DELETE(n)

DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   nums NumList := NumList(10,20,30);  -- initialize table
BEGIN
   nums.DELETE(-1);  -- does not raise SUBSCRIPT_OUTSIDE_LIMIT
   nums.DELETE(3);   -- delete 3rd element
   DBMS_OUTPUT.PUT_LINE(nums.COUNT);  -- prints 2
   nums(3) := 30;    -- allowed; does not raise NO_DATA_FOUND
   DBMS_OUTPUT.PUT_LINE(nums.COUNT);  -- prints 3
END;
/

Result:

2
3

Packaged collection types and local collection types are never compatible. For example, if you invoke the packaged procedure in Example 5-40, the second procedure call fails, because the packaged and local VARRAY types are incompatible despite their identical definitions.

Example 5-40 Incompatibility Between Package and Local Collection Types

CREATE PACKAGE pkg AS
   TYPE NumList IS TABLE OF NUMBER;
   PROCEDURE print_numlist (nums NumList);
END pkg;
/
CREATE PACKAGE BODY pkg AS
  PROCEDURE print_numlist (nums NumList) IS
  BEGIN
    FOR i IN nums.FIRST..nums.LAST LOOP
      DBMS_OUTPUT.PUT_LINE(nums(i));
    END LOOP;
  END;
END pkg;
/

DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   n1 pkg.NumList := pkg.NumList(2,4); -- type from the package.
   n2 NumList := NumList(6,8);         -- local type.
BEGIN
   pkg.print_numlist(n1); -- type from pkg is legal
-- The packaged procedure cannot accept
-- a value of the local type (n2)
-- pkg.print_numlist(n2);  -- Causes a compilation error.
END;
/

Result:

2
4

Record Definitions and Declarations

To create records, you define a RECORD type, then declare records of that type. You can also create or find a table, view, or PL/SQL cursor with the values you want, and use the %ROWTYPE attribute to create a matching record.

You can define RECORD types in the declarative part of any PL/SQL block, subprogram, or package. When you define your own RECORD type, you can specify a NOT NULL constraint on fields, or give them initial values. See "Record".

Example 5-42 and Example 5-42 illustrate record type declarations.

Example 5-41 Declaring and Initializing a Simple Record Type

DECLARE
   TYPE DeptRecTyp IS RECORD (
      deptid NUMBER(4) NOT NULL := 99,
      dname  departments.department_name%TYPE,
      loc    departments.location_id%TYPE,
      region regions%ROWTYPE );
   dept_rec DeptRecTyp;
BEGIN
   dept_rec.dname := 'PURCHASING';
END;
/

Example 5-42 Declaring and Initializing Record Types

DECLARE
-- Declare a record type with 3 fields.
  TYPE rec1_t IS RECORD
    (field1 VARCHAR2(16), field2 NUMBER, field3 DATE);
-- For any fields declared NOT NULL, you must supply an initial value.
  TYPE rec2_t IS RECORD (id INTEGER NOT NULL := -1, 
  name VARCHAR2(64) NOT NULL := '[anonymous]');
-- Declare record variables of the types declared
  rec1 rec1_t;
  rec2 rec2_t;
-- Declare a record variable that can hold
-- a row from the EMPLOYEES table.
-- The fields of the record automatically match the names and
-- types of the columns.
-- Don't need a TYPE declaration in this case.
  rec3 employees%ROWTYPE;
-- Or mix fields that are table columns with user-defined fields.
  TYPE rec4_t IS RECORD (first_name employees.first_name%TYPE,
                         last_name employees.last_name%TYPE,
                         rating NUMBER);
  rec4 rec4_t;
BEGIN
-- Read and write fields using dot notation
  rec1.field1 := 'Yesterday';
  rec1.field2 := 65;
  rec1.field3 := TRUNC(SYSDATE-1);
-- Didn't fill name field, so it takes initial value
  DBMS_OUTPUT.PUT_LINE(rec2.name);
END;
/

Result:

[anonymous]

To store a record in the database, you can specify it in an INSERT or UPDATE statement, if its fields match the columns in the table.

You can use %TYPE to specify a field type corresponding to a table column type. Your code keeps working even if the column type is changed (for example, to increase the length of a VARCHAR2 or the precision of a NUMBER). Example 5-43 defines RECORD types to hold information about a department.

Example 5-43 %ROWTYPE in Record Declaration

DECLARE
-- Best: use %ROWTYPE instead of specifying each column.
-- Use <cursor>%ROWTYPE instead of <table>%ROWTYPE because 
-- you only want some columns.
-- Declaring cursor does not run query or affect performance.
   CURSOR c1 IS
     SELECT department_id, department_name, location_id
     FROM departments;
   rec1 c1%ROWTYPE;
-- Use <column>%TYPE in field declarations to avoid problems if 
-- the column types change.
   TYPE DeptRec2 IS RECORD
     (dept_id   departments.department_id%TYPE,
      dept_name departments.department_name%TYPE,
      dept_loc  departments.location_id%TYPE);
   rec2 DeptRec2;
-- Write each field name, specifying type directly
-- (clumsy and unmaintainable for working with table data
-- use only for all-PL/SQL code).
   TYPE DeptRec3 IS RECORD (dept_id NUMBER,
                            dept_name VARCHAR2(14),
                            dept_loc VARCHAR2(13));
   rec3 DeptRec3;
BEGIN
   NULL;
END;
/

PL/SQL lets you define records that contain objects, collections, and other records (called nested records). However, records cannot be attributes of ADTs.

To declare a record that represents a row in a database table, without listing the columns, use the %ROWTYPE attribute.

Your code keeps working even after columns are added to the table. If you want to represent a subset of columns in a table, or columns from different tables, you can define a view or declare a cursor to select the right columns and do any necessary joins, and then apply %ROWTYPE to the view or cursor.

Records as Subprogram Parameters and Function Return Values

Records are easy to process using stored subprograms because you can pass just one parameter, instead of a separate parameter for each field. For example, you can fetch a table row from the EMPLOYEES table into a record, and then pass that row as a parameter to a function that computes that employee's vacation allowance. The function can access all the information about that employee by referring to the fields in the record.

Example 5-44 shows how to return a record from a function. To make the record type visible across multiple stored subprograms, declare the record type in a package specification.

Example 5-44 Returning a Record from a Function

DECLARE
   TYPE EmpRecTyp IS RECORD (
     emp_id       NUMBER(6),
     salary       NUMBER(8,2));
   CURSOR desc_salary RETURN EmpRecTyp IS
      SELECT employee_id, salary
      FROM employees
      ORDER BY salary DESC;
   emp_rec     EmpRecTyp;
   FUNCTION nth_highest_salary (n INTEGER) RETURN EmpRecTyp IS
   BEGIN
      OPEN desc_salary;
      FOR i IN 1..n LOOP
         FETCH desc_salary INTO emp_rec;
      END LOOP;
      CLOSE desc_salary;
      RETURN emp_rec;
   END nth_highest_salary;
BEGIN
   NULL;
END;
/

Like scalar variables, user-defined records can be declared as the formal parameters of subprograms, as in Example 5-45.

Example 5-45 Record as Procedure Parameter

DECLARE
   TYPE EmpRecTyp IS RECORD (
      emp_id       NUMBER(6),
      emp_sal      NUMBER(8,2) );
   PROCEDURE raise_salary (emp_info EmpRecTyp) IS
   BEGIN
      UPDATE employees SET salary = salary + salary * .10
             WHERE employee_id = emp_info.emp_id;
   END raise_salary;
BEGIN
   NULL;
END;
/

You can declare and reference nested records. That is, a record can be the component of another record.

Example 5-46 Nested Record Declaration

DECLARE
   TYPE TimeTyp IS RECORD ( minutes SMALLINT, hours SMALLINT );
   TYPE MeetingTyp IS RECORD (
      day     DATE,
      time_of TimeTyp,             -- nested record
      dept    departments%ROWTYPE,
        -- nested record representing a table row
      place   VARCHAR2(20),
      purpose VARCHAR2(50) );
   meeting MeetingTyp;
   seminar MeetingTyp;
BEGIN
-- Can assign one nested record to another
-- if they are of the same data type
   seminar.time_of := meeting.time_of;
END;
/

Such assignments are allowed even if the containing records have different data types.

Assignments to Records

To set all the fields in a record to initial values, assign to it an uninitialized record of the same type, as in Example 5-47.

Example 5-47 Assigning Initial Values to a Record

DECLARE
  TYPE RecordTyp IS RECORD (field1 NUMBER,
                            field2 VARCHAR2(32) := 'something');
  rec1 RecordTyp;
  rec2 RecordTyp;
BEGIN
  -- At first, rec1 has the values you assign:

  rec1.field1 := 100;
  rec1.field2 := 'something else';

  -- Assign empty record to rec1, resetting fields to initial values,
  -- which are NULL for field1 and 'something' for field2.

   rec1 := rec2;

   DBMS_OUTPUT.PUT_LINE (
     'field1 = ' || NVL(TO_CHAR(rec1.field1),'<NULL>') || ',
     field2 = ' || rec1.field2
   );
END;
/

Result:

field1 = <NULL>,
     field2 = something

You can assign a value to a field in a record using an assignment statement with dot notation:

emp_info.last_name := 'Fields';

Values are assigned separately to each field of a record in Example 5-47. You cannot assign a list of values to a record using an assignment statement. There is no constructor-like notation for records.

You can assign values to all fields simultaneously only if you assign a record to another record with the same data type. Having fields that match is not enough, as shown in Example 5-48.

Example 5-48 Assigning All the Fields of a Record in One Statement

DECLARE
-- Two identical type declarations.
   TYPE DeptRec1 IS RECORD
     (dept_num  NUMBER(2), dept_name VARCHAR2(14));
   TYPE DeptRec2 IS RECORD
    (dept_num  NUMBER(2), dept_name VARCHAR2(14));
   dept1_info DeptRec1;
   dept2_info DeptRec2;
   dept3_info DeptRec2;
BEGIN
-- Not allowed; different data types,
-- even though fields are the same.
--      dept1_info := dept2_info; 
-- This assignment is OK because the records have the same type.
   dept2_info := dept3_info;
END;
/

You can assign a %ROWTYPE record to a user-defined record if their fields match in number and order, and corresponding fields have the same data types, as in Example 5-49.

Example 5-49 Assigning a %ROWTYPE Record to a User-Defined Record

DECLARE
   TYPE RecordTyp IS RECORD (last employees.last_name%TYPE, 
                             id employees.employee_id%TYPE);
   CURSOR c1 IS SELECT last_name, employee_id FROM employees;
-- Rec1 and rec2 have different types,
-- but because rec2 is based on a %ROWTYPE,
-- you can assign it to rec1 if they have
-- the right number of fields and
-- the fields have the right data types.
   rec1 RecordTyp;
   rec2 c1%ROWTYPE;
BEGIN
  SELECT last_name, employee_id INTO rec2
  FROM employees
  WHERE ROWNUM < 2;

  rec1 := rec2;
  DBMS_OUTPUT.PUT_LINE
    ('Employee #' || rec1.id || ' = ' || rec1.last);
END;
/

Result:

Employee #100 = King

You can also use the SELECT or FETCH statement to fetch column values into a record. The columns in the select-list must appear in the same order as the fields in your record.

Example 5-50 SELECT INTO Assigns Values to Record

DECLARE
   TYPE RecordTyp IS RECORD (last employees.last_name%TYPE, 
                             id employees.employee_id%TYPE);
   rec1 RecordTyp;
BEGIN
  SELECT last_name, employee_id INTO rec1
  FROM employees
  WHERE ROWNUM < 2;

  DBMS_OUTPUT.PUT_LINE
    ('Employee #' || rec1.id || ' = ' || rec1.last);
END;
/

Result:

Employee #100 = King

Record Comparisons

To test records for nullity or equality, you must write functions that accept records as parameters and compare the corresponding fields, as in Example 5-51.

Example 5-51 Testing Records for Nullity and Equality

DECLARE
  TYPE RecTyp IS RECORD (
    field1 NUMBER,
    field2 VARCHAR2(10)
  );
 
  r1  RecTyp;
  r2  RecTyp;
 
  FUNCTION record_is_null (
    rec  RecTyp
  ) RETURN BOOLEAN
  IS
  BEGIN
    RETURN ((rec.field1 IS NULL) AND (rec.field2 IS NULL));
  END;
 
  FUNCTION records_are_equal (
    rec1  RecTyp,
    rec2  RecTyp
  ) RETURN BOOLEAN
  IS
  BEGIN
    RETURN (
      (rec1.field1 = rec2.field1) AND
      (rec1.field2 = rec2.field2)
    );
  END;
 
BEGIN
  r1.field1 := NULL;
  r1.field2 := NULL;
 
  r2.field1 := 123;
  r2.field2 := 'abc';
 
  IF record_is_null(r1) THEN
    DBMS_OUTPUT.PUT_LINE('r1 is NULL');
  ELSE   
    DBMS_OUTPUT.PUT_LINE('r1 is not NULL');
  END IF;
 
  IF record_is_null(r2) THEN
    DBMS_OUTPUT.PUT_LINE('r2 is NULL');
  ELSE   
    DBMS_OUTPUT.PUT_LINE('r2 is not NULL');
  END IF;
 
  IF records_are_equal (r1, r2) THEN
    DBMS_OUTPUT.PUT_LINE('r1 equals r2');
  ELSE   
    DBMS_OUTPUT.PUT_LINE('r1 does not equal r2');
  END IF;
 
  r1.field1 := 123;
  r1.field2 := 'abc';
 
  IF records_are_equal (r1, r2) THEN
    DBMS_OUTPUT.PUT_LINE('r1 equals r2');
  ELSE   
    DBMS_OUTPUT.PUT_LINE('r1 does not equal r2');
  END IF;
END;
/

Result:

r1 is NULL
r2 is not NULL
r1 does not equal r2
r1 equals r2

Inserting Records into Tables

The PL/SQL extension to the SQL INSERT statement lets you insert a record into a table. The record must represent a row of the table. For more information, see "INSERT Statement Extension". For restrictions on inserting records into tables, see "Restrictions on Record Inserts and Updates".

Example 5-52 creates the table schedule and initializes it by putting default values in a record and inserting the record into the table once for each week. (The COLUMN formatting commands are from SQL*Plus.)

Example 5-52 Initializing a Table by Inserting a Record of Default Values

DROP TABLE schedule;
CREATE TABLE schedule (
  week  NUMBER,
  Mon   VARCHAR2(10),
  Tue   VARCHAR2(10),
  Wed   VARCHAR2(10),
  Thu   VARCHAR2(10),
  Fri   VARCHAR2(10),
  Sat   VARCHAR2(10),
  Sun   VARCHAR2(10)
);
 
DECLARE
  default_week  schedule%ROWTYPE;
  i             NUMBER;
BEGIN
  default_week.Mon := '0800-1700';
  default_week.Tue := '0800-1700';
  default_week.Wed := '0800-1700';
  default_week.Thu := '0800-1700';
  default_week.Fri := '0800-1700';
  default_week.Sat := 'Day Off';
  default_week.Sun := 'Day Off';
 
  FOR i IN 1..6 LOOP
    default_week.week    := i;
    
    INSERT INTO schedule VALUES default_week;
  END LOOP;
END;
/
 
COLUMN week FORMAT 99
COLUMN Mon  FORMAT A9
COLUMN Tue  FORMAT A9
COLUMN Wed  FORMAT A9
COLUMN Thu  FORMAT A9
COLUMN Fri  FORMAT A9
COLUMN Sat  FORMAT A9
COLUMN Sun  FORMAT A9
 
SELECT * FROM schedule;

Result:

WEEK MON       TUE       WED       THU       FRI       SAT       SUN
---- --------- --------- --------- --------- --------- --------- ---------
   1 0800-1700 0800-1700 0800-1700 0800-1700 0800-1700 Day Off   Day Off
   2 0800-1700 0800-1700 0800-1700 0800-1700 0800-1700 Day Off   Day Off
   3 0800-1700 0800-1700 0800-1700 0800-1700 0800-1700 Day Off   Day Off
   4 0800-1700 0800-1700 0800-1700 0800-1700 0800-1700 Day Off   Day Off
   5 0800-1700 0800-1700 0800-1700 0800-1700 0800-1700 Day Off   Day Off
   6 0800-1700 0800-1700 0800-1700 0800-1700 0800-1700 Day Off   Day Off

To efficiently insert a collection of records into a table, put the INSERT statement inside a FORALL statement. For information about the FORALL statement, see "Running One DML Statement Multiple Times (FORALL Statement)".

Updating Rows with Records

The PL/SQL extension to the SQL UPDATE statement lets you update one or more table rows with a record. The record must represent a row of the table. For more information, see "UPDATE Statement Extensions". For restrictions on updating table rows with a record, see "Restrictions on Record Inserts and Updates".

Example 5-53 updates the first three weeks of the table schedule (created in Example 5-52) by putting the new values in a record and updating the first three rows of the table with that record.

Example 5-53 Updating Rows with a Record

DECLARE
  default_week  schedule%ROWTYPE;
BEGIN
  default_week.Mon := 'Day Off';
  default_week.Tue := '0900-1800';
  default_week.Wed := '0900-1800';
  default_week.Thu := '0900-1800';
  default_week.Fri := '0900-1800';
  default_week.Sat := '0900-1800';
  default_week.Sun := 'Day Off';
 
  FOR i IN 1..3 LOOP
    default_week.week    := i;
  
    UPDATE schedule
    SET ROW = default_week
    WHERE week = i;
  END LOOP;
/
 
SELECT * FROM schedule;

Result:

WEEK MON       TUE       WED       THU       FRI       SAT       SUN
---- --------- --------- --------- --------- --------- --------- ---------
   1 Day Off   0900-1800 0900-1800 0900-1800 0900-1800 0900-1800 Day Off
   2 Day Off   0900-1800 0900-1800 0900-1800 0900-1800 0900-1800 Day Off
   3 Day Off   0900-1800 0900-1800 0900-1800 0900-1800 0900-1800 Day Off
   4 0800-1700 0800-1700 0800-1700 0800-1700 0800-1700 Day Off   Day Off
   5 0800-1700 0800-1700 0800-1700 0800-1700 0800-1700 Day Off   Day Off
   6 0800-1700 0800-1700 0800-1700 0800-1700 0800-1700 Day Off   Day Off

To efficiently update a set of rows with a collection of records, put the UPDATE statement inside a FORALL statement. For information about the FORALL statement, see "Running One DML Statement Multiple Times (FORALL Statement)".

Returning Rows into Records

The INSERT, UPDATE, and DELETE statements can include a RETURNING clause, which returns column values from the affected row into a PL/SQL record variable. This eliminates the need to SELECT the row after an insert or update, or before a delete.

By default, you can use this clause only when operating on exactly one row. When you use bulk SQL, you can use the form RETURNING BULK COLLECT INTO to store the results in one or more collections.

If you put an UPDATE statement with a RETURNING clause in a FORALL statement, you can retrieve values from a set of updated rows into a collection of records.

Example 5-54 updates the salary of an employee and retrieves the employee's name and new salary into a record variable.

Example 5-54 RETURNING INTO Clause with Record

DECLARE
   TYPE EmpRec IS RECORD (last_name  employees.last_name%TYPE, 
                          salary     employees.salary%TYPE);
   emp_info EmpRec;
   emp_id   NUMBER := 100;
BEGIN
   UPDATE employees SET salary = salary * 1.1
     WHERE employee_id = emp_id
     RETURNING last_name, salary INTO emp_info;
   DBMS_OUTPUT.PUT_LINE
     ('Just gave a raise to ' || emp_info.last_name ||
      ', who now makes ' || emp_info.salary);
   ROLLBACK;
END;
/

Result:

Just gave a raise to King, who now makes 26510

Restrictions on Record Inserts and Updates

These restrictions apply to record inserts and updates:

Assignments to Collections of Records

You can use the BULK COLLECT clause with a SELECT INTO or FETCH statement to retrieve a set of rows into a collection of records.

Example 5-55 BULK COLLECT with SELECT INTO Statement

DECLARE
   TYPE EmployeeSet IS TABLE OF employees%ROWTYPE;
   underpaid EmployeeSet;
     -- Holds set of rows from EMPLOYEES table.
   CURSOR c1 IS SELECT first_name, last_name FROM employees;
   TYPE NameSet IS TABLE OF c1%ROWTYPE;
   some_names NameSet;
     -- Holds set of partial rows from EMPLOYEES table.
BEGIN
-- With one query,
-- bring all relevant data into collection of records.
   SELECT * BULK COLLECT INTO underpaid FROM employees
      WHERE salary < 5000 ORDER BY salary DESC;
-- Process data by examining collection or passing it to
-- eparate procedure, instead of writing loop to FETCH each row.
   DBMS_OUTPUT.PUT_LINE
     (underpaid.COUNT || ' people make less than 5000.');
   FOR i IN underpaid.FIRST .. underpaid.LAST
   LOOP
     DBMS_OUTPUT.PUT_LINE
       (underpaid(i).last_name || ' makes ' || underpaid(i).salary);
   END LOOP;
-- You can also bring in some table columns.
-- Here you get the first and last names of 10 arbitrary employees.
   SELECT first_name, last_name
     BULK COLLECT INTO some_names
     FROM employees
     WHERE ROWNUM < 11;
   FOR i IN some_names.FIRST .. some_names.LAST
   LOOP
      DBMS_OUTPUT.PUT_LINE
        ('Employee = ' || some_names(i).first_name
         || ' ' || some_names(i).last_name);
   END LOOP;
END;
/

Result:

49 people make less than 5000.
Pataballa makes 4800
Austin makes 4800
Whalen makes 4400
Lorentz makes 4300
Sarchand makes 4200
Bull makes 4100
Bell makes 4000
Everett makes 3900
Chung makes 3800
Ladwig makes 3600
Dilly makes 3600
Rajs makes 3500
Dellinger makes 3400
Mallin makes 3300
Bissot makes 3300
Taylor makes 3200
McCain makes 3200
Nayer makes 3200
Stiles makes 3200
Khoo makes 3100
Davies makes 3100
Walsh makes 3100
Fleaur makes 3100
Feeney makes 3000
Cabrio makes 3000
Rogers makes 2900
Gates makes 2900
Baida makes 2900
Jones makes 2800
Atkinson makes 2800
Geoni makes 2800
Tobias makes 2800
Seo makes 2700
Mikkilineni makes 2700
Matos makes 2600
Grant makes 2600
OConnell makes 2600
Himuro makes 2600
Marlow makes 2500
Perkins makes 2500
Patel makes 2500
Vargas makes 2500
Sullivan makes 2500
Colmenares makes 2500
Landry makes 2400
Gee makes 2400
Philtanker makes 2200
Markle makes 2200
Olson makes 2100
Employee = Ellen Abel
Employee = Sundar Ande
Employee = Mozhe Atkinson
Employee = David Austin
Employee = Hermann Baer
Employee = Shelli Baida
Employee = Amit Banda
Employee = Elizabeth Bates
Employee = Sarah Bell
Employee = David Bernstein