What is PLSQL records in Oracle

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 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#    emp_phone,
 eve_phone#    emp_phone,
 cell_phone#   emp_phone
 );
 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

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.