Oracle PL/SQL Records Type with Examples

What is a Record Type?

A record type is a complex data type that allows the programmer to create a new data type with the desired column structure. Its main characteristics are:

• It groups one or more columns to form a new data type.
• Each of these columns has its own name and data type.
• A record type can accept the data as a single record consisting of many columns, or it can accept the value for one particular column of a record.
• A record type is simply a new data type. Once created, it is stored as a new data type and can be used to declare a variable in programs.
• It uses the keyword ‘TYPE’ to instruct the compiler that a new data type is being created.
• It can be created at the database level, stored as a database object and used across the database, or at the subprogram level, visible only inside the subprogram.
• A database-level record type can also be declared for table columns, so that a single column can hold complex data.
• The data is accessed by referring to the variable name, then a period operator (.), then the column name, as ‘<record_type_variable_name>.<column_name>’.

Syntax to Declare a Record Type

Syntax for declaration at the database level:

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

In the first syntax, the keyword ‘CREATE TYPE’ instructs the compiler to create the record type named “type_name_db” with the specified columns as a database object. This is given as an individual statement, not inside any block.

Syntax for declaration at the subprogram level:

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

In this syntax, we create the record type named “type_name” only inside the subprogram. In both methods, the way of defining the column and data type is similar.

Example 1: Record Type as a Database Object

In this program, we see how to create a record type as a database object. We create the record type ’emp_det’ with four columns. The columns and their data types are:

• EMP_NO (NUMBER)
• EMP_NAME (VARCHAR2 (150))
• MANAGER (NUMBER)
• SALARY (NUMBER)

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

Output:

Type created

Code Explanation

• The above code creates the type emp_det as a database object.
• It has 4 columns: emp_no, emp_name, manager, and salary, as defined.
• Now ’emp_det’ is similar to any other data type (like NUMBER or VARCHAR2) and is visible across the entire database. Hence it can be used anywhere in the database to declare a variable of this type.

Example 2: Subprogram Level, Column-level Access

In this example, we see how to create a record type at the subprogram level and how to populate and fetch values from it by column. We create the ’emp_det’ record type at the subprogram level and use it to populate and display data.

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

Output:

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

Code Explanation

• Code line 2-8: Record type ’emp_det’ is declared with columns emp_no, emp_name, manager, and salary of data type NUMBER, VARCHAR2, NUMBER, and NUMBER.
• Code line 9: The guru99_emp_rec variable is declared as ’emp_det’ data type. This variable can hold a value that contains all four fields.
• Code line 11: Populating the ’emp_no’ field of ‘guru99_emp_rec’ with value 1001.
• Code line 12: Populating the ’emp_name’ field with value XXX.
• Code line 13: Populating the ‘manager’ field with value 1000.
• Code line 14: Populating the ‘salary’ field with value 10000.
• Code line 15-19: Displaying the value of ‘guru99_emp_rec’ in the output.

Example 3: Subprogram Level, Row-level Access

In this example, we see how to create a record type at the subprogram level and populate it at the row level. We create the ’emp_det’ record type at the subprogram level and use it to populate and display data.

DECLARE
TYPE emp_det IS RECORD
(
EMP_NO NUMBER,
EMP_NAME VARCHAR2(150),
MANAGER NUMBER,
SALARY NUMBER
);
guru99_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 guru99_emp_rec FROM emp WHERE emp_no=1002;
dbms_output.put_line ('Employee Detail');
dbms_output.put_line ('Employee Number: '||guru99_emp_rec.emp_no);
dbms_output.put_line ('Employee Name: '||guru99_emp_rec.emp_name);
dbms_output.put_line ('Employee Salary: '||guru99_emp_rec.salary);
dbms_output.put_line ('Employee Manager Number: '||guru99_emp_rec.manager);
END;
/

Code Explanation

• Code line 2-8: Record type ’emp_det’ is declared with columns emp_no, emp_name, manager, and salary of data type NUMBER, VARCHAR2, NUMBER, and NUMBER.
• Code line 9: The guru99_emp_rec variable is declared as ’emp_det’ data type and can hold all four fields.
• Code line 11: Populating the table emp with 1002 as emp_no, YYY as emp_name, 15000 as salary, and 1000 as manager number.
• Code line 12: Committing the insert transaction.
• Code line 13: Populating the ‘guru99_emp_rec’ variable at the row level from the select query for employee number 1002.
• Code line 15-19: Displaying the value of ‘guru99_emp_rec’ in the output.

Output:

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

Note: A record type can be accessed only at the column level when redirecting its value to any output mode. Notice that in this example the SELECT list order does not match the record field order, which is why the salary and manager values appear swapped in the output.

Types of PL/SQL Records

Beyond the programmer-defined record shown above, Oracle offers two anchored record kinds that copy their structure automatically. Using them avoids re-declaring columns and keeps the record in step with the table or cursor it is based on.

A %ROWTYPE record is the safest choice when the shape should follow a table or a cursor, because a column change is picked up automatically on the next compile.

FAQs