Records are composite data types that allow you to treat related data items of different data types as a single unit. Think of them as a structure or a row of data. This greatly improves code organization and readability, especially when dealing with data that naturally groups together. Oracle PL/SQL offers two primary types of records: Table-based Records (%ROWTYPE) and Programmer-defined Records.
Table-based Records (%ROWTYPE)
Table-based records, defined using the %ROWTYPE attribute, are incredibly useful when you need to represent an entire row of a database table or view. The structure of the record automatically mirrors the column names and data types of the specified table or view. This ensures data type compatibility and simplifies data manipulation, as you don't need to manually declare each variable.
Example 1: Declaring a %ROWTYPE variable and populating it from a table
DECLARE
-- Declare a record variable based on the EMPLOYEES table structure.
-- This record will automatically have fields for each column in EMPLOYEES.
l_employee_rec EMPLOYEES%ROWTYPE;
BEGIN
-- Select an entire row from the EMPLOYEES table into the record variable.
-- Assuming EMPLOYEES table has employee_id, first_name, last_name, email, etc.
SELECT *
INTO l_employee_rec
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 100; -- Example: Fetch data for employee_id 100
-- Access individual fields of the record using dot notation.
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || l_employee_rec.FIRST_NAME || ' ' || l_employee_rec.LAST_NAME);
DBMS_OUTPUT.PUT_LINE('Employee Email: ' || l_employee_rec.EMAIL);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee with ID 100 not found.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
/
Explanation
This example demonstrates how to declare a record variable l_employee_rec using EMPLOYEES%ROWTYPE. This record will have fields corresponding to all columns in the EMPLOYEES table. We then use a SELECT * INTO statement to populate this record with an entire row from the EMPLOYEES table for EMPLOYEE_ID = 100. Finally, we access and display individual column values (like FIRST_NAME and EMAIL) using dot notation, e.g., l_employee_rec.FIRST_NAME. This approach significantly streamlines fetching and manipulating row-level data.
Example 2: Updating a table row using a %ROWTYPE record
DECLARE
-- Declare a record variable for the EMPLOYEES table.
l_employee_rec EMPLOYEES%ROWTYPE;
BEGIN
-- First, retrieve the existing employee data into the record.
SELECT *
INTO l_employee_rec
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 101; -- Example: Update data for employee_id 101
-- Modify specific fields within the record.
l_employee_rec.SALARY := l_employee_rec.SALARY * 1.05; -- Give a 5% raise
l_employee_rec.COMMISSION_PCT := 0.20; -- Set commission to 20%
-- Update the table using the modified record.
-- This is a convenient way to update multiple columns at once.
UPDATE EMPLOYEES
SET ROW = l_employee_rec -- Assigns all fields of the record to the row
WHERE EMPLOYEE_ID = 101;
DBMS_OUTPUT.PUT_LINE('Employee ID ' || l_employee_rec.EMPLOYEE_ID || ' salary updated to ' || l_employee_rec.SALARY);
COMMIT; -- Commit the transaction to make changes permanent
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee with ID 101 not found.');
WHEN OTHERS THEN
ROLLBACK; -- Rollback in case of any error
DBMS_OUTPUT.PUT_LINE('An error occurred during update: ' || SQLERRM);
END;
/
Explanation
This example demonstrates a common use case: updating a table row using a %ROWTYPE record. We first fetch an existing row into l_employee_rec. Then, we modify specific fields within this record (e.g., SALARY and COMMISSION_PCT). Crucially, the UPDATE ... SET ROW = l_employee_rec syntax allows us to update all columns of the target row with the values from the record in a single, concise statement, simplifying the update operation. The COMMIT ensures the changes are saved to the database.
Example 3: Passing %ROWTYPE records as parameters to procedures
-- Create a sample procedure to demonstrate passing records
CREATE OR REPLACE PROCEDURE display_employee_info (
p_employee_details IN EMPLOYEES%ROWTYPE
)
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('--- Employee Details ---');
DBMS_OUTPUT.PUT_LINE('ID: ' || p_employee_details.EMPLOYEE_ID);
DBMS_OUTPUT.PUT_LINE('Name: ' || p_employee_details.FIRST_NAME || ' ' || p_employee_details.LAST_NAME);
DBMS_OUTPUT.PUT_LINE('Email: ' || p_employee_details.EMAIL);
DBMS_OUTPUT.PUT_LINE('Phone: ' || p_employee_details.PHONE_NUMBER);
DBMS_OUTPUT.PUT_LINE('Hire Date: ' || p_employee_details.HIRE_DATE);
DBMS_OUTPUT.PUT_LINE('Salary: ' || p_employee_details.SALARY);
DBMS_OUTPUT.PUT_LINE('------------------------');
END;
/
-- Anonymous block to call the procedure
DECLARE
l_emp_rec EMPLOYEES%ROWTYPE;
BEGIN
SELECT *
INTO l_emp_rec
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 102; -- Example: Employee 102
-- Call the procedure, passing the entire record
display_employee_info(l_emp_rec);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee with ID 102 not found.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
/
Explanation
This example showcases the power of passing %ROWTYPE records as parameters to PL/SQL procedures. We define a procedure display_employee_info that accepts a parameter of type EMPLOYEES%ROWTYPE. Inside the procedure, we can directly access the fields of the passed record. In the anonymous block, we populate an EMPLOYEES%ROWTYPE variable and then pass this entire record to the procedure. This reduces the number of individual parameters needed and improves code clarity, especially when a procedure needs to operate on a whole set of related data.
Example 4: Using %ROWTYPE in a cursor FOR loop
DECLARE
-- No need to declare a record explicitly here; the cursor FOR loop handles it.
BEGIN
DBMS_OUTPUT.PUT_LINE('--- All Employees ---');
-- The cursor FOR loop implicitly declares a record variable for each row.
FOR r_employee IN (SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY FROM EMPLOYEES ORDER BY EMPLOYEE_ID)
LOOP
-- Access fields of the implicitly declared record 'r_employee'.
DBMS_OUTPUT.PUT_LINE('ID: ' || r_employee.EMPLOYEE_ID ||
', Name: ' || r_employee.FIRST_NAME || ' ' || r_employee.LAST_NAME ||
', Salary: ' || r_employee.SALARY);
END LOOP;
DBMS_OUTPUT.PUT_LINE('---------------------');
END;
/
Explanation
This example demonstrates a common and highly efficient way to process multiple rows from a table using a CURSOR FOR loop. When you use FOR r_employee IN (SELECT ...), PL/SQL implicitly declares a record variable (named r_employee in this case) with the structure of the selected columns for each iteration. This implicitly declared record acts as a %ROWTYPE for the current row, making it easy to access column values within the loop without explicit record declaration and FETCH statements. This pattern is often preferred for its conciseness and automatic cursor management.
Example 5: Combining %ROWTYPE with a subquery for specific columns
DECLARE
-- Declare a record variable for a specific set of columns from EMPLOYEES.
-- This creates an anonymous record type on the fly based on the subquery's projection.
l_manager_info_rec EMPLOYEES%ROWTYPE; -- Initially, this will represent the full EMPLOYEES table structure
-- To get only specific columns, we can declare a cursor and use %ROWTYPE on it,
-- or, more commonly, define a programmer-defined record if the structure is fixed.
-- For demonstration, let's illustrate how a %ROWTYPE would adapt to a subquery's result set
-- if directly used with SELECT INTO, although typically not done with a full table %ROWTYPE declaration.
-- More practical with %ROWTYPE if used directly with the select:
CURSOR c_manager_details IS
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL
FROM EMPLOYEES
WHERE JOB_ID = 'IT_PROG'; -- Example: Select IT Programmers
l_programmer_rec c_manager_details%ROWTYPE; -- %ROWTYPE based on cursor definition
BEGIN
DBMS_OUTPUT.PUT_LINE('--- IT Programmers ---');
OPEN c_manager_details;
LOOP
FETCH c_manager_details INTO l_programmer_rec;
EXIT WHEN c_manager_details%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('ID: ' || l_programmer_rec.EMPLOYEE_ID ||
', Name: ' || l_programmer_rec.FIRST_NAME || ' ' || l_programmer_rec.LAST_NAME ||
', Email: ' || l_programmer_rec.EMAIL);
END LOOP;
CLOSE c_manager_details;
DBMS_OUTPUT.PUT_LINE('----------------------');
END;
/
Explanation
This example demonstrates a more refined use of %ROWTYPE when dealing with a subset of columns, particularly useful with explicit cursors. While EMPLOYEES%ROWTYPE would normally capture all columns, by defining a cursor c_manager_details with specific columns (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL), then declaring l_programmer_rec as c_manager_details%ROWTYPE, the record variable's structure perfectly matches the selected columns of the cursor. This ensures type safety and clarity, allowing you to fetch only the data you need into a precisely structured record. This approach is highly efficient for data retrieval and processing, promoting "SQL first" principles in PL/SQL development.
Programmer-defined Records
While %ROWTYPE is excellent for mirroring existing table or view structures, Programmer-defined Records give you the flexibility to create custom record structures tailored precisely to your application's needs. You define the fields within the record, specifying their names and data types, allowing you to group related but disparate pieces of data together logically. This is especially useful when the data doesn't directly correspond to a single table row or when you need to combine data from multiple sources.
Example 1: Declaring and using a simple programmer-defined record
DECLARE
-- Define a custom record type for customer details.
-- This type is local to this block or package.
TYPE customer_address_rec_typ IS RECORD (
customer_id NUMBER(6),
customer_name VARCHAR2(100),
street_address VARCHAR2(200),
city VARCHAR2(50),
zip_code VARCHAR2(10)
);
-- Declare a variable of the custom record type.
l_cust_addr_info customer_address_rec_typ;
BEGIN
-- Assign values to the fields of the record.
l_cust_addr_info.customer_id := 1001;
l_cust_addr_info.customer_name := 'Alice Johnson';
l_cust_addr_info.street_address := '123 Main St';
l_cust_addr_info.city := 'Anytown';
l_cust_addr_info.zip_code := '98765';
-- Display the record's contents.
DBMS_OUTPUT.PUT_LINE('Customer ID: ' || l_cust_addr_info.customer_id);
DBMS_OUTPUT.PUT_LINE('Name: ' || l_cust_addr_info.customer_name);
DBMS_OUTPUT.PUT_LINE('Address: ' || l_cust_addr_info.street_address || ', ' || l_cust_addr_info.city || ' ' || l_cust_addr_info.zip_code);
END;
/
Explanation
This example demonstrates the fundamental concept of creating and using a programmer-defined record. We first define a new record TYPE called customer_address_rec_typ with several fields (e.g., customer_id, customer_name, street_address), each with a specified data type. Then, we declare a variable l_cust_addr_info of this custom type. We can then assign values to its individual fields using dot notation and access them in the same manner. This allows us to logically group related customer information into a single, manageable unit.
Example 2: Programmer-defined records with nested records
DECLARE
-- Define a record type for address details.
TYPE address_rec_typ IS RECORD (
street VARCHAR2(200),
city VARCHAR2(50),
state VARCHAR2(2),
zip VARCHAR2(10)
);
-- Define a record type for person details, including a nested address record.
TYPE person_rec_typ IS RECORD (
person_id NUMBER,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
contact_no VARCHAR2(20),
home_address address_rec_typ -- Nested record!
);
-- Declare a variable of the person record type.
l_person person_rec_typ;
BEGIN
-- Assign values to the nested record's fields.
l_person.person_id := 2001;
l_person.first_name := 'Bob';
l_person.last_name := 'Smith';
l_person.contact_no := '555-123-4567';
l_person.home_address.street := '456 Oak Ave';
l_person.home_address.city := 'Springfield';
l_person.home_address.state := 'IL';
l_person.home_address.zip := '62704';
-- Display the contents, accessing nested fields.
DBMS_OUTPUT.PUT_LINE('Person ID: ' || l_person.person_id);
DBMS_OUTPUT.PUT_LINE('Name: ' || l_person.first_name || ' ' || l_person.last_name);
DBMS_OUTPUT.PUT_LINE('Contact: ' || l_person.contact_no);
DBMS_OUTPUT.PUT_LINE('Address: ' || l_person.home_address.street || ', ' ||
l_person.home_address.city || ', ' ||
l_person.home_address.state || ' ' ||
l_person.home_address.zip);
END;
/
Explanation
This example demonstrates the concept of nested records, a powerful feature for organizing complex data hierarchies. We define an address_rec_typ record for address details. Then, within person_rec_typ, we include a field home_address that is itself of type address_rec_typ. This allows for a more granular and logical grouping of data. When assigning or accessing values, we use multiple dot notations (e.g., l_person.home_address.street) to navigate through the nested structure.
Example 3: Passing programmer-defined records as parameters to functions/procedures
-- Create a sample function that accepts a custom record type
CREATE OR REPLACE FUNCTION calculate_order_total (
p_order_details IN NUMBER,
p_quantity IN NUMBER,
p_unit_price IN NUMBER
) RETURN NUMBER
AS
-- Define a custom record type for order item details
TYPE order_item_rec_typ IS RECORD (
item_id NUMBER,
item_name VARCHAR2(100),
quantity NUMBER,
unit_price NUMBER(10,2)
);
-- Declare a local record variable (not used in this specific function but for demonstration)
l_order_item order_item_rec_typ;
l_total_price NUMBER;
BEGIN
-- For this simple function, we are just calculating based on individual parameters.
-- If the function were to process a complex order item, the record would be very useful.
l_total_price := p_quantity * p_unit_price;
RETURN l_total_price;
END;
/
-- Redefine the function to accept a record for better structure
CREATE OR REPLACE FUNCTION calculate_order_item_total (
p_order_item IN order_item_rec_typ -- Parameter is now the custom record type
) RETURN NUMBER
AS
l_total_price NUMBER;
BEGIN
l_total_price := p_order_item.quantity * p_order_item.unit_price;
RETURN l_total_price;
END;
/
DECLARE
-- Define the custom record type in the anonymous block as well, or in a package specification.
-- For functions/procedures, the type must be defined globally (in a package)
-- or within the block/procedure if it's only used internally.
TYPE order_item_rec_typ IS RECORD (
item_id NUMBER,
item_name VARCHAR2(100),
quantity NUMBER,
unit_price NUMBER(10,2)
);
l_my_order_item order_item_rec_typ;
v_item_total NUMBER;
BEGIN
l_my_order_item.item_id := 1;
l_my_order_item.item_name := 'Laptop';
l_my_order_item.quantity := 2;
l_my_order_item.unit_price := 1200.50;
-- Call the function, passing the record variable.
v_item_total := calculate_order_item_total(l_my_order_item);
DBMS_OUTPUT.PUT_LINE('Total for ' || l_my_order_item.item_name || ': $' || v_item_total);
-- Example with another item
l_my_order_item.item_id := 2;
l_my_order_item.item_name := 'Mouse';
l_my_order_item.quantity := 5;
l_my_order_item.unit_price := 25.00;
v_item_total := calculate_order_item_total(l_my_order_item);
DBMS_OUTPUT.PUT_LINE('Total for ' || l_my_order_item.item_name || ': $' || v_item_total);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
/
Explanation
This example demonstrates a crucial application of programmer-defined records: passing them as parameters to PL/SQL functions or procedures. We first define order_item_rec_typ within the anonymous block (or typically in a package specification for broader use). Then, the calculate_order_item_total function is defined to accept a parameter p_order_item of this custom record type. This significantly improves the maintainability and readability of your code by encapsulating related inputs into a single parameter, especially when dealing with functions that require multiple logical inputs.
Example 4: Using programmer-defined records in PL/SQL collections (Tables of Records)
DECLARE
-- Define the record type for an employee
TYPE employee_rec_typ IS RECORD (
employee_id NUMBER(6),
first_name VARCHAR2(20),
last_name VARCHAR2(25),
salary NUMBER(8,2)
);
-- Define a collection (nested table) of employee records
TYPE employee_tbl_typ IS TABLE OF employee_rec_typ INDEX BY PLS_INTEGER;
-- Declare a variable of the collection type
l_employees_list employee_tbl_typ;
l_count PLS_INTEGER := 0;
BEGIN
-- Populate the collection with data
l_count := l_count + 1;
l_employees_list(l_count).employee_id := 100;
l_employees_list(l_count).first_name := 'Steven';
l_employees_list(l_count).last_name := 'King';
l_employees_list(l_count).salary := 24000;
l_count := l_count + 1;
l_employees_list(l_count).employee_id := 101;
l_employees_list(l_count).first_name := 'Neena';
l_employees_list(l_count).last_name := 'Kochhar';
l_employees_list(l_count).salary := 17000;
l_count := l_count + 1;
l_employees_list(l_count).employee_id := 102;
l_employees_list(l_count).first_name := 'Lex';
l_employees_list(l_count).last_name := 'De Haan';
l_employees_list(l_count).salary := 17000;
-- Loop through the collection and display the contents
DBMS_OUTPUT.PUT_LINE('--- Employee List ---');
FOR i IN 1..l_employees_list.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE('ID: ' || l_employees_list(i).employee_id ||
', Name: ' || l_employees_list(i).first_name || ' ' || l_employees_list(i).last_name ||
', Salary: ' || l_employees_list(i).salary);
END LOOP;
DBMS_OUTPUT.PUT_LINE('---------------------');
END;
/
Explanation
This example introduces the powerful combination of programmer-defined records with PL/SQL collections (specifically, a nested table INDEX BY PLS_INTEGER). We first define employee_rec_typ for a single employee's details. Then, employee_tbl_typ is defined as a TABLE OF employee_rec_typ, essentially creating an array where each element is an employee_rec_typ record. This allows us to store and manage multiple employee records within a single collection variable l_employees_list, which is invaluable for bulk processing and in-memory data manipulation. We populate the collection and then iterate through it using a FOR loop to display each employee's details.
Example 5: Combining programmer-defined records with database queries (using BULK COLLECT)
DECLARE
-- Define a record type to hold specific employee details needed
TYPE employee_info_rec IS RECORD (
employee_id NUMBER,
full_name VARCHAR2(50),
salary_grade VARCHAR2(10)
);
-- Define a collection type to hold multiple employee_info_rec records
TYPE employee_info_tab IS TABLE OF employee_info_rec;
-- Declare a variable of the collection type
l_employee_data employee_info_tab;
BEGIN
-- Use BULK COLLECT to efficiently retrieve data from the database into the collection of records
SELECT employee_id,
first_name || ' ' || last_name, -- Concatenate names directly in the select
CASE
WHEN salary < 5000 THEN 'Grade A'
WHEN salary BETWEEN 5000 AND 10000 THEN 'Grade B'
ELSE 'Grade C'
END AS salary_grade
BULK COLLECT INTO l_employee_data
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 50; -- Example: Get employees from department 50
DBMS_OUTPUT.PUT_LINE('--- Employees in Department 50 ---');
-- Iterate through the collection and display the results
FOR i IN 1..l_employee_data.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('ID: ' || l_employee_data(i).employee_id ||
', Name: ' || l_employee_data(i).full_name ||
', Salary Grade: ' || l_employee_data(i).salary_grade);
END LOOP;
DBMS_OUTPUT.PUT_LINE('----------------------------------');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employees found in Department 50.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
/
Explanation
This example illustrates a powerful and performance-enhancing technique: using BULK COLLECT with programmer-defined records. We define employee_info_rec to capture specific, derived information (employee ID, full name, and a calculated salary grade). Then, employee_info_tab is a collection of these records. The SELECT ... BULK COLLECT INTO l_employee_data statement fetches multiple rows from the EMPLOYEES table and populates the l_employee_data collection in a single round trip to the database, significantly improving performance compared to row-by-row fetching. This is ideal for processing large datasets in PL/SQL.