Everything about Oracle PLSQL records



Oracle PLSQL records

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.

 


Leave a Reply