Plsql Collection of Notes

For multi-row single-column data processing, it is not possible to use the previous scalar variable, where a new data type composite data type is introduced.

This compound type is similar to an array of languages struct[i], including index table, nested table, Edge length array of three types,

The index table is called Plsql table, subscript and array comparison can be negative, subscript number is not limited, this can only be used in the data type, can not be defined after the use of the table to take the column type, do not need to initialize.

TYPE Type_name is TABLE of Element_type

[NOT NULL] index by KEY_TYPE;

identifier type_name;

The above syntax

Element_type is the data type of the index TABLE element

Key_type is the data type of the index Table element subscript (Binary_integer,pls_integer varchar2);

Set serveroutput on;

Declare

Type Ename_table_type is table of Emp.ename%type

Index by Binary_integer;

Ename_table Ename_table_type;

Begin

Select Ename into Ename_table ( -1) the from EMP where empno =&no;--Why it is used here-1 since it is handling multi-row single-column how to take out the data.

Dbms_output.put_line (' Ename_table (-1));

End

With the VARCHAR2 type as the subscript type, there should be doubt that the subscript is a character type

Set serveroutput on;

Declare

Type Area_table_type is table of number

Index by VARCHAR2 (10);

Area_table Area_table_type;

Begin

Area_table (' beijing '): = 1;

Area_table (' Tianjin '): = 2;

Area_table (' Heaven and earth '): = 3;

Dbms_output.put_line (Area_table.first);

Dbms_output.put_line (area_table (' Tianjin '))

Dbms_output.put_line (Area_table.last);

Two, nested table and array comparison subscript starting from 1, the element is not limited, this is a waste of the array, his element value is not sequential, the nested table can be used as the data type of the table, the use of the construction method to initialize the nested table.

Grammar

Type type_name is table of Element_type:

identifier type_name;

Declare

Type Ename_table_type is table of Emp.ename%type;

Ename_table ename_table_type:=ename_table_type (' A ', ' a ');

The following shows how to use nested tables

AH)

Declare

Type Ename_table_type is TABLE of Emp.ename%type;

Ename_table ename_table_type:=ename_table_type (' A ', ' a ', ' a ', ' a ', ' a ');

Begin

Select Ename into ename_table (2) initialization from EMP #使用的时需要用构造方法ename_table_type ()

where empno=&no;

Dbms_output.put_line (Ename_table (2));

End

As a type, you need to create the type first, and then you must specify the stored table

Create type Phone_type is Table of VARCHAR2 (20);

CREATE TABLE Employee (

ID Number (4), name VARCHAR2, sal number (6,2),

Phone phone_type) nested table phone Store as phone_table;

INSERT into employee values (1, ' Scott ', 800,phone_type (' 0473-343434 ', ' 128381298 '));

) Take data This case suggests a method owned by a collection type

Set serveroutput on;

Declare

Phone_table Phone_type;

Begin

Select phone into phone_table from employee where id=1;

For I in 1..phone_table.count loop

Dbms_output.put_line (Phone_table (i));

End Loop;

End

Three, variable-length array varray can be used as a table type, subscript 1 starts with a maximum of 7, using the construction room initialization

TYPE type_name is Varray (size_limit) of the TABLE Element_type [not NULL];

IDENTIFIER type_name;

Size_limit is used to specify the maximum number of elements, and the individual understands the number of elements, not the subscript.

Declare

TYPE Ename_table_type is Varray (in) of EMP. Ename%type;

Ename_table ename_table_type:=ename_table_type (' A ', ' a ', ' a ');

The record table is the combination of the record and the table Plsql variable processing single-row Plsql records for processing single-row multi-column, plsql collection user processing multiple rows of single columns, the record table is a combination of plsql records and Plsql table of a little composition

，

The multi-level collection type, similar to a multidimensional array, is nested with the collection type of the collection type, where the element type cannot be a collection type, can be a scalar, a plsql record, and an object type as if the object type has not been seen,

Vi. Methods of Collection,

exists determines whether the set element exists, is true, is not false

If Ename_table.exists (1) Then

Ename_table (1): = ' Scott ';

Else

Dbms_output.put_line (' a ');

End If;

Count returns the number of elements in the current collection variable,

For I in 1..ename_table.count loop

Dbms_output.put_line (Ename_table (i). ename);

End Loop;

Limit is used to return the maximum number of elements of the collection, this has not been understood, as the Varray table can be defined with Varray (20) to define the number of elements, but the subscript is only 7, it is possible to understand that a subscript corresponding to the number of elements and the maximum cannot exceed 20, There is still doubt about this.

Declare
Type Ename_table_type is Varray () of Emp.ename%type;
Ename_table1 ename_table_type:=ename_table_type (' a ');
Ename_table2 ename_table_type:=ename_table_type (' aaaa ');
Ename_table3 ename_table_type:=ename_table_type (' aaaaaaaaaa ');
Ename_table4 ename_table_type:=ename_table_type (' aaaa ', ' 3333333333 ');
Begin
Dbms_output.put_line (Ename_table1.limit);
Dbms_output.put_line (Ename_table2.limit);
Dbms_output.put_line (Ename_table3.limit);
Dbms_output.put_line (Ename_table4.limit);
End

The four value outputs are 20, which is the value you define when he calculates it,

First and last, returns the index of the element in the set variable, followed by the subscript of the final element.

Dbms_output.put_line (Ename_table.first);

Dbms_output.put_line (Ename_table.last), which is used when traversing loops, for I in 1..ename_table.last loop

Prior returns the previous subscript value of the current element subscript, and next returns a subscript value after the current element subscript.

Extend the size of the extended collection variable, just with the nested table and the Varray table, there is still doubt,

Trim removes an element from the end of the collection, Trim (n) If you don't write n, you delete one from the tail, and if you write it, you delete n from the tail.

Delete Deletes the specified nth element, three call formats, delete (m,n) you know

Set of two sets to assign values, do not know what the meaning (: =) or SQL statement to the source collection of data assigned to the target collection will automatically clear the original data, to assign null values to the collection, the collection is not initialized to

Another assignment is the set operator, set multiset union,multiset intersect,multiset except

Ename_table1: =set (Ename_table2);

Multiset Union of two sets

Result=nt1 multiset Union Nt2;

Multiset Union distinct takes two sets of unions and removes duplicate values

This article from "Yun Weibang" blog, declined reprint!

Plsql Collection of Notes