Records and Pseudo Records in PL/SQL

How about this.. you can create a composite data structure in pl/sql which can hold an entire row's data. Records are made up of one or more fields of same or different datatypes, same as a row in a table.

Example:

Consider this employees table-

CREATE TABLE employees(

empname VARCHAR2(20),
empid NUMBER PRIMARYKEY NOTNULL,
salary NUMBER,
deptid NUMBER,
date_of_joining DATE
);

You can create a record based on this table and fetch entire rows:

DECLARE
emp_rec employees%ROWTYPE;
BEGIN
SELECT * INTO emp_rec FROM employees
WHERE empid = 1001;
/*access one field from the record*/
DBMS_OUTPUT.PUT_LINE('Employee of the month:' || emp_rec.empname);
END;

You can also create a record which can hold only a few fields from the table as per your requirements:

DECLARE
TYPE emp_rec_type is RECORD(
emp_name employees.empname%TYPE,
emp_id employees.empid%TYPE
);
/*declare a record of this record type*/
emp_rec emp_rec_type;
BEGIN
SELECT empname, empid INTO emp_rec FROM employees
WHERE date_of_joining = '16-11-2012';
END;

Alternatively, you can create a record using %ROWTYPE attribute with a cursor:

DECLARE
CURSOR emp_cur_type IS
SELECT * FROM employees WHERE empid = 1001;
/*declare a record of the cursor type*/
emp_cur_rec emp_cur_type%ROWTYPE;
...
END;

Quick tips:

• You can copy a record to another.
• You can assign NULL to a record.
• You can pass a record as argument to a procedure/function.
• You can return a record through a function.
• You can't compare 2 records.
• You can insert an entire record as a row in a table at once.

Pseudo records:

• You can utilize the OLD and NEW pseudo records inside a trigger which are same as the records mentioned above and can be defined using any of the methods given above.
• They are special as OLD pseudo record holds the column values before start of current transaction and NEW pseudo record holds the column values about to reflect after the transaction completes.

Quick tip:

You should precede the OLD and NEW keywords with a colon when using inside the trigger body.

Example:

CREATE TRIGGER dept_change
AFTER UPDATE OF deptid
ON employees
WHEN (OLD.deptid = NEW.deptid)
BEGIN
DBMS_OUTPUT.PUT_LINE('Just so you know, old and new departments are same.');
END;

Disclaimer - Views expressed in this blog are author's own and do not necessarily represents the policies of aclnz.com