PL/SQL Record Type - a composite data structure

In previous post we discussed about PL/SQL Collections (Associative array, VARRAY and Nested Tables) and main agenda of this post is to discuss another composite data structure - Record type in PL/SQL. Record type finds extensive uses when dealing with cursor variable ,while fetching data (rows) from result set.

Record Type - Table-based, Cursor based and programmer defined

A record is a composite data structure composed of more than one atomic elements/fields and each with its own value.In PL/SQL, Record type is local type(declared in PL/SQL block or in package). Records in PL/SQL are very similar in concept and structure to the rows of a database table. Each filed of record can be accessed by name.

Different types of PL/SQL Record :-

We have three different types of records.
1. Table based -  A record based on structure of columns of database tables. (TABLE%ROWTYPE)
2.Cursor based  - A record based on the cursor's SELECT statement.(CURSOR%ROWTYPE)
3. Programmer/Used defined records - Programmer defines structure of record.
Lets see each of them one by one and understand how it is used in PL/SQL.

Table based record :- A record based on table type(termed as table record) is created using %ROWTYPE attribute of table. general syntax is as follows:

--rcord_name is a valid identifier, table_name is database table 
<record_name> <table_name>%ROWTYPE;

Note:- %TYPE provides structure of a column of table, where as %ROWTYPE provides the datatypes of each of the columns in a table for the record's fields. Following code declares a table record based on LOCATIONS table.

DECLARE
  --TABLE BASED RECORD 
  V_LOC_REC  LOCATIONS%ROWTYPE;
BEGIN
   SELECT * into V_LOC_REC FROM LOCATIONS WHERE POSTAL_CODE = '500081';
   DBMS_OUTPUT.PUT_LINE(V_LOC_REC.STREET_ADDRESS  || '  ' 
      || V_LOC_REC.STATE_PROVINCE);
END;

Cursor based record :-  A record whose structure are drawn from the SELECT list of a cursor is termed as cursor record. General syntax of  cursor based record is :

/*Create a cursor*/
CURSOR cursor_name IS <SELECT_STMT_with_fields_retrival>;
/*Create a record based cursor- record name is a valid identifier*/
<record_name> <cursor_name>%ROWTYPE;

Cursor based record is more flexible in terms of giving proper  names for fields in cursor. It means, we can assign proper name to record fields other than table column name.(It is not possible in table based record). However, more code lines over head required - create cursor and alias the column.Following example creates an cursor based record and display record details.

--cursor based record
DECLARE
--Declare an parameterized explicit cursor emp_cur 
CURSOR emp_cur(emp_id NUMBER) IS SELECT FIRST_NAME, EMAIL FROM HR.EMPLOYEES
   WHERE EMPLOYEE_ID = emp_id;
emp_rec emp_cur%ROWTYPE;
BEGIN
 -- Open cursor by passing a parameter
  OPEN emp_cur(7);
 --Fetch record and pass it to record of cursor type
  FETCH emp_cur INTO emp_rec;
      IF emp_rec.FIRST_NAME IS NOT NULL THEN  
        DBMS_OUTPUT.PUT_LINE(emp_rec.FIRST_NAME  || ' and ' || emp_rec.EMAIL);
      else 
        DBMS_OUTPUT.PUT_LINE(' NO record found!!!' );
    END IF;
    CLOSE emp_cur;
END;

Programmer/user defined record :-  PL/SQL offers programmer to create their own record structure(complete control over - number, names, and datatypes of fields in the record.) and it is handy, when we deal with multiple tables and views.Below is the general syntax of creating programmer record: 

TYPE <type_name> IS RECORD
  (<field_name> <datatype>,
   <field_name> <datatype>,
    ...
  <field_name> <datatype>
);

Data type of above record declaration includes primitive types,declaration using %TYPE and %ROWTYPE attributes.Consider following example which creates a record based on two different tables and display fields value.

DECLARE
TYPE emp_compny_record_type IS RECORD(
EMP_NAME varchar(50), 
DEPARTMENT_ID VARCHAR(34), 
EMAIL employees.email%TYPE,
MANAGER_ID EMPLOYEES.MANAGER_ID%TYPE
);
emp_compny_rec emp_compny_record_type;
BEGIN
SELECT first_name||' '||last_name "Name", DEPARTMENT_ID,EMAIL, MANAGER_ID
   INTO emp_compny_rec FROM employees 
   WHERE EMPLOYEE_ID = 7;
DBMS_OUTPUT.put_line('NAME AND EMAIL OF EMPLOYEE is: ' || emp_compny_rec.EMP_NAME
 || ' and ' || emp_compny_rec.EMAIL||'@devinline.com');
END;

Note:- 

1. A RECORD type defined in a PL/SQL block is a local type.A RECORD type defined in a package specification is a public item, can be accessed in PL/SQL context with pkgname[.]record_name.
2. A RECORD type defined in a package specification is incompatible with an identically defined local RECORD type. i.e: Package level record type cannot be same as local record even if name is name.  
3. A RECORD type cannot be created at schema level. Record type is not standalone type.
4. A record can be part of another record that is termed as nested record. Following code sahow how it can be declared and used:

   DECLARE
   TYPE location_rec_type IS RECORD(
     LOCATION_ID NUMBER(9),
     ORGANIZATION_NAME VARCHAR(23)
   );
   TYPE emp_compny_record_type IS RECORD(
   EMP_NAME varchar(50), 
   DEPARTMENT_ID VARCHAR(34), 
   EMAIL employees.email%TYPE,
   MANAGER_ID EMPLOYEES.MANAGER_ID%TYPE,
   location_rec location_rec_type --Nested record 
   );
   emp_compny_record emp_compny_record_type;
   BEGIN
   --access nested record like this 
   emp_compny_record.location_rec.LOCATION_ID := 123;
   emp_compny_record.location_rec.ORGANIZATION_NAME := 'ABC';
   DBMS_OUTPUT.put_line('Nested record LOCATION_ID and ORGANIZATION_NAME  ' 
           || emp_compny_record.location_rec.LOCATION_ID 
           ||'and'||emp_compny_record.location_rec.ORGANIZATION_NAME);
   END;
   

5. Record is only accessible in PL/SQL context, not in SQL context except when INTO clause is used with select statement.The INTO clause of an implicit query is the only part of a SQL DML statement in which a PL/SQL record can be referenced.

   -- emp_rec is PL/SQL record
   emp_rec employees%ROWTYPE
   SELECT *  INTO emp_rec FROM EMPLOYEES; 
   

6. Default values for individual fields in a record can be assigned with the DEFAULT or := syntax.

   TYPE emp_rec_type IS RECORD (
       ORG_NAME  employees.orgname%TYPE DEFAULT 'CMK',
    Address  employees.address%TYPE := 'XYZ, #306'
     );
   

7.  Record be NOT NULL (in which case you must also assign a default value).
8. NULL assignment is allowed for record, all fields reset to NULL.(valid from oracle 7.3)

Values assignment to and from Record 

There are four ways to alter record(modify values of  fields) in PL/SQL. Consider following record for  further description.

1. Direct field assignment with the assignment operator :- Using assignment operator (:= ) values of each filed can be modified. Refer case 1 in following code lines.
2. SELECT INTO from an implicit cursor:- INTO clause of select statement passes query result into record. Query result and record should be structurally similar.Refer case 2 in following code line. 
3. FETCH INTO from an explicit cursor:- Explicit cursor uses FETCH to populate record and  it can be done in two ways - directly fetch and assign to record  or assign value to each field of record. Refer below code line case 3.
4. Aggregate assignment:- Values of one record can be assigned to another in one shot, no explicit for each field mapping(provided structure of both records are same).

Following sample code shows all above four ways to assign/alter record values:-

DECLARE
TYPE emp_compny_record_type IS RECORD(
  FIRST_NAME employees.FIRST_NAME%TYPE, 
  EMAIL employees.email%TYPE
);
 emp_record emp_compny_record_type;
 emp_record_duplicate emp_compny_record_type;
 CURSOR emp_cur(emp_id NUMBER) IS SELECT FIRST_NAME, EMAIL FROM HR.EMPLOYEES
   WHERE EMPLOYEE_ID = emp_id;  
BEGIN
  /*case 1-Direct field assignment */
  emp_record.FIRST_NAME := 'NIKHIL';
  emp_record.EMAIL := 'DUMMY@devinline.com';
  
  /*case 2 - SELECT INTO from an implicit cursor*/
  SELECT FIRST_NAME,EMAIL INTO emp_record from HR.EMPLOYEES where EMPLOYEE_ID = 7; 
  
  /*case 3 - FETCH INTO from an explicit cursor*/
  OPEN emp_cur(7);
    FETCH emp_cur INTO emp_record;
    --OR
    FETCH emp_cur
      INTO emp_record.FIRST_NAME,
           emp_record.EMAIL;
  
  /*case 4 - Aggregate assignment - no field by field mapping*/
  emp_record_duplicate := emp_record;
  DBMS_OUTPUT.put_line('Duplicate record FIRST_NAME and EMAIL '||
      emp_record_duplicate.FIRST_NAME || ' and ' ||emp_record_duplicate.EMAIL); 
  
END;

This is all about record type in PL/SQL. In next post we will learn about reference type in PL/SQL-   PL/SQL cursors.

Previous: PL/SQL Collections(Array, VARRAY and Nested Tables) Next: PL/SQL Implicit and Explicit cursor