SQL Lab Class 7 — PL/SQL Complete Notes
This document covers all topics from the SQL Lab Class 7 PL/SQL PDF, explaining each
section with syntax, examples, and detailed step-by-step explanations.
1. Warm-Up — Nested Queries and Table Aliases
These examples demonstrate the use of subqueries and aliases in SQL.
Example 1:
SELECT *
FROM PRODUCT, (SELECT * FROM REQUESTS WHERE QUANTITY > 2) P
WHERE [Link] = [Link];
Explanation: The inner subquery filters requests with quantity > 2. The outer query joins it
with PRODUCT on PRODUCTNO. The alias 'P' refers to the inner query.
Example 2:
SELECT [Link], [Link], [Link]
FROM (SELECT * FROM PRODUCT WHERE QTYONHAND > 2) P,
(SELECT * FROM PRODUCT WHERE UNITPRICE > 200) Q
WHERE [Link] = [Link];
Explanation: Both P and Q are filtered versions of PRODUCT. The result shows products
with QTYONHAND > 2 and UNITPRICE > 200.
2. PL/SQL Program — Cursor Example
PL/SQL (Procedural Language/SQL) allows procedural programming features in SQL. A
cursor is used to process query results row by row.
Structure of a PL/SQL Block:
DECLARE
-- Variable declaration
BEGIN
-- Executable statements
EXCEPTION
-- Error handling
END;
Example with Cursor (Corrected):
DECLARE
x INT;
p INT;
q INT;
CURSOR C IS
SELECT productno, unitprice, qtyonhand FROM product;
BEGIN
OPEN C;
LOOP
FETCH C INTO x, p, q;
EXIT WHEN C%NOTFOUND;
IF (p > 200) THEN
DBMS_OUTPUT.PUT_LINE(x || ' ' || p || ' ' || q);
ELSE
DBMS_OUTPUT.PUT_LINE('Low priced item ' || x);
END IF;
END LOOP;
CLOSE C;
END;
Explanation: Opens cursor C, fetches each row, checks condition, prints results, and closes
the cursor.
3. Discount Calculation
Calculate discounts and total discount across all products.
PL/SQL version:
DECLARE
discount NUMBER;
total_discount NUMBER := 0;
CURSOR C IS SELECT productno, unitprice FROM product;
pno NUMBER;
price NUMBER;
BEGIN
OPEN C;
LOOP
FETCH C INTO pno, price;
EXIT WHEN C%NOTFOUND;
IF price > 200 THEN
discount := price * 0.20;
ELSE
discount := price * 0.15;
END IF;
total_discount := total_discount + discount;
DBMS_OUTPUT.PUT_LINE('Product ' || pno || ' discount = ' || discount);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total discount across all products: ' || total_discount);
CLOSE C;
END;
Interactive SQL version:
SELECT productno, unitprice,
CASE
WHEN unitprice > 200 THEN unitprice * 0.20
ELSE unitprice * 0.15
END AS discount
FROM product;
SELECT SUM(
CASE
WHEN unitprice > 200 THEN unitprice * 0.20
ELSE unitprice * 0.15
END) AS total_discount
FROM product;
4. Exception Handling in PL/SQL
Example without exception block (may fail if multiple rows are returned):
DECLARE
qoh VARCHAR2(15);
BEGIN
SELECT productdesc INTO qoh
FROM product
WHERE qtyonhand > 2;
DBMS_OUTPUT.PUT_LINE('Product desc is: ' || qoh);
END;
Example with exception block (handles TOO_MANY_ROWS):
DECLARE
pd VARCHAR2(15);
BEGIN
SELECT productdesc INTO pd
FROM product
WHERE qtyonhand > 2;
DBMS_OUTPUT.PUT_LINE('Product desc is: ' || pd);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('Your SELECT retrieved multiple rows. Use a cursor
instead.');
END;
5. User-Defined Exception Handling
DECLARE
v_deptno NUMBER := 500;
v_name VARCHAR2(20) := 'Testing';
e_invalid_product EXCEPTION;
BEGIN
UPDATE product
SET productdesc = v_name
WHERE productno = v_deptno;
IF SQL%NOTFOUND THEN
RAISE e_invalid_product;
END IF;
ROLLBACK;
EXCEPTION
WHEN e_invalid_product THEN
DBMS_OUTPUT.PUT_LINE('No such PRODUCT');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
DBMS_OUTPUT.PUT_LINE(SQLCODE);
END;
6. Triggers
A trigger automatically executes when certain events occur (INSERT, UPDATE, DELETE).
Example Trigger:
CREATE OR REPLACE TRIGGER display_unitprice_changes
BEFORE DELETE OR INSERT OR UPDATE ON PRODUCT
FOR EACH ROW
WHEN (:[Link] > 0)
DECLARE
price_diff NUMBER;
BEGIN
price_diff := :[Link] - :[Link];
DBMS_OUTPUT.PUT_LINE('Old unitprice: ' || :[Link]);
DBMS_OUTPUT.PUT_LINE('New unitprice: ' || :[Link]);
DBMS_OUTPUT.PUT_LINE('Price difference: ' || price_diff);
END;
Testing the trigger:
INSERT INTO PRODUCT VALUES (17, 'New Table', 'Birch', 500, 10);
UPDATE PRODUCT SET unitprice = 600 WHERE productno = 17;
7. Stored Procedures and Functions
Procedure Example:
CREATE OR REPLACE PROCEDURE WC (USR IN VARCHAR) AS
BEGIN
DBMS_OUTPUT.PUT_LINE('WELCOME ' || USR);
END;
EXECUTE WC('ANUP');
Function Example:
CREATE OR REPLACE FUNCTION WCF (USR IN VARCHAR2)
RETURN VARCHAR2
AS
MSG VARCHAR(10);
BEGIN
MSG := 'WELCOME ';
RETURN(MSG || USR);
END;
DECLARE
X VARCHAR2(100);
BEGIN
X := WCF('ANUP');
DBMS_OUTPUT.PUT_LINE(X);
END;
Summary
Key PL/SQL Concepts:
Concept Description Example
PL/SQL Block Basic structure with DECLARE...END;
DECLARE, BEGIN,
EXCEPTION, END
Cursor Iterates through result rows CURSOR c IS SELECT...
Exception Handling Handles runtime errors WHEN TOO_MANY_ROWS
THEN
User-Defined Exception Manually raised exception RAISE e_invalid_product
Trigger Executes automatically on BEFORE INSERT OR
events UPDATE
Procedure Reusable subprogram (no CREATE PROCEDURE
return)
Function Reusable subprogram CREATE FUNCTION
(returns value)