Oracle PLSQL records
PLSQL records are composite datatypes .It is a group of related data items as fields ,each with its own name and datatype.
PL/SQL records are similar in structure to a row in a database table. Each scalar data types in the record holds a value.
A record consists of components of any scalar, PL/SQL record, or PL/SQL table type.
They can be manipulated as single unit .Records can have as many fields as required.
How to declare the Oracle PLSQL records
A)User Defined Record
| DECLARE TYPE employee_record_type IS RECORD (emp_id NUMBER(10) NOT NULL,dept_no dept.deptno%TYPE,nаme varchar2(20),hire_dаte DATE := SYSDATELast_name varchar2(20)); employee_record employee_record_type; |
1) There is no pre-defined datatypes for Record as there are for scalar datatype. So you need to create first and then assign
2) Each Field in Record has unique name and specific datatype
3) %TYPE is used to declare a variable that is of the same type as a specified table’s column.
4) we use the TYPE . . . RECORD statement to create your own record type is when a field of your record needs to be a PL/SQL-specific type, such as BOOLEAN. If you use %ROWTYPE, the datatypes of all the fields will be constrained to SQL types
5) Records are, themselves, PL/SQL-specific datatypes, so another nice feature of user-defined record types is that you can define a record type as a field in another record type
| DECLARE
TYPE emp_phone IS RECORD ( Stdcode PLS_INTEGER, phn_number PLS_INTEGER, extension PLS_INTEGER );
TYPE contact_phone IS RECORD ( day_phone# phone_rt, eve_phone# phone_rt, cell_phone# phone_rt ); sales_rep contact_phone; |
B) We can also define record as Table Rowtype
| DECLARE sales_data_rec apps.sales_data%ROWTYPE; |
1) This allow us to declare record based on the collection of column in table or view
2) We use %ROWTYPE prefix with the database table to declare it
3) Fields in the record take their name and datatype from column of the table
| Advantages | Disadvantage |
| 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.
|
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.
|
C) We can also use %ROWTYPE to declare a record that has the same structure as a SELECT statement in a cursor.
This is especially helpful for fetching either a subset of columns from a table or columns from multiple tables.
| DECLARE
CURSOR emp_cur IS SELECT lastname, age,firstname FROM employees;
l_employee emp_cur%ROWTYPE;
|
Whenever you are fetching data from a cursor into PL/SQL variables, you should declare a record based on that cursor with %ROWTYPE and fetch into that record. This way, when and if the SELECT list of the cursor changes, the number and type of fields in the record will change accordingly and everything will stay in sync.
How to Pass values to Oracle PLSQL records
| Syntax | Usage |
| rec_name.col_name := value; | To directly assign a value to a specific column of a record. |
| rec_name.column_name := value; | To directly assign a value to a specific column of a record, if the record is declared using %ROWTYPE. |
| SELECT column1, column2 INTO rec_name.col_name1, rec_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. |