Oracle series: PL/SQL tables

Oracle series: PL/SQL tables

1. What is a PL/SQL table?
First, PL/SQL tables and records are of the same composite data type. It can be seen as a user-defined data type.
A pl/SQL table is a temporary index table object consisting of scalar values of multiple columns and single rows. It is similar to a one-dimensional array.
The difference between a single row, multiple columns, and a table that stores physical data.
Record + PL/SQL tables can store data in multiple rows and multiple columns.
The lifecycle is within the block.

Ii. Structure of PL/SQL tables
The PL/SQL table has only two columns. The first column is the index column of the integer type, and the 9i can support the character index. The second column is a user-defined column that declares the types of available columns.
Use the index value in the index column to operate the User-Defined columns corresponding to the PL/SQL table. Similar to a key-value pair.
Make sure that the index value is unique. If the index value is repeated, no error is reported, and the previous value is overwritten.

3. How to create and use PL/SQL tables?

① Declare PL/SQL table type objects
Syntax:
Type PL/SQL table name is table of available column type [not null]
Index by binary_integer;

The available column types can be Oracle Data Types and user-defined types;

Attribute method:
Count -- returns the total number of rows in the PL/SQL table;
Delect -- delete all content in the PL/SQL table;
Delect (number of rows) -- delete the specified row in the PL/SQL table;
Delct (start row and end row) -- deletes multiple rows in the PL/SQL table;
First -- returns the first index of the table;
Next (number of rows) -- the next index of the number of rows;
Last -- return the last index of the table;
 
② Declare PL/SQL table type variables:
Syntax:
PL/SQL table type variable name PL/SQL table type;

③ Data filling and access
Syntax:
PL/SQL table type variable name (index column value): = fill in the declared type value;

PL/SQL table type variable name. Attribute method name;

Example:
Declare
Type mytabtype is table of varchar2 (10) index by binary_integer;
Mytab mytabtype;
Vn number (4 );
Begin
Mytab (1): = 'a ';
Mytab (2): = 'B ';
Mytab (3): = 'C'; -- overwrite the column B whose index value is 3.
Vn: = mytab. first;
Dbms_output.put_line ('first index: '| ''| VN | '');
Vn: = mytab. Last;
Dbms_output.put_line ('Last index: '| ''| VN );

Dbms_output.put_line ('first value after index 3 is deleted: '| ''| mytab (mytab. First ));
Dbms_output.put_line ('Last value: '| ''| mytab (mytab. Last ));
End;

Example: cyclically filling and accessing PL/SQL tables
Declare
Type mytabtype is table of varchar2 (10) index by binary_integer;
Mytab mytabtype;
Vn number (10 );
Begin
For vare in (select empno, ename from EMPA order by ename)
Loop
Mytab (vare. empno): = vare. ename;
End loop;
Vn: = mytab. first;
For I in 1 .. mytab. Count
Loop
Dbms_output.put_line (VN | ''| mytab (VN ));
Vn: = mytab. Next (VN );
End loop;
End;
 
Example: insert data to a PL/SQL table
Declare
Type mytabtype is table of varchar2 (10) index by binary_integer;
Mytab mytabtype;
Begin
Mytab (1): = 'a ';
Mytab (2): = 'B ';
Mytab (3): = 'C ';
Dbms_output.put_line ('index 3: '| ''| mytab (3 ));
Mytab (3): = 'D'; -- overwrite the column value B corresponding to 3 as the index value.
Dbms_output.put_line ('index 3: '| ''| mytab (3 ));
End;

Next article: Oracle series: Record + PL/SQL tables