Table variables (array variables) in Oracle)

Table type variables, also known as index_by tables or PL/SQL tables, are different from data tables and are similar to the two column structures of the keys and values of arrays.

The syntax is as follows:

Type table type name is table of type index by binary_integer;

Table variable name table type;

The data type can be number, varchar2, date, and other data types. The index by binary_integer clause indicates that the index is composed of symbol integers,

In this way, the data method used to access table-type variables is "Table variable name (index symbol integer)". Note that the index can be non-consecutive and different from the database table.

SQL operations such as select, insert, and update cannot be used in the index-by table.

The following program defines two one-dimensional table types, tabletype1 and tabletype2, which are equivalent to one-dimensional arrays. Table 1 and Table 2 are two types of table variables.

Declare

Type tabletype1 is table of varchar2 (10) index by binary_integer;

Type tabletype2 is table of scott. testtable. recordnumber % type index by binary_integer;

Table1 tabletype1;

Table2 tabletype2;

Begin

Table1 (1): = '';

Table1 (2): = 'Junior College ';

Table2 (1): = 88;

Table2 (2): = 55;

Dbms_output.put_line (table1 (1) | table2 (1 ));

Dbms_output.put_line (table1 (2) | table2 (2 ));

End;

Define a variable of the multidimensional table Type:

Declare

Type tabletype1 is table of testtable % rowtype index by binary_integer;

Table1 tabletype1;

Begin

Select * into table1 (60)

From scott. testtable

Where recordnumber = 60;

Dbms_output.put_line (table1 (60). recordnumber | ''| table1 (60). currentdate );

End;

This program defines a multi-dimensional table Type named tabletype1, which is equivalent to a multi-dimensional array. table1 is a multi-dimensional table type variable that includes the data table tempuser. in testtable, records with a recordnumber of 60 are extracted and stored in table1 and displayed.

In the defined table type variables, you can use attributes such as count, delete, first, last, next, exists, and prior to perform operations. property ", returns a number.