ORACLE PL/SQL Fundamentals Training Course

Introduction

• Course Goals and Learning Outcomes
• Schedule Overview
• Participant Introductions
• Entry Requirements
• Roles and Responsibilities

SQL Tooling

• Objectives for the Module
• Overview of SQL Developer
• Establishing Connections in SQL Developer
• Inspecting Table Metadata
• Executing Queries using SQL in SQL Developer
• Logging into SQL*Plus
• Direct Database Connection
• Navigating the SQL*Plus Interface
• Terminating a Session
• Core SQL*Plus Commands
• The SQL*Plus Environment
• Understanding the SQL*Plus Prompt
• Retrieving Table Information
• Accessing Help Documentation
• Executing SQL Script Files
• iSQL*Plus and Entity Modeling
• The ORDERS Dataset
• The FILM Dataset
• Course Materials Reference
• SQL Syntax Standards
• Advanced SQL*Plus Commands

Understanding PL/SQL

• Definition of PL/SQL
• Benefits of Using PL/SQL
• Code Block Architecture
• Outputting Messages
• Code Examples
• Configuring SERVEROUTPUT
• Update Demonstrations and Style Conventions

Variables

• Variable Concepts
• Data Types
• Initializing Variables
• Constants
• Scope: Local vs. Global
• Using %Type Variables
• Substitution Variables
• Adding Comments with &
• The Verify Option
• Managing && Variables
• Defining and Undefining Variables

The SELECT Statement

• Syntax and Usage
• Assigning Values to Variables
• Using %Rowtype Variables
• The CHR Function
• Independent Practice
• Defining PL/SQL Records
• Declaration Examples

Conditional Logic

• The IF Structure
• Integration with SELECT
• Independent Practice
• The CASE Structure

Error Handling

• Understanding Exceptions
• System-Defined Errors
• Retrieving Error Codes and Messages
• Handling NO_DATA_FOUND
• Raising Custom Exceptions
• Raising Application Errors
• Catching Undefined Exceptions
• Using PRAGMA EXCEPTION_INIT
• Transaction Control: Commit and Rollback
• Independent Practice
• Nested Code Blocks
• Practical Workshop

Iteration and Loops

• Basic Loop Statements
• While Loops
• For Loops
• Goto Statements and Labels

Cursors

• Cursor Concepts
• Accessing Cursor Attributes
• Explicit Cursor Management
• Demonstrations of Explicit Cursors
• Cursor Declaration
• Variable Declaration
• Opening and Fetching the First Row
• Fetching Subsequent Rows
• Exit Conditions with %Notfound
• Closing the Cursor
• For Loop Applications Part I
• For Loop Applications Part II
• Data Update Demonstrations
• Using FOR UPDATE
• Specifying Columns with FOR UPDATE OF
• Targeting Rows with WHERE CURRENT OF
• Committing Changes with Cursors
• Validation Scenarios Part I
• Validation Scenarios Part II
• Passing Parameters to Cursors
• Practical Workshop
• Workshop Solutions

Procedures, Functions, and Packages

• The CREATE Statement
• Managing Parameters
• Writing the Procedure Body
• Error Reporting
• Viewing Procedure Details
• Invoking Procedures
• Executing Procedures via SQL*Plus
• Utilizing OUT Parameters
• Calling with OUT Parameters
• Developing Functions
• Function Examples
• Error Reporting
• Viewing Function Details
• Invoking Functions
• Executing Functions via SQL*Plus
• Principles of Modular Programming
• Procedure Examples
• Function Invocation
• Using Functions Within IF Structures
• Constructing Packages
• Package Case Studies
• Advantages of Packages
• Public and Private Sub-programs
• Error Reporting
• Viewing Package Details
• Calling Packages from SQL*Plus
• Invoking Packages from Sub-programs
• Removing Sub-programs
• Locating Sub-programs
• Building a Debugging Package
• Using the Debugging Package
• Positional and Named Parameter Notation
• Default Parameter Values
• Recompiling Procedures and Functions
• Practical Workshop

Triggers

• Creating Triggers
• Statement-Level Triggers
• Row-Level Triggers
• Applying WHEN Conditions
• Conditional Triggers using IF
• Error Reporting
• Commit Behavior in Triggers
• Usage Restrictions
• Mutating Table Triggers
• Identifying Triggers
• Dropping Triggers
• Auto-Numbering Generation
• Disabling Triggers
• Enabling Triggers
• Naming Conventions for Triggers

Sample Data

• ORDER Dataset
• FILM Dataset
• EMPLOYEE Dataset

Dynamic SQL

• Executing SQL within PL/SQL
• Binding Techniques
• Dynamic SQL Concepts
• Native Dynamic SQL
• Handling DDL and DML
• The DBMS_SQL Package
• Dynamic SELECT Statements
• Dynamic SELECT Procedures

File Operations

• Working with Text Files
• The UTL_FILE Package
• Write and Append Demonstrations
• Read Demonstrations
• Trigger-Based File Examples
• DBMS_ALERT Packages
• DBMS_JOB Packages

Collections

• %Type Variable Usage
• Record Variables
• Collection Data Types
• Index-By Tables
• Assigning Values
• Handling Missing Elements
• Nested Tables
• Initializing Nested Tables
• Using the Constructor
• Adding Elements to Nested Tables
• VARRAYs
• Initializing VARRAYs
• Appending to VARRAYs
• Multi-Level Collections
• Bulk Binding Techniques
• Bulk Binding Examples
• Transaction Considerations
• The BULK COLLECT Clause
• RETURNING INTO Clauses

Ref Cursors

• Cursor Variables
• Defining REF CURSOR Types
• Declaring Cursor Variables
• Constrained vs. Unconstrained Cursors
• Utilizing Cursor Variables
• Cursor Variable Case Studies