OraclePL/SQL Composite data type

OraclePL/SQL Composite data type

Composite data types can be roughly divided into two types. The first type is record type, which is suitable for processing single row and multiple columns of data, a bit similar to VO in java; the first type is set type, suitable for processing Single Column and multiple rows of data, similar to List in java, the following experiment is performed in version 11.2.0.1.0.

1. Record type

Drop table test purge;

Create table test
(
Id number (2 ),
Name varchar2 (60)
);
Insert into test values (1, 'aaa ');
Insert into test values (2, 'bbb ');
Insert into test values (3, 'ccc ');
Insert into test values (4, 'ddd ');
Insert into test values (5, 'Eee ');

Commit;

-- Explicitly define the record type
Declare
Type t_record is record
(
Id test. id % type,
Name test. name % type
);
Var_record t_record;
Coun number: = 0;
Begin
For c_row in (select id, name from test) loop
Coun: = coun + 1;
Dbms_output.put_line ('dide' | coun | 'cycle ');
Var_record.id: = c_row.id;
Var_record.name: = c_row.name;
Dbms_output.put_line ('record: '| var_record.id |' --- '| var_record.name );
Dbms_output.put_line ('cursor: '| c_row.id |' --- '| c_row.name );
End loop;
Exception when others then
Dbms_output.put_line (sqlcode | sqlerrm );
End;
/

Output result:
1st cycles
Record: 1 --- aaa
Cursor: 1 --- aaa
2nd cycles
Record: 2 --- bbb
Cursor: 2 --- bbb
3rd cycles
Record: 3 --- ccc
Cursor: 3 --- ccc
4th cycles
Record: 4 --- ddd
Cursor: 4 --- ddd
5th cycles
Record: 5 --- eee
Cursor: 5 --- eee

-- Implicitly defines the record type
Declare
T_record1 test % rowtype;
Cursor c_row (v_id in varchar2) is select id, name from test where id <= v_id;
T_record2 c_row % rowtype;
Begin
For row_test in c_row (3) loop
T_record1.id: = row_test.id;
T_record1.name: = row_test.name;
T_record2.id: = row_test.id;
T_record2.name: = row_test.name;
Dbms_output.put_line ('table rowtype: '| t_record1.id |' --- '| t_record1.name );
Dbms_output.put_line ('rowtype: '| t_record2.id |' --- '| t_record2.name );
Dbms_output.put_line ('cursor: '| row_test.id |' --- '| row_test.name );
End loop;
Exception when others then
Dbms_output.put_line (sqlcode | sqlerrm );
End;
/

Output result:

Table rowtype: 1 --- aaa
Rowtype of the cursor: 1 --- aaa
Cursor: 1 --- aaa
Rowtype of the Table: 2 --- bbb
Rowtype of the cursor: 2 --- bbb
Cursor: 2 --- bbb
Table rowtype: 3 --- ccc
Rowtype of the cursor: 3 --- ccc
Cursor: 3 --- ccc

If you select an explicit or implicit definition record, I prefer to select an explicit definition because it makes the logic clearer.

2. Set Type

-- Index table
Declare
Cursor cur_test is select id, name from test;
Type t_test1 is table of test % rowtype index by binary_integer;
Var_test1 t_test1;
Begin
SELECT id, name INTO var_test1 (0) FROM test WHERE id = 1;
Dbms_output.put_line ('var _ test1 (0): '| var_test1 (0). id |' --- '| var_test1 (0). name );
SELECT id, name INTO var_test1 (10) FROM test WHERE id = 2;
Dbms_output.put_line ('var _ test1 (10): '| var_test1 (10). id |' --- '| var_test1 (10). name );
End;

Var_test1 (0): 1 --- aaa
Var_test1 (10): 2 --- bbb

-- Nested table
DECLARE
TYPE t_test1 is table of test. id % TYPE;
Var_test1 t_test1;
Begin
Var_test1: = t_test1 (1, 2, 3 );
Dbms_output.put_line ('var _ test1: '| var_test1 (1) |', '| var_test1 (2) |', '| var_test1 (3 ));
End;

Var_test1: 1, 2, 3

-- Varray table
DECLARE
TYPE t_test1 is varray (10) OF test. id % TYPE;
Var_test1 t_test1;
Begin
Var_test1: = t_test1 (1, 2, 3 );
Dbms_output.put_line ('var _ test1: '| var_test1 (1) |', '| var_test1 (2) |', '| var_test1 (3 ));
End;
Var_test1: 1, 2, 3

There is no limit on the number of elements in the index table and nested table set, and there is no limit in the varray set.
Index tables cannot be stored in databases, and nested tables and varray can be stored in databases.