What Is a Collection?
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.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. (They 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.
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 upper bound. 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 are unbounded, while arrays have a fixed upper bound (see Figure 5-1). The size of a nested table can increase dynamically.
-
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 functionNEXT
lets you iterate over all the subscripts of a nested table, even if the sequence has gaps.
Understanding Varrays
Items of typeVARRAY
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
.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.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:
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; /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
INSERT
and SELECT INTO
. 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 withVARCHAR2
key values, the program might encounter a runtime error. For example, changing the NLS_COMP
or NLS_SORT
initialization parameters within a session might cause methods such as NEXT
and PRIOR
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
VARCHAR2
, STRING
, or LONG
type. You can use a different type, such as NCHAR
or NVARCHAR2
, as the key value to reference an associative array. You can even use a type such as DATE
, as long as it can be converted to VARCHAR2
by the TO_CHAR
function.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
SYSDATE
might change if the NLS_DATE_FORMAT
initialization parameter changes, so that array_element(SYSDATE)
does not produce the same result as before. Two different NVARCHAR2
values might turn into the same VARCHAR2
value (containing question marks instead of certain national characters). In that case, array_element(national_string1)
and array_element(national_string2)
might refer to the same element. Two different CHAR
or VARCHAR2
values that differ in terms of case, accented characters, or
punctuation characters might also be considered the same if the value of
the NLS_SORT
initialization parameter ends in _CI
(case-insensitive comparisons) or _AI
(accent- and case-insensitive comparisons).When you pass an associative array as a parameter to a remote database using a database link, the two databases can have different globalization settings. When the remote database performs operations such as
FIRST
and NEXT
, 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 VALUE_ERROR
exception.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.
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.
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 predefined upper bound for index values.
- 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 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
To create collections, you define a collection type, then declare variables of that type. You can defineTABLE
and VARRAY
types in the declarative part of any PL/SQL block, subprogram, or package.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.
Nested Tables
To define a PL/SQL type for nested tables, use the syntax:
TYPE type_name IS TABLE OF element_type [NOT NULL];
type_name
is a type specifier used later to declare collections. For nested tables declared within PL/SQL,
element_type
is any PL/SQL datatype except:REF CURSOR
CREATE TYPE
statement have additional restrictions on the element type. They cannot use the following element types:BINARY_INTEGER
,PLS_INTEGER
BOOLEAN
LONG
,LONG
RAW
NATURAL
,NATURALN
POSITIVE
,POSITIVEN
REF
CURSOR
SIGNTYPE
STRING
Varrays
To define a PL/SQL type for varrays, use the syntax:
TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit) OF element_type [NOT NULL];The meanings of
type_name
and element_type
are the same as for nested tables.
size_limit
is a positive integer literal representing the maximum number of elements in the array.When defining a
VARRAY
type, you must specify its maximum size. In the following example, you define a type that stores up to 366 dates:DECLARE TYPE Calendar IS VARRAY(366) OF DATE; BEGIN NULL; END; /
Associative Arrays
Associative arrays (also known as index-by tables) let you insert elements using arbitrary key values. The keys do not have to be consecutive. They use the syntax:
TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY [PLS_INTEGER | BINARY_INTEGER | VARCHAR2(size_limit)]; INDEX BY key_type;The key_type can be numeric, either
PLS_INTEGER or BINARY_INTEGER
. It can also be VARCHAR2
or one of its subtypes VARCHAR
, STRING
, or LONG
. You must specify the length of a VARCHAR2
-based key, except for LONG
which is equivalent to declaring a key type of VARCHAR2(32760)
. The types RAW
, LONG RAW
, ROWID
, CHAR
, and CHARACTER
are not allowed as keys for an associative array.An initialization clause is not allowed. There is no constructor notation for associative arrays.
When you reference an element of an associative array that uses a
VARCHAR2
-based key, you can use other types, such as DATE
or TIMESTAMP
, as long as they can be converted to VARCHAR2
with the TO_CHAR
function.Associative arrays can store data using a primary key value as the index, where the key values are not sequential. The example below creates a single element in an associative array, with a subscript of 100 rather than 1:
DECLARE TYPE EmpTabTyp IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER; emp_tab EmpTabTyp; BEGIN /* Retrieve employee record. */ SELECT * INTO emp_tab(100) FROM employees WHERE employee_id = 100; END; /
Defining SQL Types Equivalent to PL/SQL Collection Types
To store nested tables and varrays inside database tables, you must also declare SQL types using theCREATE TYPE
statement. The SQL types can be used as columns or as attributes of SQL object types.You can declare equivalent types within PL/SQL, or use the SQL type name in a PL/SQL variable declaration.
Example 5-1 Declaring a Nested Table in SQL
The following SQL*Plus script shows how you might declare a nested table in SQL, and use it as an attribute of an object type:
The following SQL*Plus script shows how you might declare a nested table in SQL, and use it as an attribute of an object type:
CREATE TYPE CourseList AS TABLE OF VARCHAR2(10) -- define type / CREATE TYPE Student AS OBJECT ( -- create object id_num INTEGER(4), name VARCHAR2(25), address VARCHAR2(35), status CHAR(2), courses CourseList); -- declare nested table as attribute / DROP TYPE Student; DROP TYPE CourseList;The identifier
courses
represents an entire nested table. Each element of courses
stores the name of a college course such as 'Math 1020'
.
Example 5-2 Creating a Table with a Varray Column
The script below creates a database column that stores varrays. Each varray element contains a
The script below creates a database column that stores varrays. Each varray element contains a
VARCHAR2
.-- Each project has a 16-character code name. -- We will store up to 50 projects at a time in a database column. CREATE TYPE ProjectList AS VARRAY(50) OF VARCHAR2(16); / CREATE TABLE department ( -- create database table dept_id NUMBER(2), name VARCHAR2(15), budget NUMBER(11,2), -- Each department can have up to 50 projects. projects ProjectList); DROP TABLE department; DROP TYPE ProjectList;
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 asNUMBER
.
Example 5-3 Declaring Nested Tables, Varrays, and Associative Arrays
DECLARE TYPE nested_type IS TABLE OF VARCHAR2(20); 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 v1 := nested_type('Arbitrary','number','of','strings'); v2 := varray_type(10, 20, 40, 80, 160); -- Up to 5 integers v3(99) := 10; -- Just start assigning to elements. v3(7) := 100; -- Subscripts can be any integer values. v4(42) := 'Cat'; -- Just start assigning to elements. v4(54) := 'Hat'; -- Subscripts can be any integer values. v5('Canada') := 'North America'; -- Just start assigning to elements. v5('Greece') := 'Europe'; -- Subscripts can be string values. END; /
Example 5-4 Declaring Collections with %TYPE
You can use
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_Colors IS VARRAY(10) OF VARCHAR2(20); TYPE Many_Colors IS VARRAY(100) OF VARCHAR2(64); some_colors Few_Colors; -- If we change the type of SOME_COLORS from FEW_COLORS to MANY_COLORS, -- RAINBOW and CRAYONS will use the same type when this block is recompiled. rainbow some_colors%TYPE; crayons some_colors%TYPE; BEGIN NULL; END; /
Example 5-5 Declaring a Procedure Parameter as a Nested Table
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. The following example declares a nested table as a parameter of a packaged procedure:
You can also specify a collection type in the
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. The following example 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 (who_gets_em IN Staff_List); END personnel; / DROP PACKAGE personnel;To call
PERSONNEL.AWARD_BONUSES
from outside the package, you declare a variable of type PERSONNEL.STAFF
and pass that variable as the parameter.You can also specify a collection type in the
RETURN
clause of a function specification.
Example 5-6 Specifying Collection Element Types with %TYPE and %ROWTYPE
To specify the element type, you can use
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. Two examples follow: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; -- 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 Top_Salespeople IS VARRAY(10) OF employees%ROWTYPE; -- Declare a cursor to select a subset of columns. CURSOR c1 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 c1%ROWTYPE; BEGIN NULL; END; /
Example 5-7 VARRAY of Records
This example uses a
This example uses a
RECORD
type to specify the element type:DECLARE TYPE GlossEntry IS RECORD ( term VARCHAR2(20), meaning VARCHAR2(200) ); TYPE Glossary IS VARRAY(250) OF GlossEntry; BEGIN NULL; END; /
Example 5-8 NOT NULL Constraint on Collection Elements
You can also impose a
You can also impose a
NOT
NULL
constraint on the element type:DECLARE TYPE EmpList IS TABLE OF employees.employee_id%TYPE NOT NULL; my_employees EmpList := EmpList(100, 150, 160, 200); BEGIN my_employees(3) := NULL; -- Assigning NULL raises an exception 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-9 Constructor for a Nested Table
The following example initializes a nested table using a constructor, which looks like a function with the same name as the collection type:
The following example initializes a nested table using a constructor, which looks like a function with the same name as the collection type:
DECLARE TYPE Colors IS TABLE OF VARCHAR2(16); rainbow Colors; BEGIN rainbow := Colors('Red','Orange','Yellow','Green','Blue','Indigo','Violet'); END; /Because a nested table does not have a declared maximum size, you can put as many elements in the constructor as necessary.
Example 5-10 Constructor for a Varray
This example initializes a varray using a constructor, which looks like a function with the same name as the collection type:
This example initializes a varray using a constructor, which looks like a function with the same name as the collection type:
DECLARE -- In the varray, we put an upper limit on the number of elements. TYPE Colors IS VARRAY(10) OF VARCHAR2(16); rainbow Colors; BEGIN -- Since COLORS is declared as VARRAY(10), we can put up to 10 -- elements in the constructor. rainbow := Colors('Red','Orange','Yellow','Green','Blue','Indigo','Violet'); END; /
Example 5-11 Collection Constructor Including Null Elements
Unless you impose the
Unless you impose the
NOT
NULL
constraint in the type declaration, you can pass null elements to a constructor:DECLARE TYPE Colors IS TABLE OF VARCHAR2(20); my_colors Colors; TYPE ColorsNoNulls IS TABLE OF VARCHAR2(20) NOT NULL; BEGIN my_colors := Colors('Sienna',NULL,'Teal','Umber',NULL); -- If MY_COLORS was of type ColorsNoNulls, we could not include -- null values in the constructor. END; /
Example 5-12 Combining Collection Declaration and Constructor
You can initialize a collection in its declaration, which is a good programming practice:
You can initialize a collection in its declaration, which is a good programming practice:
DECLARE TYPE Colors IS TABLE OF VARCHAR2(20); my_colors Colors := Colors('Brown','Gray','Beige'); BEGIN NULL; END; /
Example 5-13 Empty Varray Constructor
If you call a constructor without arguments, you get an empty but non-null collection:
If you call a constructor without arguments, you get an empty but non-null collection:
DECLARE TYPE Colors IS VARRAY(100) OF VARCHAR2(20); my_colors Colors; BEGIN IF my_colors IS NULL THEN dbms_output.put_line('Before initialization, the varray is null.'); -- While the varray is null, we can't check its COUNT attribute. -- dbms_output.put_line('It has ' || my_colors.COUNT || ' elements.'); ELSE dbms_output.put_line('Before initialization, the varray is not null.'); END IF; my_colors := Colors(); -- initialize empty varray IF my_colors 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 ' || my_colors.COUNT || ' elements.'); END IF; END; /In this case, you can call the collection's
EXTEND
method to add elements later.
Example 5-14 Nested Table Constructor Within a SQL Statement
In this example, you insert several scalar values and a
In this example, you insert several scalar values and a
CourseList
nested table into the SOPHOMORES
table.BEGIN INSERT INTO sophomores VALUES (5035, 'Janet Alvarez', '122 Broad St', 'FT', CourseList('Econ 2010', 'Acct 3401', 'Mgmt 3100'));
Example 5-15 Varray Constructor Within a SQL Statement
In this example, you insert a row into database table
In this example, you insert a row into database table
DEPARTMENT
. The varray constructor ProjectList()
provides a value for column PROJECTS
.BEGIN INSERT INTO department VALUES(60, 'Security', 750400, ProjectList('New Badges', 'Track Computers', 'Check Exits'));
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 syntaxcollection_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 .. 2**31.
-
For varrays, 1 ..
size_limit
, where you specify the limit in the declaration. - For associative arrays with a numeric key, -2**31 .. 2**31.
-
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-16 Referencing a Nested Table Element By Subscript
This example shows how to reference an element in the nested table
This example shows how to reference an element in the nested table
NAMES
:DECLARE TYPE Roster IS TABLE OF VARCHAR2(15); names Roster := Roster('J Hamil', 'D Caruso', 'R Singh'); BEGIN FOR i IN names.FIRST .. names.LAST LOOP IF names(i) = 'J Hamil' THEN NULL; END IF; END LOOP; END; /
Example 5-17 Passing a Nested Table Element as a Parameter
This example shows that you can reference the elements of a collection in subprogram calls:
This example shows that you can reference the elements of a collection in subprogram calls:
DECLARE TYPE Roster IS TABLE OF VARCHAR2(15); names Roster := Roster('J Hamil', 'D Piro', 'R Singh'); i BINARY_INTEGER := 2; BEGIN verify_name(names(i)); -- call procedure END; /
Assigning Collections
One collection can be assigned to another by anINSERT
, UPDATE
, FETCH
, or SELECT
statement, an assignment statement, or a subprogram call.You can assign the value of an expression to a specific element in a collection using the syntax:
collection_name(subscript) := expression;where
expression
yields a value of the type specified for elements in the collection type definition.You can use operators such as
SET
, MULTISET UNION
, MULTISET INTERSECT
, and MULTISET EXCEPT
to transform nested tables as part of an assignment statement.
Example 5-18 Datatype Compatibility for Collection Assignment
This example shows that collections must have the same datatype for an assignment to work. Having the same element type is not enough.
This example shows that collections must have the same datatype for an assignment to work. Having the same element type is not enough.
DECLARE TYPE last_name_typ IS VARRAY(3) OF VARCHAR2(64); TYPE surname_typ IS VARRAY(3) OF VARCHAR2(64); -- These first two variables have the same 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; END; /
Example 5-19 Assigning a Null Value to a Nested Table
If you assign an atomically null nested table or varray to a second nested table or varray, the second collection must be reinitialized:
If you assign an atomically null nested table or varray to a second nested table or varray, the second collection must be reinitialized:
DECLARE TYPE Colors IS TABLE OF VARCHAR2(64); -- This nested table has some values. crayons Colors := Colors('Silver','Gold'); -- This nested table is not initialized ("atomically null"). empty_set Colors; BEGIN -- At first, the initialized variable is not null. if crayons IS NOT NULL THEN dbms_output.put_line('OK, at first crayons is not null.'); END IF; -- Then we assign a null nested table to it. crayons := empty_set; -- Now it is null. if crayons IS NULL THEN dbms_output.put_line('OK, now crayons has become null.'); END IF; -- We must use another constructor to give it some values. crayons := Colors('Yellow','Green','Blue'); END; /In the same way, assigning the value
NULL
to a collection makes it atomically null.
Example 5-20 Possible Exceptions for Collection Assignments
Assigning a value to a collection element can cause various exceptions:
Assigning a value to a collection element can cause various exceptions:
-
If the subscript is null or is not convertible to the right datatype, PL/SQL raises the predefined exception
VALUE_ERROR
. Usually, the subscript must be an integer. Associative arrays can also be declared to haveVARCHAR2
subscripts. -
If the subscript refers to an uninitialized element, PL/SQL raises
SUBSCRIPT_BEYOND_COUNT
. -
If the collection is atomically null, PL/SQL raises
COLLECTION_IS_NULL
.
DECLARE TYPE WordList IS TABLE OF VARCHAR2(5); words WordList; BEGIN /* Assume execution continues despite the raised exceptions. */ -- Raises COLLECTION_IS_NULL. We haven't used a constructor yet. -- This exception applies to varrays and nested tables, but not to -- associative arrays which don't need a constructor. words(1) := 10; -- After using a constructor, we can assign values to the elements. words := WordList(10,20,30); -- Any expression that returns a VARCHAR2(5) is OK. words(1) := 'yes'; words(2) := words(1) || 'no'; -- Raises VALUE_ERROR because the assigned value is too long. words(3) := 'longer than 5 characters'; -- Raises VALUE_ERROR because the subscript of a nested table must -- be an integer. words('B') := 'dunno'; -- Raises SUBSCRIPT_BEYOND_COUNT because we only made 3 elements -- in the constructor. To add new ones, we must call the EXTEND -- method first. words(4) := 'maybe'; END; /
Example 5-21 Assigning Nested Tables with Set Operators
This example shows some of the ANSI-standard operators that you can apply to nested tables:
This example shows some of the ANSI-standard operators that you can apply to nested tables:
DECLARE TYPE nested_typ IS TABLE OF NUMBER; nt1 nested_typ := nested_typ(1,2,3); nt2 nested_typ := nested_typ(3,2,1); nt3 nested_typ := nested_typ(2,3,1,3); nt4 nested_typ := nested_typ(1,2,4); answer nested_typ; -- The results might be in a different order than you expect. -- (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; /
Comparing Collections
You can check whether a collection is null, and whether two collections are the same. Comparisons such as greater than, less than, and so on are not allowed.This restriction also applies to implicit comparisons. For example, collections cannot appear in a
DISTINCT
, GROUP
BY
, or ORDER
BY
list.If you want to do such comparison operations, you must define your own notion of what it means for collections to be greater than, less than, and so on, and write one or more functions to examine the collections and their elements and return a true or false value.
You can apply set operators (
CARDINALITY
, MEMBER OF
, IS A SET
, IS EMPTY
) to check certain conditions within a nested table or between two nested tables.
Example 5-22 Checking if a Collection Is Null
Nested tables and varrays can be atomically null, so they can be tested for nullity:
Nested tables and varrays can be atomically null, so they can be tested for nullity:
DECLARE TYPE Staff IS TABLE OF Employee; members Staff; BEGIN -- Condition yields TRUE because we haven't used a constructor. IF members IS NULL THEN ... END;
Example 5-23 Comparing Two Collections
Collections can be compared for equality or inequality. They cannot be ordered, because there is no "greater than" or "less than" comparison.
Collections can be compared for equality or inequality. They cannot be ordered, because there is no "greater than" or "less than" comparison.
DECLARE TYPE Colors IS TABLE OF VARCHAR2(64); primaries Colors := Colors('Blue','Green','Red'); rgb Colors := Colors('Red','Green','Blue'); traffic_light Colors := Colors('Red','Green','Amber'); BEGIN -- We can use = or !=, but not < or >. -- Notice that these 2 are equal even though the members are in different order. IF primaries = rgb THEN dbms_output.put_line('OK, PRIMARIES and RGB have the same members.'); END IF; IF rgb != traffic_light THEN dbms_output.put_line('OK, RGB and TRAFFIC_LIGHT have different members.'); END IF; END; /
Example 5-24 Comparing Nested Tables with Set Operators
You can test certain properties of a nested table, or compare two nested tables, using ANSI-standard set operations:
You can test certain properties of a nested table, or compare two nested tables, using ANSI-standard set operations:
DECLARE TYPE nested_typ IS TABLE OF NUMBER; nt1 nested_typ := nested_typ(1,2,3); nt2 nested_typ := nested_typ(3,2,1); nt3 nested_typ := nested_typ(2,3,1,3); nt4 nested_typ := nested_typ(1,2,4); answer BOOLEAN; howmany NUMBER; PROCEDURE testify(truth BOOLEAN 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 PL/SQL Collections with SQL Statements
Collections let you manipulate complex datatypes within PL/SQL. Your program can compute subscripts to process specific elements in memory, and use SQL to store the results in database tables.
Example 5-25 Creating a SQL Type Corresponding to a PL/SQL Nested Table
In SQL*Plus, you can create SQL types whose definitions correspond to PL/SQL nested tables and varrays:
In SQL*Plus, you can create SQL types whose definitions correspond to PL/SQL nested tables and varrays:
SQL> CREATE TYPE CourseList AS TABLE OF VARCHAR2(64);You can use these SQL types as columns in database tables:
SQL> CREATE TABLE department ( 2 name VARCHAR2(20), 3 director VARCHAR2(20), 4 office VARCHAR2(20), 5 courses CourseList) 6 NESTED TABLE courses STORE AS courses_tab;Each item in column
COURSES
is a nested table that will store the courses offered by a given department. The NESTED
TABLE
clause is required whenever a database table has a nested table column.
The clause identifies the nested table and names a system-generated
store table, in which Oracle stores the nested table data.
Example 5-26 Inserting a Nested Table into a Database Table
Now, you can populate the database table. The table constructor provides values that all go into the single column
Now, you can populate the database table. The table constructor provides values that all go into the single column
COURSES
:BEGIN INSERT INTO department VALUES('English', 'Lynn Saunders', 'Breakstone Hall 205', CourseList('Expository Writing', 'Film and Literature', 'Modern Science Fiction', 'Discursive Writing', 'Modern English Grammar', 'Introduction to Shakespeare', 'Modern Drama', 'The Short Story', 'The American Novel')); END;
Example 5-27 Using PL/SQL Nested Tables with INSERT, UPDATE, DELETE, and SELECT Statements
You can retrieve all the courses offered by the English department into a PL/SQL nested table:
You can retrieve all the courses offered by the English department into a PL/SQL nested table:
CREATE TYPE ColorList AS TABLE OF VARCHAR2(64); / CREATE TABLE flowers (name VARCHAR2(20), colors ColorList) NESTED TABLE colors STORE AS colors_tab; BEGIN INSERT INTO flowers VALUES('Rose', ColorList('Red','Yellow','White')); INSERT INTO flowers VALUES('Tulip', ColorList('Red','White','Yellow', 'Blue')); INSERT INTO flowers VALUES('Iris', ColorList('White','Purple')); COMMIT; END; / DECLARE -- This type declaration is not needed, because PL/SQL can see the SQL type. -- TYPE ColorList IS TABLE OF VARCHAR2(64); -- Declare a variable that can hold a set of colors. my_colors ColorList; -- Declare a record that can hold a row from the table. -- One of the record fields is a set of colors. my_flower flowers%ROWTYPE; new_colors ColorList; BEGIN -- Look up a name and query just the associated colors. SELECT colors INTO my_colors FROM flowers WHERE name = 'Rose'; FOR i IN my_colors.FIRST .. my_colors.LAST LOOP dbms_output.put_line('Rose color = ' || my_colors(i)); END LOOP; -- Look up a name and query the entire row. SELECT * INTO my_flower FROM flowers WHERE name = 'Iris'; -- Now COLORS is a field in a record, so we access it with dot notation. FOR i IN my_flower.colors.FIRST .. my_flower.colors.LAST LOOP -- Because we have all the table columns in the record, we can refer to NAME also. dbms_output.put_line(my_flower.name || ' color = ' || my_flower.colors(i)); END LOOP; -- We can replace a set of colors by making a new collection and using it -- in an UPDATE statement. new_colors := ColorList('Red','Yellow','White','Pink'); UPDATE flowers SET colors = new_colors WHERE name = 'Rose'; -- Or we can modify the original collection and use it in the UPDATE. -- We'll add a new final element and fill in a value. my_flower.colors.EXTEND(1); my_flower.colors(my_flower.colors.COUNT) := 'Yellow'; UPDATE flowers SET colors = my_flower.colors WHERE name = my_flower.name; -- We can even treat the nested table column like a real table and -- insert, update, or delete elements. -- The TABLE operator makes the statement apply to the nested table produced by the subquery. INSERT INTO TABLE(SELECT colors FROM flowers WHERE name = 'Rose') VALUES('Black'); DELETE FROM TABLE(SELECT colors FROM flowers WHERE name = 'Rose') WHERE column_value = 'Yellow'; UPDATE TABLE(SELECT colors FROM flowers WHERE name = 'Iris') SET column_value = 'Indigo' WHERE column_value = 'Purple'; COMMIT; END; / DROP TABLE flowers; DROP TYPE ColorList;Within PL/SQL, you can manipulate the nested table by looping through its elements, using methods such as
TRIM
or EXTEND
, and updating some or all of the elements. Afterwards, you can store the updated table in the database again.
Example 5-28 Updating a Nested Table within a Database Table
You can revise the list of courses offered by the English Department:
You can revise the list of courses offered by the English Department:
DECLARE new_courses CourseList := CourseList('Expository Writing', 'Film and Literature', 'Discursive Writing', 'Modern English Grammar', 'Realism and Naturalism', 'Introduction to Shakespeare', 'Modern Drama', 'The Short Story', 'The American Novel', '20th-Century Poetry', 'Advanced Workshop in Poetry'); BEGIN UPDATE department SET courses = new_courses WHERE name = 'English'; END;
Using PL/SQL Varrays with INSERT, UPDATE, and SELECT Statements
This example shows how you can transfer varrays between PL/SQL variables and SQL tables. You can insert table rows containing varrays, update a row to replace its varray, and select varrays into PL/SQL variables. You cannot update or delete individual varray elements directly with SQL; you have to select the varray from the table, change it in PL/SQL, then update the table to include the new varray.-- By using a varray, we put an upper limit on the number of elements -- and ensure they always come back in the same order. CREATE TYPE RainbowTyp AS VARRAY(7) OF VARCHAR2(64); / CREATE TABLE rainbows (language VARCHAR2(64), colors RainbowTyp); BEGIN INSERT INTO rainbows VALUES('English', RainbowTyp('Red','Orange','Yellow','Green','Blue','Indigo','Violet')); INSERT INTO rainbows VALUES('Francais', RainbowTyp('Rouge','Orange','Jaune','Vert','Bleu','Indigo','Violet')); COMMIT; END; / DECLARE new_colors RainbowTyp := RainbowTyp('Crimson','Orange','Amber','Forest','Azure','Indigo','Violet'); some_colors RainbowTyp; BEGIN UPDATE rainbows SET colors = new_colors WHERE language = 'English'; COMMIT; SELECT colors INTO some_colors FROM rainbows WHERE language = 'Francais'; FOR i IN some_colors.FIRST .. some_colors.LAST LOOP dbms_output.put_line('Color = ' || some_colors(i)); END LOOP; END; / DROP TABLE rainbows; DROP TYPE RainbowTyp;
Manipulating Individual Collection Elements with SQL
By default, SQL operations store and retrieve whole collections rather than individual elements. To manipulate the individual elements of a collection with SQL, use theTABLE
operator. The TABLE
operator uses a subquery to extract the varray or nested table, so that the INSERT
, UPDATE
, or DELETE
statement applies to the nested table rather than the top-level table.
Example 5-29 Performing INSERT, UPDATE, and DELETE Operations on PL/SQL Nested Tables
To perform DML operations on a PL/SQL nested table, use the operators
The operands of
To perform DML operations on a PL/SQL nested table, use the operators
TABLE
and CAST
.
This way, you can do set operations on nested tables using SQL
notation, without actually storing the nested tables in the database.The operands of
CAST
are PL/SQL collection variable and a SQL collection type (created by the CREATE TYPE
statement). CAST
converts the PL/SQL collection to the SQL type.DECLARE revised CourseList := CourseList(Course(1002, 'Expository Writing', 3), Course(2020, 'Film and Literature', 4), Course(4210, '20th-Century Poetry', 4), Course(4725, 'Advanced Workshop in Poetry', 5)); num_changed INTEGER; BEGIN SELECT COUNT(*) INTO num_changed FROM TABLE(CAST(revised AS CourseList)) new, TABLE(SELECT courses FROM department WHERE name = 'English') AS old WHERE new.course_no = old.course_no AND (new.title != old.title OR new.credits != old.credits); dbms_output.put_line(num_changed); 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
CREATE TABLE
statement to see how to define the storage table.Here are some examples showing the syntax and possibilities for multilevel collections.
Example 5-30 Multilevel VARRAY
declare type t1 is varray(10) of integer; type nt1 is varray(10) of t1; -- multilevel varray type va t1 := t1(2,3,5); -- initialize multilevel varray nva nt1 := nt1(va, t1(55,6,73), t1(2,4), va); i integer; va1 t1; begin -- multilevel access i := nva(2)(3); -- i will get value 73 dbms_output.put_line('I = ' || i); -- add a 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-31 Multilevel Nested Table
declare type tb1 is table of varchar2(20); type ntb1 is table of tb1; -- table of table elements type tv1 is varray(10) of integer; type ntb2 is table of tv1; -- table of varray elements vtb1 tb1 := tb1('one', 'three'); vntb1 ntb1 := ntb1(vtb1); vntb2 ntb2 := ntb2(tv1(3,5), tv1(5,7,3)); -- table of varray elements begin vntb1.extend; vntb1(2) := vntb1(1); -- delete the first element in vntb1 vntb1.delete(1); -- delete the first string from the second table in the nested table vntb1(2).delete(1); end; /
Example 5-32 Multilevel Associative Array
declare type tb1 is table of integer index by binary_integer; -- the following is index-by table of index-by tables type ntb1 is table of tb1 index by binary_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 binary_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; /
Example 5-33 Multilevel Collections and Bulk SQL
create type t1 is varray(10) of integer; / create table tab1 (c1 t1); insert into tab1 values (t1(2,3,5)); insert into tab1 values (t1(9345, 5634, 432453)); declare type t2 is table of t1; v2 t2; begin select c1 BULK COLLECT INTO v2 from tab1; dbms_output.put_line(v2.count); -- prints 2 end; / drop table tab1; drop type t1;
Using Collection Methods
These collection methods make collections easier to use, and make your applications easier to maintain:- EXISTS
- COUNT
- LIMIT
- FIRST and LAST
- PRIOR and NEXT
- EXTEND
- TRIM
- DELETE
Collection methods cannot be called from SQL statements.
EXTEND
and TRIM
cannot be used with associative arrays.EXISTS
, COUNT
, LIMIT
, FIRST
, LAST
, PRIOR
, and NEXT
are functions; EXTEND
, TRIM
, and DELETE
are procedures.EXISTS
, PRIOR
, NEXT
, TRIM
, EXTEND
, and DELETE
take parameters corresponding to collection subscripts, which are
usually integers but can also be strings for associative arrays.Only
EXISTS
can be applied to atomically null collections. If you apply another method to such collections, PL/SQL raises COLLECTION_IS_NULL
.Checking If a Collection Element Exists (EXISTS Method)
EXISTS(n)
returns TRUE
if the n
th 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.Checking the Maximum Size of a Collection (LIMIT Method)
For nested tables and associative arrays, which have no maximum 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. For instance, if the maximum size of varray PROJECTS
is 25 elements, the following IF
condition is true:DECLARE TYPE Colors IS VARRAY(7) OF VARCHAR2(64); c Colors := Colors('Gold','Silver'); BEGIN dbms_output.put_line('C has ' || c.COUNT || ' elements now.'); dbms_output.put_line('C''s type can hold a maximum of ' || c.LIMIT || ' elements.'); dbms_output.put_line('The maximum number you can use with C.EXTEND() is ' || (c.LIMIT - c.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.The following example 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. The following example 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, useEXTEND
.You cannot use
EXTEND
with index-by tables.This procedure has three forms:
-
EXTEND
appends one null element to a collection. -
EXTEND(n)
appendsn
null elements to a collection. -
EXTEND(n,i)
appendsn
copies of thei
th element to a collection.
EXTEND
to add elements to an uninitialized.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. 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
. 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)
removesn
elements from the end of a collection.
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; / 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. Consider the following example: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
removes all elements from a collection. -
DELETE(n)
removes then
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. Ifn
is null,DELETE(n)
does nothing. -
DELETE(m,n)
removes all elements in the rangem..n
from an associative array or nested table. Ifm
is larger thann
or ifm
orn
is null,DELETE(m,n)
does nothing.
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'; nicknames.DELETE('Chip'); -- deletes element denoted by this key nicknames.DELETE('Buffy','Fluffy'); -- deletes elements with keys in this alphabetic range END; /Varrays always have consecutive subscripts, so you cannot delete individual elements except from the end (by using the
TRIM
method).If an element to be deleted does not exist,
DELETE
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.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.Note: For varray parameters, the value of
LIMIT
is always derived from the parameter type definition, regardless of the parameter mode.Avoiding Collection Exceptions
In most cases, if you reference a nonexistent collection element, PL/SQL raises a predefined exception. Consider the following example:DECLARE TYPE NumList IS TABLE OF NUMBER; nums NumList; -- atomically null BEGIN /* Assume execution continues despite the raised exceptions. */ nums(1) := 1; -- raises COLLECTION_IS_NULL (1) nums := NumList(1,2); -- initialize nested table nums(NULL) := 3; -- raises VALUE_ERROR (2) nums(0) := 3; -- raises SUBSCRIPT_OUTSIDE_LIMIT (3) nums(3) := 3; -- raises SUBSCRIPT_BEYOND_COUNT (4) nums.DELETE(1); -- delete element 1 IF nums(1) = 1 THEN NULL; END IF; -- raises NO_DATA_FOUND (5) END; /In the first case, the nested table is atomically null. In the second case, the subscript is null. In the third case, the subscript is outside the allowed range. In the fourth case, the subscript exceeds the number of elements in the table. In the fifth case, the subscript designates a deleted element.
The following list shows when a given exception is raised:
Collection Exception | Raised when... |
---|---|
COLLECTION_IS_NULL |
you try to operate on an atomically null collection. |
NO_DATA_FOUND |
a subscript designates an element that was deleted, or a nonexistent element of an associative array. |
SUBSCRIPT_BEYOND_COUNT |
a subscript exceeds the number of elements in a collection. |
SUBSCRIPT_OUTSIDE_LIMIT |
a subscript is outside the allowed range. |
VALUE_ERROR |
a subscript is null or not convertible to the key type. This exception might occur if the key is defined as a PLS_INTEGER range, and the subscript is outside this range. |
DELETE
, it does nothing. You can replace deleted elements by assigning values to them, without raising NO_DATA_FOUND
: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:
CREATE PACKAGE pkg AS TYPE NumList IS TABLE OF NUMBER; PROCEDURE print_numlist (nums NumList); 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); -- The packaged procedure can't accept a value of the local type. pkg.print_numlist(n2); -- Causes a compilation error. END; / DROP PACKAGE pkg;The second procedure call fails, because the packaged and local
VARRAY
types are incompatible despite their identical definitions.What Is a PL/SQL Record?
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
%ROWTYPE
attribute lets you declare a 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.Defining and Declaring Records
To create records, you define aRECORD
type, then declare records of that type. You can also create or find a
table, view, or PL/SQL cursor with the values you want, and use the %ROWTYPE
attribute to create a matching record.You can define
RECORD
types in the declarative part of any PL/SQL block, subprogram, or package. When you define your own RECORD
type, you can specify a NOT NULL
constraint on fields, or give them default values.DECLARE -- Declare a record type with 3 fields. TYPE rec1_t IS RECORD (field1 VARCHAR2(16), field2 NUMBER, field3 DATE); -- For any fields declared NOT NULL, 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 above. 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 above. dbms_output.put_line(rec2.name); END; /To store a record in the database, you can specify it in an
INSERT
or UPDATE
statement, if its fields match the columns in the table:...You can use
%TYPE
to specify a field type corresponding
to a table column type. Your code keeps working even if the column type
is changed (for example, to increase the length of a VARCHAR2
or the precision of a NUMBER
). The following example defines RECORD
types to hold information about a department:DECLARE -- Best: use %ROWTYPE instead of specifying each column. -- Using <cursor>%ROWTYPE instead of <table>%ROWTYPE since 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 theEMPLOYEES
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 EmpRec IS RECORD ( emp_id NUMBER(4) last_name VARCHAR2(10), dept_num NUMBER(2), job_title VARCHAR2(9), salary NUMBER(7,2)); FUNCTION nth_highest_salary (n INTEGER) RETURN EmpRec IS ... BEGIN NULL; END; /Like scalar variables, user-defined records can be declared as the formal parameters of procedures and functions:
DECLARE TYPE EmpRec IS RECORD ( emp_id emp.empno%TYPE, last_name VARCHAR2(10), job_title VARCHAR2(9), salary NUMBER(7,2)); ... PROCEDURE raise_salary (emp_info EmpRec); BEGIN ... END; /
Assigning Values to Records
To set all the fields in a record to default values, assign to it an uninitialized record of the same type:DECLARE TYPE RecordTyp IS RECORD (field1 NUMBER, field2 VARCHAR2(32) DEFAULT 'something'); rec1 RecordTyp; rec2 RecordTyp; BEGIN -- At first, rec1 has the values we assign. rec1.field1 := 100; rec1.field2 := 'something else'; -- Assigning an empty record to rec1 resets fields to their default values. -- Field1 is NULL and field2 is 'something' (because of the DEFAULT clause above). rec1 := rec2; dbms_output.put_line('Field1 = ' || NVL(TO_CHAR(rec1.field1),'<NULL>') || ', field2 = ' || rec1.field2); END; /You can assign a value to a field in a record using an assignment statement with dot notation:
emp_info.last_name := 'Fields';Instead of assigning values separately to each field in a record, you can assign values to all fields at once.
You can assign one user-defined record to another if they have the same datatype. Having fields that match exactly is not enough. Consider the following example:
DECLARE -- Two identical type declarations. TYPE DeptRec1 IS RECORD ( dept_num NUMBER(2), dept_name VARCHAR2(14)); TYPE DeptRec2 IS RECORD ( dept_num NUMBER(2), dept_name VARCHAR2(14)); dept1_info DeptRec1; dept2_info DeptRec2; dept3_info DeptRec2; BEGIN -- Not allowed; different datatypes, even though fields are the same. -- dept1_info := dept2_info; -- This assignment is OK because the records have the same type. dept2_info := dept3_info; END; /You can assign a
%ROWTYPE
record to a user-defined record if their fields match in number and order, and corresponding fields have the same datatypes:DECLARE TYPE RecordTyp IS RECORD (last employees.last_name%TYPE, id employees.employee_id%TYPE); CURSOR c1 IS SELECT last_name, employee_id FROM employees; -- Rec1 and rec2 have different types. But because rec2 is based on a %ROWTYPE, we can -- assign is to rec1 as long as they have the right number of fields and the fields -- have the right datatypes. rec1 RecordTyp; rec2 c1%ROWTYPE; BEGIN SELECT last_name, employee_id INTO rec2 FROM employees WHERE ROWNUM < 2; rec1 := rec2; dbms_output.put_line('Employee #' || rec1.id || ' = ' || rec1.last); END; /You can also use the
SELECT
or FETCH
statement to fetch column values into a record. The columns in the
select-list must appear in the same order as the fields in your record.DECLARE TYPE RecordTyp IS RECORD (last employees.last_name%TYPE, id employees.employee_id%TYPE); rec1 RecordTyp; BEGIN SELECT last_name, employee_id INTO rec1 FROM employees WHERE ROWNUM < 2; dbms_output.put_line('Employee #' || rec1.id || ' = ' || rec1.last); END; /You cannot assign a list of values to a record using an assignment statement. There is no constructor-like notation for records.
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 theINSERT
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-34 Inserting a PL/SQL Record Using %ROWTYPE
This example declares a record variable using a
This example 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 dept%ROWTYPE; BEGIN -- deptno, dname, and loc are the table columns. -- The record picks up these names from the %ROWTYPE. dept_info.deptno := 70; dept_info.dname := 'PERSONNEL'; dept_info.loc := 'DALLAS'; -- Using the %ROWTYPE means we can leave out the column list -- (deptno, dname, loc) from the INSERT statement. INSERT INTO dept VALUES dept_info; END; /
Updating the Database with PL/SQL Record Values
A PL/SQL-only extension of theUPDATE
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.
Example 5-35 Updating a Row Using a Record
You can use the keyword
The argument to
The record can also contain collections or objects.
You can use the keyword
ROW
to represent an entire row:DECLARE dept_info dept%ROWTYPE; BEGIN dept_info.deptno := 30; dept_info.dname := 'MARKETING'; dept_info.loc := 'ATLANTA'; -- The row will have values for the filled-in columns, and null -- for any other columns. UPDATE dept SET ROW = dept_info WHERE deptno = 30; END; /The keyword
ROW
is allowed only on the left side of a SET
clause.The argument to
SET ROW
must be a real PL/SQL record, not a subquery that returns a single row.The record can also contain collections or objects.
Example 5-36 Using the RETURNING Clause with a Record
The
By default, you can use this clause only when operating on exactly one row. When you use bulk SQL, you can use the form
The following example updates the salary of an employee and retrieves the employee's name, job title, and new salary into a record variable:
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.The following example 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/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 anUPDATE
statement -
In the
VALUES
clause of anINSERT
statement -
In the
INTO
subclause of aRETURNING
clause
SELECT
list,WHERE
clause,GROUP
BY
clause, orORDER
BY
clause. -
On the right side of the
-
The keyword
ROW
is allowed only on the left side of aSET
clause. Also, you cannot useROW
with a subquery. -
In an
UPDATE
statement, only oneSET
clause is allowed ifROW
is used. -
If the
VALUES
clause of anINSERT
statement contains a record variable, no other variable or value is allowed in the clause. -
If the
INTO
subclause of aRETURNING
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/updates using the
EXECUTE IMMEDIATE
statement.
Querying Data into Collections of Records
You can use theBULK 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 < 2500 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 2500.'); 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; /
No comments:
Post a Comment