Composite Datatypes 

Introduction to Oracle SQL & PL/SQL
Updated 11/5/2001

Concepts

  PL/SQL Records

DECLARE 
--Create a record type. Note similarity to table creation syntax.
TYPE emp_record_type IS RECORD
empno NUMBER(10),
ename VARCHAR2(10),
job VARCHAR2(9),
sal NUMBER(8,2));

--Create an record named EMP_RECORD based on the structure above
emp_record emp_record_type;
BEGIN
...
END;
 
DECLARE 
--Create a record type. Note similarity to table creation syntax.
TYPE emp_record_type IS RECORD
empno NUMBER(10) NOT NULL:=0, --NOT NULL fields must be initialized
ename VARCHAR2(10),
job VARCHAR2(9):='CLERK',
sal NUMBER(8,2));
--Create an record named EMP_RECORD based on the structure above
emp_record emp_record_type;
BEGIN
...
END;

   Using %ROWTYPE 

DECLARE
dept_record dept%ROWTYPE;
/* The dept record will have the following structure:
* DEPTNO, NUMBER(2)
* DNAME, VARCHAR2(14)
* LOC, VARCHAR2(13)
*/
BEGIN
...
END;

record_name.field_name

emp_record.ename:='GATES';

v_header:=emp_record.ename || ' works in '||dept_record.loc;

DECLARE

dept_record1 dept%ROWTYPE;
--base this record structure on the first record structure
dept_record2 dept_record1%ROWTYPE;
CURSOR dept_cursor IS
SELECT *
FROM dept;
BEGIN
OPEN dept_cursor;
FETCH dept_cursor INTO dept_record1;
CLOSE dept_cursor;
dept_record2:=dept_record1; --assign from one record to the other.
DBMS_OUTPUT.PUT_LINE(dept_record2.dname);
END;
/

DECLARE
dept_record1 dept%ROWTYPE;
TYPE dept_record_type IS RECORD (
deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13));
dept_record2 dept_record_type;
CURSOR dept_cursor IS
SELECT *
FROM dept;
BEGIN
OPEN dept_cursor;
FETCH dept_cursor INTO dept_record1;
CLOSE dept_cursor;
dept_record2:=dept_record1; --assign from one record to the other.
DBMS_OUTPUT.PUT_LINE(dept_record2.dname);
END;
/

  PL/SQL Tables

  Creating a PL/SQL table

Creating a PL/SQL table is a two step process, first you declare the table type, and then declare an identifier using that type.

DECLARE
TYPE ename_table_type IS
--This defines the datatype and size of the column. In this case its anchored to ENAME.
TABLE OF emp.ename%TYPE
--Each row in a PL/SQL table is identified by a BINARY_INTEGER.
INDEX BY BINARY_INTEGER;
--This creates a table named ename_table with the structure defined above.
ename_table ename_table_type;
BEGIN
...
END;
/

   Using a PL/SQL table

You can assign and reference elements in a PL/SQL table using an integer to identify the specific row.

ename_table(1):='My name';

v_grade_report:='Report for '||ename_table(10);

You can navigate a PL/SQL table with the following methods

PL/SQL Table Methods

table_name.EXISTS(n)

table_name.COUNT

table_name.FIRST / table_name.LAST

table_name.PRIOR(n)

table_name.NEXT(n)

DECLARE
TYPE ename_table_type IS TABLE OF emp.ename%TYPE
INDEX BY BINARY_INTEGER;
ename_table ename_table_type;
v_index BINARY_INTEGER:=1;

BEGIN
FOR emp_record IN (SELECT * FROM emp) LOOP
ename_table(v_index):=emp_record.ename;
v_index:=v_index+1;
END LOOP;
v_index:=ename_table.FIRST;
WHILE ename_table.EXISTS(v_index) LOOP
DBMS_OUTPUT.PUT_LINE('ename_table('||v_index||') '||ename_table(v_index));
v_index:=ename_table.NEXT(v_index);
END LOOP;
END;
/

DECLARE
TYPE ename_table_type IS
TABLE OF emp%ROWTYPE --PL/SQL column is a record based on the EMP table.
INDEX BY BINARY_INTEGER;
ename_table ename_table_type;
v_index BINARY_INTEGER:=1;

BEGIN
FOR emp_record IN (SELECT * FROM emp) LOOP
ename_table(v_index):=emp_record;
/* Of course, you could also assign values like so:
* ename_table(v_index).ename:=emp_record.ename;
* ename_table(v_index).job:=emp_record.job;
*/
v_index:=v_index+1;
END LOOP;
v_index:=ename_table.FIRST;
WHILE ename_table.EXISTS(v_index) LOOP
DBMS_OUTPUT.PUT_LINE('ename_table('||v_index||') '
||ename_table(v_index).ename||' is a '||ename_table(v_index).job);
v_index:=ename_table.NEXT(v_index);
END LOOP;
END;
/