5 Using PL/SQL Collections and Records

Understanding PL/SQL Collections

PL/SQL offers these collection types:

• Associative arrays, also known as index-by tables, let you look up elements using arbitrary numbers and strings for subscript values. These are similar to hash tables in other programming languages.

• Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts. You can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.

• Varrays (short for variable-size arrays) hold a fixed number of elements (although you can change the number of elements at runtime). They use sequential numbers as subscripts. You can define equivalent SQL types, allowing varrays to be stored in database tables. They can be stored and retrieved through SQL, but with less flexibility than nested tables.

Although collections have only one dimension, you can model multi-dimensional arrays by creating collections whose elements are also collections.

To use collections in an application, you define one or more PL/SQL types, then define variables of those types. You can define collection types in a procedure, function, or package. You can pass collection variables as parameters to stored subprograms.

To look up data that is more complex than single values, you can store PL/SQL records or SQL object types in collections. Nested tables and varrays can also be attributes of object types.

Understanding Nested Tables

PL/SQL nested tables represent sets of values. You can think of them as one-dimensional arrays with no declared number of elements. You can model multi-dimensional arrays by creating nested tables whose elements are also nested tables.

Within the database, nested tables are column types that hold sets of values. Oracle 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. That gives you array-like access to individual rows.

Nested tables differ from arrays in two important ways:

1. Nested tables do not have a declared number of elements, while arrays have a predefined number as illustrated in Figure 5-1. The size of a nested table can increase dynamically; however, a maximum limit is imposed. See "Referencing Collection Elements".

2. Nested tables might not have consecutive subscripts, while arrays are always dense (have consecutive subscripts). Initially, nested tables are dense, but they can become sparse (have nonconsecutive subscripts). You can delete elements from a nested table using the built-in procedure DELETE. The built-in function NEXT lets you iterate over all the subscripts of a nested table, even if the sequence has gaps.

Figure 5-1 Array versus Nested Table

Description of the illustration lnpls016.gif

Understanding Varrays

Items of type VARRAY are called varrays. They let you reference individual elements for array operations, or manipulate the collection as a whole. To reference an element, you use standard subscripting syntax (see Figure 5-2). For example, Grade(3) references the third element in varray Grades.

Figure 5-2 Varray of Size 10

Description of the illustration lnpls017.gif

A varray has a maximum size, which you specify in its type definition. Its index has a fixed lower bound of 1 and an extensible upper bound. For example, the current upper bound for varray Grades is 7, but you can increase its upper bound to maximum of 10. A varray can contain a varying number of elements, from zero (when empty) to the maximum specified in its type definition.

DECLARE  TYPE population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
  country_population population_type;
  continent_population population_type;
  howmany NUMBER;
  which VARCHAR2(64);
BEGIN
  country_population('Greenland') := 100000; -- Creates new entry
  country_population('Iceland') := 750000;   -- Creates new entry
-- Looks up value associated with a string
  howmany := country_population('Greenland');
  continent_population('Australia') := 30000000;
  continent_population('Antarctica') := 1000; -- Creates new entry
  continent_population('Antarctica') := 1001; -- Replaces previous value 
-- Returns 'Antarctica' as that comes first alphabetically.
  which := continent_population.FIRST;
-- Returns 'Australia' as that comes last alphabetically.  which := continent_population.LAST;
-- Returns the value corresponding to the last key, in this
-- case the population of Australia.
  howmany := continent_population(continent_population.LAST);
END;
/

Understanding PL/SQL Records

Records are composed of a group of fields, similar to the columns in a row. The %ROWTYPE attribute lets you declare a PL/SQL record that represents a row in a database table, without listing all the columns. 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.

For information on using records in PL/SQL, see the following sections in this chapter:

• Defining and Declaring Records

• Assigning Values to Records

Choosing Between Nested Tables and Associative Arrays

Both nested tables and associative arrays (formerly known as index-by tables) use similar subscript notation, but they have different characteristics when it comes to persistence and ease of parameter passing.

Nested tables can be stored in a database column, but associative arrays cannot. Nested tables can simplify SQL operations where you would normally join a single-column table with a larger table.

Associative arrays are appropriate for relatively small lookup tables where the collection can be constructed in memory each time a procedure is called or a package is initialized. They are good for collecting information whose volume is unknown beforehand, because there is no fixed limit on their size. Their index values are more flexible, because associative array subscripts can be negative, can be nonsequential, and can use string values instead of numbers.

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, then use those associative arrays with bulk constructs (the FORALL statement or BULK COLLECT clause).

Choosing Between Nested Tables and Varrays

Varrays are a good choice when:

• The number of elements is known in advance.

• The elements are usually all accessed in sequence.

When stored in the database, varrays keep their ordering and subscripts.

Each varray is stored as a single object, either inside the table of which it is a column (if the varray is less than 4KB) or outside the table but still in the same tablespace (if the varray is greater than 4KB). You must update or retrieve all elements of the varray at the same time, which is most appropriate when performing some operation on all the elements at once. But you might find it impractical to store and retrieve large numbers of elements this way.

Nested tables are a good choice when:

• The index values are not consecutive.

• There is no set number of index values. However, a maximum limit is imposed. See "Referencing Collection Elements".

• You need to delete or update some elements, but not all the elements at once.

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

Nested tables can be sparse: you can delete arbitrary elements, rather than just removing an item from the end.

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

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.

Declaring PL/SQL Collection Variables

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

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 datatype 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.

DECLARE
   TYPE few_depts IS VARRAY(10) OF VARCHAR2(30);
   TYPE many_depts IS VARRAY(100) OF VARCHAR2(64);
   some_depts few_depts;
-- If we change the type of some_depts 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 functions and procedures. 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 procedure.

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 call personnel.award_bonuses from outside the package, you declare a variable of type personnel.staff_list and pass that variable as the 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 datatype 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.

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. 
-- We do not need to 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 "Defining and Declaring 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.

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 error
END;
/

Referencing Collection Elements

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 the syntax

collection_name(subscript)

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

The allowed subscript ranges are:

• For nested tables, 1 .. 2147483647 (the upper limit of PLS_INTEGER).

• For varrays, 1 ..  size_limit, where you specify the limit in the declaration (not to exceed 2147483647).

• For associative arrays with a numeric key, -2147483648 to 2147483647.

• For associative arrays with a string key, the length of the key and number of possible values depends on the VARCHAR2 length limit in the type declaration, and the database character set.

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

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 with reference to element
END;
/

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

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
-- call function to retrieve the 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;
/

Checking If a Collection Element Exists (EXISTS Method)

EXISTS(n) returns TRUE if the nth element in a collection exists. Otherwise, EXISTS(n) returns FALSE. By combining EXISTS with DELETE, you can work with sparse nested tables. You can also use EXISTS to avoid referencing a nonexistent element, which raises an exception. When passed an out-of-range subscript, EXISTS returns FALSE instead of raising SUBSCRIPT_OUTSIDE_LIMIT.

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 has been deleted.');
   END IF;
   IF n.EXISTS(99) = FALSE THEN
      DBMS_OUTPUT.PUT_LINE('OK, element #99 does not exist at all.');
   END IF;
END;
/

Counting the Elements in a Collection (COUNT Method)

COUNT returns the number of elements that a collection currently contains.

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 new elements at the end.
   DBMS_OUTPUT.PUT_LINE('Now there are ' || n.COUNT || ' elements in N.');
   n := NumList(86,99); -- Assign a completely new 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;
/

COUNT is useful because the current size of a collection is not always known. For example, you can fetch a column of Oracle data into a nested table, where the number of elements depends on the size of the result set.

For varrays, COUNT always equals LAST. You can increase or decrease the size of a varray using the EXTEND and TRIM methods, so the value of COUNT can change, up to the value of the LIMIT method.

For nested tables, COUNT normally equals LAST. But, if you delete elements from the middle of a nested table, COUNT becomes smaller than LAST. When tallying elements, COUNT ignores deleted elements. Using DELETE with no parameters sets COUNT to 0.

Checking the Maximum Size of a Collection (LIMIT Method)

For nested tables and associative arrays, which have no declared size, LIMIT returns NULL. For varrays, LIMIT returns the maximum number of elements that a varray can contain. You specify this limit in the type definition, and can change it later with the TRIM and EXTEND methods.

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;
/

Finding the First or Last Collection Element (FIRST and LAST Methods)

FIRST and LAST return the first and last (smallest and largest) index numbers in a collection that uses integer subscripts.

For an associative array with VARCHAR2 key values, the lowest and highest key values are returned. By default, the order is based on the binary values of the characters in the string. If the NLS_COMP initialization parameter is set to ANSI, the order is based on the locale-specific 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 index value.

Example 5-31 shows how to use FIRST and LAST to iterate through the elements in a collection that has consecutive subscripts.

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. We start at the first element, and
-- keep looking 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;
/

For varrays, FIRST always returns 1 and LAST always equals COUNT.

For nested tables, normally FIRST returns 1 and LAST equals COUNT. But if you delete elements from the beginning of a nested table, FIRST returns a number larger than 1. If you delete elements from the middle of a nested table, LAST becomes larger than COUNT.

When scanning elements, FIRST and LAST ignore deleted elements.

Looping Through Collection Elements (PRIOR and NEXT Methods)

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

For associative arrays with VARCHAR2 keys, these methods return the appropriate key value; 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 the subscripts might not be in consecutive order and so the sequence of subscripts might be (1,2,4,8,16) or ('A','E','I','O','U').

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;
/

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 have been deleted.

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, the loop logic is more extensive. We start at
-- the first element, and keep looking for the 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;
/

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

Increasing the Size of a Collection (EXTEND Method)

To increase the size of a nested table or varray, use EXTEND.

This procedure has three 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.

You cannot use EXTEND with index-by tables. You cannot use EXTEND to add elements to an uninitialized collection. If you impose the NOT NULL constraint on a TABLE or VARRAY type, you cannot apply the first two forms of EXTEND to collections of that type.

EXTEND operates on the internal size of a collection, which includes any deleted elements. This refers to deleted elements after using DELETE(n), but not DELETE without parameters which completely removes all elements. If EXTEND encounters deleted elements, it includes them in its tally. PL/SQL keeps placeholders for deleted elements, so that you can re-create them by assigning new values.

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;
/

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 instance, 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.

Decreasing the Size of a Collection (TRIM Method)

This procedure has two forms:

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

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

If you want to remove all elements, use DELETE without parameters.

For example, this statement removes the last three elements from nested table courses:

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('I guess there weren''t 100 elements that could 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;
/

If n is too large, 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. This refers to deleted elements after using DELETE(n), but not DELETE without parameters which completely removes all 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;
/

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 new value.

Deleting Collection Elements (DELETE Method)

This procedure has various forms:

• DELETE with no parameters removes all elements from a collection, setting COUNT to 0.

• DELETE(n) removes the nth element from an associative array with a numeric key or a nested table. If the associative array has a string key, the element corresponding to the key value is deleted. If n is null, DELETE(n) does nothing.

• DELETE(m,n) removes 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.

For example:

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;
/

Varrays always have consecutive subscripts, so you cannot delete individual elements except from the end by using the TRIM method. You can use DELETE without parameters to delete all elements.

If an element to be deleted does not exist, DELETE(n) simply skips it; no exception is raised. PL/SQL keeps placeholders for deleted elements, so you can replace a deleted element by assigning it a new 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 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.

Applying Methods to Collection Parameters

Within a subprogram, a collection parameter assumes the properties of the argument bound to it. You can apply the built-in collection methods (FIRST, LAST, COUNT, and so on) to such parameters. You can create general-purpose subprograms that take collection parameters and iterate through their elements, add or delete elements, and so on. For varray parameters, the value of LIMIT is always derived from the parameter type definition, regardless of the parameter mode.

Using Records as Procedure Parameters and Function Return Values

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

The next example shows how to return a record from a function. To make the record type visible across multiple stored functions and stored procedures, declare the record type in a package specification.

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 procedures and functions:

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.

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
-- you can assign one nested record to another if they are of the same datatype
   seminar.time_of := meeting.time_of;
END;
/

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

Comparing Records

Records cannot be tested for nullity, or compared for equality, or inequality. If you want to make such comparisons, write your own function that accepts two records as parameters and does the appropriate checks or comparisons on the corresponding fields.

Inserting PL/SQL Records into the Database

A PL/SQL-only extension of the INSERT statement lets you insert records into database rows, using a single variable of type RECORD or %ROWTYPE in the VALUES clause instead of a list of fields. That makes your code more readable and maintainable.

If you issue the INSERT through the FORALL statement, you can insert values from an entire collection of records. The number of fields in the record must equal the number of columns listed in the INTO clause, and corresponding fields and columns must have compatible datatypes. To make sure the record is compatible with the table, you might find it most convenient to declare the variable as the type table_name%ROWTYPE.

Example 5-50 declares a record variable using a %ROWTYPE qualifier. You can insert this variable without specifying a column list. The %ROWTYPE declaration ensures that the record attributes have exactly the same names and types as the table columns.

DECLARE
   dept_info departments%ROWTYPE;
BEGIN
-- department_id, department_name, and location_id are the table columns
-- The record picks up these names from the %ROWTYPE
  dept_info.department_id := 300;
  dept_info.department_name := 'Personnel';
  dept_info.location_id := 1700;
-- Using the %ROWTYPE means we can leave out the column list
-- (department_id, department_name, and location_id) from the INSERT statement
  INSERT INTO departments VALUES dept_info;
END;
/

Updating the Database with PL/SQL Record Values

A PL/SQL-only extension of the UPDATE statement lets you update database rows using a single variable of type RECORD or %ROWTYPE on the right side of the SET clause, instead of a list of fields.

If you issue the UPDATE through the FORALL statement, you can update a set of rows using values from an entire collection of records. Also with an UPDATE statement, you can specify a record in the RETURNING clause to retrieve new values into a record. If you issue the UPDATE through the FORALL statement, you can retrieve new values from a set of updated rows into a collection of records.

The number of fields in the record must equal the number of columns listed in the SET clause, and corresponding fields and columns must have compatible datatypes.

You can use the keyword ROW to represent an entire row, as shown in Example 5-51.

DECLARE
   dept_info departments%ROWTYPE;
BEGIN
-- department_id, department_name, and location_id are the table columns
-- The record picks up these names from the %ROWTYPE.
  dept_info.department_id := 300;
  dept_info.department_name := 'Personnel';
  dept_info.location_id := 1700;
-- The fields of a %ROWTYPE can completely replace the table columns
-- The row will have values for the filled-in columns, and null
-- for any other columns
   UPDATE departments SET ROW = dept_info WHERE department_id = 300;
END;
/

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.

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

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;
/

Restrictions on Record Inserts and Updates

Currently, the following restrictions apply to record inserts/updates:

• Record variables are allowed only in the following places:

  • On the right side of the SET clause in an UPDATE statement

  • In the VALUES clause of an INSERT statement

  • In the INTO subclause of a RETURNING clause

  Record variables are not allowed in a SELECT list, WHERE clause, GROUP BY clause, or ORDER BY clause.

• The keyword ROW is allowed only on the left side of a SET clause. Also, you cannot use ROW with a subquery.

• In an UPDATE statement, only one SET clause is allowed if ROW is used.

• If the VALUES clause of an INSERT statement contains a record variable, no other variable or value is allowed in the clause.

• If the INTO subclause of a RETURNING clause contains a record variable, no other variable or value is allowed in the subclause.

• The following are not supported:

  • Nested record types

  • Functions that return a record

  • Record inserts and updates using the EXECUTE IMMEDIATE statement.

Querying Data into 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.

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, we bring all the relevant data into the collection of records.
   SELECT * BULK COLLECT INTO underpaid FROM employees
      WHERE salary < 5000 ORDER BY salary DESC;
-- Now we can process the data by examining the collection, or passing it to
-- a separate procedure, instead of writing a 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;
-- We can also bring in just some of the table columns.
-- Here we 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;
/