Description

Description

Introduction to Oracle PL/SQL is a hands-on course designed for those who already understand Oracle SQL and want to dive into the powerful procedural capabilities of PL/SQL. This course teaches the core concepts and structures of PL/SQL programming and equips learners to develop robust, secure, and maintainable applications within the Oracle Database environment.

Participants will explore key PL/SQL features, including blocks, variables, control structures, subprograms (procedures and functions), exception handling, cursors, packages, and triggers. Each concept is reinforced with practical exercises and real-world examples to solidify understanding and build real development skills.

Training Objectives

• Understand the structure and purpose of PL/SQL blocks
• Declare and manage variables and data types
• Use conditional and loop control structures
• Write and execute procedures and functions
• Handle exceptions and errors gracefully
• Work with implicit and explicit cursors for data retrieval
• Create reusable code units with packages
• Define triggers to automate database actions
• Key Benefits
• Hands-On Learning: Apply what you learn through guided labs and real-world scenarios
• Build Application Logic: Go beyond SQL to build procedural logic directly in the Oracle Database
• Improve Maintainability: Write modular reusable code with PL/SQL subprograms and packages
• Boost Performance and Security: Use PL/SQL to execute complex logic securely and efficiently Career Growth: Build a strong foundation for advanced Oracle development and DBA roles

Course Outline

• PL/SQL Basics<br />
• The HR Schema<br />
• What is PL/SQL?<br />
• Blocks<br />
• Outputting Information<br />
• Variables and Constants<br />
• Constants<br />
• Data Types<br />
• Naming Variables and Other Elements<br />
• Embedding SQL in PL/SQL<br />
• SELECT…INTO and RETURNING…INTO<br />
• Using Variables (Exercise)<br />
• PL/SQL Features<br />
• Subprograms<br />
• Introduction to Subprograms<br />
• Procedures<br />
• Variable Declarations<br />
• Parameters<br />
• Parameters with Default Values<br />
• Parameter Modes<br />
• IN Mode<br />
• OUT Mode<br />
• IN OUT Mode<br />
• Named Notation<br />
• Using SQL in a Subprogram<br />
• %TYPE<br />
• Creating a Procedure (Exercise)<br />
• Functions<br />
• Creating a Function (Exercise)<br />
• Using PL/SQL Functions in SQL Queries<br />
• Dropping a Subprogram<br />
• Conditional Processing<br />
• Conditions and Booleans<br />
• IF-ELSIF-ELSE Conditions<br />
• Creating a get_age() Function (Exercise)<br />
• ELSIF<br />
• Creating a check_rights() Procedure (Exercise)<br />
• Creating an is_manager() Function (Exercise)<br />
• BOOLEAN Values and Standard SQL<br />
• The CASE Statement<br />
• CASE Expressions<br />
• Replacing the Head Honcho (Exercise)<br />
• Exceptions<br />
• Introduction to Exceptions<br />
• Predefined Exceptions<br />
• The EXCEPTION Part of the Block<br />
• Catching NO_DATA_FOUND Exception (Exercise)<br />
• User-defined Exceptions<br />
• User-defined Exceptions in Subprograms<br />
• Re-raising Exceptions<br />
• Replacing the Head Honcho (revisited) (Exercise)<br />
• Adding Exceptions to update_employee_manager() (Exercise)<br />
• Naming Unnamed Predefined Exceptions<br />
• WHILE Loops<br />
• When to Use Exceptions<br />
• Cursors<br />
• Implicit Cursors<br />
• Using Implicit Cursor Attributes (Exercise)<br />
• Explicit Cursors<br />
• %ROWTYPE<br />
• Explicit Cursor Use Case<br />
• Cursor FOR LOOP<br />
• Using an Explicit Cursor (Exercise)<br />
• Cursor Parameters<br />
• Packages<br />
• Package Basics<br />
• The Package Specification<br />
• The Package Body<br />
• Modifying the Package (Exercise)<br />
• Building an Employee Package<br />
• Adding a get_manager() Function (Exercise)<br />
• Overloading Subprograms<br />
• Adding Overloaded Functions to the Package (Exercise)<br />
• Auditing<br />
• Validation Procedures<br />
• Adding a Validation Procedure (Exercise)<br />
• Package Cursors<br />
• Adding a Cursor to the Package (Exercise)<br />
• Benefits of Packages<br />
• Triggers<br />
• What are Triggers?<br />
• Trigger Parts<br />
• Validation Triggers<br />
• Creating a Trigger on the jobs Table (Exercise)<br />
• The WHEN Clause<br />
• Using the WHEN Clause (Exercise)<br />
• Audit Triggers<br />
• Statement-level Triggers<br />
• Compound Triggers<br />
• Trigger Warning