ORACLE PL/SQL Fundamentals Training Course

Introduction

• Course Aims and Objectives
• Schedule Overview
• Participant Introductions
• Prerequisites
• Roles and Responsibilities

SQL Tooling

• Learning Objectives
• SQL Developer Interface
• Establishing Connections in SQL Developer
• Inspecting Table Metadata
• Executing Queries in SQL Developer
• Authenticating in SQL*Plus
• Direct Database Connection
• Operating SQL*Plus
• Terminating the Session
• SQL*Plus Command Reference
• SQL*Plus Environment Setup
• Prompts in SQL*Plus
• Retrieving Table Details
• Accessing Help Resources
• Executing SQL Scripts
• iSQL*Plus and Entity Models
• The ORDERS Tables
• The FILM Tables
• Course Table Handouts
• SQL Syntax Conventions
• Review of SQL*Plus Commands

Understanding PL/SQL

• Definition of PL/SQL
• Advantages of Using PL/SQL
• PL/SQL Block Architecture
• Outputting Messages
• Code Examples
• Configuring SERVEROUTPUT
• Update Examples and Style Guidelines

Variables

• Variable Concepts
• Data Types
• Assigning Values to Variables
• Constants
• Local vs. Global Variables
• %Type Variables
• Substitution Variables
• Using Comments with &
• Verify Option
• && Variables
• Define and Undefine Commands

SELECT Statements

• SELECT Statement Usage
• Populating Variables
• %Rowtype Variables
• The CHR Function
• Independent Practice
• PL/SQL Records
• Declaration Examples

Conditional Logic

• IF Statements
• SELECT Statement Integration
• Independent Practice
• CASE Statements

Error Handling

• Exceptions Overview
• Internal Errors
• Error Codes and Messages
• Handling No Data Found
• User-Defined Exceptions
• Raising Application Errors
• Catching Undefined Errors
• Utilizing PRAGMA EXCEPTION_INIT
• Commit and Rollback Operations
• Independent Practice
• Nested Blocks
• Practical Workshop

Iteration and Loops

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

Cursors

• Cursor Fundamentals
• Cursor Attributes
• Explicit Cursors
• Explicit Cursor Use Cases
• Cursor Declaration
• Variable Declaration
• Opening and Fetching the First Row
• Retrieving the Next Row
• Exit Condition: %Notfound
• Closing the Cursor
• For Loop Part I
• For Loop Part II
• Update Scenarios
• FOR UPDATE Clause
• FOR UPDATE OF Clause
• WHERE CURRENT OF Clause
• Committing with Cursors
• Validation Scenario I
• Validation Scenario II
• Cursor Parameters,
• Practical Workshop
• Workshop Solutions

Procedures, Functions, and Packages

• CREATE Statement
• Parameters
• Procedure Body Structure
• Error Display
• Describing a Procedure
• Invoking Procedures
• Calling Procedures in SQL*Plus
• Using Output Parameters
• Invoking with Output Parameters
• Creating Functions
• Function Example
• Error Display
• Describing a Function
• Invoking Functions
• Calling Functions in SQL*Plus
• Modular Programming Principles
• Example Procedure
• Calling Functions
• Functions Within IF Statements
• Package Creation
• Package Example
• Benefits of Packages
• Public and Private Subprograms
• Error Display
• Describing a Package
• Invoking Packages in SQL*Plus
• Invoking Packages from Subprograms
• Dropping Subprograms
• Locating Subprograms
• Creating a Debug Package
• Invoking the Debug Package
• Positional and Named Notation
• Default Parameter Values
• Recompiling Procedures and Functions
• Practical Workshop

Triggers

• Trigger Creation
• Statement-Level Triggers
• Row-Level Triggers
• WHEN Restrictions
• Selective Triggers using IF
• Error Display
• Committing Within Triggers
• Trigger Constraints
• Mutating Table Issues
• Locating Triggers
• Dropping Triggers
• Generating Auto-Numbers
• Disabling Triggers
• Enabling Triggers
• Trigger Naming Conventions

Sample Data

• ORDER Tables
• FILM Tables
• EMPLOYEE Tables

Dynamic SQL

• SQL within PL/SQL
• Binding Variables
• Dynamic SQL Concepts
• Native Dynamic SQL
• DDL and DML Operations
• DBMS_SQL Package
• Dynamic SQL for SELECT
• Dynamic SQL SELECT Procedure

File Operations

• Handling Text Files
• UTL_FILE Package
• Write and Append Examples
• Read Examples
• Trigger Integration Example
• DBMS_ALERT Package
• DBMS_JOB Package

Collections

• %Type Variables
• Record Variables
• Collection Types
• Index-By Tables
• Assigning Values
• Handling Nonexistent Elements
• Nested Tables
• Nested Table Initialization
• Using the Constructor
• Appending to Nested Tables
• VArrays
• VArray Initialization
• Adding Elements to VArrays
• Multilevel Collections
• Bulk Bind
• Bulk Bind Example
• Transaction Considerations
• BULK COLLECT Clause
• RETURNING INTO Clause

Ref Cursors

• Cursor Variables
• Defining REF CURSOR Types
• Declaring Cursor Variables
• Constrained vs. Unconstrained
• Utilizing Cursor Variables
• Cursor Variable Examples