Query for Retrieving Data from multiple tables in PL/SQL

After utilizing the above concepts, we will now we write a query for retrieving data from multiple tables in PL/SQL. We will proceed with the mentioned steps only, which help you to understand the structure of the query thoroughly.

1. Creating ‘Customers’, ‘Products’ & ‘Orders’ Table using CREATE TABLE Statement

Query for Creating ‘Customers’ Table

CREATE TABLE customers (
    customer_id NUMBER PRIMARY KEY,
    customer_name VARCHAR2(100)
);

Query for Creating ‘Products’ Table

CREATE TABLE products (
    product_id NUMBER PRIMARY KEY,
    product_name VARCHAR2(100),
    price NUMBER
);

Query for Creating ‘Orders’ Table

CREATE TABLE orders (
    order_id NUMBER PRIMARY KEY,
    customer_id NUMBER,
    product_id NUMBER,
    order_date DATE,
    quantity NUMBER,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

2. Inserting values to the tables using INSERT INTO Statement

Inserting the values into ‘Customers’:

INSERT INTO customers VALUES (1, 'Custom-1');
INSERT INTO customers VALUES (2, 'Custom-2');

Output:

Inserting the values into ‘Products’:

INSERT INTO products VALUES (101, 'Product-A', 2000);
INSERT INTO products VALUES (102, 'Product-B', 3000);

Output:

Inserting the values into ‘Orders’:

INSERT INTO orders VALUES (1001, 1, 101, SYSDATE, 2);
INSERT INTO orders VALUES (1002, 1, 102, SYSDATE-1, 1);
INSERT INTO orders VALUES (1003, 2, 101, SYSDATE-2, 3);

Output:

3. Declaring the Variables

DECLARE
    v_customer_id NUMBER := 1; -- Specify the desired customer ID
    v_product_name products.product_name%TYPE;
    v_product_price products.price%TYPE;

4. Working with Cursor

BEGIN
    FOR order_rec IN (
        SELECT o.order_id, p.product_name, p.price, o.order_date, o.quantity
        FROM orders o
        JOIN products p ON o.product_id = p.product_id
        WHERE o.customer_id = v_customer_id
    ) 

5. Processing Cursor Results

LOOP
        -- Assign values to variables
        v_product_name := order_rec.product_name;
        v_product_price := order_rec.price;

        -- Display order details
        DBMS_OUTPUT.PUT_LINE('Order ID: ' || order_rec.order_id);
        DBMS_OUTPUT.PUT_LINE('Product Name: ' || v_product_name);
        DBMS_OUTPUT.PUT_LINE('Product Price: ' || v_product_price);
        DBMS_OUTPUT.PUT_LINE('Order Date: ' || TO_CHAR(order_rec.order_date, 'MM/DD/YYYY'));
        DBMS_OUTPUT.PUT_LINE('Quantity: ' || order_rec.quantity);
        DBMS_OUTPUT.PUT_LINE('---');
    END LOOP;
END;
/

Output:

Retrieving the data from the ‘Customers’, ‘Products’ and ‘Orders’ Table:

Second Record:

PL/SQL is “Procedural Language extensions to the Structured Query Language”. SQL is a popular language for both querying and updating data in relational database management systems (RDBMS). PL/SQL adds many procedural constructs to SQL language to overcome some limitations of SQL. In addition, PL/SQL provides a more comprehensive programming language solution for building mission-critical applications on Oracle Databases.

Retrieving data from multiple tables in PL/SQL mainly involves using SQL Joins, which allows to combination of rows from one or more tables based on the related column between them. Before going with the query of retrieving data from multiple tables make sure you have sufficient or basic knowledge about SQL joins.