5Using PL/SQL Collections and Records
Many programming techniques use collection types such as arrays, bags, lists, nested tables, sets, and trees. You can model these types in database applications using the PL/SQL datatypes and , which allow you to declare nested tables, associative arrays, and variable-size arrays. This chapter shows how to reference and manipulate collections of data as local variables. You also learn how the datatype lets you manipulate related values of different types as a logical unit.
This chapter contains these topics:
What are PL/SQL Collections and Records?
Collections and records are composite types that have internal components that can be manipulated individually, such as the elements of an array, record, or table.
A collection is an ordered group of elements, all of the same type. It is a general concept that encompasses lists, arrays, and other datatypes used in classic programming algorithms. Each element is addressed by a unique subscript.
A record is a group of related data items stored in fields, each with its own name and datatype. You can think of a record as a variable that can hold a table row, or some columns from a table row. The fields correspond to table columns.
The following sections discuss 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:
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".
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 . The built-in function lets you iterate over all the subscripts of a nested table, even if the sequence has gaps.
Items of type 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, references the third element in varray .
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 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.
Understanding Associative Arrays (Index-By Tables)
Associative arrays are sets of key-value pairs, where each key is unique and is used to locate a corresponding value in the array. The key can be an integer or a string.
Assigning a value using a key for the first time adds that key to the associative array. Subsequent assignments using the same key update the same entry. It is important to choose a key that is unique. For example, key values might come from the primary key of a database table, from a numeric hash function, or from concatenating strings to form a unique string value.
For example, here is the declaration of an associative array type, and two arrays of that type, using keys that are strings:
Example 5-1 Declaring Collection TypesDECLARE 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; /
Associative arrays help you represent data sets of arbitrary size, with fast lookup for an individual element without knowing its position within the array and without having to loop through all the array elements. It is like a simple version of a SQL table where you can retrieve values based on the primary key. For simple temporary storage of lookup data, associative arrays let you avoid using the disk space and network operations required for SQL tables.
Because associative arrays are intended for temporary data rather than storing persistent data, you cannot use them with SQL statements such as and . You can make them persistent for the life of a database session by declaring the type in a package and assigning the values in a package body.
How Globalization Settings Affect VARCHAR2 Keys for Associative Arrays
If settings for national language or globalization change during a session that uses associative arrays with key values, the program might encounter a runtime error. For example, changing the or initialization parameters within a session might cause methods such as and to raise exceptions. If you need to change these settings during the session, make sure to set them back to their original values before performing further operations with these kinds of associative arrays.
When you declare an associative array using a string as the key, the declaration must use a , , or type. You can use a different type, such as or , as the key value to reference an associative array. You can even use a type such as , as long as it can be converted to by the function. Note that the datatype is supported only for backward compatibility; see "LONG and LONG RAW Datatypes" for more information.
However, you must be careful when using other types that the values used as keys are consistent and unique. For example, the string value of might change if the initialization parameter changes, so that does not produce the same result as before. Two different values might turn into the same value (containing question marks instead of certain national characters). In that case, and might refer to the same element. Two different or values that differ in terms of case, accented characters, or punctuation characters might also be considered the same if the value of the initialization parameter ends in (case-insensitive comparisons) or (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 performs operations such as and , it uses its own character order even if that is 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 receives a exception.
Understanding PL/SQL Records
Records are composed of a group of fields, similar to the columns in a row. The 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 to the view or cursor.
For information on using records in PL/SQL, see the following sections in this chapter:
Choosing Which PL/SQL Collection Types to Use
If you already have code or business logic that uses some other language, you can usually translate that language's array and set types directly to PL/SQL collection types.
Arrays in other languages become varrays in PL/SQL.
Sets and bags in other languages become nested tables in PL/SQL.
Hash tables and other kinds of unordered lookup tables in other languages become associative arrays in PL/SQL.
When you are writing original code or designing the business logic from the start, you should consider the strengths of each collection type to decide which is appropriate for each situation.
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 statement or 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.
Defining Collection Types and Declaring Collection Variables
To create collections, you define a collection type, then declare variables of that type. 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 and types in the declarative part of any PL/SQL block, subprogram, or package using a definition. For the syntax information, see "Collection Definition".
For nested tables and varrays declared within PL/SQL, the element type of the table or varray can be any PL/SQL datatype except .
When defining a type, you must specify its maximum size with a positive integer. In the following example, you define a type that stores up to 366 dates:DECLARE TYPE Calendar IS VARRAY(366) OF DATE;
Associative arrays (also known as index-by tables) let you insert elements using arbitrary key values. The keys do not have to be consecutive.
The key datatype can be , , or , or one of subtypes , , or . Note that and are identical dataypes.
You must specify the length of a -based key, except for which is equivalent to declaring a key type of . The types , , , , and are not allowed as keys for an associative array. Note that the and datatypes are supported only for backward compatibility; see "LONG and LONG RAW Datatypes" 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 -based key, you can use other types, such as or , as long as they can be converted to with the 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 ArrayDECLARE 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; /
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 .
Example 5-3 Declaring Nested Tables, Varrays, and Associative ArraysDECLARE 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 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.
Example 5-4 Declaring Collections with %TYPEDECLARE 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.
Example 5-5 Declaring a Procedure Parameter as a Nested TableCREATE 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 from outside the package, you declare a variable of type and pass that variable as the parameter.
Example 5-6 Calling a Procedure With a Nested Table ParameterDECLARE 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 clause of a function specification.
To specify the element type, you can use , which provides the datatype of a variable or database column. Also, you can use , 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 %ROWTYPEDECLARE -- 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 type to specify the element type. See "Defining and Declaring Records".
Example 5-8 VARRAY of RecordsDECLARE 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 constraint on the element type, as shown in Example 5-9.
Example 5-9 NOT NULL Constraint on Collection ElementsDECLARE 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; /
Initializing and Referencing Collections
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 TableDECLARE 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 VarrayDECLARE -- In the varray, we 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), we can put up to 10-- elements in the constructor dept_names := dnames_var('Shipping','Sales','Finance','Payroll'); END; /
Unless you impose the 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 ElementsDECLARE 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 was of type dnamesNoNulls_type, we 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 call the collection's method to add elements later.
Example 5-13 Combining Collection Declaration and ConstructorDECLARE 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 ConstructorDECLARE 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, we 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; /
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
where is an expression that yields an integer in most cases, or a for associative arrays declared with strings as keys.
The allowed subscript ranges are:
For nested tables, 1 .. 2147483647 (the upper limit of ).
For varrays, 1 .. , 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 length limit in the type declaration, and the database character set.
Example 5-15 shows how to reference an element in a nested table.
Example 5-15 Referencing a Nested Table ElementDECLARE 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.
Example 5-16 Referencing an Element of an Associative ArrayDECLARE 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; /
One collection can be assigned to another by an , , , or 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:
() := ;
where yields a value of the type specified for elements in the collection type definition.
You can use operators such as , , , and to transform nested tables as part of an assignment statement.
Assigning a value to a collection element can cause exceptions, such as:
If the subscript is or is not convertible to the right datatype, PL/SQL raises the predefined exception . Usually, the subscript must be an integer. Associative arrays can also be declared to have subscripts.
If the subscript refers to an uninitialized element, PL/SQL raises .
If the collection is atomically null, PL/SQL raises .
For more information on collection exceptions, see "Avoiding Collection Exceptions", Example 5-38, and "Summary of Predefined PL/SQL Exceptions".
Example 5-17 shows that collections must have the same datatype for an assignment to work. Having the same element type is not enough.
Example 5-17 Datatype Compatibility for Collection AssignmentDECLARE 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 datatype. 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 not the same type. group3 surname_typ := surname_typ('Trevisi','Macleod','Marquez'); BEGIN -- Allowed because they have the same datatype group1 := group2; -- Not allowed because they have different datatypes -- group3 := group2; -- raises an error 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 the value to a collection makes it atomically null.
Example 5-18 Assigning a Null Value to a Nested TableDECLARE 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 we 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; -- We must use another constructor to give it some values. dept_names := dnames_tab('Shipping','Sales','Finance','Payroll'); END; /
Example 5-19 shows some of the ANSI-standard operators that you can apply to nested tables.
Example 5-19 Assigning Nested Tables with Set OperatorsDECLARE 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. -- Remember, you should 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('Results: <NULL>'); RETURN; END IF; IF the_nt.COUNT = 0 THEN DBMS_OUTPUT.PUT_LINE('Results: 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('Results: ' || 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; /
Example 5-20 shows an assignment to a of records with an assignment statement.
Example 5-20 Assigning Values to VARRAYs with Complex DatatypesDECLARE 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; /
Example 5-21 shows an assignment to a nested table of records with a statement.
Example 5-21 Assigning Values to Tables with Complex DatatypesDECLARE 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; /
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 , , or 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 within 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 NullDECLARE 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 we 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; /
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 TablesDECLARE 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 -- We can use = or !=, but not < or >. -- Notice that these 2 are equal even though the 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; /
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 OperatorsDECLARE 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 DEFAULT NULL, quantity NUMBER DEFAULT 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; /
Using Multilevel Collections
In addition to collections of scalar or object types, you can also create collections whose elements are 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 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. See also Example 12-17, "Using BULK COLLECT with Nested Tables".
Example 5-25 Multilevel VARRAYDECLARE 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 new 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 a new element to the 4th varray element -- and store integer 89 into it. nva(4).EXTEND; nva(4)(5) := 89; END; /
Example 5-26 Multilevel Nested TableDECLARE 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 ArrayDECLARE TYPE tb1 IS TABLE OF INTEGER INDEX BY PLS_INTEGER; -- the following is 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); -- the following is 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; /
Using Collection Methods
Collection methods make collections easier to use, and make your applications easier to maintain. These methods include , , , , , , , , , and .
A collection method is a built-in function or procedure that operates on collections and is called using dot notation. The following apply to collection methods:
Collection methods cannot be called from SQL statements.
and cannot be used with associative arrays.
, , , , , , and are functions; , , and are procedures.
, , , , , and take parameters corresponding to collection subscripts, which are usually integers but can also be strings for associative arrays.
Only can be applied to atomically null collections. If you apply another method to such collections, PL/SQL raises .
For more information, see "Collection Methods".
Checking If a Collection Element Exists (EXISTS Method)
returns if the th element in a collection exists. Otherwise, returns . By combining with , you can work with sparse nested tables. You can also use to avoid referencing a nonexistent element, which raises an exception. When passed an out-of-range subscript, returns instead of raising .
Example 5-28 Checking Whether a Collection Element EXISTSDECLARE 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)
returns the number of elements that a collection currently contains.
Example 5-29 Counting Collection Elements With COUNTDECLARE 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; /
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, always equals . You can increase or decrease the size of a varray using the and methods, so the value of can change, up to the value of the method.
For nested tables, normally equals . But, if you delete elements from the middle of a nested table, becomes smaller than . When tallying elements, ignores deleted elements. Using with no parameters sets to 0.
Checking the Maximum Size of a Collection (LIMIT Method)
For nested tables and associative arrays, which have no declared size, returns . For varrays, 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 and methods.
Example 5-30 Checking the Maximum Size of a Collection With LIMITDECLARE 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)
and return the first and last (smallest and largest) index numbers in a collection that uses integer subscripts.
For an associative array with 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 initialization parameter is set to , the order is based on the locale-specific sort order specified by the initialization parameter.
If the collection is empty, and return . If the collection contains only one element, and return the same index value.
Example 5-31 shows how to use and to iterate through the elements in a collection that has consecutive subscripts.
Example 5-31 Using FIRST and LAST With a CollectionDECLARE 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, always returns 1 and always equals .
For nested tables, normally returns 1 and equals . But if you delete elements from the beginning of a nested table, returns a number larger than 1. If you delete elements from the middle of a nested table, becomes larger than .
When scanning elements, and ignore deleted elements.
Looping Through Collection Elements (PRIOR and NEXT Methods)
() returns the index number that precedes index in a collection. returns the index number that succeeds index . If has no predecessor, returns . If has no successor, returns .
For associative arrays with keys, these methods return the appropriate key value; ordering is based on the binary values of the characters in the string, unless the initialization parameter is set to , in which case the ordering is based on the locale-specific sort order specified by the 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').
Example 5-32 Using PRIOR and NEXT to Access Collection ElementsDECLARE 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 or to traverse collections indexed by any series of subscripts. Example 5-33 uses to traverse a nested table from which some elements have been deleted.
Example 5-33 Using NEXT to Access Elements of a Nested TableDECLARE 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, and skip over deleted elements.
Increasing the Size of a Collection (EXTEND Method)
To increase the size of a nested table or varray, use .
This procedure has three forms:
appends one null element to a collection.
appends null elements to a collection.
appends copies of the th element to a collection.
You cannot use with index-by tables. You cannot use to add elements to an uninitialized collection. If you impose the constraint on a or type, you cannot apply the first two forms of to collections of that type.
operates on the internal size of a collection, which includes any deleted elements. This refers to deleted elements after using , but not without parameters which completely removes all elements. If 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.
Example 5-34 Using EXTEND to Increase the Size of a CollectionDECLARE 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 and . This refers to deleted elements after using , but not 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, returns 3, and returns 4. All deleted elements, regardless of position, are treated alike.
Decreasing the Size of a Collection (TRIM Method)
This procedure has two forms:
removes one element from the end of a collection.
removes elements from the end of a collection.
If you want to remove all elements, use without parameters.
For example, this statement removes the last three elements from nested table :
Example 5-35 Using TRIM to Decrease the Size of a CollectionDECLARE 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 is too large, raises .
operates on the internal size of a collection. If encounters deleted elements, it includes them in its tally. This refers to deleted elements after using , but not without parameters which completely removes all elements.
Example 5-36 Using TRIM on Deleted ElementsDECLARE 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 and . It is better to treat nested tables like fixed-size arrays and use only , or to treat them like stacks and use only and .
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:
with no parameters removes all elements from a collection, setting to 0.
removes the th 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 is null, does nothing.
removes all elements in the range from an associative array or nested table. If is larger than or if or is null, does nothing.
Example 5-37 Using the DELETE Method on a CollectionDECLARE 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 method. You can use without parameters to delete all elements.
If an element to be deleted does not exist, 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 , but not without parameters which completely removes all elements.
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 (, , , 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 is always derived from the parameter type definition, regardless of the parameter mode.
Avoiding 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 ExceptionsDECLARE 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, we 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 new elements, call 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) has been deleted. EXCEPTION WHEN OTHERS THEN display_error; END; END; /
Execution continues in Example 5-38 because the raised exceptions are handled in sub-blocks. See "Continuing after an Exception Is Raised". For information about the use of with exception handling, see "Retrieving the Error Code and Error Message: SQLCODE and SQLERRM".
The following list summarizes when a given exception is raised. See also "Summary of Predefined PL/SQL Exceptions".
|Collection Exception||Raised when...|
|you try to operate on an atomically null collection.|
|a subscript designates an element that was deleted, or a nonexistent element of an associative array.|
|a subscript exceeds the number of elements in a collection.|
|a subscript is outside the allowed range.|
|a subscript is null or not convertible to the key type. This exception might occur if the key is defined as a range, and the subscript is outside this range.|
In some cases, you can pass invalid subscripts to a method without raising an exception. For instance, when you pass a null subscript to , it does nothing. You can replace deleted elements by assigning values to them, without raising . This refers to deleted elements after using , but not without parameters which completely removes all elements. For example:
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; /
Packaged collection types and local collection types are never compatible. For example, suppose you want to call the following packaged procedure:
Example 5-40 Incompatibility Between Package and Local Collection TypesCREATE 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; /
The second procedure call fails, because the packaged and local types are incompatible despite their identical definitions.
Defining and Declaring Records
To create records, you define a 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 attribute to create a matching record.
You can define types in the declarative part of any PL/SQL block, subprogram, or package. When you define your own type, you can specify a constraint on fields, or give them default values. See "Record Definition".
Example 5-42 and Example 5-42 illustrate record type declarations.
Example 5-41 Declaring and Initializing a Simple Record TypeDECLARE 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 TypesDECLARE -- 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, we must supply a default 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 we can 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); -- We didn't fill in the name field, so it takes the default value declared DBMS_OUTPUT.PUT_LINE(rec2.name); END; /
To store a record in the database, you can specify it in an or statement, if its fields match the columns in the table:
You can use 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 or the precision of a ). Example 5-43 defines types to hold information about a department:
Example 5-43 Using %ROWTYPE to Declare a RecordDECLARE -- Best: use %ROWTYPE instead of specifying each column. -- Use <cursor>%ROWTYPE instead of <table>%ROWTYPE because -- we only want some columns. -- Declaring the cursor doesn't run the query, so no performance hit. 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; -- Final technique, writing out each field name and specifying the type directly, -- is 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 object types.
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 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.
Example 5-44 Returning a Record from a FunctionDECLARE 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:
Example 5-45 Using a Record as Parameter to a ProcedureDECLARE 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 Declaring a Nested RecordDECLARE 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.
Assigning Values to Records
Variables and Constants in PL/SQL
The following Tip is from the outstanding book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006:
Variables and constants must be declared for use in procedural and SQL code, although the datatypes available in SQL are only a subset of those available in PL/SQL. All variables and constants must be declared before they are referenced.
The declarations of variables and constants are similar, but constant definitions must contain the CONSTANT keyword and must be assigned a value as part of the definition. Subsequent attempts to assign a value to a constant will result in an error.
The following example shows some basic variable and constant definitions, along with a subsequent assignment of a value to a constant resulting in an error.
l_con_string CONSTANT VARCHAR2(20) := 'This is a constant.';
l_string := 'Variable';
l_number := 1;
l_con_string := 'This will fail';
l_con_string := 'This will fail';
ERROR at line 10:
ORA-06550: line 10, column 3:
PLS-00363: expression 'L_CON_STRING' cannot be used as an assignment target
ORA-06550: line 10, column 3:
PL/SQL: Statement ignored
In addition to standard variable declarations used within SQL, PL/SQL allows variable datatypes to match the datatypes of existing columns, rows or cursors using the %TYPE and %ROWTYPE qualifiers. This makes code maintenance much easier. The following code shows each of these definitions in practice.
-- Specific column from table.
-- Whole record from table.
CURSOR c_user_data IS
WHERE username = 'SYS';
-- Record that matches cursor definition.
-- Specific column from table.
WHERE username = 'SYS';
DBMS_OUTPUT.put_line('l_username=' || l_username);
-- Whole record from table.
WHERE username = 'SYS';
-- Record that matches cursor definition.
PL/SQL procedure successfully completed.
The %TYPE qualifier signifies that the variable datatype should match that of the specified table column, while the %ROWTYPE qualifier signifies that the variable datatype should be a record structure that matches the specified table or cursor structure. Notice that the record structures use the dot notation (variable.column) to reference the individual column data within the record structure.
Values can be assigned to variables directly using the “:=” assignment operator, via a SELECT ... INTO statement or when used as OUT or IN OUT parameter from a procedure. All three assignment methods are shown in the example below.
PROCEDURE add(p1 IN NUMBER,
p2 IN NUMBER,
p3 OUT NUMBER) AS
p3 := p1 + p2;
-- Direct assignment.
l_number := 1;
-- Assignment via a select.
-- Assignment via a procedure parameter.
add(1, 2, l_number);
SQL inside PL/SQL
The SQL language is fully integrated into PL/SQL, so much so that they are often mistaken as being a single language by newcomers. It is possible to manually code the retrieval of data using explicit cursors, or to allow Oracle do the hard work and use implicit cursors. Examples of both explicit and implicit cursors are presented below, all of which rely on the following definition table.
CREATE TABLE sql_test (
INSERT INTO sql_test (id, description) VALUES (1, 'One');
INSERT INTO sql_test (id, description) VALUES (2, 'Two');
INSERT INTO sql_test (id, description) VALUES (3, 'Three');
The SELECT ... INTO statement allows data from one or more columns of a specific row to be retrieved into variables or record structures using an implicit cursor.
SET SERVEROUTPUT ON
WHERE id = 1;
DBMS_OUTPUT.put_line('l_description=' || l_description);
PL/SQL procedure successfully completed.
The previous example can be recoded to use an explicit cursor as shown below. Notice that the cursor is now defined in the declaration section and is explicitly opened and closed, making the code larger and a little ugly.
SET SERVEROUTPUT ON
CURSOR c_data (p_id IN NUMBER) IS
WHERE id = p_id;
OPEN c_data (p_id => 1);
DBMS_OUTPUT.put_line('l_description=' || l_description);
PL/SQL procedure successfully completed.
When a query returns multiple rows, it can be processed within a loop. The following example uses a cursor FOR-LOOP to cycle through multiple rows of an implicit cursor. Notice there is no need for a variable definition as “cur_rec” acts as a pointer to the current record of the cursor.
SET SERVEROUTPUT ON
FOR cur_rec IN (SELECT description
DBMS_OUTPUT.put_line('cur_rec.description=' || cur_rec.description);
PL/SQL procedure successfully completed.
The explicit cursor version of the previous example is displayed below. Once again the cursor management is all done manually, but this time the exit from the loop must also be managed manually.
SET SERVEROUTPUT ON
CURSOR c_data IS
EXIT WHEN c_data%NOTFOUND;
DBMS_OUTPUT.put_line('l_description=' || l_description);
PL/SQL procedure successfully completed.
In most situations the explicit cursors provide a faster and cleaner solution to data retrieval than their explicit equivalents, a subject that will be covered in more detail in Chapter 2.