Defining and Declaring Records
To create records, you have to define a RECORD type, and then declare records of that type. You may also define RECORD types in the declarative part of any PL/SQL subprogram, block, or package using the syntax as shown:
TYPE type_name IS RECORD (field_declaration[,field_declaration]...);
where field_declaration stands for
field_name field_type [[NOT NULL] {:= | DEFAULT} expression]
And where the type_name is a type specifier used later to declare the records, field_ type is any PL/SQL datatype except REF CURSOR, and expression yields a value of the similar type as the field_type.
Note: Unlike the TABLE and VARRAY types, the RECORD types cannot be CREATED and stored in the database.
You can use %TYPE & %ROWTYPE to state the field types. In the illustration shown below, you define a RECORD type named DeptRec:
DECLARE
TYPE DeptRec IS RECORD (
dept_id dept.deptno%TYPE,
dept_name VARCHAR2(15),
dept_loc VARCHAR2(15));
Notice that the field declarations are just like the variable declarations. Each field has an exclusive name and specific datatype. Therefore, the value of a record is actually a collection of values, each of some of the simpler type.
As the illustration below shows, the PL/SQL defines records that contain collections, objects, and other records (known as the nested records). Though, the object types cannot have attributes of type RECORD.
DECLARE
TYPE TimeRec IS RECORD (
seconds SMALLINT,
minutes SMALLINT,
hours SMALLINT);
TYPE FlightRec IS RECORD (
flight_no INTEGER,
plane_id VARCHAR2(10),
captain Employee, -- declare object
passengers PassengerList, -- declare varray
depart_time TimeRec, -- declare nested record
airport_code VARCHAR2(10));
The illustration later shows that you can specify a RECORD type in the RETURN clause of a function specification. That permits the function to return a user-defined record of the same type.
DECLARE
TYPE EmpRec IS RECORD (
emp_id INTEGER
last_name VARCHAR2(15),
dept_num INTEGER(2),
job_title VARCHAR2(15),
salary REAL(7,2));
...
FUNCTION nth_highest_salary (n INTEGER) RETURN EmpRec IS...