Record and PL/SQL tables

Document directory

• Record and PL/SQL tables

Record and PL/SQL tables

1. What are record and PL/SQL tables?
 
Record: a temporary record object type consisting of a single row and multiple columns of scalar type. Similar to multi-dimensional arrays.
PL/SQL table: a temporary index table object type consisting of multiple index columns and available columns. Similar to one-dimensional arrays and key-value pairs.
 
All are user-defined data types.
 
Ii. What are the purposes of record + PL/SQL tables?
Record + PL/SQL tables can store data in multiple rows and multiple columns. In this way, we can use the record + PL/SQL table to encapsulate a temporary table object as needed for transfer and operation.
Use record to customize the table structure and encapsulate a record. The PL/SQL table declares that the available column type is record (pointing available columns to record type variables), and each index corresponds to a record type variable.
 
 
3. Use record + PL/SQL tables to store data in multiple rows and multiple columns
 ① Declare the record type and PL/SQL table,
The index column of the PL/SQL table is a primary key constraint and a unique constraint column or an auto-increment integer. The available columns are record type or % rowtype.
 
② Filling available columns in PL/SQL tables (record type): points to record through an index and uses record to access record members.
Syntax:
PL/SQL table name (index column value). Record Member: = record Member type value;
Or
PL/SQL table name (index column value): = record type variable;
-- Note that the available columns declared in the PL/SQL table must have the same structure as the record type variable assigned here.
 ③ Access the PL/SQL table
 
The following is an example:
/* Conn Scott/Tiger
Create Table EMPA as select * from EMP;
*/
 Example:
Declare
Type rectype is record
(
Rno EMPA. empno % type,
Rname EMPA. ename % type,
Rsal empa. Sal % Type
);
Type tabtype is table of rectype index by binary_integer;
Mytab tabtype;
Vn number;
Begin
-- Fill
Vn: = 1;
For varr in (select * from EMPA order by empno ASC)
Loop
Mytab (VN). rno: = varr. empno;
Mytab (VN). rname: = varr. ename;
Mytab (VN). RSAL: = varr. Sal;
Vn: = Vn + 1;
End loop;
-- Access
Vn: = mytab. first;
For varr in VN... mytab. Count
Loop
Dbms_output.put_line (VN | ''| mytab (VN ). rno | ''| mytab (VN ). rname | ''| mytab (VN ). RSAL );
Vn: = mytab. Next (VN );
End loop;
End;
 
Example: Filling PL/SQL tables with the record's available overall assignment feature
Declare
Type rectype is record
(
Rno EMPA. empno % type,
Rname EMPA. ename % type,
Rsal empa. Sal % Type
);
Type tabtype is table of rectype index by binary_integer;
Mytab tabtype;
Vn number;
Begin
-- Fill
Vn: = 1;
For varr in (select empno, ename, Sal from EMPA order by empno ASC)
Loop
Mytab (VN): = varr; -- Record overall assignment
Vn: = Vn + 1;
End loop;
-- Access
Vn: = mytab. first;
For varr in VN... mytab. Count
Loop
Dbms_output.put_line (VN | ''| mytab (VN ). rno | ''| mytab (VN ). rname | ''| mytab (VN ). RSAL );
Vn: = mytab. Next (VN );
End loop;
End;
 
Example: use primary key constraints and unique constraint columns as index columns (use empno In the EMP table as index columns)
And use the custom record type as the available Columns
Declare
Type rectype is record
(
Rno EMPA. empno % type,
Rname EMPA. ename % type,
Rsal empa. Sal % Type
);
Type tabtype is table of rectype index by binary_integer;
Mytab tabtype;
Vn EMPA. empno % type;
Begin
-- Fill
For varr in (select empno, ename, Sal from EMPA order by empno)
Loop
Mytab (varr. empno): = varr;
End loop;
-- Access
Vn: = mytab. first;
For varr in 1 .. mytab. Count
Loop
Dbms_output.put_line (mytab (VN). rno | ''| mytab (VN). rname |'' | mytab (VN). RSAL );
Vn: = mytab. Next (VN );
End loop;
End;
 
Example: use primary key constraints and unique constraint columns as index columns (use empno In the EMP table as index columns)
And use % rowtype as available Columns
Declare
Type tabtype is table of EMPA % rowtype index by binary_integer;
Mytab tabtype;
Vn EMPA. empno % type;
Begin
-- Fill
For varr in (select * from EMPA order by empno)
Loop
Mytab (varr. empno): = varr;
End loop;
-- Access
Vn: = mytab. first;
For varr in 1 .. mytab. Count
Loop
Dbms_output.put_line (mytab (VN). empno | ''| mytab (VN). ename |'' | mytab (VN). Sal );
Vn: = mytab. Next (VN );
End loop;
End;