PL/SQL collection method

PL/SQL provides three common set union arrays, nested tables, and variable-length arrays, PL/SQL provides corresponding functions or procedures for operation.
Element or subscript in the vertical array. These functions or procedures are called set methods. A set method is a function or process built into the set and can operate the set. You can use the vertex flag
. This document describes how to operate these methods.

I. Methods and call methods provided by Collection types

1. Set methods and call Methods
Exists
The function exists (n) returns true if the nth element exists. Otherwise, false is returned.
Usually, exists and delete are used to maintain nested tables. Exists can also prevent reference of nonexistent elements and avoid exceptions.
When the subscript is out of bounds, exists returns false instead of throwing a subscript_outside_limit exception.

Count
Count can return the number of elements contained in the set. Count is very useful when the size is not sure.
The count function can be used wherever an integer expression can be used, for example, as the upper limit of the for loop.
When calculating the number of elements, the deleted elements are not counted by count.
For a variable-length array, the count and last values are constant.
For nested tables, the Count value is normally equal to the last value. However, when we delete an element from the nest table, the Count value is smaller than the last value.

Limit
Maximum capacity used to detect a set
Since no upper limit is imposed on nested tables and associated arrays, limit always returns NULL.
For a variable-length array, limit returns the maximum number of elements it can hold. This value is specified when the variable-length array is declared and can be adjusted using the trim and extend methods.

First, last
First and last return the subscript index values of the first and last elements in the set.
For associated arrays that use the varchar2 type as the key, the lowest and highest key values are returned respectively. The high and low order of the key values is based on the binary values of the characters in the string.
However, if the initialization parameter nls_comp is set to ANSI, the order of the key values is affected by the initialization parameter nls_sort.
The first and last methods of an empty set always return null. There is only one set of elements. First and last return the same index value.
For a variable-length array, first is always equal to 1, and last is always equal to count.
For nested tables, first usually returns 1. If the first element is deleted, the first value is greater than 1. If an element in the middle is deleted, the last value is greater than count.
When traversing an element, both first and last ignore the deleted element.

Prior, next,
Prior (n) returns the index value of the element whose index is N in the set. Next (n) returns the index value of the element whose index is N in the set.
If n is not a precursor or a successor, prior (n) or next (n) returns NULL.
For associated arrays that use varchar2 as the key, they return the lowest and highest key values respectively. The high and low order of the key values is based on the binary values of the characters in the string.
Prior and next will not reach the other end of the set from one end of the set, that is, the next of the last element will not point to the first in the set.
When traversing an element, both prior and next ignore the deleted element, that is, if prior (3) is deleted before 2, it points to 1. If 1 is also deleted, null is returned.

Extend
Used to expand the capacity of nested tables or variable-length arrays. This method cannot be used to combine arrays.
There are three extend forms
Extend adds an empty element to the end of the set.
Extend (n) adds n null elements to the end of the Set
Extend (n, I) copies the I-th element to N parts and adds it to the end of the set.
After the not null constraint is added to a nested table or a variable-length array, the first two extend forms cannot be used.
Extend operates on the internal size of the Set, which also includes the deleted elements. Therefore, when calculating the number of elements, extend also takes into account the deleted elements.
For elements operated using the delete method, PL/SQL retains its placeholder, which can be reused later.

Trim
Delete one (TRIM) or a specified number of trim (n) elements from the end of the set. PL/SQL no longer retains placeholders for the trim elements.
If the value of N is too large, trim (n) will throw a subscript_beyond_count exception.
Generally, do not use the trim and delete methods at the same time. The nested table can be regarded as a fixed-length array, and only the delete method or the stack can be used. Only the trim and extend methods can be used for the nested table.

Delete
Delete all or specified range elements in a set. The following call methods are usually used.
Delete deletes all elements in the set.
Delete (n) deletes the nth element from an associated array or nested table that uses numbers as the primary key.
If the associated array has a string key, the key-value element will be deleted. If n is null, delete (n) will not do anything.
Delete (m, n) deletes all elements with an index range of m to N from an associated array or nested table.
If the m value is greater than N or if one of M and N is null, delete (m, n) will not do anything.
PL/SQL reserves a placeholder for the elements deleted using the delete method. You can assign values to the deleted elements again later.
Note: you cannot use Delete to delete elements in a variable-length array.

Call method:
Collection_name.method_name [(parameters)]

2. Collection Method considerations
The set method cannot be used in SQL statements.
The extend and trim methods cannot be used to associate arrays.
Exists, Count, limit, first, last, Prior, And next are functions; Extend, trim, and delete are processes.
The parameters corresponding to exists, Prior, next, trim, extend, and delete are subscript indexes of the Set, which are usually integers, but may also be strings for associated arrays.
Only Exists can be used for empty sets. If other methods are called when the empty set is closed, PL/SQL throws the exception collection_is_null.

2. Comprehensive demonstration of each method

--> Example 1 declare output varchar2 (300); Type index_by_type is table of varchar2 (10) index by binary_integer; index_by_table index_by_type; Type nested_type is table of number; nested_table nested_type --> initialize the nested table in the Declaration block and assign a value: = nested_type (90,100, 50, 60 ); begin -- populate index by table for I in 1 .. 10 --> assign loop index_by_table (I): = 'value _ 'to the Union array in the execution block | I; end loop; dbms_outp Ut. put_line ('----------------------------- before deleted -----------------------------------------'); for I in index_by_table.first .. index_by_table.last --> uses first and last as the upper and lower mark of the loop counter to output all the elements of the current Union array loop output: = output | nvl (to_char (index_by_table (I )), 'null') | ''; end loop; dbms_output.put_line ('element of index_by_table are: '| output); output: =''; for I in 1 .. nested_table.coun T --> Use count as the cyclic counter to output all the elements of the current nested table loop output: = output | nvl (to_char (nested_table (I), 'null ') | ''; end loop; dbms_output.put_line ('element of nested_table are: '| output); If index_by_table.exists (3) then --> the exists function checks whether the 3rd elements in the Union array exist dbms_output.put_line ('index _ by_table (3) exists and the value is '| index_by_table (3); end if; -- delete 10th element from a collection nest Ed_table.delete (10); -- delete elements 1 through 3 from a collection nested_table.delete (1, 3); index_by_table.delete (10); dbms_output.put_line ('nested _ table. count = '| nested_table.count); dbms_output.put_line ('index _ by_table.count =' | index_by_table.count); dbms_output.put_line ('nested _ table. first = '| nested_table.first); dbms_output.put_line ('nested _ table. last = '| Neste D_table.last); Evaluate ('index _ by_table.first = '| index_by_table.first); Evaluate ('index _ by_table.last =' | index_by_table.last); dbms_output.put_line ('nested _ table. prior (2) = '| nested_table.prior (2); dbms_output.put_line ('nested _ table. next (2) = '| nested_table.next (2); dbms_output.put_line ('index _ by_table.prior (2) =' | index_by_table.prior (2) ); Evaluate ('index _ by_table.next (2) = '| index_by_table.next (2); -- trim last two elements nested_table.trim (2); -- trim last element nested_table.trim; dbms_output.put_line ('nested _ table. last = '| nested_table.last); dbms_output.put_line (' ------------------------- after deleted SUCCESS '); output: = ''; for I in index_by_table.first .. I Ndex_by_table.last --> output all the remaining elements in the array after the elements are deleted. loop output: = output | nvl (to_char (index_by_table (I), 'null') | ''; end loop; dbms_output.put_line ('element of index_by_table are: '| output); output: = ''; for I in nested_table.first .. nested_table.last --> output all the remaining elements of the nested table loop output: = output | nvl (to_char (nested_table (I), 'null') | ''; end loop; dbms_output.put_li Ne ('element of values are: '| output); end; describefore deleted partition element of index_by_table are: value_1 value_2 value_3 value_4 value_5 value_6 value_7 value_8 value_9 value_10element of values are: 10 20 30 40 50 60 70 80 90 100index_by_table (3) exists and the value is value_3nested_table.count = 6 --> the nested table uses two delete operations, which are Delete Except the last element and the 1st to 3rd elements, the Count output of the nested table is 6index_by_table.count = 9 --> the last element is deleted from the Union array, therefore, the Count output of the combined array is 9nested_table.first = 4 --> the nested table deletes 1st to 3rd elements, so its first is 4nested_table.last = 9 --> the nested table deletes the last element, therefore, the last value is changed to 9index_by_table.first = 1index_by_table.last = 9nested_table.prior (2) = --> prior (2) of the nested table. The first one of the 2nd elements (subscript: 1) is deleted because both 1-3, and there is no element before 1, so it is the subscript of the element after nullnested_table.next (2) = 4 --> nested table 2. It should have been 3. Because 3 is deleted, 3 is ignored, returns 4index_by_tabl. E. Prior (2) = 1index_by_table.next (2) = 3 nested_table.last = 7 --> nested_table.trim (2) and nested_table.trim delete three elements and placeholders in total, so last is 7. Lost after deleted partition element of index_by_table are: value_1 value_2 value_3 value_4 value_5 value_6 value_7 value_8 value_9element of nested_table are: 40 50 60 70pl/SQL procedure successfully completed. examples> Example 2 declare type varray_type is varray (10) of number; varray varray_type: = varray_type (1, 2, 3, 4, 5, 6); Procedure print_numlist (the_list varray_type) is output varchar2 (128); begin for I in the_list.first .. the_list.last loop output: = output | nvl (to_char (the_list (I), 'null') | ''; end loop; dbms_output.put_line (output); end; begin print_numlist (varray); dbms_output.put_line ('varray. count = '| varray. count); dbms_output.put_line ('varray. limit = '| varray. limit); dbms_output.put_line ('varray. first = '| varray. first); dbms_output.put_line ('varray. last = '| varray. last); dbms_output.put_line ('the maximum number you can use with' | 'varray. extend () is '| (varray. limit-varray. count); varray. extend (2, 4); --> copy two copies of the values of the 4th elements and append them to dbms_output.put_line ('varray. last = '| varray. last); dbms_output.put_line ('varray ('| varray. last | ') =' | varray (varray. last); print_numlist (varray); -- trim last two elements varray. trim (2); dbms_output.put_line ('varray. last = '| varray. last); end; 1 2 3 4 5 6 --> output all elements of varray. count = 6varray. limit = 10 --> limit method to get the maximum capacity varray of the variable-length array. first = 1varray. last = 6The maximum number you can use with varray. extend () is 4 --> to get the extend capacity, that is, four elements of varray can be saved. last = 8 --> the value of the last tag after extend is 8 varray (8) = 4 --> the value of the 8th elements is 41 2 3 4 5 6 4 4 --> output all the elements of varray. last = 6 --> varray is used. trim (2), so last becomes 6pl/SQL procedure successfully completed. --> author: Robinson Cheng --> blog: http://blog.csdn.net/robinson_0612

Iii. More references

Initialization and assignment of PL/SQL Sets

PL/SQL Union arrays and nested tables
PL/SQL variable-length Array
PL/SQL --> PL/SQL records

SQL tuning steps

Efficient SQL statements

Parent cursor, child cursor, and shared cursor

Bind variables and their advantages and disadvantages

Use of the display_cursor function of dbms_xplan

Use of the display function of dbms_xplan

Description of each field module in the execution plan

Use explain plan to obtain the SQL statement execution plan

Enable autotrace

The function invalidates the index column.

Oracle variable binding

Oracle adaptive shared cursor