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 PLSQL 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 := SYSDATE Last_name varchar2(20) ); employee_record employee_record_type;
(1) There are no pre-defined datatypes for Record as there are for the scalar datatype. So you need to create first and then assign
(2) Each Field in Record has a 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# emp_phone, eve_phone# emp_phone, cell_phone# emp_phone ); sales_rep contact_phone;
B) We can also define the record as Table Row type
| DECLARE sales_data_rec apps.sales_data%ROWTYPE; |
1) This allow us to declare a record based on the collection of the column in the table or view
2) We use the %ROWTYPE prefix with the database table to declare it
3) Fields in the record take their name and datatype from the column of the table
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 it 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. |
You must read these other PLSQL posts
Oracle PLSQL Block Structure and Types
Oracle plsql interview questions
Quick Oracle sql and plsql tutorials