Oracle PL/SQL Records Type with Examples

Details

What is Record Type?

A Record type is a complex data type which allows the programmer to create a new data type with the desired column structure.

Syntax for declaration at the database level:

Complex Data Types in PL/SQL 

CREATE TYPE <type_name_db> IS RECORD
(
<column 1> <datatype>,
);

In the first syntax, we can see the keyword 'CREATE TYPE' this instructs the compiler to create the record type named "type_name_db" with the specified column as a database object.

This is given as an individual statement and not inside any block.

Syntax for declaration at subprogram level:

Complex Data Types in PL/SQL 

DECLARE
TYPE <type_name> IS RECORD
(
<columnl> <datatype>,
);
BEGIN
<execution_section>;
END;

In the syntax, we are creating the record type named "type_name" only inside the subprogram.

In both declaration method, the way of defining the column and data type is similar.

Example 1: RECORD Type as Database Object

In this program, we are going to see how to create "Record type" as a database object. We are going to create record type 'emp_det' with four columns. The columns and their data type are as follows: 

CREATE TYPE emp_det IS OBJECT
(
EMP_NO NUMBER,
EMP_NAME VARCHAR2(150),
MANAGER NUMBER,
SALARY NUMBER 
);
/
Output: 
Type created

Code Explanation:

Output:

Created the type 'emp_det' as record type at the database level.

Example 2: Record Type at Subprogram level- Column level access

In this example, we are going to see how to create a record type at subprogram level and how to populate and fetch the values from it by column level.

We are going to create 'emp_det' record_type at subprogram level, and we are going to use the same to populate and to display data from it.

Complex Data Types in PL/SQL 

DECLARE
TYPE emp_det IS RECORD
(
EMP_NO NUMBER, 
EMP_NAME VARCHAR2(150),
MANAGER NUMBER, 
SALARY NUMBER
);
gtupapers_emp_rec emp_det;
BEGIN
gtupapers_emp_rec.emp_no:= 1001;	
gtupapers_emp_rec.emp_name:=:'XXX';	
gtupapers_emp_rec.manager:= 1000;	
gtupapers_emp_rec.salary:=10000;	
dbms_output.put.line('Employee Detail');
dbms_output.put_line ('Employee Number: '||gtupapers_emp_rec.emp_no); 
dbms_output.put_line ('Employee Name: '||gtupapers_emp_rec.emp_name); 
dbms_output.put_line ('Employee Salary: ' ||gtupapers_emp_rec.salary); 
dbms_output.put_line ('Employee Manager Number: '||gtupapers_emp_rec.manager);
END;
/

Output:

Employee Detail
Employee Number: 1001
Employee Name: XXX
Employee Salary: 10000 
Employee Manager Number: 1000

Code Explanation:

Example 3: Record Type at Subprogram level-Row level access

In this example, we are going to see how to create a record type at subprogram level and how to populate it as a row level. We are going to create 'emp_det' record_type at subprogram level, and we are going to use the same to populate and to display data from it.

Complex Data Types in PL/SQL 

DECLARE
TYPE emp_det IS RECORD
(
EMP_NO NUMBER,
EMP_NAME YARCHAR2( 150),
MANAGER NUMBER,
SALARY NUMBER
);
gtupapers_emp_rec emp_det;
BEGIN
INSERT INTO emp (emp_no, emp_name, salary, manager) VALUES (1002,'YYY',15000,1000);
COMMIT;
SELECT emp_no, emp_name, salary, manager INTO gtupapers_emp_rec FROM emp WHERE emp_no=1002;
dbms_output.put_line (‘Employee Detail’);
dbms_output.put_line (‘Employee Number: '||gtupapers_emp_rec.emp_no); 
dbms_output.put_line (‘Employee Name: '||gtupapers_emp_rec.emp_name); 
dbms_output.put_line (‘Employee Salary: '||gtupapers_emp_rec. salary); 
dbms_output.put_line (‘Employee Manager Number: '||gtupapers_emp_rec.manager);
END;
/

Code Explanation:

Output: 

Employee Detail 
Employee Number: 1002 
Employee Name: YYY 
Employee Salary: 1000 
Employee Manager Number: 15000

Note: The record type can be accessed only in column level while redirecting its value to any output mode.

 

YOU MIGHT LIKE: