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:
Pseudo records:
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