If all the fields of a record are based on the columns of a table, we can declare the record as follows:
record_name table_name%ROWTYPE;
For example, the above declaration of employee_rec can as follows:
DECLARE
employee_rec employee%ROWTYPE;
The advantages of declaring the record as a ROWTYPE are:
1) You do not need to explicitly declare variables for all the columns in a table.
2) If you alter the column specification in the database table, you do not need to update the code.
The disadvantage of declaring the record as a ROWTYPE is:
1) When you create a record as a ROWTYPE, fields will be created for all the columns in the table and memory will be used to create the datatype for all the fields.
So use ROWTYPE only when you are using all the columns of the table in the program.
NOTE: When you are creating a record, you are just creating a datatype, similar to creating a variable.
You need to assign values to the record to use them.
The following table consolidates the different ways in which you can define and declare a pl/sql record.
Syntax
Usage
TYPE record_type_name IS RECORD ( column_name1 datatype, column_name2 datatype, ... );
Define a composite datatype, where each field is scalar.
col_name table_name.column_name%type;
Dynamically define the datatype of a column based on a database column.
record_name record_type_name;
Declare a record based on a user-defined type.
record_name table_name%ROWTYPE;
Dynamically declare a record based on an entire row of a table. Each column in the table corresponds to a field in the record.
Assigning Values
When you assign values to a record, you actually assign values to the fields within it.
Method 1 - Direct Assignment
The General Syntax to assign a value to a column within a record direclty is:
record_name.col_name := value;
Method 2 - Value Assignment through SELECT
We can assign values to records using SELECT Statements as shown:
SELECT col1, col2
INTO record_name.col_name1, record_name.col_name2
FROM table_name
[WHERE clause];
If %ROWTYPE is used to declare a record then you can directly assign values to the whole record instead of each columns separately.
In this case, you must SELECT all the columns from the table into the record as shown:
SELECT * INTO record_name
FROM table_name
[WHERE clause];
Getting values from a record
Lets see how we can get values from a record.
The General Syntax to retrieve a value from a specific field into another variable is:
var_name := record_name.col_name;
The following table consolidates the different ways you can assign values to and from a record:
Syntax
Usage
record_name.col_name := value;
To directly assign a value to a specific column of a record.
record_name.column_name := value;
To directly assign a value to a specific column of a record, if the record is declared using %ROWTYPE.
SELECT col1, col2 INTO record_name.col_name1, record_name.col_name2 FROM table_name [WHERE clause];
To assign values to each field of a record from the database table.
SELECT * INTO record_name FROM table_name [WHERE clause];
To assign a value to all fields in the record from a database table.
variable_name := record_name.col_name;
To get a value from a record column and assigning it to a variable.