How to Retrieve Data From Multiple Tables Using PL/SQL Cursors

In database programming, the ability to retrieve data from multiple tables is essential for building robust and efficient applications. PL/SQL Cursors is a powerful feature that enables developers to navigate through result sets and make them the best option for querying data from multiple tables.

In this article, We will understand the use of PL/SQL Cursors to retrieve data from multiple tables by understanding the various methods along with the examples and so on.

How to Retrieve Data from Multiple Tables Using PL/SQL Cursors?

When working with relational databases, it is often necessary to retrieve data from multiple tables to perform complex queries or generate reports. PL/SQL Cursors provide a convenient way to fetch data from multiple tables and process it efficiently. Below are the methods that help us to retrieve data from multiple tables.

1. Using Implicit Cursors
   
2. Using Explicit Cursors
   
3. Using Cursor FOR Loops

To retrieve data from multiple tables utilizing PL/SQL Cursors, the ensuing syntax can be employed:

DECLARE
  CURSOR cursor_name IS
    SELECT column1, column2
    FROM table1, table2
    WHERE table1.id = table2.id;
BEGIN
  FOR record IN cursor_name LOOP
    DBMS_OUTPUT.PUT_LINE('Column 1: ' || record.column1 || ', Column 2: ' || record.column2);
  END LOOP;
END;

Explanation:

• DECLARE: Initiates the commencement of the declaration segment in a PL/SQL block, where variables, cursors, and other program structures are defined.
  
• CURSOR: Declares a cursor, which constitutes a named SQL query preserved within the PL/SQL block, utilized to retrieve and process rows from the result set.
  
• BEGIN: Marks the inception of the executable segment of a PL/SQL block, where the principal program logic is inscribed.
  
• FOR: It runs a loop that iterates over a sequence of values. In this scenario, it iterates over the rows fetched by the cursor.
  
• LOOP: Signifies the commencement of a loop that persists until an exit condition is met.
  
• END LOOP: Marks the culmination of a loop instigated by the FOR statement.
  
• DBMS_OUTPUT.PUT_LINE: A procedure from the DBMS_OUTPUT package employed to exhibit text output, customarily for debugging or informational intents.
  
• END: Marks the conclusion of the PL/SQL block.

Let' set up an environment

To understand How to Retrieve Data from Multiple Tables Using PL/SQL Cursors we need two table on which we will perform various operations and queries. Here we will consider a table called Departments and employees where employees table contains employee_id, employee_name, and department_id as Columns.

Also, Departments table which contains department_id and department_name as Columns.

-- Create the departments table
CREATE TABLE departments (
    department_id NUMBER PRIMARY KEY,
    department_name VARCHAR2(100)
);

-- Insert sample data into the departments table
INSERT INTO departments (department_id, department_name) VALUES (101, 'HR');
INSERT INTO departments (department_id, department_name) VALUES (102, 'Finance');
INSERT INTO departments (department_id, department_name) VALUES (103, 'IT');

-- Create the employees table
CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    employee_name VARCHAR2(100),
    department_id NUMBER,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

-- Insert sample data into the employees table
INSERT INTO employees (employee_id, employee_name, department_id) VALUES (1, 'John', 101);
INSERT INTO employees (employee_id, employee_name, department_id) VALUES (2, 'Alice', 102);
INSERT INTO employees (employee_id, employee_name, department_id) VALUES (3, 'Bob', 101);
INSERT INTO employees (employee_id, employee_name, department_id) VALUES (4, 'Emma', 103);

Upon inserting data into the departments and employees tables, the tables manifest as follows:

1. Using Implicit Cursors

Implicit Cursors are automatically created by Oracle when executing DML statements like INSERT, UPDATE, DELETE, or SELECT INTO. They do not require explicit declaration. The syntax for an Implicit Cursor is as follows:

BEGIN
  INSERT INTO employees (employee_id, employee_name, department_id) VALUES (5, 'Sarah', 102);
  COMMIT;
END;

Output:

Explanation: Inserts a new employee record into the 'employees' table.

2. Using Explicit Cursors

Explicit Cursors are user-defined cursors that offer more control over result set processing. They need to be explicitly declared, opened, fetched, and closed. The syntax for an Explicit Cursor is demonstrated below:

DECLARE
  CURSOR emp_cursor IS
    SELECT employee_name FROM employees WHERE department_id = 101;
  emp_rec employees.employee_name%TYPE;
BEGIN
  OPEN emp_cursor;
  FETCH emp_cursor INTO emp_rec;
  CLOSE emp_cursor;
END;

Output:

Explanation: The Explicit Cursor fetches the 'employee_name' from the 'employees' table for the 'HR' department.

3. Using Cursor FOR Loops

Cursor FOR Loops simplify the process of fetching data from a cursor by automatically handling opening, fetching, and closing operations. The syntax for a Cursor FOR Loop is illustrated as follows:

BEGIN
  FOR emp_rec IN (SELECT employee_name FROM employees WHERE department_id = 102) LOOP
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_rec.employee_name);
  END LOOP;
END;

Output:

Explanation: The Cursor FOR Loop displays the 'employee_name' from the 'employees' table for the 'Finance' department.

Conclusion

Overall, Retrieving data from multiple tables using PL/SQL Cursors is a powerful feature that allows developers to efficiently fetch and process data from relational databases. By using Implicit Cursors, Explicit Cursors, and Cursor FOR Loops, programmers can perform complex queries and generate reports that involve data from multiple tables.