1. Using nested tables and variable arrays in pl/sql

1. Using nested tables in pl/sql

Nested tables are used in PL/SQL, where they are equivalent to arrays in other languages.

(1) Examples:

set SERVEROUTPUT ON
DECLARE
      --The following sentence is nested table declaration
TYPE ename_array_type is table of emp.ename%TYPE ;
--Define a cursor
CURSOR ename_cursor is select ename from emp where job='CLERK';
--Initialize the nested table, where the nested table is empty
ename_arrayename_array_type:=ename_array_type();
i number:=0;
BEGIN
forename in ename_cursor
loop
i:=i+1;
--implement extend Function, add one to the array null element
ename_array.EXTEND;
ename_array(i):=ename.ename;
    DBMS_OUTPUT.PUT_LINE(ename.ename);    
end loop;
--Nested table subscripts start at 1, not 0
DBMS_OUTPUT.PUT_LINE('Nested table:'||ename_array(1)||'  '|| ename_array(2));
END;
/

(2) Nested table declaration:

TYPE type_name is table of table element type. For example:

TYPE ename_array_type is table of emp.ename%TYPE ;

(3) Initialization of nested tables:

Nested tables must be initialized before they can be used. There are two ways of initialization.
One is to initialize to null, as in the example above.

ename_arrayename_array_type:=ename_array_type();

Since ename_array is initialized to be empty, if you need to put data in it, then
You need to add an element to it, otherwise you will report an error. EXTEND is used to add elements to an array, such as in the example above:
ename_array.EXTEND;

The other is to allocate elements directly to groups when initializing.
For example: ename_array:=ename_array_type('grass mud horse','grass mud horse');
The values of ename_array(1) and ename_array(2) are grass mud horses. You can also assign values directly to ename_array(1) and ename_array(2), such as

ename_array(1):='Alpaca';
ename_array(2):='Red alpaca';

But if you want to assign a value to the third element, you need to execute it first: ename_array.EXTEND.
Because only two elements are allocated to the array at initialization time, I want to give the third element.
An element assignment must first assign an element to a group.

2. Using Variable Array in pl/sql

(1) Examples:

set SERVEROUTPUT ON
DECLARE
--This example is basically the same as the previous one, except that the following sentence is the original one.is tableBecomeis varray(5)
TYPE ename_array_type is VARRAY(4) of emp.ename%TYPE ;
CURSOR ename_cursor is select ename from emp where job='CLERK';
ename_arrayename_array_type:=ename_array_type();
i number:=0;
BEGIN
forename in ename_cursor
loop
i:=i+1;
ename_array.EXTEND;
ename_array(i):=ename.ename;
DBMS_OUTPUT.PUT_LINE(ename.ename);    
end loop;
--Subscription also starts from 1, not 0.
DBMS_OUTPUT.PUT_LINE('Nested table:'||ename_array(1)||'  '|| ename_array(2));
END;
/

Variable array declaration

TYPE type_name is varray (number of array elements) of table element type. For example:

TYPE ename_array_type is VARRAY(4) of emp.ename%TYPE ;

Because ename_array_type is declared as VARRAY(4), this indicates the ename_array_type class
Type array, can only put up to four elements. If you want to assign a value to the fifth element, you will report an error, that is
ename_array(5): ='hello', at which point an error will be reported, even if EXTEND is called before assignment:
Enme_array.EXTEND will still report errors.

3. The difference between using nested tables and available arrays in pl/sql

Using nested tables and variable arrays in PL/SQL is basically the same. The only difference is that variables
The number of elements in a group is limited, while nested tables have no restrictions on the number of elements.

2. Use nested tables and variable arrays in tables (object-oriented SQL)

1. Use nested tables in tables

Example:

Creating Objects

create or replace TYPE comm_info AS OBJECT ( /*This type is a collection of communication modes.*/
no number(3), /*Communication type number*/
comm_type varchar2(20), /*Type of communication*/
comm_no varchar2(30)/*number*/
);

(2) Creating nested tables

create or replace TYPE comm_info_list AS table OF comm_info;

(3) Creating tables

create table user_info(
user_id number(6), /*User number ID*/
user_name varchar2(20), /*User name*/
user_comm  comm_info_list /*Communications with Users*/
)--The following sentence means user_comm Data Placement user_comm_table Table store
nested table user_comm store as user_comm_table;

After executing the command, you will find that you have created two tables, one is user_info and the other is nested table.
user_comm_table

4. Insert data

insert into user_info
values(1,'mary',comm_info_list(
    comm_info(1,'Mobile phone','13651401919'),
    comm_info(2,'Pager','1281234567')
  )
);

insert into user_info
values(2,'carl',comm_info_list(
    comm_info(1,'Mobile phone','13901018888'),
    comm_info(2,'Pager','1281234567')
  )
);

_. Query nested tables

When querying the nested table information through user_info, pay attention to the query in parentheses. Only one column can be queried, that is, user_comm can be selected instead of user_comm, user_name, and only one record can be returned. Otherwise, errors will be reported.

selectcomm_type,comm_nofrom table(
select user_comm from user_info where user_id=1
);
The results are as follows:
COMM_TYPE            COMM_NO                      
-------------------- ------------------------------
Mobile phone 13651401919                   
Pager 1281234567   

If you do not query specific nested table information, there is no restriction that only one column can be queried and only one record can be returned, but it seems that the information obtained is not so useful:

select user_comm from user_info;
//The implementation is as follows:

-------------------------------------------------------------------------------------------------------
SCOTT.COMM_INFO_LIST(
    SCOTT.COMM_INFO(1,'Mobile phone','13651401919'),
    SCOTT.COMM_INFO(2,'Pager','1281234567')
) 
SCOTT.COMM_INFO_LIST(
    SCOTT.COMM_INFO(1,'Mobile phone','13901018888'),
    SCOTT.COMM_INFO(2,'Pager','1281234567')
)    

2. Use variable arrays in tables

Example:

Creating Objects

create or replace TYPE comm_info AS OBJECT ( /*This type is a collection of communication modes.*/
    no number(3), /*Communication type number*/
    comm_type varchar2(20), /*Type of communication*/
    comm_no varchar2(30)/*number*/
);

(2) Create variable arrays

create or replace TYPE comm_info_list AS varray(50) OF comm_info;

(3) Creating tables

create table user_info(
    user_id number(6), /*User number ID*/
    user_name varchar2(20), /*User name*/
    user_comm comm_info_list /*Communications with Users*/
)

As for data insertion and query, they are the same as nested tables.

3. The difference between using nested tables and variable arrays in tables

The first difference is that tables are created differently. Nested tables need to be added when they are created.
After nested table user_comm store as user_comm_table and executing the create table statement,
Two tables are generated, one of which is a nested table, and information about the type of nested table is placed in the nested table. and
Variable arrays do not need to add this sentence, so the statement to create the table is relatively simple, and the execution of the creation.
Later, only one table will be created.

The second difference is:
Variable arrays are limited by the number of elements, such as
create or replace TYPE comm_info_list AS varray(50) OF comm_info;
An array of comm_info_list type can only contain up to 50 elements, that is, when inserted,

insert into user_info
    values(1,'mary',comm_info_list(
    comm_info(1,'Mobile phone','13651401919'),
    comm_info(2,'Pager','1281234567')
  )
);

The comm_info in parentheses can only be placed at most 50, and there are two on it, respectively.
comm_info(1,'cell phone','13651401919') and comm_info(2,'pager','1281234567')
Nested tables do not have this limitation.

index by

Index tables can only be used in PL/SQL. In previous PL/SQL, nested tables were used with the declaration that:
TYPE ename_array_type is table of emp.ename%TYPE ;
Changing this declaration to index by becomes an index table, i.e.
TYPE ename_array_type is table of emp.ename%TYPE index by binary_integer ;
Biary_integer can be followed by other PL/SQL supported types, such as index by varchar2
Although index tables are similar to nested tables, nested tables are like arrays, and index tables are more like map of key-value mapping.
And after index by is the data type of key.

1. Examples of index table usage:

set SERVEROUTPUT ON
DECLARE
    TYPE ename_array_type is table of emp.ename%TYPE index by BINARY_INTEGER;
    CURSOR ename_cursor is select ename from emp where job='CLERK';
    ename_array ename_array_type;--Just define it directly here. No initialization is needed.
    i BINARY_INTEGER:=0;
BEGIN
   for ename in ename_cursor
    loop
       -- ename_array.EXTEND;This sentence has been annotated.
        ename_array(i):=ename.ename;
        DBMS_OUTPUT.PUT_LINE(ename.ename);    
        i:=i+1;
    end loop;
--Subscripts start at 0
    DBMS_OUTPUT.PUT_LINE('Nested table:'||ename_array(0)||'  '|| ename_array(1));
end;
/

You can see that using index tables in PL/SQL is much simpler than using nested tables and variable arrays.

2. Index table as an example of map usage

Indexed tables are more like map s. Why do you say that? Let's look at the following examples:

set SERVEROUTPUT ON
declare
    type map_type is table of number(3) index by varchar2(3);
    map_ map_type;  
    map_key varchar2(3);
begin
    map_ ('a') := 10;
    map_ ('b') := 20;  
    map_key :=map_ .first;
    while(map_key is not null) loop
        dbms_output.put_line(map_ (map_key));
        map_key:=map_ .next(map_key);
    end loop;
end;
/

Fourth, the difference between nested tables, variable arrays and index tables

The difference between nested tables and variable arrays is that nested tables have no restrictions on the number of elements, while variable arrays have restrictions on the number of elements. The difference between nested tables and variable arrays and index tables is that nested tables and variable arrays can be used in non-PL/SQL environments, while index tables can only be used in PL/SQL.

V. CORRELATION FUNCTIONS

1. COUNT returns the number of elements in a collection

2. Delete set elements DELETE, DELETE(x), DELETE(x,y), TRIM, TRIM(x)
DELETE deletes all elements in a collection
DELETE(x) deletes elements with an element subscript x, and if x is null, the set remains unchanged against VARRAY
illegal
DELETE(x,y) deletes elements with subscripts ranging from X to Y if the set of X > Y remains unchanged for VARRAY
illegal
TRIM deletes an element from the end of the collection that is not valid for index_by
TRIM(x) Deleting x elements from the end of the collection is not valid for index_by

3. Judging whether an element exists EXIST(x)
If the collection element x is initialized, TRUE is returned, otherwise FALSE is returned.

4. Adding elements EXTEND, EXTEND(x), EXTEND(x,n) to the collection
EXTEND adds an element at the end of the collection that is illegal for Index_by
EXTEND(x) Adding x elements at the end of the collection is illegal for Index_by
EXTEND(x,n) Adding x copies of element n at the end of the collection is illegal for Index_by

5. Return set element subscripts FIRST, LAST, NEXT(x), PRIOR(x)
FIRST returns the subscript of the first element in the collection and always returns 1 for the VARRAY collection.
LAST returns the subscript of the last element in the collection, and the return value for VARRAY is always equal to COUNT.
NEXT(x) returns the subscript value of the element after and next to element x, if that element is the last
For each element, null is returned.
PRIOR(x) returns the subscript value of the element in the set immediately before element x, if the element is the first
An element returns null.

6. LIMIT returns the maximum number of elements in the VARRY set, which is not useful for nested tables and Index_by sets.