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 TABLE and VARRAY, 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 RECORD datatype lets you manipulate related values of different types as a logical unit.
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.
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 store subprograms.
To loop 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 array 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 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. 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 build-in procedure DELETE. The built-in function NEXT lets you iterate over all the subscripts of a nested table, even if the sequence has gaps.
Understanding Varrays
Items of type VARRAY are called varrays. They let you reference individual elements for array operations, or manipulate the collection as a whole. To reference an element, you use standard subscripting syntax. For example, Grades(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 set 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 assignment 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 string.
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('Antartica') : 1--1; -- 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 with VARCHAR2 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 VARCAHR2 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 comparisions) or _AI (accent- and case-insensitive comparisions).
When you pass an associative array as a parameter to a remove 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 dtables 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 tables (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 non-sequential, 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 arryas with bulk constructs (the FORALL statement or BULK COLLECT clause).
Choosing Between Nested Tables and Varrays
Varrays are a good choice when:
- The number of elements is known in advance.
- The elements are usually all accessed in sequence.
When stored in the database, varrays keep their ordering and subscripts.
Each varray is stored as a single object, either inside the table of which it is a column (if the varray is less than 4KB) or outside the table but still in the same tablespace (if the varray is greater than 4KB). You must update or retrieve all elements of the varray at the same time, which is most appropriate when performing some operation on all the elements at once. But you might find it impractical to store and retrieve large numbers of elements this way.
Nested tables are a good choice when:
- The index values are not consecutive.
- There is no 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 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 table 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 define TABLE 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
Nested tables declared in SQL using the CREATE TYPE statement 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 defines a PL/SQL type for varrays, use the syntax:
TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit)
OF element_type [NOT NULL];
The meaning 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 VARHCAR2-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, 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 the CREATE 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.
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);
/
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'.
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 (
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 as NUMBER.
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);
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';
END;
/
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;
/
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 packages 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.
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 of 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;
/
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;
/
You can also impose a NOT NULL constrain on the element type:
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 a RECORD type, then declare records of that type. You can also create or find a table, view, or PL/SQL cursor with the values you want, and use the %ROWTYPE attribute to create a matching record.
You can define RECORD types in the declarative part of any PL/SQL block, subprogram, or package. When you define your own RECORD type, you can specify a NOT NULL constraint on fields, or give them 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);
rec 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 a 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:
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 a RECORD type, then declare records of that type. You can also create or find a table, view, or PL/SQL cursor with the values you want, and use the %ROWTYPE attribute to create a matching record.
You can define RECORD types in the declarative part of any PL/SQL block, subprogram, or package. When you define your own RECORD type, you can specify a NOT NULL constraint on fields, or give them 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);
rec 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 a 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:
0 Comments