Record type in Oracle PL/SQL.

Preamble

Record type is a group of linked data elements stored in fields, each with its own name and data type. You can use Record as a variable, which may contain a table row or some columns (fields) from a table row.

Defining and declaring Record in Oracle PL/SQL

The %ROWTYPE attribute allows you to declare a record representing a row in the database table without listing all columns.

Your code shall continue to work even after the columns have been added to the table. If you want to represent a subset of columns in a table or columns from different tables, you can define VIEW or declare CURSOR to select the right columns and any necessary associations and then apply the %ROWTYPE attribute to VIEW or CURSOR.

Syntax for Record type definition and variable declaration in Oracle PL/SQL

TYPE type_rec_name IS Record (field_1 datatype,
field_2 datatype,
... field_n datatype);
var_rec type_rec_name;

Parameters and arguments of the type

• type_rec_name – name of a certain Record type
• var_rec – name of a variable of Record type
• field_1, field_2,… field_n – fields of Record type
• datatype – data type for fields of Record type. It can be any from the list:

Note:

• To create a Record you define a record type and then declare a variable of this type.
• You can also create or find COLUMN, VIEW or CURSOR PL/SQL with the values you want to use and the %ROWTYPE attribute to create the corresponding Record.
• You can define the types of Record in the declaration part of any PL/SQL block, subroutine or package. When you define your own Record type, you can specify a NOT NULL limit for fields or assign them default values.
• The Record type defined in the package specification is incompatible with an identically defined local Record type.

Let’s look at some examples to understand how to use Record in Oracle PL/SQL.

Example Record and declaration of a variable

In the following example, Oracle PL/SQL fields are initialized as scalar data types.

DECLARE
TYPE DeptRecTyp IS RECORD (
dept_id NUMBER(4) NOT NULL := 10,
dept_name VARCHAR2(30) NOT NULL := 'Administration',
mgr_id NUMBER(6) := 200,
loc_id NUMBER(4) := 1700
);

dept_rec DeptRecTyp;
BEGIN
DBMS_OUTPUT.PUT_LINE('dept_id: ' || dept_rec.dept_id);
DBMS_OUTPUT.PUT_LINE('dept_name: ' || dept_rec.dept_name);
DBMS_OUTPUT.PUT_LINE('mgr_id: ' || dept_rec.mgr_id);
DBMS_OUTPUT.PUT_LINE('loc_id: ' || dept_rec.loc_id);
END;
Result:
dept_id: 10
dept_name: Administration
mgr_id: 200
loc_id: 1700

In this example, the Record type with the name DeptRecTyp is defined, indicating the initial value for each field. Then it is declared a variable of this type with the name dept_rec and its fields are printed.

Example Record with field attribute NOT NULL

In the following Oracle PL/SQL example let’s define the Record type, specify the fields with NOT NULL limitation and assign initial values;

DECLARE
-- For the fields declared NOT NULL, we must specify the default value.
TYPE type_rec IS RECORD (id INTEGER NOT NULL := -1, name VARCHAR2(64) NOT NULL := '[anonymous]');
-- Declaration of a variable of Record type
v_rec type_rec;

BEGIN
-- We haven't filled in the NAME field, so it takes the default value specified above.
dbms_output.put_line('id = '|| v_rec.id||', name = '||v_rec.name);
END;
Result:
id = -1, name = [anonymous]

Example of writing to Oracle PL/SQL using the %ROWTYPE attribute

If we have a table record or some of its columns, we use the %ROWTYP attribute to determine the table record. Use the cursor to define some columns of the table.
In the following example, declare the records using the %ROWTYPE attribute;

DECLARE
-- Declare a record variable that may contain a row from the EMPLOYEES table.
-- The fields of the record automatically correspond to the names and types of columns.
-- No TYPE declaration is required in this case.
rec1 employees%ROWTYPE;
-- Use <cursor>%ROWTYPE instead of <table>%ROWTYPE, because we only need some columns.
CURSOR c1 IS SELECT department_id, department_name, location_id FROM departments;
rec2 c1%ROWTYPE;

BEGIN
NULL;
END;

In this example, the rec1 record was declared with the table_name%ROWTYPE construct and the rec2 record with the cursor_name%ROWTYPE construct.

Example Record with %TYPE attribute

In the following example let’s define Record fields using the %TYPE attribute, using dot notation table_name.column_name%TYPE and add a custom field;

DECLARE
-- mix the fields that are columns of the table with the custom fields.
TYPE type_rec IS RECORD (first_name employees.first_name%TYPE,
last_name employees.last_name%TYPE,
rating NUMBER);
v_rec type_rec;

BEGIN
NULL;
END;

In this example, we defined a Record record using the table_name.column_name%TYPE construct and smeared the custom rating field with the record.

Example of Record as function return values

In the following example let’s define the RECORD type and the function which this type will return.

DECLARE
TYPE EmpRec IS RECORD (
emp_id NUMBER(4)
last_name VARCHAR2(10),
dept_num NUMBER(2),
job_title VARCHAR2(9),
salary NUMBER(7,2));
FUNCTION nth_highest_salary (n INTEGER) RETURN EmpRec IS ...
BEGIN
NULL;
END;

In this example, the function nth_highest_salary returns a value of EmpRec type. Record type in Oracle PL/SQL.

The example is Record as a procedure parameter

Like scalar variables, custom Record(s) can be declared as formal procedure parameters.

DECLARE
TYPE EmpRec IS RECORD (
emp_id employees. employee_id%TYPE,
last_name VARCHAR2(10),
job_title VARCHAR2(9),
salary NUMBER(7,2));
...
PROCEDURE raise_salary (emp_info EmpRec);
BEGIN
...
END;

In this example, the raise_salary procedure accepts EmpRec type as a parameter.

Example assignment of values for Record

To define all fields for Record by default, assign it an uninitialized record of the same type. For example .

DECLARE
TYPE Rec_Typ IS RECORD (field1 NUMBER, field2 VARCHAR2(32) DEFAULT 'something');
rec1 Rec_Typ;
rec2 Rec_Typ;
BEGIN
-- First rec1 has the values that we assign.
rec1.field1 := 100;
rec1.field2 := 'something else';
-- Assigning rec1 to an empty record resets the fields to their default values.
-- Field 1 is NULL and field 2 is 'something' (because of DEFAULT above).
rec1 := rec2;
dbms_output.put_line('Field1 = '.
|| NVL(TO_CHAR(rec1.field1), '<NULL>') || ', field2 = ' || rec1.field2);
END;
Result:
Field1 = <NULL>, field2 = something

Instead of assigning values separately to each field in the record, you can assign values to all fields at once.
You can assign one custom record to another if they have the same data type. There are fields that correspond exactly to each other.

Let’s look at the following example:

DECLARE
--Two identical ads of the type.
TYPE DeptRec1 IS RECORD ( dept_num NUMBER(2), dept_name VARCHAR2(14));
TYPE DeptRec2 IS RECORD ( dept_num NUMBER(2), dept_name VARCHAR2(14));
dept1_info DeptRec1;
dept2_info DeptRec2;
dept3_info DeptRec2;
BEGIN .
-- Not allowed; different types of data, although the fields are the same.
-- dept1_info: = dept2_info;
-- This assignment is allowed because the records have the same type.
dept2_info := dept3_info;
END;

Example of a Record with Record field (nested record)

The following example shows how you can define a Record type as an attachment to another Record type.

DECLARE
TYPE name_rec IS RECORD (
first employees.first_name%TYPE,
last employees.last_name%TYPE
);

TYPE contact IS RECORD (
name_rec, -- name_rec subscript
phone employees.phone_number%TYPE
);
friend contact;--variable contact type
BEGIN
friend.name.first := 'John';
friend.name.last := 'Smith';
friend.phone := '1-650-555-1234';

DBMS_OUTPUT.PUT_LINE (
friend.name.first || ' ' ||
friend.name.last || ', ' ||
friend.phone
);
END;
Result:
John Smith, 1-650-555-1234

In this example we defined the type name_rec as a nested record of the type contact. The fields of the nested record type were accessed with dot notation.

Example of a Record with Varray field

The following example shows how VARRAY can be defined as a Record type field.

DECLARE
TYPE full_name IS VARRAY(2) OF VARCHAR2(20);

TYPE contact IS RECORD ((20); TYPE contact IS VARRAY(2) OF VARCHAR2(20).
name full_name := full_name('John', 'Smith'), -- varray fields
phone employees.phone_number%TYPE);

friend contact;
BEGIN
friend.phone := '1-650-555-1234';

DBMS_OUTPUT.PUT_LINE (
friend.name(1) || ' ' ||
friend.name(2) || ', ' ||.
friend.phone);
END;

Result:
John Smith, 1-650-555-1234

In this example we have defined the type VARRAY with the name full_name and the type Record with the name contact. Now in the type contact a field of the type full_name is available. The data of the nested type VARRAY were accessed with dot notation.

Example of INSERT in an Oracle PL/SQL database using Record

Oracle PL/SQL operator INSERT allows inserting Record into database strings using one variable of RECORD type or attribute %ROWTYPE in VALUES sentence instead of field list.

This makes your code more readable and supported. If you issue INSERT via the FORALL instruction, you can insert values from a whole set of records.

The number of fields in the entry must be equal to the number of columns listed in the INTO sentence, and the corresponding fields and columns must have compatible data types. To make sure the record is compatible with the table, it may be more convenient to declare the variable as table_name%ROWTYPE type.

Example of inserting a record in Oracle PL/SQL using %ROWTYPE

DECLARE
dept_info dept%ROWTYPE;
BEGIN
-- deptno, dname, and loc are the columns of the table.
-- Record writes these names from %ROWTYPE.
dept_info.deptno := 70;
dept_info.dname := 'PERSONNEL';
dept_info.loc := 'DALLAS';
-- Using %ROWTYPE, we can remove the list of columns
-- (deptno, dname, loc) from the INSERT instruction.
INSERT INTO dept VALUES dept_info;
END;

In this example, the variable dept_info is declared using %ROWTYPE. You can execute INSERT with the value dept_info without specifying the column list. Declaring %ROWTYPE ensures that the record attributes have exactly the same names and types as the columns in the table.

Example of an Oracle PL/SQL UPDATE database using Record

The UPDATE PL/SQL-only extension allows you to update database strings using a single variable of the RECORD or %ROWTYPE type on the right side of the SET operator instead of the field list.

If you run UPDATE with the FORALL operator, you can update a set of strings using values from a whole set of records.

You can also use the UPDATE operator to specify a record in the RETURNING sentence to extract new values to the record. If you specify UPDATE using the FORALL operator, you can extract new values from the updated row set to the record collection.

The number of fields in the record must be equal to the number of columns listed in the SET proposal, and the corresponding fields and columns must have compatible data types.

For example: You can use the ROW keyword to represent an entire row:

DECLARE
dept_info dept%ROWTYPE;
BEGIN
dept_info.deptno := 30;
dept_info.dname := 'MARKETING';
dept_info.loc := 'ATLANTA';
-- Record dept_info will have full column values, and null
-- for any other columns.
UPDATE dept SET ROW = dept_info WHERE deptno = 30;
END;

The keyword ROW is only allowed on the left side of the SET sentence.
The SET ROW argument must be a real PL/SQL record, not a subquery that returns a single line.
The record may also contain collections or objects.

Example of using RETURNING with Record

Operators INSERT, UPDATE and DELETE may include the RETURNING sentence, which returns the column values from the affected row to the PL/SQL record variable. This eliminates the need to select a row after inserting or updating, or before deleting.

By default you can use this sentence only when working with one row. When you use an SQL collection, you can use the RETURNING BULK COLLECT INTO form to store results in one or more collections.

The following example updates the salary of the employee and returns the last_name of the employee, and the new salary to the emp_info variable:

DECLARE
TYPE EmpRec IS RECORD (last_name employees.last_name%TYPE, salary employees.salary%TYPE);
emp_info EmpRec;
emp_id NUMBER := 100;
BEGIN
UPDATE employees SET salary = salary * 1.1 WHERE employee_id = emp_id
RETURNING last_name, salary INTO emp_info;
dbms_output.put_line('Simple increase for '.
|| emp_info.last_name || ', in quantity ' || emp_info.salary);
ROLLBACK;
END;

Restrictions on INSERT / UPDATE records

Currently the following restrictions apply to INSERT/UPDATE records:

Record variables are only allowed in the following places:

• On the right side of the SET offer in the UPDATE instructions
• In the VALUES proposal, the INSERT instructions
• In subitem INTO conditions RETURNING

Record variables are not allowed in the SELECT, WHERE list, provided GROUP BY or ORDER BY.

Keyword ROW is allowed only on the left side of SET offer. In addition, you may not use ROW with subquery.

Only one SET sentence is allowed in UPDATE if ROW is used.

If in VALUES of INSERT operator contains Record variable, no other variable or value is allowed in this sentence.

If INTO in the sentence RETURNING contains the variable Record, then no other variable or value is allowed in this sentence.
The following ones are not supported:

• Nested Record Types
• Functions that return a record
• Record attachments/updates with EXECUTE IMMEDIATE operator.

Example of data request to Record collection

You can use the BULK COLLECT offer with the SELECT INTO or FETCH instruction to extract a set of rows into a record collection.

DECLARE
TYPE EmployeeSet IS TABLE OF employees%ROWTYPE;
underpaid EmployeeSet; -- Hold the row set from the EMPLOYEES table.

CURSOR c1 IS SELECT first_name, last_name FROM employees;
TYPE NameSet IS TABLE OF c1%ROWTYPE;
some_names NameSet; -- Hold a set of partial rows from the EMPLOYEES table.

BEGIN ; -- HOLD A SET OF PARTIAL ROWS FROM AN EMPLOYEES TABLE.
-- With a single query, we bring all relevant data into the record collection.
SELECT * BULK COLLECT INTO underpaid FROM employees
WHERE salary < 2500 ORDER BY salary DESC;

-- Now we can process data by studying the collection or by passing it on.
-- is a separate procedure, instead of writing a FETCH cycle for each line.
dbms_output.put_line(underpaid.COUNT || ' people make less than 2500.');
FOR i IN underpaid.FIRST . underpaid.LAST
LOOP
dbms_output.put_line(underpaid(i).last_name || ' makes ' || underpaid(i).salary);
END LOOP;

-- We can also only give some of the columns in the table.
-- Here we get the names of 10 arbitrary employees.
SELECT first_name, last_name BULK COLLECT INTO some_names FROM employees WHERE ROWNUM < 11;
FOR i IN some_names.FIRST . some_names.LAST
LOOP
dbms_output.put_line('Employee = '
|| some_names(i).first_name || ' '.
|| some_names(i).last_name);
END LOOP;
END;

PL/SQL tutorial: Introduction To PL/SQL Record Datatype In Oracle Database

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.