Oracle11gRelease1 (11.1) PL/SQL _ understand the Collection type

The significance of understanding Oracle collections is that when we develop applications using programming languages, we use a lot of built-in Collection types, such as arrays and linked lists.

The significance of understanding Oracle collections is that when we develop applications using programming languages, we use a lot of built-in Collection types, such as arrays and linked lists.

Content

Define Collection type

Declare Collection variables

Initialize and reference Collection

Reference Collection Element

Assign a value to Collection

Multi-dimensional Collection

Compare Collection

Collection Method

Collection exception

The significance of understanding Oracle collections is that when we develop applications using programming languages, we use a lot of built-in Collection types, such as arrays and linked lists, however, when the business logic becomes complex, if you want to insert data to multiple tables at the same time and insert multiple data records to one table, you may need transaction control. In this case, it seems appropriate to use anonymous subprograms. Therefore, it is inevitable to use the set type in anonymous subprograms.

In addition, if a function written in a programming language involves set operations and wants to rewrite it to an Oracle function for some reason, it is necessary to understand Oracle set operations.

Define Collection type

Define the collection type before declaring the variables of this type.

You can define a collection type in the Mode Level, package, or PL/SQL block.

The collection type created at the mode level is standalone stored type. CREATE with the create type statement. It is stored in the database until the TYPE is deleted using the drop type statement.

The collection type created in the package is packaged type. It is stored in the database until the PACKAGE is deleted using the drop package statement.

Collection types created in PL/SQL blocks are only available in blocks. Only blocks embedded in standalone or packaged subprogram are stored in the database.

The collection type follows the same scope and instantiation rules as other types and variables. When you enter a block or subroutine, the collection is instantiated and destroyed when you exit. In a package, the collection is instantiated from the reference package and destroyed when the database session ends.

You can use TYPE to define the TABLE and VARRAY types in any PL/SQL block, subroutine, or package declaration section.

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

When defining a VARRAY type, a positive integer must be used to specify the maximum size. Define a VARRAY that can store up to 366 dates as follows:

DECLARE
TYPE CalendarISVARRAY (366)OF DATE;

Associative arrays can insert elements with any key value. The key does not need to be continuous. The data type of the key can be PLS_INTEGER, VARCHAR2, or VARCHAR2: VARCHAR, STRING, or LONG.

The Length Based on the VARCHAR2 key must be specified, except LONG, because it is equivalent to declaring a VARCHAR2 (32760) type key. The RAW, long raw, ROWID, CHAR, and CHARACTER types cannot be used as the keys of an associative array. LONG and long raw are only for backward compatibility.

Initialization clauses are not allowed. Associative arrays has no constructor mark. When you use the VARCHAR2 key to reference an element in associative arrays, you can use a type such as DATE or TIMESTAMP, as long as the type can be converted to VARCHAR2 using the TO_CHAR function.

Declare Collection variables

After defining a collection type, declare the variable with this type. Use the new type name in the declaration, like the predefined type, such as NUMBER.

Example 1: demonstrate the declaration of nested table, varray, and associative array

VARCHAR2 (30); TYPE varray_type NUMBER VARCHAR2 (32) VARCHAR2 (32)INDEX BYVARCHAR2 (64); v1 nested_type; v2 varray_type; v3 assoc_array_num_type; v4 assoc_array_str_type; v5 assoc_array_str_type2;BEGIN -- An arbitrary number of strings can be inserted v1V1: = nested_type ('Shipping','Sales','Finance','Payroll'); V2: = varray_type (1, 2, 3, 4, 5 );-- Up to 5 integersV3 (99): = 10;-- Just start assigning to elementsV3. (7): = 100;-- Subscripts can be any integer valuesV4 (42): ='Smith';-- Just start assigning to elementsV4 (54): ='Jones';-- Subscripts can be any integer valuesV5 ('Canada'): ='North America';-- Just start assigning to elementsV5 ('Greece'): ='Europe';-- Subscripts can be string valuesEND;/