Oracle PLSQL collection type

Reprinted: http://www.cnblogs.com/gkl0818/archive/2009/02/25/1397769.html
Oracle collections
I. Types of Collections
1. associative arrays Array
It is a one-dimensional, borderless sparse set of the same type and can only be used for PL/SQL.
Declare
Type t_name is table of varchar2 (10) index by pls_integer; -- declare collection type
I _name t_name; -- declare instance
Rochelle row pls_integer;
Begin
I _name (202020): = 'aaa ';
I _name (-125): = 'bbb ';
I _name (88): = 'ccc '; -- assign a value. row number can be any integer, skip (sparse), and do not assign values in order, it is eventually sorted by row number internally.
Rochelle row: = I _name.first; -- returns the first row number.
Rochelle row: = I _name.next (Rochelle row); -- returns a row number after Rochelle row, and automatically skips blank rows.
Dbms_output.put_line (I _name (l_row); -- return the value of the specified row
End;

2. nested tables Table
It is also a one-dimensional borderless set of the same type. It is dense at first and will become sparse after deletion. It can be used in PL/SQL and database (defining a column as a nested table. It is a multisets, that is, the elements in the nested table do not have an internal order.
Declare
Type t_name is table of varchar2 (10 );
-- Create a collection, which is different from the associative arrays index
I _n1 t_name: = t_name (); -- create an instance and use the constructor.
I _n2 t_name: = t_name ();
I _n3 t_name: = t_name ();
Begin
I _n1.extend (2); -- allocate space before assigning values
I _n1 (1): = 'aaa'; -- assign a value. The row number is 1 at the minimum, and the maximum is the number of space allocated by extend. Skip a row and leave it null.
I _n1 (2): = 'bbb ';
I _n2.extend; -- allocate a space
I _n2 (1): = 'bbb ';
I _n3: = I _n1 Multiset encode t I _n2;
-- The function provided at the beginning of 10 Gb. The I _n2 value is removed from I _n1 and assigned to I _n3.
For l_row in I _n1.first... I _n1.last -- output from the beginning to the end of the row in sequence
Loop
Dbms_output.put_line (I _n1 (l_row ));
End loop;
End;

3. array of varrays Variables
Varrays (Variable-sized arrays) is a one-dimensional set of the same type. However, it is bounded and sparse. Specify the maximum range when defining varrays. Varrays can also be defined in PL/SQL and database, but its elements are ordered.
Declare type t_name is varray (2) of varchar2 (10); -- create a collection and specify the maximum range.
I _n1 t_name: = t_name (); -- create an instance and use the constructor.
Begin
I _n1.extend (2); -- the space must be allocated before the value is assigned. The maximum value cannot be exceeded.
I _n1 (1): = 'aaa'; -- assign a value. The row number must be 1 at the minimum and the maximum number of space allocated by extend.
I _n1 (2): = 'bbb ';
End;

II. Where you can use collections
1. As a record Member.
2. As a program parameter
Since Oracle does not have a predefined collection type, you need to define the collection type before using it as a parameter:
1. Use create type to define the schema-level type
2. Declare in package specification
3. Definition in outer Scope
3. return value as a function
1. The returned collection is directly assigned to the Collection variable. In this case, the collection variable does not need to be initialized.
2. assign an element in the returned set to a compatible type variable.
Variable_of_element_type: = function () (subscript );
If the function returns a null value, a collection_is_null exception is generated when the value is assigned. The exception should be caught and handled as appropriate.
4. Columns used as database tables
When using the nested table datatype as a column, you must specify the name of the store table:
Create Table personality_inventory (
Person_id number,
Favorite_colors color_tab_t,
Date_tested date,
Test_results BLOB)
Nested table favorite_colors store as favorite_colors_st;
You cannot maintain the store table or directly query or store data. You can only use the outer table to obtain its attributes. You cannot specify its storage parameters, which inherits from the outermost table.
The difference between nested tables and varrays is that varray and other data are stored in the table, while nested table is stored outside the table. varray is suitable for "small" arrays, and nested table is suitable for "large" arrays.
5. Attributes of Object Type
 
Iii. Choosing a collection type
1. If sparse array is used, only associative array can be used. Although the nested table can be allocated first to delete the project, the efficiency is very low.
2. If the subscript of a negative number is used in PL/SQL, only associative array can be used.
3. If you want to use the set-level operation for 10 Gb, select the nested tables + Multiset reset T method.
4. If you want to limit the number of rows stored, use varrays
5. If you want to store a large data set in column, use the nested table. Oracle can use a separate table to store it.
6. if you want to keep the data in the collection column in the original order, and the data volume is small, you can use varray. The small concept can be determined by the block size. If the data volume exceeds one block, A row connection will be generated.
7. varray: You don't have want to worry about deletions occurring in the middle of the data set; your data has an intrinsic upper bound; or you have CT, in general, to retrieve the entire collection simultaneously.

Collection methods (built-ins)
Collection methods can only be used in PL/SQL, and cannot be used in SQL
1. The Count Method
Used to calculate the number of elements in associative array, nested table, or varray. Using deleted or trimmed reduces the count
Definition:
Function count return pls_integer;
Returns 0 for a collection that is initialized but does not contain elements, and 0 for an empty associative array.
If you use count for uninitialized nested table or a varray, the returned collection_is_null exception is returned. associative arrays does not need to be initialized, so no exception is returned.

2. The delete Method
Delete is used to delete one or more elements in a collection:
1. Remove all elements without adding parameters. This method can only be used for varrays, because varrays cannot be sparse. to delete a row, you can only use TRIM to delete the last row.
2. Delete (I) Delete row I
3. Delete (I, j) deletes the I ~ Row J
For the delete parameter, a placeholder is used to replace the deleted elements. In the future, you can assign values to them and then count them in count. From the physical point of view, PL/SQL only releases the memory when it deletes enough elements to release the entire page of memory, however, deleting without parameters immediately releases all memory.
Definition:
Procedure Delete;
Procedure Delete (I [binary_integer | varchar2 (size_limit)]);
Procedure Delete (I [binary_integer | varchar2 (size_limit)],
J [binary_integer | varchar2 (size_limit)]);
If I and j are out of the range, no exception will occur. They are only deleted from the range. The excess part is ignored.
If you use Delete For uninitialized nested table or a varray, the returned collection_is_null exception is returned.
3. The exists Method
Determines whether the specified row in the collection exists. If the row exists before and is deleted later, false is returned.
Definition:
Function exists (I in [binary_integer | varchar2 (size_limit)]) return Boolean;
4. The extend Method
Used to allocate space for the nested table or varray
1. assign a null element without adding a parameter
2. Extend (n): allocate n null elements.
3. Extend (n, I), assign n elements with the same I value, which is necessary for elements with the not null restriction.
Definition:
Procedure extend (N pls_integer: = 1 );
Procedure extend (N pls_integer, I pls_integer );
If deleted or trimmed is used to delete the last element of the collection, and extend is used, this will be skipped and space will be allocated later.
If delete is used for uninitialized nested table or a varray, collection_is_null is returned. If extend exceeds the maximum limit of varray, subscript_beyond_limit is returned.

5. The first and last methods
Returns the available lowest and highest subscripts in the collection, which are not displayed when deleted.
Definition:
Function first return pls_integer;
Function last return pls_integer;
Returns NULL for collections with initialization but no elements. For varrays with at least one element, the first value is 1, and the last value is equal to count.
If you use first and last for uninitialized nested table or a varray, A collection_is_null exception is returned.

6. The Limit Method
Returns the maximum number of varray entries. If used for nested tables or to associative arrays, null is returned.
Definition:
Function Limit return pls_integer;
If you use limit for uninitialized nested table or a varray, A collection_is_null exception is returned.
7. The prior and next Methods
Used to traverse the content in the collection.
Definition:
Function prior (I [binary_integer | varchar2 (size_limit)])
Return [binary_integer | varchar2 (size_limit)];
Function next (I [binary_integer | varchar2 (size_limit)])
Return [binary_integer | varchar2 (size_limit)];
If it is applied to a collection that is initialized but empty, null is returned. If I is greater than or equal to count, null is returned for next. If I is less than or equal to first, prior returns NULL.
For the moment, if I is greater than count, prior returns limit; if I is less than first, next returns limit, but later versions do not know if this is the case.
If prior and next is used for uninitialized nested table or a varray, A collection_is_null exception is returned.

8. The trim method
Because n rows at the end of the nested table or varray are deleted, if no parameter is added, the last row is deleted. If it is used for the associative array, a compilation error is generated.
Joint Use of Delete and trim results in repeated deletion. For example, after deleting the last row of Delete, trim is used to delete the last n rows, in fact, trim repeat the row deleted by the delete operation (because it becomes a placeholder after deletion), so that the row is actually deleted less. Therefore, we recommend that you do not use both of them in Oracle.
Definition:
Procedure trim (N pls_integer: = 1 );
If n is null, trim does nothing
If trim is used for uninitialized nested table or a varray, A collection_is_null exception is returned.
If the N value of trim is greater than the number of actually existing elements, subscript_beyond_count is returned.
Working with collections
I. Declaring Collection types
Two declaration methods:
1. Use the type statement in PL/SQL. If it is defined in a package, you must grant the execute permission of the package to the user who uses this type.
2. Run the create Type command in Schema-level for the nested table type or varray type and grant the Execute Permission of this type to the user to use.
Declaring an associative array collection type
Type table_type_name is table of ype [not null]
Index by index_type;
Datatype includes:
1. scalar datatype: varchar2, clob, positive, date, or Boolean, etc.
2. Anchored datatype: Use % type, % rowtype
3. for complex datatype: 9i R2, object types and Collection types can be used.
Index_type includes:
Index by binary_integer; (this type can only be used before 9i R2)
Index by pls_integer;
Index by positive;
Index by natural;
Index by signtype;
Index by varchar2 (32767 );
Index by table. column % type;
Index by cursor. column % type;
Index by package. Variable % type;
Index by package. Subtype;

Declaring a nested table or varray
Create [or replace] type type_name as | is
Table of element_datatype [not null];
Create [or replace] type type_name as | is
Varray (max_elements) of element_datatype [not null];
Drop type type_name [force];
Element_datatype includes: Most scalar datatypes, an object type, or a ref object type. If it is an object type, its attribute cannot be a collection type. In PL/SQL, if it is a record, it can only contain the scalars or objects type
Force: even if the collection type is referenced by another type, drop it
Changing nested table or varray Characteristics
1. Change the maximum number of varray elements:
Alter type list_t modify limit 100 invalidate | Cascade
2. Change the type length or accuracy of the element type variable character, raw, or numeric.
Create type list_t as varray (10) of varchar2 (80 );
Alter type list_t modify element type varchar2 (100) invalidate | Cascade
Invalidate: all objects of this type are invalid.
Cascade: Pass changes to all objects using this type

Ii. Declaring and initializing collection variables

Collection_name collection_type [: = collection_type (...)];
For nested table or varray that needs to be initialized with constructor can accept an initial value separated by commas as a parameter, if it is not initialized, an error will be returned: ORA-06531: Reference to uninitialized collection
1. Initializing implicitly during direct assignment
Different variables of the same collection type (must be of the same type, even if the element type is the same but different collection types) can be implicitly initialized by directly assigning values.
Declare
Earth_colors color_tab_t: = color_tab_t ('brick', 'rust', 'dirt ');
Wedding_colors color_tab_t; -- must be of the same type, all of which are color_tab_t, which is not initialized here
Begin
Wedding_colors: = earth_colors; -- initialize simultaneously when assigning values
Wedding_colors (3): = 'canvas ';
End;
2. Initializing implicitly via fetch
If the column in the table is collection type, you can directly pass the value to a variable of the same type Through select... into or fetch as the normal type, and automatically initialize the variable.
 
3. populating collections with data
Using the assignment operator
1. Single Value assignment:
Countdown_test_list (43): = 'internal pressure ';
2. assign values to the entire record (with the same structure:
Declare
Type emp_copy_t is table of EMP % rowtype;
Rochelle EMPs emp_copy_t: = emp_copy_t ();
Rochelle emprec EMP % rowtype;
Begin
Rochelle emprec.ename: = 'steven ';
MAID: = 10000;
Rochelle emps.extend
Rochelle EMPs (Rochelle emps.last): = Rochelle emprec;
End;
You can also use select... into, cursor for loop, select... bulk collect into (after 9i R2, you can assign the entire table to the collection without initialization ).
3. directly assign values to collections of the same type (Initialization is not required)

Iv. Accessing data inside a collection
Notes:
1. For sparse collection, no_data_found exception occurs when elements that do not exist are accessed.
2. ORA-06533 generated when accessing elements that exceed the extend allocated space: subscript beyond count exception
3. For varray, an ORA-06532 is generated when accessing elements greater than the maximum number of elements: subscript outside of limit exception

5. Collections of complex PES ypes
Collections of records
You can define a collection whose element type is record. Use % rowtype or a programmer-defined record type. Applicable to PL/SQL. % rowtype cannot be used for columns defined in tables.
Multilevel collections
9i R2 can start to define the collection element type as collection, which is nested and called multilevel collections. This method can be used to construct a multi-dimensional collection.

6. using string-indexed collections
Start 9i R2 and use strings as index type, first, last, Prior, and next to return strings
 
VII. Working with collections in SQL
Oracle8 began to provide collection pseudo-functions, which can convert data in data tables and collections and can only be used in SQL statements.

1. The cast pseudo-Function
In SQL, it is used to convert built-in datatype or collection type to another built-in datatype or collection type.
Create type color_nt as table of varchar2 (30 );
Create type color_vat as varray (16) of varchar2 (30 );
Create Table color_models (
Model_type varchar2 (12 ),
Colors color_vat );
Select column_value from
Table (select cast (colors as color_nt)
From color_models
Where model_type = 'fgb ');
2. The Multiset pseudo-Function
Multiset must be used with casts. Multiset converts a dataset to collection. SQL Multiset function is completely different from PL/SQL Multiset for operating nested tables.
Select cast (Multiset (select field from Table) as collection-type)
From dual;
As with the cast pseudo-function, Multiset cannot serve as the target of an insert, update, or delete statement.
3. The table pseudo-Function
Table converts a collection to a result set that can be selected from. Both the content in the collection is selected like the data table.
For example, if the table contains the collection field, you need to find all the data that contains a specific content in the collection:
Select *
From color_models C
Where 'red' in
(Select column_value from table (C. colors ));

Model_type colors
------------------------------------------------------------------
RGB color_tab_t ('red', 'green', 'blue ')

Column_value is the name of the system-defined column created by table operator.
Nested table Multiset operations
After 10 Gb, High-Level Set operations are provided for nested tables, which are treated as multisets
Operation Return Value description
= Boolean compares two nested tables. If named type, cardinality, and elements are equal, true is returned. If the nested table contains null, they are not equal. Compared before 10 Gb, only one element can be used for comparison.
<> Or! = Boolean: Compares whether two nested tables are different.
[Not] In () Boolean determines whether the nested table on the left is in the nested Tables List in the right Brace
X Multiset into T nested table returns a nested table whose elements are in X, but not in Y. All the nested tables must be of the same type. Distinct indicates that duplicate elements are removed from the returned nested table.
[Distinct] y
X Multiset intersect nested table returns a nested table whose elements are the intersection of elements in X and Y.
[Distinct] y
X Multiset Union nested table returns a nested table whose elements are the union of elements in X and Y.
[Distinct] y
Set (x) nested table returns a nested table that contains all elements not repeated in X
X is [not] A set Boolean determines whether the elements in X have repeated values.
X is [not] Empty Boolean determines whether X is null
E [not] member Boolean judge whether expression e is contained in the element of X
[Of] x
Y [not] submultiset Boolean determines whether all y elements are included in X
[Of] x
 
 
Maintaining Schema-level collections

1. necessary privileges
If you create a schema-level nested tables and varrays, other schemas must have the execute permission for this type if you want to use this type:
Grant execute on color_tab_t to Joe;
For packages, procedures, and funures that contain Schema-level collections, you can use these types as long as you have the Execute Permission for these processes, but these types are used in PL/SQL anonymous blocks, only execute permissions of these types can be directly granted to the users who execute
If a table column is of the schema-level collection type, you must have the select, insert, udpate, and delete permissions on the table to perform the corresponding operations, the execute permission of the collection type is also required during insert or update.

Ii. collections and the data dictionary
1. user_types
All created types
Select type_name from user_types
Where typecode = 'collect ';
2. user_source
Type Definition
Select text from user_source
Where name = 'foo _ t' and type = 'type'
Order by line;
3. user_dependencies
Dependent object of the created type
Select name, type from user_dependencies
Where referenced_name = 'foo _ T ';