PL/SQL - (Nested) Record

About

Records are simply a row representation in PL/SQL.

Records (as rows) contain uniquely named fields (column name for row), which can have different datatypes whereas collection contains elements of the same data type.

Suppose you have various data about an employee such as name, salary, and hire date. These items are dissimilar in type but logically related. A record containing a field for each item lets you treat the data as a logical unit.

Advantage/Restriction

You can define records that contain:

objects,
collections,
and other records (called nested records).

However, records cannot be attributes of object types.

Syntax

Best:

Use <cursor>%ROWTYPE instead of <table>%ROWTYPE because you only want some columns.
use %ROWTYPE instead of specifying each column.
Use %TYPE instead of defining your datatype

Row-defined

You can use the %ROWTYPE attribute to declare a record that represents:

a row fetched from a cursor.
or a row in a table

Cursor

Declaring cursor doesn't run query or affect performance.

CURSOR c1 IS
     SELECT department_id, department_name, location_id
     FROM departments;
rec1 c1%ROWTYPE;

(Table|View)

To declare a record that represents a row in a database table or a view, without listing the columns, use the %ROWTYPE attribute

myTable%ROWTYPE;
myView%ROWTYPE;

User-defined

With a user-defined record, you can declare fields of your own.

Column Datatype

Use ''<column>%TYPE'' in field declarations to avoid problems if the column types change.

You can use %TYPE to specify a field type corresponding to a table column type. Your code keeps working even if the column type is changed (for example, to increase the length of a VARCHAR2 or the precision of a NUMBER).

TYPE DeptRec2 IS RECORD
     (dept_id   departments.department_id%TYPE,
      dept_name departments.department_name%TYPE,
      dept_loc  departments.location_id%TYPE);
rec2 DeptRec2;

Specific Datatype

Write each field name, specifying type directly (clumsy and unmaintainable for working with table data use only for all-PL/SQL code).

TYPE DeptRec3 IS RECORD (dept_id NUMBER,
                         dept_name VARCHAR2(14),
                         dept_loc VARCHAR2(13));
rec3 DeptRec3;

Management

Initialization

Single

Assigning an empty record to rec1 resets fields to their default values.

DECLARE
   TYPE RecordTyp IS RECORD (field1 NUMBER, 
                             field2 VARCHAR2(32) DEFAULT 'something');
   rec1 RecordTyp;
   rec2 RecordTyp;
BEGIN
-- At first, rec1 has the values you assign.
   rec1.field1 := 100; 
   rec1.field2 := 'something else';

-- Assigning an empty record to rec1
-- resets fields to their default values.
-- Field1 is NULL and field2 is 'something'
-- due to the DEFAULT clause
   rec1 := rec2;
   DBMS_OUTPUT.PUT_LINE
     ('Field1 = ' || NVL(TO_CHAR(rec1.field1),'<NULL>') || ', field2 = ' || rec1.field2);
END;
/

Output:

Field1 = <NULL>, field2 = something

SELECT INTO

Specific datatype

DECLARE
   TYPE RecordTyp IS RECORD (last employees.last_name%TYPE, 
                             id employees.employee_id%TYPE);
   rec1 RecordTyp;
BEGIN
  SELECT last_name, employee_id INTO rec1
     FROM employees WHERE ROWNUM < 2;
     WHERE ROWNUM < 2;
   DBMS_OUTPUT.PUT_LINE
     ('Employee #' || rec1.id || ' = ' || rec1.last);
END;
/

With %ROWTYPE

DECLARE
  dept_info departments%ROWTYPE;
BEGIN
  -- department_id, department_name, and location_id
  -- are the table columns
  -- The record picks up these names from the %ROWTYPE
     dept_info.department_id := 300;
     dept_info.department_name := 'Personnel';
     dept_info.location_id := 1700;
  -- Using the %ROWTYPE means you can leave out the column list
  -- (department_id, department_name, and location_id)
  -- from the INSERT statement
  INSERT INTO departments VALUES dept_info;
END;
/

Fetch

DECLARE
   -- The record definition
   TYPE EmpRecTyp IS RECORD (
     emp_id       NUMBER(6),
     salary       NUMBER(8,2));
 
   -- The cursor supporting the record definition
   CURSOR desc_salary RETURN EmpRecTyp IS
      SELECT emp_id, salary
      FROM employees
      ORDER BY salary DESC;
 
   -- The record variable
   emp_rec     EmpRecTyp;
 
   -- The function
   FUNCTION nth_highest_salary (n INTEGER) RETURN EmpRecTyp IS
   BEGIN
      OPEN desc_salary;
      FOR i IN 1..n LOOP
         FETCH desc_salary INTO emp_rec;
      END LOOP;
      CLOSE desc_salary;
      RETURN emp_rec;
   END nth_highest_salary;
 
BEGIN
   emp_rec := nth_highest_salary(1);
   DBMS_OUTPUT.PUT_LINE('First Salary of ' || emp_rec.salary || ' is for the employee (' || emp_rec.emp_id || ')');
   emp_rec := nth_highest_salary(5);
   DBMS_OUTPUT.PUT_LINE('Fifth Salary of ' || emp_rec.salary || ' is for the employee (' || emp_rec.emp_id || ')');
END;
/

Parameter

Input

DECLARE
   TYPE EmpRecTyp IS RECORD (
      emp_id       NUMBER(6),
      emp_sal      NUMBER(8,2) );
   PROCEDURE raise_salary (emp_info EmpRecTyp) IS
   BEGIN
      UPDATE employees SET salary = salary + salary * .10
             WHERE employee_id = emp_info.emp_id;
   END raise_salary;
BEGIN
   NULL;
END;
/

Return

-- The function
FUNCTION nth_highest_salary (n INTEGER) RETURN EmpRecTyp IS
BEGIN
  OPEN desc_salary;
  FOR i IN 1..n LOOP
	 FETCH desc_salary INTO emp_rec;
  END LOOP;
  CLOSE desc_salary;
  RETURN emp_rec;
END nth_highest_salary;

DML

Insert

DECLARE
  dept_info departments%ROWTYPE;
BEGIN
  -- department_id, department_name, and location_id
  -- are the table columns
  -- The record picks up these names from the %ROWTYPE
     dept_info.department_id := 300;
     dept_info.department_name := 'Personnel';
     dept_info.location_id := 1700;
  -- Using the %ROWTYPE means you can leave out the column list
  -- (department_id, department_name, and location_id)
  -- from the INSERT statement
  INSERT INTO departments VALUES dept_info;
END;
/

Update

DECLARE
   dept_info departments%ROWTYPE;
BEGIN
-- department_id, department_name, and location_id
-- are the table columns
-- The record picks up these names from the %ROWTYPE.
  dept_info.department_id := 300;
  dept_info.department_name := 'Personnel';
  dept_info.location_id := 1700;
-- The fields of a %ROWTYPE
-- can completely replace the table columns
-- The row will have values for the filled-in columns, and null
-- for any other columns
   UPDATE departments SET ROW = dept_info WHERE department_id = 300;
END;
/

where:

The keyword ROW represents an entire row,

Returning

The INSERT, UPDATE, and DELETE statements can include a RETURNING clause, which returns column values from the affected row into a PL/SQL record variable. This eliminates the need to SELECT the row after an insert or update, or before a delete.

By default, you can use this clause only when operating on exactly one row. When you use bulk SQL, you can use the form RETURNING BULK COLLECT INTO to store the results in one or more collections. See PL/SQL - Bulk Collect - Fetch collection of (records|Collection)

DECLARE
   TYPE EmpRec IS RECORD (last_name  employees.last_name%TYPE, 
                          salary     employees.salary%TYPE);
   emp_info EmpRec;
   emp_id   NUMBER := 100;
BEGIN
   UPDATE employees SET salary = salary * 1.1
     WHERE employee_id = emp_id
     RETURNING last_name, salary INTO emp_info;
   DBMS_OUTPUT.PUT_LINE
     ('Just gave a raise to ' || emp_info.last_name ||
      ', who now makes ' || emp_info.salary);
   ROLLBACK;
END;
/

¹⁾

Defining and Declaring Records