Oracle three Collection types comparison (index-by table, nested table, data group)

Comparison of Oracle three collection data types (index table, nested table, and varray array)-PLSQL-3

PL/SQL does not use arrays. Its set data types are similar to those of arrays. In versions earlier than 7.3, there was only one set, called PL/SQL tables. After that, there were two sets of data types: Nested tables and varray. The number of elements in the varray set is limited. The index_by table and the nested table do not have this limit. The index-by table is sparse, that is, the subscript can be discontinuous, and the set of varray types is tight, and there is no interval between their subscript. Index_by tables cannot be stored in the database, but nested tables and varray can be stored in the database.
The set must be defined by type before it can be used.

1. index_by table

Type type_name is table of element_type [not null] index by binary_integer

2. nested tables

Type type_name is table of element_type [not null]

3. varray

Type type_name is [varray using varying array] (max_size) of element_type [not null]

1. index_by table

Type type1 is table of varchar2 (10) index by binary_integer;

1. When using the need to first assign values and then read, at least to the first initial, otherwise there will be an exception: ORA-01403: No data found.

2. You do not need to specify the upper limit for this array. The subscript can be non-consecutive and can be 0 or negative.

Example: V1 type1;

V1 (-1): = '-1 ';
V1 (0): = '0 ';
V1 (1): = '1 ';
Dbms_output.put_line (V1 (-1); -- the access is valid.

Dbms_output.put_line (V1 (2); -- the access is invalid.

2. nested tables

Type type2 is table of varchar2 (10 );

54com.cn

1. The Initialization is required, otherwise an exception occurs: ORA-06531: Reference to uninitialized collection

2. Initial methods:

54ne.com

V1 type2: = type2 (); -- When declaring, the initial array is empty.

54com.cn

V2 type2: = type2 ('1', '2', '3', '4', '5'); -- When declaring, the initial array is five elements.

V1: = type2 (); -- the array is empty after Initialization

V2: = type2 ('1', '2', '3', '4', '5'); -- the array is five elements after the initialization.

3. Access to array elements:

Subscript starts from 1 and cannot exceed the sum of all elements in the array. When the subscript exceeds the permitted range, an exception occurs: ORA-06532: subscript outside of Limit

Because empty arrays cannot be accessed, array expansion is required for empty arrays. 54ne.com

Example: v1.extend;

V1 (1): = '1'; -- access valid 54com.cn

V1 (2): = '2'; -- the access is invalid. You must run v1.extend again.

For example, the subscript range of V2 is 1 ~ 5.

V2 (5): = 'hello'; -- the access is valid.

Dbms_output.put_line (V2 (6); -- the access is invalid.

Iii. varray

Feedom.net

Type type3 is array (5) of varchar2 (10 );

Because the number of elements is limited during type definition, the maximum number of elements in a type3 variable cannot exceed 5.

Similar to nested tables (omitted)

Iv. Set built-in functions

There are many built-in functions in the collection. These functions are called methods. The syntax for calling methods is as follows: 54ne.com

Collection. Method

The following table lists the methods integrated in Oracle.

Method description restrictions

Count returns the number of elements in the collection.

Delete Delete delete all elements in the Set 54com.cn

Delete (x) deletes an element whose subscript is X. If X is null, the Set remains unchanged and is invalid for varray.

Delete (x, y) deletes the element whose subscript is from X to Y. If the x> Y Set remains unchanged, it is invalid for varray.

Exist (x) if the set element x has been initialized, true is returned; otherwise, false is returned.

Extend adds an element at the end of the set to illegal index_by 54com.cn

Extend (x) adding x elements at the end of the set is invalid for index_by.

X copies of element N added to the end of the set by extend (x, n) are invalid for index_by.

First returns the bottom label of the first element in the Set, and 1 is always returned for the varray set. Feedom.net

The bottom number of the last element in the last returned set. The return value for varray is always equal to count.

Limit returns the maximum number of elements in the varry set, which is useless for nested tables and index_by sets.

Next (x) returns the value of the element after element x and next to it. If the element is the last element, null is returned.

Prior (x) returns the value of the element that is placed next to element x in the set. If the element is the first element, null is returned.

Trim deletes an element from the end of the set. It is invalid for index_by.

Trim (x) Deleting x elements from the end of the set is illegal for index_by

 

 

Second solution:

/* Review Oracle composite types (records and sets )*/
/* 1. The record is used to conveniently process multiple rows in a single row, and the set is used to conveniently process multiple rows in a single column */
-- (1) Record
/* 1. Create a simple table first */
Create Table test_complex (
Id varchar2 (20 ),
Username varchar2 (20)
);
-- Insert several records to the test_complex table
Insert into test_complex values ('20140901', 'aaaaa ');
Insert into test_complex values ('20140901', 'bbbbbbb ');
Insert into test_complex values ('20140901', 'ccccccccc ');

/* 2. Create a record
There are two types of records: explicit and implicit.
*/
-- Display Definition
Declare
-- 1. Display Definition
Type t_record is record (
Id test_complex.id % type,
Username test_complex.username % Type
);
R_record t_record; -- declares a record type variable r_record

-- 2. implicit definition
R_record test_complex % rowtype;

-- Cursor Definition
Cursor c_cursor (v_id) is select ID, username from test_complex t where T. ID = v_id;
-- 3. The cursor defines the record variable.
R_record2 c_cursor % rowtype;

Begin

For V in (select * From test_complex) loop
R_record.id: = V. ID;
R_record.username: = V. Username;
Dbms_output.put_line ('Id = '| r_record.id | 'username =' | r_record.username );
End loop;
Exception when others then
Raise_application_error (-20201, 'program error! ');
End;

--- Select * From tab;
--- Select * From test_complex
-- (2) Set
/* The set mainly includes three types: index table, nested table, and array)
1. index tables cannot be stored in databases (stored in memory), but nested tables and varray can be stored in databases.
The index table syntax is as follows;
Type type_name is table of element_type [not null] index by binary_integer;
The keyword is index by binary_integer. Without this keyword, the set will be a nested table. Because it is not stored in the database,
Element_type can be any valid PL/SQL data type, including: pls/integer, signtype, and Boolean.
The index table subscript can be negative, and there is no limit on the number of elements (there are three types of indexes: pls_integer, binary_integer 10g, varchar2 is allowed)
1. pls_integer can be stored like binary_integer-2 ^ 31-2 ^ 31
1. High Storage Performance
2. An exception is thrown when pls_integer overflows. If binary_integer is assigned to number, no exception is thrown.
3. pls_integer is faster than binary_integer (simulated by Oracle) by CPU.
*/
Type t_tab is table of test_complex % rowtype index by binary_integer;
R_tab t_tab;
Select ID, username into r_tab (0) from test_complex where id = '20140901 ';
Bytes ---------------------------------------------------------------------------------------------------------------
/* 2. syntax for defining nested tables:
Nested tables are very similar to index_by tables, and the syntax for creating them is also very similar. There is no index by binary_integer substring
Type type_name is table of element_type [not null];
Nested tables stored in a database are not stored in the same data block as other data in the table. They are actually stored in the second table.
Nested tables retrieved from the database do not guarantee the order of elements. Set data is stored offline, so nested tables are suitable for large sets.
Difference 1: The subscript of a nested table starts from 1 and the element format is not limited.
Difference 2: The index table type cannot be used as the Data Type of the table column, but the nested table can
Difference 3: nested table type variables must be initialized by the constructor before they can be used.
When using a nested table as a table column, you must use eg: Create type t_tbl is table of varchar2 (2) and specify a special storage table for the column.
Create type T _ TBL is table of varchar2 (10 );
/
Create Table tab_t (
Username varchar2 (8 ),
Phone T _ tab1
) Nested table phone store as T _ tab1;

To process multiple rows and multiple columns, you can use a record table.
Eg: type T _ TBL is table of tab_t % rowtype index by binary_integer;
*/
Type t_n_tab is table of test_complex.id % type;
R_n_tab t_n_tab;
R_n_tab: = r_n_tab ('20140901', '20160901'); -- initialize the assignment.
Bytes ---------------------------------------------------------------------------------------------------------------
/* 3. varray definition Syntax: (fixed-length array, subscript starts from 1)
Type type_name is [varray | varying array] (max_size) of element_type [not null];
Max_size is an integer used to indicate the maximum number of elements owned by the varray set. The number of elements in the varray set can be lower than max_size, but cannot exceed max_size.
Element_type is the data type of one-dimensional elements. If element_type is a record, this record can only use a scalar data field (similar to the nested tag ).
When varray is stored in the database, it is stored in the same data block as other data in the table, and the element sequence is stored in varray.
Sets are stored online, and varray is suitable for small sets.

Nested tables and varray can both be stored as columns in database tables. Therefore, the set itself can be null. When the set is null, elements in the set cannot be referenced.
The is null operator can be used to check whether the set is null.
*/
Type t_array is array (100) of test_complex.id % type; -- can be used as a table column
R_array t_array;
R_array: = r_array ('20140901', '20160901'); -- initialize the value assignment.
Bytes -------------------------------------------------------------------------------------------------------------------
/* 4: Batch binding: the execution of a single SQL operation can complete all element data, improving performance
The forall syntax is as follows:
1. forall index in low_bound, up_bount SQL _statement;
2. forall index int indices of Collection [between low_bound and up_bound] SQL _statement;
3. forall index in values of index_collection SQL _statement;

For I in indices of t_tabl -- indices of (Oracle 10g feature) is used to skip 'null'

1. The bulk collect into clause is used to obtain batch data. It is used only for select into, fetch into, and DML statements.
2. On the forall statement, the values of clause is used to integrate the value of the lower mark from other sets (the purpose is to specify the insertion) subset.
Forall I in values of index_tab SQL _statement

3. SQL % bulk_rowcount is used for the function of the I-th element.
Eg; SQL % bulk_rowcount (2) What is the row that the second element acts on?
*/

---------- Common functions ------------------------------------------------------------------------------------------------
/* COUNT = returns the number of elements in the set.
Delete ======== delete all elements in the Set
Delete (x) ==== delete an element whose subscript is X ==== invalid for varray
Delete (x, y) ===delete the element whose subscript is from X to Y ==== invalid for varray
Exist (x) ====== if the set element x has been initialized, true is returned; otherwise, false is returned.
Extend =========add an element at the end of the Set ====== invalid for index_by
Extend (x) =====add x elements to the end of the Set ================== invalid for index_by
Extend (x, n) ===add x copies of element N at the end of the Set ================ invalid for index_by
First ========== return the bottom label of the first element in the Set, and 1 is always returned for the varray set.
Last ========== returns the bottom label of the last element in the set. The return value for varray is always equal to count.
Limit ========== returns the maximum number of elements in the varry set ============ index_by set and nested table useless
Next (x) ====== returns the value of the element next to and next to the X element. If X is the last element, null is returned.
Prior (x) ====== returns the value of the element next to it before the X element. If X is the first element, null is returned.
Trim ========== deleting an element from the end of the Set ====== it is not legal for index_by
Trim (x) ====== Delete X from the end of the Set ====== the index_by statement is invalid.
*/</P>