Oracle PL/SQL Fundamentals

Module 1: PL/SQL Basics & Block Structure

• Anatomy of a PL/SQL block (declaration, executable, exception)
• Anonymous blocks vs named blocks
• Variables, constants, datatypes
• %TYPE, %ROWTYPE usage

Lab

• Write simple anonymous blocks
• Declare and initialize variables
• Use %TYPE and %ROWTYPE in blocks

Module 2: Control Structures & Loops

• Conditional logic (IF, CASE)
• Loop constructs: LOOP, WHILE, FOR
• EXIT, GOTO, nested loops

Lab

• Build blocks with IF and CASE
• Write loops to process sets of data
• Use EXIT, nested loops for real logic

Module 3: Cursors & Cursor Management

• Implicit vs explicit cursors
• Cursor FOR loops
• Parameterized cursors
• Cursor attributes (FOUND, NOTFOUND, %ROWCOUNT)

Lab

• Create explicit cursors over queries
• Use cursor FOR loops
• Parameterize cursors and fetch attributes

Module 4: Procedures & Functions

• Create procedures and functions
• IN, OUT, IN OUT parameters
• Deterministic vs non-deterministic functions
• Function calls in SQL

Lab

• Build stored procedures and functions
• Pass parameters and return values
• Call functions in SQL and PL/SQL contexts

Module 5: Packages & Modular PL/SQL Design

• Package specification and body
• Public vs private elements
• Initialization blocks
• Overloading and package state

Lab

• Design and build a package
• Encapsulate utility code
• Use overloading and initialization

Module 6: Exception Handling & Logging

• Predefined and user-defined exceptions
• RAISE, PRAGMA EXCEPTION_INIT
• Logging and error propagation
• WHEN OTHERS best practices

Lab

• Include exception blocks in procedures
• Define custom errors and map to codes
• Log errors to tables or files

Module 7: Collections, Records & Data Structures

• PL/SQL collections: nested tables, varrays, associative arrays
• Records and %ROWTYPE
• Multilevel data structures
• Using collections in programs

Lab

• Create and manipulate collections
• Use records to aggregate data
• Combine collections and records

Module 8: Bulk Processing & Performance Techniques

• FORALL, BULK COLLECT
• LIMIT clause
• Bulk binds and reducing context switches
• Dynamic SQL (EXECUTE IMMEDIATE, DBMS_SQL)

Lab

• Rework cursor logic using bulk operations
• Use FORALL and BULK COLLECT
• Write dynamic SQL blocks

Module 9: Advanced PL/SQL Features

• Autonomous transactions
• PRAGMA SERIALLY_REUSABLE, NOCOPY
• Optimizing PL/SQL: PLSQL_OPTIMIZE_LEVEL, profiling
• Security: invoker’s rights vs definer’s rights

Lab

• Build a routine with autonomous transactions
• Test NOCOPY and evaluate output
• Profile PL/SQL units
• Demonstrate privilege models