PL/SQL

Module 1: Introduction to PL/SQL

• What is PL/SQL and why it’s needed

• Differences between SQL and PL/SQL

• Advantages of PL/SQL (performance, modularity, security)

• Understanding PL/SQL architecture (Block structure, Engine, etc.)

• Anonymous blocks and the PL/SQL execution environment

• Hands-on: Writing your first PL/SQL program

Module 2: PL/SQL Block Structure

• Structure of a PL/SQL block:

  • DECLARE (declaration section)

  • BEGIN (executable section)

  • EXCEPTION (error handling section)

  • END (termination)

• Understanding variables, constants, and datatypes

• Using bind variables and substitution variables

• Hands-on: Writing and executing simple blocks in SQL Developer

Module 3: Variables and Data Types

• Scalar and composite data types

• Anchored declarations (%TYPE, %ROWTYPE)

• Assignments and expressions

• Using substitution variables

• Hands-on: Working with different data types

Module 4: Control Structures

• Conditional statements:

  • IF...THEN

  • IF...THEN...ELSE

  • ELSIF and nested conditions

• Looping structures:

  • LOOP, WHILE LOOP, FOR LOOP

  • EXIT, CONTINUE

• Hands-on: Writing PL/SQL programs using decision and looping constructs

Module 5: Cursors

• What is a cursor?

• Implicit Cursors (for SELECT INTO, DML statements)

• Explicit Cursors:

  • Declaring, opening, fetching, and closing

  • Cursor attributes (%FOUND, %NOTFOUND, %ROWCOUNT, %ISOPEN)

• Cursor FOR loops

• Hands-on: Retrieving data using explicit cursors

Module 6: Exception Handling

• What are exceptions?

• Types of exceptions:

  • Predefined exceptions (NO_DATA_FOUND, TOO_MANY_ROWS, etc.)

  • User-defined exceptions

• Raising exceptions using RAISE and RAISE_APPLICATION_ERROR

• Hands-on: Handling and debugging PL/SQL exceptions

Module 7: Records and Collections

• Introduction to records (%ROWTYPE, custom RECORD types)

• Introduction to collections:

  • Associative arrays

  • Nested tables

  • VARRAYs

• Iterating over collections

• Hands-on: Working with records and arrays

Module 8: Stored Procedures and Functions

• Difference between anonymous and named blocks

• Creating and executing procedures

• Creating and executing functions

• Parameter modes: IN, OUT, IN OUT

• Using stored programs inside SQL statements

• Hands-on: Writing and invoking procedures and functions

Module 9: Packages

• What are packages and why use them?

• Package specification vs body

• Creating and calling packaged procedures and functions

• Advantages of packages (modularity, encapsulation)

• Hands-on: Building a package for business logic

Module 10: Triggers

• What are triggers and their purpose?

• Types of triggers:

  • Row-level vs Statement-level

  • BEFORE, AFTER, INSTEAD OF

• Creating triggers for:

  • Auditing

  • Data validation

  • Logging changes

• Hands-on: Writing and testing row-level triggers