Oracle handbook Series 3: Collection types in PL/SQL (Collections in PL/SQL)

 

1) classification of Sets

Oracle supports three types of collections:

Associative array (Index-by table)

Nested table)

Variable-length array (VARRAY)

One of their differences is that the Nested table and VARRY can be used either in PL/SQL or directly in the database, but the Associative array doesn't work, that is, associative array cannot be created separately using the create type statement. It can only be defined and used in PL/SQL blocks (or packages) (that is, it applies to PL/SQL Block levels ), nested table and VARRAY can be created using create type (that is, the scope is Schema level), and they can also be directly used as the TYPE of columns in the database table.

BTW: To avoid misunderstanding, the "Index" is explicitly mentioned below, not the index of the database table, but the "subscript" used to access the element of the set ", that is, the index in Collection_Name (index.

 

2) Declaration and initialization of Collection types

2.1) Associative array

First, let's take a look at the Associative array declaration and initialization. The first part of the example shows that Associative array cannot be created using create type. The second part uses an anonymous PL/SQL block to demonstrate the use of Associative array. It can be seen that the usage is relatively simple.

Associative array indexes can be sparse (that is, they can be discontinuous ).

SQL> CREATE OR REPLACE TYPE ib_planguage IS TABLE OF VARCHAR2 (10) INDEX BY PLS_INTEGER;
2/
Warning: Type created with compilation errors.
SQL> SHOW ERROR;
Errors for TYPE IB_PLANGUAGE:
LINE/COL ERROR
-------------------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
1/22 PLS-00355: use of pl/SQL table not allowed in this context
 
DECLARE
TYPE ib_planguage is table of VARCHAR2 (10) index by PLS_INTEGER;
Lang ib_planguage;
Idx PLS_INTEGER;
BEGIN
Lang (1): = 'java ';
Lang (9): = 'C #';
Lang (3): = 'C ++ ';
Idx: = lang. FIRST;
WHILE (idx is not null) LOOP
DBMS_OUTPUT.PUT_LINE (lang (idx ));
Idx: = lang. NEXT (idx );
End loop;
END;
SQL>/
Java
C ++
C #

 

2.2) Nested table

Next, let's take a look at the Declaration and initialization of the Nested table. You can declare the global TYPE through the create type statement, or declare the block-level TYPE in the PL/SQL block.

Unlike Associative array, the Nested table variable needs to be explicitly initialized. Its syntax is similar to C #, but the new keyword is not used. Additionally, you need to call the EXTEND process after Nested table initialization to expand the "capacity" of this set ".

The second part of the example provides a relatively simple initialization method, that is, you can directly input a set Member in the constructor. This simple initialization method is not applicable to Associative array.

SQL> CREATE OR REPLACE TYPE nt_planguage IS TABLE OF VARCHAR2 (10 );
2/
Type created.
 
DECLARE
Lang nt_planguage;
BEGIN
Lang: = nt_planguage ();
Lang. EXTEND (3 );
Lang (1): = 'java ';
Lang (2): = 'C #';
Lang (3): = 'C ++ ';
FOR I IN 1 .. lang. COUNT LOOP
DBMS_OUTPUT.PUT_LINE (lang (I ));
End loop;
END;
SQL>/
Java
C #
C ++

Or you can simply write it as follows:

DECLARE
Lang nt_planguage;
BEGIN
Lang: = nt_planguage ('java', 'c # ', 'c ++ ');
FOR I IN 1 .. lang. COUNT LOOP
DBMS_OUTPUT.PUT_LINE (lang (I ));
End loop;
END;

 

2.3) VARRAY

Finally, let's look at the Declaration and initialization of VARRAY. It is similar to the Nested table, but there are still some differences, including (1) VARRAY can specify the length of the set in the Declaration time limit, the length of the EXTEND cannot be greater than the length declared by the Set (in the following example, the length is 8). However, when assigning values to the set members, the EXTEND prevails, for example, in the following example, only three set members can be used .; (2) Its indexes are always continuous, while the indexes of the Nested table are continuous during the initialization assignment. However, as the set elements are deleted, they may become discontinuous.

SQL> CREATE OR REPLACE TYPE va_planguage IS VARRAY (8) OF VARCHAR2 (10 );
2/
Type created.
 
DECLARE
Lang va_planguage;
BEGIN
Lang: = va_planguage ();
Lang. EXTEND (3 );
Lang (1): = 'java ';
Lang (2): = 'C #';
Lang (3): = 'C ++ ';
FOR I IN 1 .. lang. COUNT LOOP
DBMS_OUTPUT.PUT_LINE (lang (I ));
End loop;
END;

Similarly, you can simply write it as follows:

DECLARE
Lang va_planguage;
BEGIN
Lang: = va_planguage ('java', 'c # ', 'c ++ ');
FOR I IN 1 .. lang. COUNT LOOP
DBMS_OUTPUT.PUT_LINE (lang (I ));
End loop;
END;

 

3) how to select the applicable collection type

Generally, the first choice for the set type should be Associative array, because it does not require initialization or EXTEND operations and is the most efficient set type so far. The only drawback is that it can only be used for PL/SQL but not for databases.

If you want to allow negative index, select Associative array;

If you need to use the set operations in 10g and 11g, you should select Nested table;

To limit the number of elements in a set, select VARRAY.

 

4) Others

It IS very confusing that the TYPE declaration in the PL/SQL statement block must use the keyword "IS" instead of "AS". However, when using CREATE TYPE to declare a global TYPE, these two are common.

Originally, we should have finished the introduction here. However, Oracle introduced OO, which causes me to talk a little bit about Object declaration and initialization, because in some cases, you will also encounter a situation where the set Member is of the object type.

SQL> CREATE OR REPLACE TYPE o_planguage IS OBJECT (lang VARCHAR2 (10), lang_desc VARCHAR2 (100 ));
2/
Type created.
 
DECLARE
Lang o_planguage;
BEGIN
Lang: = o_planguage ('java', 'java is an OO programming language ...');
DBMS_OUTPUT.PUT_LINE (lang. lang_desc );
END;

Note: objects cannot be declared in PL/SQL statement blocks.

Note 2: The following Code cannot be used for initialization:

Lang: = o_planguage ();

Lang. lang: = 'java ';

Lang. lang_desc: = 'java is an OO programming language... '

This syntax causes {PLS-00306: number of parameters or type error when calling 'lang '}, that is, the Object type does not have a default no-argument constructor. After this introduction, even if the set member is an Object, it can be declared and initialized as a method.

 

 

Author Snowtoday MSN: MyYe110w@hotmail.com