1, Definition of Oracle collection

Set is a data type in Oracle, which stores a group of data with the same data type.

2, Composition of set

Consists of subscripts and values.
The types of subscripts include numeric types (integer type, pls_integer and binary_integer) and string types.
The types of values are all types in the database (basic data type, record type (record,% rowtype,% type), and collection type).
pls_integer and binary_integer is equivalent to number type, but takes less memory and faster arithmetic operation than number type.
pls_ When the operation performed by integer overflows, an exception will be triggered. The execution is run by the hardware cpu, and the execution speed is fast. binary_integer will not trigger exceptions. When type variables are executed by Oracle, they will not overflow, but the execution is slow. They are all integer types.

3, Type of collection

Set is a relatively broad concept. Three types of sets are provided in PL/SQL:
Index table: you can find the elements in it by using numbers or strings as subscripts. It can only be used in PL/SQL.
Nested table: it has all the characteristics of an indexed table, but the subscript can only be a data type (continuous integer). It can be used in PL/SQL and database.
Variable length array: subscripts can only be numeric types. You need to specify the maximum length when creating. It can be used in PL/SQL and database.

Collection common properties and methods

Collection properties / methods	describe
first	Take the subscript of the first element of the set
last	Take the subscript of the last element of the set
next (subscript)	Gets the subscript of the next element of the current element of the collection
prior (subscript)	Gets the subscript of the previous element of the current element of the collection
count	Get the number of elements in the collection
delete	Delete elements in the collection
limit	Maximum number of elements to set (variable length array)

3.1 index table type

3.1.1 syntax of index table

--Syntax for defining index table types:
type Type name is table of Data type of element value index by Data type of subscript;
--Declaration of index variables
 Variable name type name;
--Use of index variables
 Variable name(subscript);

3.1.2 use cases of index table

Case 1: define an index table type (subscript is numeric type), declare an index variable with the defined index table type, store three names, and output the first name

declare
  type type1 is table of varchar2(30) index by pls_integer;
  names type1;
begin
  names(1):='Xiao Cao 1';
  names(2):='Xiao Cao 2';
  names(3):='Xiao Cao 3';
  dbms_output.put_line(names(1));
end;

Case 2: define an index table type (subscript is string type) and use it to test the common properties and methods of the set

declare
  type type2 is table of varchar2(30) index by varchar2(10);
  names type2;
begin
  names('a'):='Xiao Cao 1';
  names('b'):='Xiao Cao 2';
  names('c'):='Xiao Cao 3';
  dbms_output.put_line(names.first); --a
  dbms_output.put_line(names.last); --c
  dbms_output.put_line(names.next(names.first)); --b
  dbms_output.put_line(names.prior(names.last)); --b
  dbms_output.put_line(names.count); --3
  names.delete;
end;

3.1.3 statement loop traversal

The collection provides a bulk collect statement to get the data in the table.
The element subscripts stored in the collection through the bulk collect statement start from 1 and are continuous, and the set subscripts must be numeric.

Grammar 1

select column.. bulk collect into Set variable from surface where condition

--Case:
declare
  type i_empno is table of emp.empno%type index by pls_integer;
  type i_ename is table of emp.ename%type index by pls_integer;
  eno i_empno;
  ena i_ename;
begin
  select empno,ename bulk collect into eno,ena from emp where deptno=30;
  for i in eno.first..eno.last loop
    dbms_output.put_line(eno(i) || ' ' || ena(i));
  end loop;
end;

Grammar 2

execute immediate 'select sentence' bulk collect into Set variable

--case
declare
  --Declare a variable to store select Query results
  v_sql varchar2(255);
  --Define index table collection storage emp In the table empno and ename
  type i_empno is table of emp.empno%type index by pls_integer;
  type i_ename is table of emp.ename%type index by pls_integer;
  --Define index variables
  eno i_empno;
  eme i_ename;
begin
  --take sql Statement assigned to v_sql
  v_sql:='select empno,ename from emp where deptno=30';
  --bulk collect sentence,take v_sql Query results eno and eme in
  execute immediate v_sql bulk collect into eno,eme;
  --Cyclic printing eno and eme All data in
  for i in eno.first..eme.last loop
    dbms_output.put_line(eno(i) || ' ' || eme(i));
  end loop;
end;

Grammar 3

fetch cursor bulk collect into Set variable

--case
declare
  --Declare a cursor
  cursor cursor1 is select empno,ename from emp where deptno=30;
  --Define index table collection storage emp In the table empno and ename
  type i_empno is table of emp.empno%type index by pls_integer;
  type i_ename is table of emp.ename%type index by pls_integer;
  --Define index variables
  eno i_empno;
  eme i_ename;
begin
  --Open cursor
  open cursor1;
  --bulk collect sentence
  fetch cursor1 bulk collect into eno,eme;
  --Close cursor
  close cursor1;
  --Cyclic printing eno and eme All data in
  for i in eno.first..eme.last loop
    dbms_output.put_line(eno(i) || ' ' || eme(i));
  end loop;
end;

3.2 nested tables

Nested tables, like index tables, store a group of data with the same data type.
Its subscript can only be of integer type (subscript is a continuous integer).
Nested table variables need to be initialized before use.

3.2.1 use in PL/SQL

3.2.1.1 syntax

--Syntax:
type Type name is table of Data type of stored data(length);
--Declare variables:
Variable name nested table type name;
--Initialization assignment:
Variable name:=Nested table type name();  
--Initializes a nested table with elements
 Variable name:=Nested table type name(value,value,value,..,value);
--Extended nested table
 Variable name.extend(n);
--Assignment:
Variable name(subscript):=value;

3.2.1.2 use cases
Case 1: initialize an empty nested table

declare
  type mytype is table of varchar2(20);
  c1 mytype;
begin
  --initialization
  c1 := mytype();
  dbms_output.put_line(c1.count);
  --Nested table c1 Extend two elements
  c1.extend(2);
  dbms_output.put_line(c1.count);
  c1(1) := 'A';
  c1.extend(5);
  for i in 2..6 loop
    c1(i) := chr(64 + i);
  end loop;
  for i in c1.first..c1.last loop
    dbms_output.put_line(c1(i));
  end loop;
end;

Case 2: initializing a nested table with elements

declare
  --Define a nested table
  type mytype is table of varchar2(20);
  c2 mytype;
begin
  --Initializes a nested table with elements
  c2 := mytype('Zhang San','Li Si','Wang Wu','Zhao Liu');
  --Nested table c2 Extend 2 elements
  c2.extend(2);
  c2(5):='millet';
  c2(6):='Xiao Cao';
  --Traversing nested tables
  for i in c2.first..c2.last loop
    dbms_output.put_line(c2(i));
  end loop;
end;

3.2.2 use in database

If nested tables are used in pl/sql, their functions are similar to index tables.
An important feature of nested tables is that they can be stored as data table columns. Therefore, nested tables can be stored in or taken out of data tables, which is a function that index tables do not have.
In order for the nested table type to be used in the data table, the nested table type must be saved in the data dictionary. Therefore, you need to use the create type statement to create a persistent nested table type.

3.2.2.1 syntax

create type Type name is table of Element data type;

3.2.2.2 cases

create type ctype is table of varchar2(30);
declare
 c ctype;
begin
  select ename bulk collect into c from emp where deptno=10;
  for i in c.first..c.last loop
    dbms_output.put_line(c(i));
  end loop;
end;

3.2.3 table in progress

3.2.3.1 syntax

--Create syntax:
create table Table name(
  Column name type,
  Nested table column name nested table type
)nested table Nested table column name store as Table name;
--insert data
insert into Table name(Field name 1,Field name 2)values(Nested table type name(Value 1),Nested table type name(Value 2));

3.2.3.2 cases

create table x(
  id number(10),
  namelist ctype
)nested table namelist store as y;

--insert data
insert into x(id,namelist)values(1,ctype('Xiao Hong','millet','potato'));
--query
select * from x;
select * from table(select namelist from x where id=1);

3.3 variable length array

Subscripts can also be numeric types. They need to be initialized and extended before use.
When defining a variable length array type, you need to specify its maximum length.
Variable length array cannot exceed the maximum length when expanding.
A nested table with a maximum length limit is encoded exactly the same as a nested table.

3.3.1 use in PL/SQL

3.3.1.1 syntax

--Create statement:
type Type name is varray(Maximum length) of The data type of the element;
--initialization:
Variable name:=Type name();--Initializes an empty variable length array
 Variable name:=Type name(Value 1,Value 2);--Initializes a variable length array with elements
--Element extension:
Variable name.extend(n);
--Assignment:
Variable name(n):=value;

3.3.1.2 cases

declare
  type atype is varray(5) of varchar2(30);
  a atype;
begin
  --initialization
  a:=atype();
  --extend
  a.extend(3);
  --Add data
  a(1):='A';
  a(2):='B';
  a(3):='C';
  for i in a.first..a.last loop
    dbms_output.put_line(a(i));
  end loop;
end;

3.3.2 use in database

3.3.2.1 create syntax

create type Type name is varray(length) of type;

3.3.2.2 case: query the names ranking from 5th to 14th in the emp table

create type atype1 is varray(10) of varchar2(30);
declare
  a atype1;
begin
  select ename bulk collect into a from (
  select ename,row_number() over(order by sal desc nulls last)ranks from emp)
  where ranks between 5 and 14;
  for i in a.first..a.last loop
    dbms_output.put_line(a(i));
  end loop;
end; 

3.3.3 table in progress

3.3.3.1 create syntax
create table table name(
Variable type,
Variable length array column variable length array type
);
3.3.3.2 cases

create table z(
  id number(10),
  namelist atype1
)
----insert data
insert into z(id,namelist) values(1,atype1('a','b','c','d'));
select * from table(select namelist from z where id=1);