PL/SQL Cursor Lab Exam: 30 Questions with Sample Database Schema for Practice

📘 PL/SQL Cursor – Complete Sample Schema & 30 Questions with Solutions

This post contains a complete database schema and 30 PL/SQL cursor questions with explanations and full solutions for final lab exam practice.

plsql cursor questions, plsql cursor lab exam, explicit cursor plsql, implicit cursor plsql, parameterized cursor, cursor attributes, plsql lab questions with solution, plsql final exam, plsql cursor for loop, nested cursor plsql, plsql exception cursor, database schema, sample tables plsql

🔹 Sample Database Schema

-- Product Table
CREATE TABLE Product (
  ProductID NUMBER PRIMARY KEY,
  ProductName VARCHAR2(50),
  CategoryID NUMBER,
  Price NUMBER
);

\-- Customer Table
CREATE TABLE Customer (
CustomerID NUMBER PRIMARY KEY,
CustomerName VARCHAR2(50),
CategoryID NUMBER
);

\-- Orders Table
CREATE TABLE Orders (
OrderID NUMBER PRIMARY KEY,
CustomerID NUMBER REFERENCES Customer(CustomerID),
OrderDate DATE,
OrderType VARCHAR2(20),
Status VARCHAR2(20)
);

\-- Invoice Table
CREATE TABLE Invoice (
InvoiceID NUMBER PRIMARY KEY,
OrderID NUMBER REFERENCES Orders(OrderID),
TotalAmount NUMBER,
PaymentStatus VARCHAR2(20),
Discount NUMBER
);

\-- InvoiceDetails Table
CREATE TABLE InvoiceDetails (
InvoiceID NUMBER REFERENCES Invoice(InvoiceID),
ProductID NUMBER REFERENCES Product(ProductID),
Quantity NUMBER
); 

🔹 PL/SQL Cursor Questions with Explanation & Solution (Q1–Q30)

Q1. Display all products using explicit cursor.
\-- Explanation: Use explicit cursor to fetch ProductName and Price.
DECLARE
CURSOR prod\_cur IS SELECT ProductName, Price FROM Product;
v\_prod Product.ProductName%TYPE;
v\_price Product.Price%TYPE;
BEGIN
OPEN prod\_cur;
LOOP
FETCH prod\_cur INTO v\_prod, v\_price;
EXIT WHEN prod\_cur%NOTFOUND;
DBMS\_OUTPUT.PUT\_LINE('Product: ' || v\_prod || ', Price: ' || v\_price);
END LOOP;
CLOSE prod\_cur;
END;
/

Q2. Customers with invoices above BDT 5000 using parameterized cursor.
DECLARE
CURSOR cust\_cur(p\_amount NUMBER) IS
SELECT c.CustomerName, i.TotalAmount
FROM Customer c JOIN Orders o ON c.CustomerID=o.CustomerID
JOIN Invoice i ON o.OrderID=i.OrderID
WHERE i.TotalAmount>p\_amount;
v\_name Customer.CustomerName%TYPE;
v\_total Invoice.TotalAmount%TYPE;
BEGIN
OPEN cust\_cur(5000);
LOOP
FETCH cust\_cur INTO v\_name, v\_total;
EXIT WHEN cust\_cur%NOTFOUND;
DBMS\_OUTPUT.PUT\_LINE('Customer: ' || v\_name || ', Total: ' || v\_total);
END LOOP;
CLOSE cust\_cur;
END;
/

Q3. Display all orders using cursor FOR loop.
BEGIN
FOR rec IN (SELECT o.OrderID, i.TotalAmount FROM Orders o JOIN Invoice i ON o.OrderID=i.OrderID) LOOP
DBMS\_OUTPUT.PUT\_LINE('Order: ' || rec.OrderID || ', Amount: ' || rec.TotalAmount);
END LOOP;
END;
/

Q4. Update product prices by 10% in category 1 using cursor FOR UPDATE.
DECLARE
CURSOR prod\_update\_cur IS SELECT ProductID, Price FROM Product WHERE CategoryID=1 FOR UPDATE;
v\_id Product.ProductID%TYPE;
v\_price Product.Price%TYPE;
BEGIN
OPEN prod\_update\_cur;
LOOP
FETCH prod\_update\_cur INTO v\_id, v\_price;
EXIT WHEN prod\_update\_cur%NOTFOUND;
UPDATE Product SET Price=v\_price\*1.10 WHERE CURRENT OF prod\_update\_cur;
END LOOP;
CLOSE prod\_update\_cur;
COMMIT;
END;
/

Q5. Count total invoices using cursor %ROWCOUNT.
DECLARE
CURSOR inv\_cur IS SELECT InvoiceID FROM Invoice;
v\_invoice Invoice.InvoiceID%TYPE;
BEGIN
OPEN inv\_cur;
LOOP
FETCH inv\_cur INTO v\_invoice;
EXIT WHEN inv\_cur%NOTFOUND;
END LOOP;
DBMS\_OUTPUT.PUT\_LINE('Total invoices: ' || inv\_cur%ROWCOUNT);
CLOSE inv\_cur;
END;
/

Q6. Display products sold more than 50 units using cursor.
DECLARE
CURSOR c1 IS SELECT ProductID, SUM(Quantity) AS total\_sold FROM InvoiceDetails GROUP BY ProductID HAVING SUM(Quantity)>50;
v\_id InvoiceDetails.ProductID%TYPE;
v\_qty NUMBER;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO v\_id, v\_qty;
EXIT WHEN c1%NOTFOUND;
DBMS\_OUTPUT.PUT\_LINE('ProductID: ' || v\_id || ', Sold: ' || v\_qty);
END LOOP;
CLOSE c1;
END;
/

Q7. Display all customers using implicit cursor.
BEGIN
FOR rec IN (SELECT \* FROM Customer) LOOP
DBMS\_OUTPUT.PUT\_LINE('CustomerID: ' || rec.CustomerID || ', Name: ' || rec.CustomerName);
END LOOP;
END;
/

Q8. Nested cursor to show orders with products.
DECLARE
CURSOR order\_cur IS SELECT OrderID FROM Orders;
v\_order Orders.OrderID%TYPE;
CURSOR prod\_cur(p\_orderID Orders.OrderID%TYPE) IS SELECT d.ProductID, d.Quantity FROM InvoiceDetails d JOIN Invoice i ON d.InvoiceID=i.InvoiceID WHERE i.OrderID=p\_orderID;
BEGIN
OPEN order\_cur;
LOOP
FETCH order\_cur INTO v\_order;
EXIT WHEN order\_cur%NOTFOUND;
DBMS\_OUTPUT.PUT\_LINE('OrderID: ' || v\_order);
FOR rec IN prod\_cur(v\_order) LOOP
DBMS\_OUTPUT.PUT\_LINE('  ProductID: ' || rec.ProductID || ', Qty: ' || rec.Quantity);
END LOOP;
END LOOP;
CLOSE order\_cur;
END;
/

Q9. Use %FOUND to check if cursor returned any customer.
DECLARE
CURSOR c1 IS SELECT \* FROM Customer WHERE CustomerID>100;
v\_name Customer.CustomerName%TYPE;
BEGIN
OPEN c1;
FETCH c1 INTO v\_name;
IF c1%FOUND THEN
DBMS\_OUTPUT.PUT\_LINE('Customer exists: ' || v\_name);
ELSE
DBMS\_OUTPUT.PUT\_LINE('No customer found');
END IF;
CLOSE c1;
END;
/

Q10. Delete products with price <50 --="" adding="" and="" begin="" c1="" close="" code="" commit="" continue="" current="" cursor.="" cursor="" declare="" delete="" end="" exit="" explanation="" fetch="" for="" format="" from="" full="" here="" id="" in="" into="" is="" loop="" of="" open="" pl="" price="" product.productid="" product="" productid="" q11="" same="" select="" the="" update="" using="" v="" when="" where="" with="">
DECLARE
  -- Cursor to select products with price < 50
  CURSOR c1 IS 
    SELECT ProductID 
    FROM Product 
    WHERE Price < 50 
    FOR UPDATE;
    
  v_id Product.ProductID%TYPE;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO v_id;
    EXIT WHEN c1%NOTFOUND;
    
    -- Delete the current row
    DELETE FROM Product WHERE CURRENT OF c1;
  END LOOP;
  
  CLOSE c1;
  COMMIT; -- Save changes
  DBMS_OUTPUT.PUT_LINE('All products with price < 50 have been deleted.');
END;
/

Q11. Cursor to display orders between two dates.
DECLARE
CURSOR c\_orders(p\_start DATE, p\_end DATE) IS SELECT OrderID, OrderDate FROM Orders WHERE OrderDate BETWEEN p\_start AND p\_end;
v\_order Orders.OrderID%TYPE;
v\_date Orders.OrderDate%TYPE;
BEGIN
OPEN c\_orders(TO\_DATE('2025-01-01','YYYY-MM-DD'), TO\_DATE('2025-06-30','YYYY-MM-DD'));
LOOP
FETCH c\_orders INTO v\_order, v\_date;
EXIT WHEN c\_orders%NOTFOUND;
DBMS\_OUTPUT.PUT\_LINE('OrderID: ' || v\_order || ', Date: ' || v\_date);
END LOOP;
CLOSE c\_orders;
END;
/

Q12. Cursor FOR loop to calculate total invoice per customer.
BEGIN
FOR rec IN (SELECT c.CustomerID, c.CustomerName, SUM(i.TotalAmount) AS TotalInvoice FROM Customer c JOIN Orders o ON c.CustomerID=o.CustomerID JOIN Invoice i ON o.OrderID=i.OrderID GROUP BY c.CustomerID, c.CustomerName) LOOP
DBMS\_OUTPUT.PUT\_LINE('Customer: ' || rec.CustomerName || ', TotalInvoice: ' || rec.TotalInvoice);
END LOOP;
END;
/

Q13. Parameterized cursor for products in a price range.
DECLARE
CURSOR c\_prod(p\_min NUMBER, p\_max NUMBER) IS SELECT ProductName, Price FROM Product WHERE Price BETWEEN p\_min AND p\_max;
v\_name Product.ProductName%TYPE;
v\_price Product.Price%TYPE;
BEGIN
OPEN c\_prod(100, 500);
LOOP
FETCH c\_prod INTO v\_name, v\_price;
EXIT WHEN c\_prod%NOTFOUND;
DBMS\_OUTPUT.PUT\_LINE('Product: ' || v\_name || ', Price: ' || v\_price);
END LOOP;
CLOSE c\_prod;
END;
/

Q14. Nested cursor to show customer orders with product names.
DECLARE
CURSOR c\_cust IS SELECT CustomerID, CustomerName FROM Customer;
v\_cid Customer.CustomerID%TYPE;
v\_cname Customer.CustomerName%TYPE;
CURSOR c\_order(p\_cid Customer.CustomerID%TYPE) IS SELECT o.OrderID FROM Orders o WHERE o.CustomerID=p\_cid;
v\_oid Orders.OrderID%TYPE;
CURSOR c\_prod(p\_oid Orders.OrderID%TYPE) IS SELECT p.ProductName FROM InvoiceDetails d JOIN Invoice i ON d.InvoiceID=i.InvoiceID JOIN Product p ON d.ProductID=p.ProductID WHERE i.OrderID=p\_oid;
BEGIN
OPEN c\_cust;
LOOP
FETCH c\_cust INTO v\_cid, v\_cname;
EXIT WHEN c\_cust%NOTFOUND;
DBMS\_OUTPUT.PUT\_LINE('Customer: ' || v\_cname);
FOR rec\_o IN c\_order(v\_cid) LOOP
DBMS\_OUTPUT.PUT\_LINE('  OrderID: ' || rec\_o.OrderID);
FOR rec\_p IN c\_prod(rec\_o.OrderID) LOOP
DBMS\_OUTPUT.PUT\_LINE('    Product: ' || rec\_p.ProductName);
END LOOP;
END LOOP;
END LOOP;
CLOSE c\_cust;
END;
/

Q15–Q30. (All remaining questions added in the same format as previous examples: explicit/implicit cursors, parameterized, nested cursors, updates/deletes, exception handling, cursor attributes %FOUND, %NOTFOUND, %ROWCOUNT, %ISOPEN.)

Q15. Cursor to check pending payments using %NOTFOUND.
DECLARE
CURSOR c\_pending IS SELECT InvoiceID FROM Invoice WHERE PaymentStatus='Pending';
v\_inv Invoice.InvoiceID%TYPE;
BEGIN
OPEN c\_pending;
FETCH c\_pending INTO v\_inv;
IF c\_pending%NOTFOUND THEN
DBMS\_OUTPUT.PUT\_LINE('No pending payments.');
ELSE
DBMS\_OUTPUT.PUT\_LINE('Pending InvoiceID: ' || v\_inv);
END IF;
CLOSE c\_pending;
END;
/

Q16. Cursor with exception handling for division by zero.
DECLARE
CURSOR c1 IS SELECT InvoiceID, TotalAmount, Discount FROM Invoice;
v\_inv Invoice.InvoiceID%TYPE;
v\_total Invoice.TotalAmount%TYPE;
v\_discount Invoice.Discount%TYPE;
v\_ratio NUMBER;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO v\_inv, v\_total, v\_discount;
EXIT WHEN c1%NOTFOUND;
BEGIN
v\_ratio := v\_total / v\_discount;
DBMS\_OUTPUT.PUT\_LINE('Invoice: ' || v\_inv || ', Ratio: ' || v\_ratio);
EXCEPTION
WHEN ZERO\_DIVIDE THEN
DBMS\_OUTPUT.PUT\_LINE('Invoice: ' || v\_inv || ', Discount is zero!');
END;
END LOOP;
CLOSE c1;
END;
/

Q17. Cursor to find top 5 selling products.
DECLARE
CURSOR c\_top IS SELECT ProductID, SUM(Quantity) AS total\_sold FROM InvoiceDetails GROUP BY ProductID ORDER BY total\_sold DESC;
v\_id InvoiceDetails.ProductID%TYPE;
v\_qty NUMBER;
v\_count NUMBER:=0;
BEGIN
OPEN c\_top;
LOOP
FETCH c\_top INTO v\_id, v\_qty;
EXIT WHEN c\_top%NOTFOUND OR v\_count=5;
DBMS\_OUTPUT.PUT\_LINE('ProductID: ' || v\_id || ', Sold: ' || v\_qty);
v\_count := v\_count+1;
END LOOP;
CLOSE c\_top;
END;
/

Q18. Cursor FOR loop to display order totals per category.
BEGIN
FOR rec IN (SELECT p.CategoryID, SUM(d.Quantity\*p.Price) AS TotalAmount FROM InvoiceDetails d JOIN Product p ON d.ProductID=p.ProductID GROUP BY p.CategoryID) LOOP
DBMS\_OUTPUT.PUT\_LINE('CategoryID: ' || rec.CategoryID || ', TotalAmount: ' || rec.TotalAmount);
END LOOP;
END;
/

Q19. Cursor to calculate average invoice amount.
DECLARE
CURSOR c\_avg IS SELECT TotalAmount FROM Invoice;
v\_total Invoice.TotalAmount%TYPE;
v\_sum NUMBER:=0;
v\_count NUMBER:=0;
v\_avg NUMBER;
BEGIN
OPEN c\_avg;
LOOP
FETCH c\_avg INTO v\_total;
EXIT WHEN c\_avg%NOTFOUND;
v\_sum := v\_sum + v\_total;
v\_count := v\_count +1;
END LOOP;
CLOSE c\_avg;
IF v\_count>0 THEN
v\_avg := v\_sum / v\_count;
DBMS\_OUTPUT.PUT\_LINE('Average Invoice Amount: ' || v\_avg);
ELSE
DBMS\_OUTPUT.PUT\_LINE('No invoices found');
END IF;
END;
/

Q20. Parameterized cursor to list orders above given amount.
DECLARE
CURSOR c\_orders(p\_min NUMBER) IS SELECT OrderID, TotalAmount FROM Invoice WHERE TotalAmount>p\_min;
v\_oid Invoice.OrderID%TYPE;
v\_amt Invoice.TotalAmount%TYPE;
BEGIN
OPEN c\_orders(3000);
LOOP
FETCH c\_orders INTO v\_oid, v\_amt;
EXIT WHEN c\_orders%NOTFOUND;
DBMS\_OUTPUT.PUT\_LINE('OrderID: ' || v\_oid || ', Amount: ' || v\_amt);
END LOOP;
CLOSE c\_orders;
END;
/

Q21. Cursor to update order status for shipped orders.
DECLARE
CURSOR c\_ship IS SELECT OrderID FROM Orders WHERE Status='Shipped' FOR UPDATE;
v\_oid Orders.OrderID%TYPE;
BEGIN
OPEN c\_ship;
LOOP
FETCH c\_ship INTO v\_oid;
EXIT WHEN c\_ship%NOTFOUND;
UPDATE Orders SET Status='Completed' WHERE CURRENT OF c\_ship;
END LOOP;
CLOSE c\_ship;
COMMIT;
END;
/

Q22. Cursor to delete canceled orders.
DECLARE
CURSOR c\_del IS SELECT OrderID FROM Orders WHERE Status='Canceled' FOR UPDATE;
v\_oid Orders.OrderID%TYPE;
BEGIN
OPEN c\_del;
LOOP
FETCH c\_del INTO v\_oid;
EXIT WHEN c\_del%NOTFOUND;
DELETE FROM Orders WHERE CURRENT OF c\_del;
END LOOP;
CLOSE c\_del;
COMMIT;
END;
/

Q23. Cursor FOR loop to display all order types.
BEGIN
FOR rec IN (SELECT DISTINCT OrderType FROM Orders) LOOP
DBMS\_OUTPUT.PUT\_LINE('OrderType: ' || rec.OrderType);
END LOOP;
END;
/

Q24. Cursor to calculate total quantity sold per product.
DECLARE
CURSOR c\_qty IS SELECT ProductID, SUM(Quantity) AS TotalQty FROM InvoiceDetails GROUP BY ProductID;
v\_pid InvoiceDetails.ProductID%TYPE;
v\_total NUMBER;
BEGIN
OPEN c\_qty;
LOOP
FETCH c\_qty INTO v\_pid, v\_total;
EXIT WHEN c\_qty%NOTFOUND;
DBMS\_OUTPUT.PUT\_LINE('ProductID: ' || v\_pid || ', Total Sold: ' || v\_total);
END LOOP;
CLOSE c\_qty;
END;
/

Q25. Nested cursor to display invoice and customer info.
DECLARE
CURSOR c\_inv IS SELECT InvoiceID, OrderID FROM Invoice;
v\_inv Invoice.InvoiceID%TYPE;
v\_oid Invoice.OrderID%TYPE;
CURSOR c\_cust(p\_oid Orders.OrderID%TYPE) IS SELECT c.CustomerName FROM Customer c JOIN Orders o ON c.CustomerID=o.CustomerID WHERE o.OrderID=p\_oid;
v\_cname Customer.CustomerName%TYPE;
BEGIN
OPEN c\_inv;
LOOP
FETCH c\_inv INTO v\_inv, v\_oid;
EXIT WHEN c\_inv%NOTFOUND;
DBMS\_OUTPUT.PUT\_LINE('InvoiceID: ' || v\_inv);
FOR rec IN c\_cust(v\_oid) LOOP
DBMS\_OUTPUT.PUT\_LINE('  Customer: ' || rec.CustomerName);
END LOOP;
END LOOP;
CLOSE c\_inv;
END;
/

Q26. Cursor to display invoices without payments.
DECLARE
CURSOR c\_unpaid IS SELECT InvoiceID FROM Invoice WHERE PaymentStatus='Pending';
v\_inv Invoice.InvoiceID%TYPE;
BEGIN
OPEN c\_unpaid;
LOOP
FETCH c\_unpaid INTO v\_inv;
EXIT WHEN c\_unpaid%NOTFOUND;
DBMS\_OUTPUT.PUT\_LINE('Unpaid InvoiceID: ' || v\_inv);
END LOOP;
CLOSE c\_unpaid;
END;
/

Q27. Cursor with %ISOPEN to check status.
DECLARE
CURSOR c1 IS SELECT OrderID FROM Orders;
BEGIN
OPEN c1;
IF c1%ISOPEN THEN
DBMS\_OUTPUT.PUT\_LINE('Cursor is open');
END IF;
CLOSE c1;
IF NOT c1%ISOPEN THEN
DBMS\_OUTPUT.PUT\_LINE('Cursor is closed');
END IF;
END;
/

Q28. Parameterized cursor to list customers of a category.
DECLARE
CURSOR c\_cat(p\_cid NUMBER) IS SELECT CustomerID, CustomerName FROM Customer WHERE CategoryID=p\_cid;
v\_id Customer.CustomerID%TYPE;
v\_name Customer.CustomerName%TYPE;
BEGIN
OPEN c\_cat(2);
LOOP
FETCH c\_cat INTO v\_id, v\_name;
EXIT WHEN c\_cat%NOTFOUND;
DBMS\_OUTPUT.PUT\_LINE('CustomerID: ' || v\_id || ', Name: ' || v\_name);
END LOOP;
CLOSE c\_cat;
END;
/

Q29. Cursor FOR loop to calculate grand total of all invoices.
DECLARE
v\_total NUMBER:=0;
BEGIN
FOR rec IN (SELECT TotalAmount FROM Invoice) LOOP
v\_total := v\_total + rec.TotalAmount;
END LOOP;
DBMS\_OUTPUT.PUT\_LINE('Grand Total: ' || v\_total);
END;
/

Q30. Nested cursor with exception handling to display all details.
DECLARE
CURSOR c\_order IS SELECT OrderID, CustomerID FROM Orders;
v\_oid Orders.OrderID%TYPE;
v\_cid Orders.CustomerID%TYPE;
CURSOR c\_prod(p\_oid Orders.OrderID%TYPE) IS SELECT ProductID, Quantity FROM InvoiceDetails WHERE InvoiceID=p\_oid;
v\_pid InvoiceDetails.ProductID%TYPE;
v\_qty InvoiceDetails.Quantity%TYPE;
BEGIN
OPEN c\_order;
LOOP
FETCH c\_order INTO v\_oid, v\_cid;
EXIT WHEN c\_order%NOTFOUND;
DBMS\_OUTPUT.PUT\_LINE('OrderID: ' || v\_oid || ', CustomerID: ' || v\_cid);
BEGIN
FOR rec\_p IN c\_prod(v\_oid) LOOP
DBMS\_OUTPUT.PUT\_LINE('  ProductID: ' || rec\_p.ProductID || ', Qty: ' || rec\_p.Quantity);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS\_OUTPUT.PUT\_LINE('  Error fetching products for OrderID: ' || v\_oid);
END;
END LOOP;
CLOSE c\_order;
END;
/