How to Retrieve Data From Multiple Tables in PL/SQL?

Retrieving data from multiple tables is a common task in PL/SQL and It is a skill that can significantly enhance our data manipulation capabilities. Whether we are joining tables to fetch related data or using subqueries to extract specific information, knowing how to navigate multiple tables is essential for efficient database management. In this article, we will learn How to retrieve data from multiple tables in PL/SQL by various examples and so on.

How to Get Data From Multiple Tables?

PL/SQL is a procedural language for Oracle databases that integrates SQL queries to retrieve and manipulate data. Retrieving data from multiple tables typically involves using JOIN operations, which allow combining records based on specified relationships. Understanding the syntax for joins, subqueries and other SQL constructs within PL/SQL fast effective data retrieval from various sources.

Syntax:

The syntax for retrieving data from multiple tables in PL/SQL involves using SQL queries with appropriate JOIN conditions, selecting desired columns, and applying any necessary filtering criteria. Commonly used JOIN types include INNER JOIN, LEFT JOIN and RIGHT JOIN.

SELECT table1.column1, table2.column2
FROM table1
INNER JOIN table2 ON table1.common_column = table2.common_column
WHERE condition;

Example of Retrieving Data From Multiple Tables in PL/SQL

Example 1: Retrieving Data with INNER JOIN

Let’s Create a PL/SQL block to retrieve and display the name of an employee and their department. The database contains two tables: departments with columns department_id and department_name, and employees with columns employee_id, employee_name and department_id. These two tables are linked by the department_id foreign key in the employees table. Use the employee_id = 101 as the condition for retrieving the data. Display the employee’s name and the department they belong to using the DBMS_OUTPUT.PUT_LINE function.

Query:

-- PL/SQL Code
CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

INSERT INTO departments VALUES (1, 'IT');
INSERT INTO departments VALUES (2, 'HR');

INSERT INTO employees VALUES (101, 'John Doe', 1);
INSERT INTO employees VALUES (102, 'Jane Smith', 2);

-- PL/SQL Block to Retrieve Data
DECLARE
    v_employee_name employees.employee_name%TYPE;
    v_department_name departments.department_name%TYPE;
BEGIN
    SELECT e.employee_name, d.department_name
    INTO v_employee_name, v_department_name
    FROM employees e
    INNER JOIN departments d ON e.department_id = d.department_id
    WHERE e.employee_id = 101;

    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
    DBMS_OUTPUT.PUT_LINE('Department Name: ' || v_department_name);
END;
/

Output:

Employee Name	Department Name
John Doe	IT

Explanation:

• In this example, the PL/SQL block retrieves data from the employees and departments tables using an INNER JOIN.
• The employee with employee_id 101 is associated with the department ‘IT’, and the result displays the employee name and department name.

Example 2: Retrieving Data with Subquery

Suppose a company wants to track its customers and their orders. They have two tables, customers and orders, where customers stores customer information and orders stores order details along with the customer ID. The company needs a PL/SQL block to retrieve the total order amount for a specific customer. The block should fetch the customer’s name and the total amount of all orders placed by that customer. If the customer has not placed any orders, the total order amount should be displayed as 0. The block should be able to handle situations where there are multiple customers with the same name.

Query:

-- -- PL/SQL Code
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_amount DECIMAL(10, 2),
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

INSERT INTO customers VALUES (1, 'John Doe');
INSERT INTO customers VALUES (2, 'Jane Smith');

INSERT INTO orders VALUES (101, 150.00, 1);
INSERT INTO orders VALUES (102, 200.00, 2);

-- PL/SQL Block to Retrieve Data with Subquery
DECLARE
    v_customer_name customers.customer_name%TYPE;
    v_total_order_amount DECIMAL(10, 2);
BEGIN
    SELECT c.customer_name,
           (SELECT SUM(o.order_amount) FROM orders o WHERE o.customer_id = c.customer_id)
    INTO v_customer_name, v_total_order_amount
    FROM customers c
    WHERE c.customer_id = 1;

    DBMS_OUTPUT.PUT_LINE('Customer Name: ' || v_customer_name);
    DBMS_OUTPUT.PUT_LINE('Total Order Amount: ' || v_total_order_amount);
END;
/

Output:

Customer Name	Total Order Amount
John Doe	150.00

Explanation:

• This example uses a subquery to retrieve the total order amount for a customer from the orders table.
• The PL/SQL block fetches the customer name John Doe (with customer_id 1) and calculates the total order amount, resulting in 150.00.

Conclusion

Retrieving data from multiple tables in PL/SQL is a fundamental aspect of database operations which enable users to combine and extract valuable information from various sources. Understanding JOIN operations, subqueries and other SQL constructs in PL/SQL is essential for effective data retrieval and manipulation. We have seen some examples which help us to understand the how to retrieve data from multiple tables in PL/SQL effectively.