Oracle_pl/sql (7) Collection

PL/SQL Collection
Processing single row of data, you can use the scalar quantity;
Processing single-row multi-column data, you can use PL/SQL records (%rowtype,record);
Working with single-column multi-row data, you can use PL/SQL collections.

The PL/SQL collection type is a composite data type similar to a high-level language array.
Includes: Nested tables (table), Array (varray), two types.

One, nested table
1. Definition:
Nested tables are used to handle multiple numeric values of the same type, each of which is called an element.
Number of elements starting from 1, number of elements no limit
1.2 Syntax:
Type type_name is table of Element_type;
identifier type_name;
Description
Type_name is a user-defined type name
Element_type is an element type
Identifier is a variable defined by a user using a custom type
1.3 Examples:
First example: an example of 1 elements
Declare
Type type_varchar2 is Table of VARCHAR2 (20);
V_type type_varchar2;
Begin
V_type:=type_varchar2 (' A ');
Dbms_output.put_line (' output element: ' | | V_type (1));
End
Examples of multiple elements
Declare
Type type_varchar2 is Table of VARCHAR2 (20);
V_type type_varchar2;
Begin
V_type:=type_varchar2 (' A ', ' Adsvad ', ' svadfvadv ');
Dbms_output.put_line (' output element: ' | | V_type (2));
End
Summarize:
When assigning a value to a nested table variable, you must add elements within the nested table type, which can be 1 or more elements.
You cannot assign an element directly to a nested table variable.
Nested table variables can be used only after they are assigned, using the elements within the nested table variable by subscript.
Subscripts are the order in which elements are added to nested table variables.

Second, array (varray)
1. Definition
Arrays are used to handle multiple numeric values of the same type, each of which is called an element.
The number of elements starts at 1, and the maximum number of elements is limited.
2. Syntax:
Type type_name is Varray (size_limite) of element_type [NOT NULL];
identifier type_name;
Description
Type_name is a user-defined type name
Varray () is the keyword of the array
Size_limite is the length of the array
Element_type is an element type
Identifier is a variable defined by a user using a custom type
Differences between arrays and nested tables:
Different keywords: table, varray
Length restrictions differ: table has no length limit, Varray has a length limit
3. For example
Declare
Type type_arr_varchar2 is Varray of VARCHAR2 (20);
V_arr type_arr_varchar2;
Begin
V_arr:=type_arr_varchar2 (' Zhang San ', ' John Doe ');
Dbms_output.put_line (' output element: ' | | V_arr (1));
Dbms_output.put_line (' output element: ' | | V_arr (2));
End
To assign a value by using a query:
Declare
Type Type_arr_ename is Varray () of Emp.ename%type;
V_arr type_arr_ename:=type_arr_ename (");
Begin
Select Ename to V_arr (1) from EMP where empno=&no;
Dbms_output.put_line (' Employee Name: ' | | V_arr (1));
End
Description
Table and Varray functions are the same and can be used to store multiple values of the same type;
Table and Varray are used the same way, you need to define the type first, then define the variable,
The elements are then added to the variable, and the element is finally used by subscript.
The properties of table and Varray are the same.

Third, set properties
A collection property is a method (function and procedure) that a database provides to a user's collection of actions.
The process includes: Extend,trim,delete
Functions include: Exists,count,limit,first,last,prior,next.
The collection method can only be used in a PL/SQL statement and cannot be used in a statement.
1.extend:
Used to extend the dimensions of collection variables and to add elements to them.
Initialization of a collection variable:
A collection-type constructor allows you to initialize a set variable (a chunk of memory space is allocated to the set variable).
Extend is the memory space in which the element is allocated a small chunk of memory space allocated to the set variable.
Three types of call formats:
Extend allocating the memory space of an element
Extend (n) allocates memory space for n elements
Extend (n,i) allocates the memory space of n elements and assigns an initial value to the n elements with the element I

The first example can be rewritten as the following generic method.
Declare
Type type_varchar2 is Table of VARCHAR2 (20);
V_type type_varchar2:=type_varchar2 ();
Begin
V_type.extend;
V_type (1): = ' A ';
Dbms_output.put_line (' output element: ' | | V_type (1));
End

Table Syntax Refinement:
Type type_name is table of element_type;
identifier type_name:=type_name ();
Varray syntax improvements:
Type type_name is Varray (size_limite) of element_type [NOT NULL];
identifier type_name:=type_name ();

Example 1:extend for table
Declare
Type type_tab_ename is table of Emp.ename%type;
V_arr Type_tab_ename:=type _tab_ename ();
V_cnt number:=1;
Begin
for RS in (select ename from emp) loop
V_arr.extend;
V_arr (v_cnt): =rs.ename;
dbms_output.put_l INE (' Employee Name: ' | | V_arr (v_cnt));
V_cnt:=v_cnt+1;
End Loop;
End;

Example 2:extend (n) for Varray
Select COUNT (1) from EMP;
Declare
Type Type_arr_ename is Varray (+) of Emp.ename%type;
V_arr type_arr_ename:=type_arr_ename ();
v_cnt Number:=1;
begin
V_arr.extend;
for RS in (select ename from emp) loop
V_arr (v_cnt): =rs.ename;
DBMS_OUTPUT.P Ut_line (' Employee Name: ' | | V_arr (v_cnt));
V_cnt:=v_cnt+1;
End Loop;
End;
Example 3:extend (n,i) is used for Varray, and the I element must already exist
declare
type Ename_table_type is Varray (all) of VARCHAR2 (ten);
Ename_ Table Ename_table_type;
Begin
Ename_table:=ename_table_type (' Mary ');
Ename_table.extend (5,1);
Dbms_output.put_line (' Number of elements: ' | | Ename_table.count);
for I in 1..ename_table.count loop
Dbms_output.put_line (' element ' | | i| | ': ' | | Ename_table (i));
End Loop;
End;

2.trim: Used to delete elements from the end of the collection
There are two types of invocation formats:
Trim: Delete last element
Trim (n): Delete last n elements
Applies to nested tables and Varray
Declare
Type Ename_table_type is Table of VARCHAR2 (10);
Ename_table Ename_table_type;
Begin
Ename_table:=ename_table_type (' A ', ' B ', ' C ', ' d ', ' e ');
Ename_table.trim (2);
Dbms_output.put_line (' Number of elements: ' | | Ename_table.count);
End

3.delete: Deletes the specified element,
There are three ways to call:
Delete Deletes all elements
Delete (n) deletes the nth element (delete 1 elements)
Delete (m,n) removes all elements from the collection variable from m to n
Applies only to nested tables, delete (n) \delete (m,n) does not apply to Varray.
Example 1:delete Delete nth element
Declare
Type Type_tab_ename is table of Emp.ename%type;
V_arr Type_tab_ename:=type_tab_ename ();
V_cnt number:=1;
Begin
For RS in (select ename from emp) loop
V_arr.extend;
V_arr (v_cnt): =rs.ename;
Dbms_output.put_line (' Employee Name: ' | | V_arr (v_cnt));
v_cnt:=v_cnt+1;
End Loop;
Dbms_output.put_line (' Number of elements: ' | | V_arr.count);
V_arr.delete;
Dbms_output.put_line (' Number of elements: ' | | V_arr.count);
End
Example 1-2:varray
Declare
Type Type_tab_ename is Varray (+) of emp.ename%type;
V_arr Type_tab_ename:=type_tab_ename ();
V_cnt number:=1;
Begin
For RS in (select ename from emp) loop
V_arr.extend;
V_arr (v_cnt): =rs.ename;
Dbms_output.put_line (' Employee Name: ' | | V_arr (v_cnt));
v_cnt:=v_cnt+1;
End Loop;
Dbms_output.put_line (' Number of elements: ' | | V_arr.count);
V_arr.delete;
Dbms_output.put_line (' Number of elements: ' | | V_arr.count);
End
Example 2:delete (n) Delete nth element
Declare
Type Type_tab_ename is table of Emp.ename%type;
V_arr Type_tab_ename:=type_tab_ename ();
V_cnt number:=1;
Begin
For RS in (select ename from emp) loop
V_arr.extend;
V_arr (v_cnt): =rs.ename;
Dbms_output.put_line (' Employee Name: ' | | V_arr (v_cnt));
v_cnt:=v_cnt+1;
End Loop;
Dbms_output.put_line (' Number of elements: ' | | V_arr.count);
V_cnt:=v_arr.count;
V_arr.delete (5);
Dbms_output.put_line (' Number of elements: ' | | V_arr.count);
For I in 1..v_cnt loop
If I=5 Then
Continue
End If;
Dbms_output.put_line (' Element ' | | i| | ': ' | | V_arr (i));
End Loop;
End

Example 3:delete (m,n) deletes all elements of a collection variable from m to n
Declare
Type type_tab_ename is table of Emp.ename%type;
V_arr type_tab_ Ename:=type_tab_ename ();
V_cnt number:=1;
Begin
for RS in (select ename from emp) loop
V_arr.extend;
V_arr (v_cnt): =rs.ename;
dbms_output.put_l INE (' Employee Name: ' | | V_arr (v_cnt));
V_cnt:=v_cnt+1;
End Loop;
Dbms_output.put_line (' Number of elements: ' | | V_arr.count);
V_cnt:=v_arr.count;
V_arr.delete (5,10);
Dbms_output.put_line (' Number of elements: ' | | V_arr.count);
for I in 1..v_cnt loop
if i>=5 and i<=10 then
continue;
End If;
Dbms_output.put_line (' element ' | | i| | ': ' | | V_arr (i));
End Loop;
End;

4.exists: Used to determine whether the collection element exists
Declare
Type Ename_table_type is table of Emp.ename%type;
Ename_table Ename_table_type:=ename_table_type ();
Begin
If Ename_table.exists (1) Then
Ename_table (1): = ' Scott ';
Dbms_output.put_line (' element: ' | | Ename_table (1));
Else
Dbms_output.put_line (' must initialize the elements of the collection ');
End If;
End

5.count: Used to return the total number of elements in the current collection variable.

6.limit: The maximum number of elements used to return a collection.
No limit on the number of nested table elements, no return value
The number of Varray elements is set when defined, returning the maximum number of elements allowed by Varray
Declare
Type Ename_table_type is Varray () of Emp.ename%type;
Ename_table Ename_table_type:=ename_table_type ();
Begin
Dbms_output.put_line (' Maximum number of collection elements: ' | | Ename_table.limit);
End
Declare
Type Ename_table_type is table of Emp.ename%type;
Ename_table Ename_table_type:=ename_table_type ();
Begin
Dbms_output.put_line (' Maximum number of collection elements: ' | | Ename_table.limit);
End

7.first and Last:
first to return the subscript for a set variable,
last to return the subscript of the final element of the collection variable
Declare
type Ename_table_type is Varray (ten) of Emp.ename%type;
Ename_table ename_table_type:=ename_table_type (' Scott ', ' Smith ', ' Mary ', ' Blake ');
Begin
Dbms_output.put_line (' subscript for the first element: ' | | Ename_table.first);
Dbms_output.put_line (' subscript of the last element: ' | | Ename_table.last);
Dbms_output.put_line (' first element: ' | | Ename_table (Ename_table.first));
Dbms_output.put_line (' last element: ' | | Ename_table (Ename_table.last));
End;

8.prior and Next:
Prior is used to return the subscript of the previous element of the current collection element,
Next returns the subscript for the latter element of the current collection element
Declare
Type Ename_table_type is Varray (ten) of Emp.ename%type;
Ename_table ename_table_type:=ename_table_type (' Scott ', ' Smith ', ' Mary ', ' Blake ');
Begin
Dbms_output.put_line (' current element: ' | | Ename_table (2));
Dbms_output.put_line (' Subscript of the previous element of the current element: ' | | Ename_table.prior (2));
Dbms_output.put_line (' subscript of the latter element of the current element: ' | | Ename_table.next (2));
Dbms_output.put_line (' The previous element of the current element: ' | | Ename_table (Ename_table.prior (2)));
Dbms_output.put_line (' The last element of the current element: ' | | Ename_table (Ename_table.next (2)));
End

Processing single-row single-column data, you can use the scalar quantity (varchar2, number, date, Boolean);
Processing single-row multi-column data, you can use PL/SQL records (%rowtype,record);
Processing single-column multi-row data, you can use PL/SQL collections (table, Varray);
So how do you deal with multiple rows of data?

Iv. Nesting of collection types (union)
Combination of 1.table and%rowtype
Declare
Type Type_tab_ename is table of Emp%rowtype;
V_arr Type_tab_ename:=type_tab_ename ();
V_cnt number:=1;
Begin
For RS in (SELECT * from emp) loop
V_arr.extend;
V_arr (v_cnt): =rs;
Dbms_output.put_line (' Employee Name: ' | | V_arr (v_cnt). ename);
v_cnt:=v_cnt+1;
End Loop;
End

2. Nesting of two-d arrays Varray and Varray
Declare
Type D1_varray_type is Varray (ten) of number;--defines one-dimensional varray
Type Nd1_varray_type is Varray (5) of d1_varray_type;--defines two-dimensional Varray collection
--Initialize a two-dimensional set of variables
V_arr Nd1_varray_type:=nd1_varray_type (
D1_varray_type (2,4),
D1_varray_type (5,73));
Begin
Dbms_output.put_line (' Show all elements of a two-dimensional array ');
For I in 1..v_arr.count loop
For j in 1..v_arr (i). Count Loop
Dbms_output.put_line (' V_arr (' | | i| | ', ' | | j| | ') = ' | | V_arr (i) (j));
End Loop;
End Loop;
End

(2,4)
(5,73)

If there is no limit to the number of elements in a multidimensional collection, you can nest another nested table type in a nested table type
3. Nested table of two-D tables and table nesting
Declare
Type D1_table_type is table of number;--defining a one-dimensional nested table
Type Nd1_table_type is table of d1_table_type;--defines a two-dimensional nested table collection
--Initializes a two-dimensional set variable
V_tab nd1_table_type : =nd1_table_type (
D1_table_type (58,100,102),
D1_table_type (55,6),
D1_table_type (2));
Begin
Dbms_output.put_line (' Show all elements of a two-dimensional array ');
for i in 1..v_tab.count Loop
for J in 1..v_tab (i). Count Loop
dbm S_output.put_line (' V_tab (' | | i| | ', ' | | j| | ') = ' | | V_tab (i) (j));
End Loop;
End Loop;
End;

V. Assignment between collections: =
1. Assignment: =
Assign a collection's data to another collection
the source and destination collections must have exactly the same data type.
Declare
Type Name_varray_type is Varray (4) of VARCHAR2 (ten);
Name_array1 name_varray_type;
Name_array2 name_ Varray_type;
Begin
Name_array1:=name_varray_type (' Scott ', ' Smith ');
Name_array2:=name_varray_type (' A ', ' a ', ' a ', ' a ') ;
Dbms_output.put_line (' Name_array2 's original data: ');
for I in 1..name_array2.count Loop
Dbms_output.put ("| | Name_array2 (i));
End Loop;
Dbms_output.new_line;
Name_array2:=name_array1;--With: = Sign assignment
Dbms_output.put_line (' name_array2 new data: ');
for I in 1..name_ Array2.count Loop
Dbms_output.put ("| | Name_array2 (i));
End Loop;
Dbms_output.new_line;
End;

2. Assign a null value to the collection:
Empties all data for the collection variable (collection method Detele,trim can also)
Declare
Type Name_varray_type is Varray (4) of VARCHAR2 (10);
Name_array Name_varray_type;
Name_empty Name_varray_type;
Begin
Name_array:=name_varray_type (' Scott ', ' Smith ');
Dbms_output.put_line (' Name_array ' number of original elements: ' | | Name_array.count);
Name_array:=name_empty;
If Name_array is null then
Dbms_output.put_line (the number of existing elements of ' Name_array: 0 ');
End If;
End

Vi. nested table-specific operations:
Set: Used to cancel duplicate values in a nested table.
Multiset Union: Used to obtain a set of two nested tables (can be heavy)
Multiset Union distinct: used to get the unions of two nested tables (distinct).
Multiset intersect: Used to get the intersection of two nested tables.
Nultiset except: Used to get the difference set of two nested tables
1.set:
Used to cancel duplicate values in a particular nested table.
Declare
Type Nt_table_type is table of number;
V_tab Nt_table_type:=nt_table_type (2,4,3,1,2);
R_tab Nt_table_type;
Begin
R_tab:=set (V_tab);
For I in 1..r_tab.count loop
Dbms_output.put_line (' R_tab ' | | i| | ': ' | | R_tab (i));
End Loop;
End

2.multiset Union:
Used to get a set of two nested tables.
Elements of two nested tables are duplicated in the result set.
Declare
Type Nt_table_type is table of number;
Nt1 Nt_table_type:=nt_table_type (a);
Nt2 Nt_table_type:=nt_table_type (3,4,5);
Result Nt_table_type;
Begin
Result:=nt1 multiset Union Nt2;
For I in 1..result.count loop
Dbms_output.put_line (' Result ' | | i| | ': ' | | Result (i));
End Loop;
End
3.multiset Union DISTINCT:
Used to get a set of two nested tables.
Elements of two nested tables are not duplicated in the result set.
Declare
Type Nt_table_type is table of number;
Nt1 Nt_table_type:=nt_table_type (a);
Nt2 Nt_table_type:=nt_table_type (3,4,5);
Result Nt_table_type;
Begin
Result:=nt1 multiset Union distinct Nt2;
For I in 1..result.count loop
Dbms_output.put_line (' Result ' | | i| | ': ' | | Result (i));
End Loop;
End
4.multiset intersect
Used to get the intersection of two nested tables
Declare
Type Nt_table_type is table of number;
Nt1 Nt_table_type:=nt_table_type (a);
Nt2 Nt_table_type:=nt_table_type (3,4,5);
Result Nt_table_type;
Begin
Result:=nt1 multiset intersect Nt2;
For I in 1..result.count loop
Dbms_output.put_line (' Result ' | | i| | ': ' | | Result (i));
End Loop;
End
5.multiset except
Used to get a difference set of two nested tables.
That is, the element that exists in the first nested table but does not exist in the second nested table.
Declare
Type Nt_table_type is table of number;
Nt1 Nt_table_type:=nt_table_type (a);
Nt2 Nt_table_type:=nt_table_type (3,4,5);
Result Nt_table_type;
Begin
Result:=nt1 multiset except Nt2;
For I in 1..result.count loop
Dbms_output.put_line (' Result ' | | i| | ': ' | | Result (i));
End Loop;
End

Operator is empty: used to detect whether a nested table is null.
operator = and! =: Used to detect whether two nested tables are the same.
Function cardinality: The number of elements used to return a nested table variable
Submultiset of: Used to determine whether a nested table is a subset of another nested table
Operator member of: used to detect whether a particular data is a nested TABLE element
Operator is a set: used to detect whether a nested table contains duplicate element values
6.is Empty:
Used to detect if a nested table is null.
Detects whether the nested table has no elements.
Declare
Type Name_table_type is Table of VARCHAR2 (10);
Name_table Name_table_type:=name_table_type ();
Begin
If name_table is empty then
Dbms_output.put_line (' Nested table is empty ');
Else
Dbms_output.put_line (' Elements in nested tables ');
End If;
End
7.= and! =
Used to detect if two nested tables are the same.
Declare
Type Name_table_type is Table of VARCHAR2 (10);
Name_table1 Name_table_type;
Name_table2 Name_table_type;
Begin
Name_table1:=name_table_type (' Scott ');
Name_table2:=name_table_type (' Smith ');
If Name_table1=name_table2 Then
Dbms_output.put_line (' two nested tables are identical ');
Else
Dbms_output.put_line (' Two nested table values are different ');
End If;
End
8.cardinality
Used to return the number of elements in a nested table.
Declare
Type Nt_table_type is table of number;
Nt1 Nt_table_type:=nt_table_type (1,2,3,1);
Begin
Dbms_output.put_line (' Number of elements: ' | | Cardinality (NT1));
End
9.submultiset of
Used to determine whether a nested table is a subset of another nested table.
Declare
Type Nt_table_type is table of number;
Nt1 Nt_table_type:=nt_table_type (a);
Nt2 Nt_table_type:=nt_table_type (1,2,3,4);
Begin
If Nt1 Submultiset of Nt2 Then
Dbms_output.put_line (' Nt1 is a subset of the Nt2 ');
End If;
End
10.member of
An element used to detect whether a particular data is a nested table.
Declare
Type Nt_table_type is table of number;
Nt1 Nt_table_type:=nt_table_type (1,2,3,4);
V1 number:=&v1;
Begin
If V1 member of NT1 Then
Dbms_output.put_line (v1| | ' is the element of the nested table Nt1 ');
Else
Dbms_output.put_line (v1| | ' is not a nested table nt1 element ');
End If;
End
11.is a set
Used to detect whether a nested table contains duplicate element values
Declare
Type Nt_table_type is table of number;
Nt1 Nt_table_type:=nt_table_type (1,2,3,5);
Begin
If NT1 is a set then
Dbms_output.put_line (' Nested table without duplicate values ');
Else
Dbms_output.put_line (' Nested table with duplicate values ');
End If;
End

Seven, the work
1. Do a split function with the following functions:
Enter ' Zhang San, John Doe, Harry '
Multiple lines are output when the function is called, as shown below:
Create or Replace type type_table_varchar2 is Table of VARCHAR2 (20);
Create or Replace function split (P_var varchar2,p_fengefu varchar2) return type_table_varchar2
Is
V_rs type_table_varchar2:=type_table_varchar2 ();
Begin
Xxxx
return v_rs;
End

Select column from table (Split (' Zhang San, John Doe, Harry ', ', '));
Tom
John doe
Harry
2. Make an anonymous block, sort the array, and display the sorted elements. Like what:
array element order before ordering: 13 25 7 18 2 6 9 12
The order of the array elements after sorting is: 2 6 7 9 12 13 18 25

Oracle_pl/sql (7) Collection