PL/SQL in Oracle 19C Training in Marina Bay, Singapore

This course is designed to introduce participants to PL/SQL, Oracle's procedural language extension to SQL, within the context of Oracle Database 19c. It covers the basics of PL/SQL syntax, control structures, exception handling, procedures, functions, packages, triggers, and advanced features like bulk operations and dynamic SQL. Through practical exercises, learners will develop the skills to write, debug, and optimize PL/SQL code effectively for real-world database applications.

Course Agenda

Introduction to PL/SQL

• What is PL/SQL?

• Benefits and architecture

• Anonymous blocks: DECLARE, BEGIN, EXCEPTION, END

PL/SQL Syntax and Variables

• Declaring variables and constants

• Data types and scope

• Assignments and expressions

Control Structures

• Conditional statements (IF-THEN-ELSE, CASE)

• Loops (LOOP, WHILE, FOR)

Cursors and Records

• Implicit vs. Explicit cursors

• Cursor FOR loops

• Working with records

Exception Handling

• Predefined and user-defined exceptions

• WHEN OTHERS clause

• Logging and re-raising exceptions

Procedures and Functions

• Creating stored procedures and functions

• IN, OUT, IN OUT parameters

• Calling and managing subprograms

Packages

• Benefits of packages

• Creating package specifications and bodies

• Using and managing package components

Triggers

• Trigger types and timing events

• Row-level and statement-level triggers

• Mutating tables and restrictions

Optimization and Best Practices

• PL/SQL compiler settings

• Performance tuning techniques

• Coding standards and modularization

Hands-on Exercises