PL/SQL Records:Rowtype
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:
- You do not need to explicitly declare variables for all the columns in a table.
- 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:
- When u 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 data type 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 data type, similar to creating a variable. You need to assign values to the record to use them.
The following are consolidates the different ways in which you can define and declare a pl/sql record.
TYPE record_type_name IS RECORD (column_name1 data type, column_name2 data type, …);
Define a composite data type, where each field is scalar
col_name table_name.column_name%type;
Dynamically define the data type 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.
