Oracle PL/SQL composite data type

Composite data types can be broadly divided into two categories. One is the record type, which is suitable for processing single-line multi-column data, a bit similar to Vo in Java, and a collection type for processing multiple rows of data in a single column, similar to the list in Java, the following experiment is done under the 11.2.0.1.0 version.

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 record types
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& nbsp; : = COUN  + 1;
    dbms_output.put_line (' | | 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:
1th Cycle
Entries: 1---AAA
Cursors: 1---AAA
2nd cycle
Entries: 2---BBB
Cursors: 2---BBB
3rd Cycle
Entries: 3---CCC
Cursors: 3---CCC
4th cycle
Entries: 4---DDD
Cursors: 4---DDD
5th cycle
Entries: 5---eee
Cursors: 5---eee

--Implicitly define record types
Declare
T_record1 Test%rowtype;
Cursor C_row (v_id in varchar2) is a 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 (' RowType of table: ' | | t_record1.id| | ' ---' | | T_record1.name);
Dbms_output.put_line (' RowType of cursor: ' | | 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 of Rowtype:1---AAA
Rowtype:1 of Cursors---AAA
Cursors: 1---AAA
Table of Rowtype:2---BBB
Rowtype:2 of the cursor---BBB
Cursors: 2---BBB
Table of Rowtype:3---CCC
Rowtype:3 of Cursors---CCC
Cursors: 3---CCC

If you choose between explicit and implicit definition records, I tend to choose explicit definitions because it makes the logic clearer.

2. Collection type

--Index Table
Declare
Cursor Cur_test is a 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 to 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 (ten) from Test WHERE id=2;
Dbms_output.put_line (' Var_test1 (10): ' | | Var_test1. id| | ' ---' | | Var_test1 (Ten). Name);
End

Var_test1 (0): 1---AAA
Var_test1 (): 2---BBB

--Nested tables
DECLARE
TYPE T_test1 is TABLE of Test.id%type;
Var_test1 T_test1;
Begin
Var_test1: = T_test1 (n/a);
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 (ten) of Test.id%type;
Var_test1 T_test1;
Begin
Var_test1: = T_test1 (n/a);
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 to the number of elements in the Index table and nested table collections, and there is no limit in the Varray collection.
Index tables cannot be stored in the database, and nested tables and varray can be stored in the database.

Oracle PL/SQL composite data type