Definition:
In database terminology, a RECORD is row in a table where each column contains a single value.
In Oracle PL/SQL, it is a set of logical attributes. Each attribute carries its own name and data type. In terms of behaviour, it is analogous to an object type in SQL whose scope and visibility is within a PL/SQL block. Note that it cannot be created or stored in database.
Example Syntax:
A PL/SQL record can be declared in two ways.
1. Declare it as a block variable using %ROWTYPE attribute using the following syntax:
[VARIABLE] TABLE%ROWTYPE
Using above declaration, the record will hold the same structure as the table record.
2. Declare record as a type. It defines the structure of its own. A variable must be declared of this type to use it in the block.
DECLARE
TYPE [NAME] IS RECORD
( attribute declaration [, attribute declaration] )
Example Usage:
The below PL/SQL block declares two PL/SQL records T_REC and L_TABREC. Note that T_REC is a type and owns its structure. L_TABREC uses %ROWTYPE notation to inherit structure of an EMPLOYEE record.
DECLARE
TYPE T_REC IS RECORD
(EMPID NUMBER, SAL NUMBER, ENAME VARCHAR2(100));
L_TREC T_REC;
L_TABREC EMPLOYEE%ROWTYPE;
BEGIN
SELECT EMPNO, SAL, ENAME
INTO L_TREC
FROM EMPLOYEE
WHERE EMPNO = 100;
SELECT *
INTO L_TABREC
FROM EMPLOYEE
WHERE EMPNO = 100;
END;
PL/SQL procedure successfully completed.
Related Code Snippets:
