PL/SQL Records
What are records?Records are another type of datatypes which oracle allows to be defined as a placeholder. Records are composite datatypes, which means it is a combination of different scalar datatypes like char, varchar, number etc. Each scalar data types in the record holds a value. A record can be visualized as a row of data. It can contain all the contents of a row.Declaring a record:To declare a record, you must first define a composite datatype; then declare a record for that type. |
|||
The General Syntax to define a composite datatype is:
There are different ways you can declare the datatype of the fields. 2) If a field is based on a column from database table, you can define the field_type as follows:
|
|||
NOTE: You can
use also %type to declare variables and
constants.
The General Syntax to declare a record of a uer-defined datatype is:
The General Syntax to declare a record of a uer-defined datatype is:
record_name record_type_name; |
DECLARE TYPE employee_type IS RECORD (employee_id number(5), employee_first_name varchar2(25), employee_last_name employee.last_name%type, employee_dept employee.dept%type); employee_salary employee.salary%type; employee_rec employee_type; |
record_name table_name%ROWTYPE; |
DECLARE employee_rec employee%ROWTYPE; |
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 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 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.
|
Passing Values To and From a Record
When you assign values to a record, you actually assign values to the fields within it.The General Syntax to assign a value to a column within a record direclty is:
record_name.col_name := value; |
record_name.column_name := value; |
SELECT col1, col2 INTO record_name.col_name1, record_name.col_name2 FROM table_name [WHERE clause]; |
SELECT * INTO record_name FROM table_name [WHERE clause]; |
The General Syntax to retrieve a value from a specific field into another variable is:
var_name := record_name.col_name; |
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. |