Composite Datatypes

Composite Datatypes

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

Concepts
PL/SQL Records

Using %ROWTYPE

PL/SQL Tables

Concepts

Oracle allows you to define several kinds of composite datatypes, also knows as collections, including RECORD, PL/SQL TABLE, Nested Table, and VARRAY.
A composite datatype is different from a scalar datatype in that a composite datatype has an internal structure.
We will over PL/SQL records and PL/SQL Tables

PL/SQL Records

A record is a group of related data items stored in fields, each with its own name and datatype.
The structure of a record can be declared explicitly, or declared based on a cursor or database table.
Creating a user defined record is a two step process, you first declare a record type and the declare a record based on that type.

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;

There is a small overhead (performance penalty) when using the %ROWTYPE attribute with anonymous PL/SQL blocks.
The values within a record type can be assigned or referenced like so

record_name.field_name

emp_record.ename:='GATES';

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

Identical record types can be assigned to each other directly.

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;
/

Note that a user defined record and a record anchored to a database table may have a compatible datatype, if they match in number, name, and datatype of fields. SInce they have compatible datatypes, the values can be directly assigned from one row to another.

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

PL/SQL tables are:
- One dimensional.
- Unbounded.
- Sparse.
- Homogenous.
- Indexed by integer.
PL/SQL Tables are quite different from database tables
- PL/SQL do not recognize any SQL statements: PL/SQL are referenced a-row-at-a-time not a-set-at-a-time as in SQL.
- PL/SQL tables do not participate in transactions.
- Database tables exist on disk; PL/SQL tables exist in memory. Therefore accessing information in PL/SQL tables can be much faster than in database 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)

An example using some of the built in methods:

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;
/

You can create a PL/SQL table of records like so:

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;
/