How to Retrieve Data from Multiple Tables in PL/SQL

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.

Steps for Retrieving data from multiple tables in PL/SQL

Step 1: CREATE TABLE Statement

The PL/SQL CREATE TABLE statement allows you to create and define a table.

Syntax:

The syntax for the CREATE TABLE statement in Oracle/PLSQL is:

CREATE TABLE table_name
( 
  column1 constraints,
  column2 constraints,
  ...
  column_n constraints
);

Step 2: INSERT Statement

The Oracle/PLSQL INSERT statement is used to insert a single record or multiple records into a table in Oracle.

Syntax:

The syntax for the Oracle/PLSQL INSERT statement when inserting a single record using the VALUES keyword is:

INSERT INTO table_name
(column1, column2, ... column_n )
VALUES
(expression1, expression2, ... expression_n );

The syntax for the Oracle INSERT statement when inserting multiple records using a SELECT statement is:

INSERT INTO table_name
(column1, column2, ... column_n )
SELECT expression1, expression2, ... expression_n
FROM source_table
[WHERE conditions];

PS: expressions are nothing but values that we want to insert into the table.

Step 3: SQL Queries with Joins to Retrieve Data from Tables

Oracle JOINS are used to retrieve data from multiple tables. An Oracle JOIN is performed whenever two or more tables are joined in an SQL statement.

There are 4 different types of Oracle joins:

• INNER JOIN
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN

Visual Representation

JOINS

Step 4: Declaration of variables within a PL/SQL Block

You must have to declare the PL/SQL variables in the declaration section as global variables. After the declaration, the variable’s name is associated with storage location, and variable’s value is allocated by PL/SQL in memory.

Syntax:

DECLARE
         variable_name datatype;
         -- additional variable's declaration
BEGIN
        -- PL/SQL statements here
END;
/

Step 5: Working with Cursor

A PL/SQL cursor is a pointer that points to the result set of an SQL query against database tables. The following picture describes steps that you need to follow when you work with PL/SQL Cursor :

Cursors

Syntax:

CURSOR cursor_name [([parameter_1 [, parameter_2...])]
            [RETURN return_specification]
IS sql_select_statements
[FOR UPDATE [OF [Column-_list]];

Step 6: Retrieving the Data from the SQL query using the PL/SQL loop

In this step, we use cursor to retrieve and process data that is returned by SQL queries. Since the cursor provides mechanism for iterating over the rows of a result set and enables us to perform operations on each row individually.

Step 7: End of PL/SQL block

The end of PL/SQL block is marked by ‘END’ statement. This represents the conclusion of the block’s executable section.

Syntax:

DECLARE
     -- variable declaration
BEGIN
      -- PL/SQL Statements
      -- End of executive section
END;
/

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:

Inserted data into ‘Customers’

Inserting the values into ‘Products’:

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

Output:

Inserted data into ‘Products’

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:

Inserted data into ‘Orders’

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:

First Record

Second Record:

Second Record

Conclusion

So, by this, we have successfully demonstrated the PL/SQL query for retrieving the data from multiple tables using cursors within the PL/SQL loop. Additionally, we utilized the concept of JOINS to connect the tables based on their common keys, which allows us to extract the relevant information from the database. At last, by utilizing the DBMS_OUTPUT.PUT_LINE procedure, we represented the Order details in an obvious and organized manner.