Oracle Database: PL/SQL Workshop

Overview

This course provides a comprehensive guide to Oracle Database 19c PL/SQL programming. Participants will explore PL/SQL's capabilities to manage data, automate tasks, and develop scalable database solutions. Through a blend of theoretical concepts and practical exercises, learners will gain the skills to write robust, efficient, and maintainable PL/SQL programs.

Prerequisites

• A basic understanding of relational database concepts.
• Familiarity with SQL and basic querying techniques.
• Prior experience with Oracle SQL Developer is recommended but not required

This course is ideal for:

• Database developers and administrators seeking to enhance their PL/SQL programming skills.
• Application developers who need to integrate PL/SQL with their software solutions.
• IT professionals responsible for managing Oracle-based systems and automating database processes.

Objectives

By the end of this course, participants will be able to:

• Understand the limitations of SQL and the benefits of PL/SQL.
• Write efficient PL/SQL anonymous blocks and manage variable scope.
• Use SQL statements within PL/SQL to interact with database objects.
• Implement control structures for procedural logic.
• Develop robust exception-handling mechanisms.
• Create modular subprograms using stored procedures and functions.
• Utilise advanced PL/SQL features like triggers, packages, and dynamic SQL.
• Debug and optimise PL/SQL code for better performance.

Outline

Module one: Introduction to PL/SQL

• Overview of PL/SQL runtime architecture.
• Understanding PL/SQL block structure.
• Benefits of using PL/SQL for database operations.

Module two: Declaring PL/SQL variables

• Variable declaration, initialization, and scope.
• Using the %TYPE and %ROWTYPE attributes.
• Working with composite data types.

Module three: Writing executable statements

• Lexical units and PL/SQL block syntax.
• SQL functions and sequences in PL/SQL.
• Best practices for writing readable and maintainable code.

Module four: Using SQL statements within PL/SQL

• Executing SELECT, INSERT, UPDATE, and DELETE within PL/SQL.
• Managing cursors and SQL cursor attributes.
• Handling naming ambiguities in SQL statements.

Module five: Writing control structures

• Conditional statements (IF, CASE).
• Loops (FOR, WHILE, BASIC).
• Nested and labelled loops with scope management.

Module six: Exception handling in PL/SQL

• Types of exceptions (predefined, user-defined).
• Using RAISE and RAISE_APPLICATION_ERROR.
• Guidelines for structured exception management.

Module seven: Creating and managing subprograms

• Differences between procedures and functions.
• Parameter modes and their applications (IN, OUT, IN OUT).
• Best practices for modular program design.

Module eight: Advanced PL/SQL features

• Creating and using packages for modular programming.
• Writing and managing triggers.
• Implementing dynamic SQL with EXECUTE IMMEDIATE.

Module nine: Debugging and performance tuning

• Using Oracle SQL Developer for debugging.
• Managing dependencies and recompilation.
• Optimising PL/SQL code using compiler warnings.