PL/SQL Records

PL/SQL record is a group of fields, each with own name and data type, similar to the columns in a table row. You can use a record variable to store a table row, or some columns from a table row.

The %ROWTYPE attribute lets you declare a PL/SQL record that represents a row in a table, without listing all the columns. You can also declare a cursor to select specific columns, and then apply %ROWTYPE to the cursor.

Before you can use a record variable, you need to define RECORD type and then declare a variable of that type. You can specify a NOT NULL constraint or default values. You can use %TYPE to specify a field type corresponding to a table column type.

TYPE empRecType IS RECORD ( 
    id NUMBER(4),
    name VARCHAR2(100),
    dept_id NUMBER(4)
);
emp_rec empRecTyp; 

You do not need to define a RECORD type if you create a record variable using %ROWTYPE% attribute

emp_rec employees%ROWTYPE%;

You can assign a value to a record field using an assignment statement with dot notation:

emp_rec.name := 'John';

SQLWays Product Page - Migration to Oracle Free Evaluation