Term: RECORD

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:

Eliminate duplicate records - Simply runs through the table and eliminates duplicate records. Very...
INSERT: Using A Record - Insert by Using RECORD Type Variable.
UPDATE: update a specific record - Updating a single record in a single table.
UPDATE: Update based on a record - Update a table based on the value or presence of a record.