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.
- Using Implicit Cursors
- Using Explicit Cursors
- 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:
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.