Database Management Systems
Unit 5: PL/SQL Programming
Blocks, Variables, Cursors, Stored Procedures, Functions, Triggers & Exception Handling — turning SQL into programmable enterprise logic.
🏢 Oracle PL/SQL & PostgreSQL PL/pgSQL | 📝 15 MCQs (Bloom's) | 🔬 5 Lab Exercises | 💼 Interview Prep
Why This Chapter Pays Your Salary
SQL is declarative — you tell the database what you want. PL/SQL is procedural — you tell the database how to do it. Every bank, insurance company, telecom, and government system in India runs on PL/SQL stored procedures and triggers. SBI alone has 50,000+ stored procedures managing ₹45 lakh crore in assets. If you work in enterprise IT in India, PL/SQL is not optional.
🏢 Industry Snapshot
SBI Core Banking — Every ATM withdrawal triggers a PL/SQL procedure: validate PIN, check balance, debit account, log transaction, update mini-statement — all in one atomic database call. If any step fails, the exception handler rolls back everything. Zero partial updates.
IRCTC — The Tatkal booking engine uses PL/SQL cursors to loop through waitlisted passengers when a cancellation occurs: fetch next waitlisted PNR, confirm seat, send SMS, update status — all inside a stored procedure that runs in <100ms.
LIC — Premium calculation, bonus declaration, maturity payout — all PL/SQL packages. A single pkg_policy.calculate_maturity function handles 40+ policy types with complex actuarial logic.
Learning Outcomes — Bloom's Taxonomy
| Bloom's Level | Outcome Statement |
|---|---|
| L1 — Remember | List the three sections of a PL/SQL block; recall cursor types; name predefined Oracle exceptions |
| L2 — Understand | Explain the difference between implicit and explicit cursors; describe how exception propagation works; distinguish procedures from functions |
| L3 — Apply | Write PL/SQL blocks with variables, loops, cursors, and exception handlers; create stored procedures, functions, and triggers for business logic |
| L4 — Analyze | Debug PL/SQL code with cursor attribute checks; analyze trigger execution order (BEFORE/AFTER, ROW/STATEMENT); trace exception propagation through nested blocks |
| L5 — Evaluate | Evaluate when to use procedures vs functions vs triggers; justify cursor FOR loop over explicit OPEN/FETCH/CLOSE; assess trigger design for audit systems |
| L6 — Create | Design a complete PL/SQL package for a banking system with procedures, functions, cursors, exceptions, and triggers working together |