The record data type in PL/SQL is a composite data type that allows you to define a structure that can hold multiple related data elements. It is similar to a struct in other programming languages.
Defining a Record
To define a record, you use the TYPE keyword followed by the name of the record and the IS RECORD clause. Each field within the record is defined using the %TYPE attribute, which specifies the data type of that field.
DECLARE TYPE employee_rec IS RECORD( id employees.employee_id%TYPE, name employees.last_name%TYPE, salary employees.salary%TYPE ); emp employee_rec; -- Declare a variable of type employee_rec BEGIN -- Assign values to fields emp.id := 1001; emp.name := 'Smith'; emp.salary := 5000; -- Accessing fields dbms_output.put_line('Employee ID: ' || emp.id); dbms_output.put_line('Employee Name: ' || emp.name); dbms_output.put_line('Employee Salary: ' || emp.salary); END;
You can use records to store and manipulate related data elements. Once you have defined a record, you can declare variables of that record type and assign values to its individual fields.
In the example above, we declared an employee_rec record type with three fields: id, name, and salary. We then declared a variable emp of type employee_rec and assigned values to its fields using the dot notation.
To access the fields of a record, you use the dot notation. For example, emp.id refers to the id field of the emp record variable.
You can also define nested records, where a record can have another record as one of its fields. This allows you to represent complex data structures.
TYPE address_rec IS RECORD( street VARCHAR2(100), city VARCHAR2(50), state VARCHAR2(50) ); TYPE employee_rec IS RECORD( id employees.employee_id%TYPE, name employees.last_name%TYPE, address address_rec ); emp employee_rec; -- Declare a variable of type employee_rec -- Assigning values to nested fields emp.address.street := '123 Main St'; emp.city := 'New York'; emp.state := 'NY'; BEGIN -- Accessing nested fields dbms_output.name); dbms_output.put_line('Address: ' || emp.street || ', ' || emp.city || ', ' || emp.state); END;
Collections and Records
You can also use records in collections such as arrays and nested tables. This allows you to store multiple records in a single collection.
TYPE employee_table_type IS TABLE OF employee_rec INDEX BY PLS_INTEGER; employees employee_table_type; BEGIN -- Adding records to collection employees(1).id := 1001; employees(1).name := 'Smith'; employees(1).salary := 5000; employees(2).id := 1002; employees(2).name := 'Johnson'; employees(2).salary := 6000; -- Accessing records in collection FOR i IN employees.FIRST..employees.LAST LOOP dbms_output.put_line('Employee ID: ' || employees(i).id); dbms_output.put_line('Employee Name: ' || employees(i).name); dbms_output.put_line('Employee Salary: ' || employees(i).salary); dbms_output.put_line(''); END LOOP; END;
The record data type in PL/SQL allows you to define structured data types that can hold multiple related data elements. You can use records to store and manipulate data, access fields using the dot notation, define nested records for complex data structures, and use records in collections.
The flexibility of records makes them a powerful tool for working with structured data in PL/SQL.