← Back to Recording

Summary

Overview

This session is a comprehensive PL/SQL course covering core programming constructs, cursor management, exception handling, modular programming with procedures and functions, and package design. The instructor guides learners through hands-on coding exercises in an interactive SQL environment, emphasizing best practices for database operations, resource management, and scalable application design. Topics progress from basic loops and cursors to advanced concepts like overloading, encapsulation, and DDL logging, culminating in a real-world business case presentation on a badminton court franchise model that contextualizes the technical concepts.

Topic (Timeline)

1. Environment Setup & Basic PL/SQL Constructs [00:00:00 - 00:03:16]

  • Initial setup and connectivity check with participants.
  • Execution of basic PL/SQL code using Ctrl+Enter in an interactive tool.
  • Introduction to FOR loops: syntax (FOR counter IN start..end LOOP), automatic initialization and increment, and use cases for fixed iterations.
  • Demonstration of FOR loop with reverse iteration (REVERSE) to count down from higher to lower values.

2. While Loops and GOTO Statements [00:03:18 - 00:07:51]

  • Explanation of WHILE loops: condition-checked before each iteration, exits when condition becomes false.
  • Emphasis on WHILE loops for unknown iteration counts and dependency on dynamic conditions.
  • Introduction to GOTO statements: unconditional jump to labeled code sections within the same block.
  • Syntax and usage of labels (e.g., <<label_name>>), with example using GOTO end_loop to exit nested logic.
  • Caution against overuse of GOTO due to reduced readability and maintainability in structured programming.

3. Implicit vs Explicit Cursors [00:07:54 - 00:13:13]

  • Definition and distinction between implicit and explicit cursors.
  • Implicit cursors: automatically created by PL/SQL for single-row DML (INSERT, UPDATE, DELETE) and SELECT INTO statements; no manual management required.
  • Explicit cursors: manually declared by the programmer for multi-row queries; provide granular control over row processing.
  • Four-step lifecycle of explicit cursors: DECLARE, OPEN, FETCH, CLOSE.
  • Practical demonstration: declaring a cursor to select first_name and last_name from employees table.

4. Cursor Attributes and Practical Implementation [00:13:17 - 00:18:26]

  • Cursor attributes: %FOUND, %NOTFOUND, %ROWCOUNT, %ISOPEN.
  • Usage of %NOTFOUND to exit loops when no more rows are returned.
  • %ROWCOUNT to track number of fetched rows; used to limit output (e.g., display only first 5 employees).
  • %ISOPEN to verify cursor state before operations.
  • Hands-on exercise: modifying code to use %ROWCOUNT to cap output at 5 rows.
  • Emphasis on closing cursors to prevent resource leaks and performance degradation.

5. Cursor Lifecycle, Error Handling, and Best Practices [00:18:28 - 00:32:10]

  • Mandatory cursor closing: best practice even though PL/SQL auto-closes on block exit.
  • Closing cursors in exception handlers to prevent resource leaks.
  • Consequences of unclosed cursors: memory leaks, exceeding open cursor limits (ORA-01000), lock retention, performance degradation, and session instability.
  • Introduction to FOR cursor loops: automatic OPEN, FETCH, and CLOSE; eliminates manual cursor management.
  • Syntax: FOR record_var IN cursor_name LOOP.
  • Demonstration: using FOR cursor loop to iterate over employees and print names.

6. Cursor-Based Updates and Concurrency Control [00:32:10 - 00:48:46]

  • Updating rows via cursor using UPDATE ... WHERE CURRENT OF cursor_name.
  • FOR UPDATE clause: locks entire rows during cursor processing to prevent concurrent modifications.
  • FOR UPDATE OF column_list: locks only specified columns, allowing other columns to be modified by other sessions.
  • Importance of COMMIT to release locks and ensure data consistency.
  • Demonstration: updating employee salaries conditionally using cursor and WHERE CURRENT OF.
  • Concurrency control: preventing lost updates in multi-user environments.

7. Commit Strategies and Transaction Management [00:48:46 - 00:50:00]

  • Strategic use of COMMIT: row-by-row commits to reduce lock contention, or batch commits after N rows (e.g., every 10 rows using MOD(rowcount, 10) = 0).
  • Benefits: improved performance, reduced lock duration, and incremental data persistence.
  • Hands-on exercise: implementing conditional commit logic in a loop.
  • Error handling: FETCH OUT OF SEQUENCE error when cursor is closed prematurely; resolved with exception handling.

8. Validation, Conditional Logic, and Cursor Parameters [00:50:00 - 01:24:28]

  • Using IF statements for data validation (e.g., reject insert if salary < 3000).
  • IF-ELSIF-ELSE constructs for multi-condition logic.
  • Cursor parameters: enabling dynamic filtering by passing values at OPEN time (e.g., cursor emp_cur(p_dept_id NUMBER)).
  • Multiple parameters: combining department ID and minimum salary to filter results.
  • Reusability benefit: same cursor definition used with different parameters for varied queries.

9. Workshop: Custom Cursor Implementation [01:24:28 - 01:37:04]

  • 40-minute hands-on task: create a cursor with parameters (department ID and job title) to fetch and display matching employees.
  • Instructions: use Google Docs to write, test, and export code as PDF.
  • Instructor provides debugging tips: enable line numbers in IDE for easier error tracking.

10. Sequences, Views, Procedures, and Functions [01:37:04 - 02:40:35]

  • Creating sequences (CREATE SEQUENCE) for auto-incrementing primary keys (e.g., EMP_ID).
  • Creating views (CREATE VIEW) as virtual tables for simplified data access (e.g., high-salary employees).
  • Creating procedures: reusable blocks for DML operations (e.g., UPDATE_SALARY with IN parameters).
  • Creating functions: return a single computed value (e.g., CALCULATE_BONUS).
  • Best practices: functions should be deterministic, avoid DML, and handle exceptions.
  • Using DESCRIBE to inspect procedure/function signatures.

11. Parameter Modes: IN, OUT, IN OUT [02:40:35 - 03:22:23]

  • IN: input-only parameters.
  • OUT: return values from procedure (must be assigned within).
  • IN OUT: both input and output; retains initial value and returns modified value.
  • Demonstration: procedure with IN OUT parameter to increment a bonus value.
  • Debugging with DBMS_OUTPUT.PUT_LINE to trace execution flow.
  • Using bind variables in SQL*Plus to capture OUT parameter values.

12. Nested Functions, Default Parameters, and SQL Integration [03:22:23 - 03:40:51]

  • Nested function calls: one function invoking another (e.g., double_area calls calculate_area).
  • Default parameters: optional values (e.g., p_tax_rate NUMBER DEFAULT 10).
  • Using functions in SQL queries: must be deterministic (same input → same output).
  • Best practices: avoid complex or non-deterministic functions in SELECT to prevent performance issues.
  • Aggregations: SUM(CALCULATE_AREA(length, width)).

13. Packages: Structure, Encapsulation, and Benefits [03:40:51 - 04:31:30]

  • Package structure: specification (public interface) and body (implementation).
  • Public subprograms: declared in spec, accessible externally (e.g., add_employee, get_salary).
  • Private elements: declared in body only; hidden from external access (e.g., private variable be_private_var).
  • Benefits:
    • Encapsulation: protects internal logic.
    • Reusability: single load into memory reduces parsing overhead.
    • Performance: preloaded package reduces execution latency.
    • Security: grant/revoke access at package level.
    • Overloading: multiple subprograms with same name but different parameters.
    • Persistent state: public variables retain values across calls in a session.
    • Centralized error handling and documentation.

14. Package Dependencies, DDL Logging, and Exam Preparation [04:31:30 - 04:43:06]

  • DDL dependencies: dropping a function invalidates dependent objects (e.g., calculate_area if get_length is dropped).
  • Enabling DDL logging: ALTER SYSTEM SET ENABLE_DDL_LOGGING=TRUE to audit schema changes.
  • Exam instructions: 30-item multiple-choice quiz on PL/SQL concepts.
  • Submission: copy Google Doc, write name, bold answers, export as PDF, email to “mamcha” by 4:50 p.m.

15. Business Case: SSBC Badminton Franchise Model [04:43:06 - 05:40:20]

  • Presentation by Ray Villamar on SSBC (Shuttle Showdown Badminton Court) as a business venture.
  • Core features: Nataraflex flooring, smart lighting, RFID-based booking/payment system, certified BWF coaches.
  • Investment model: 50/50 co-ownership or franchise licensing; max 4 investors per location.
  • Financial projections: 5% monthly ROI (e.g., ₱500K investment → ₱25K/month), 2–3 year payback.
  • Long-term vision: build owned facilities (not rent), transition to corporation, franchise expansion.
  • Emphasis on community building, tournaments, and scalable operations.

Appendix

Key Principles

  • Cursor Management: Always CLOSE explicit cursors; prefer FOR cursor loops for automatic handling.
  • Exception Handling: Use EXCEPTION blocks to catch NO_DATA_FOUND, TOO_MANY_ROWS, ZERO_DIVIDE, and OTHERS.
  • Modular Design: Use procedures for DML, functions for calculations; avoid DML in functions.
  • Encapsulation: Use packages to group related logic; hide implementation details in package body.
  • Performance: Avoid non-deterministic functions in SQL queries; use DBMS_OUTPUT.PUT_LINE for debugging.
  • Concurrency: Use FOR UPDATE to lock rows during updates; always COMMIT to release locks.

Tools Used

  • Oracle SQL Developer or similar IDE
  • DBMS_OUTPUT.PUT_LINE for debugging
  • DESCRIBE to inspect procedure/function signatures
  • SELECT text FROM all_source to view package/function source code
  • ALTER SYSTEM SET ENABLE_DDL_LOGGING=TRUE for schema change auditing

Common Pitfalls

  • Forgetting to CLOSE cursors → memory leaks and ORA-01000 errors.
  • Using GOTO excessively → unreadable, unmanageable code.
  • Performing DML in functions → invalidates use in SQL queries.
  • Not using COMMIT after FOR UPDATE → locks persist, blocking other users.
  • Not handling NO_DATA_FOUND in SELECT INTO → unhandled exceptions.
  • Hardcoding values instead of using parameters → reduced reusability.

Practice Suggestions

  • Recreate all cursor examples with different filters (e.g., by department, salary range).
  • Implement a package with 2 procedures and 1 function; use private variables to track session state.
  • Write a function that calculates tax with default rate; test with and without parameter.
  • Simulate a multi-user scenario: one session updates with FOR UPDATE, another tries to modify same row.
  • Log DDL changes in a test schema and review the alert log.
  • Design a procedure that accepts IN, OUT, and IN OUT parameters; test with bind variables.