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:
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.