ORACLE PL/SQL Fundamentals Training Course

Introduction

• Course Aims and Objectives
• Session Schedule
• Participant Introductions
• Required Prerequisites
• Participant Responsibilities

SQL Tools

• Learning Objectives
• Overview of SQL Developer
• Establishing SQL Developer Connections
• Reviewing Table Information
• Querying with SQL in SQL Developer
• Logging into SQL*Plus
• Direct Database Connections
• Navigating the SQL*Plus Interface
• Terminating Sessions
• Essential SQL*Plus Commands
• SQL*Plus Environment Setup
• Understanding the SQL*Plus Prompt
• Retrieving Table Metadata
• Accessing Help Resources
• Executing SQL Scripts
• Entity Models in iSQL*Plus
• Examining the ORDERS Tables
• Examining the FILM Tables
• Course Table Reference Handout
• SQL Statement Syntax Guidelines
• Additional SQL*Plus Commands

What is PL/SQL?

• Overview of PL/SQL
• Benefits of Using PL/SQL
• Understanding Block Structure
• Outputting Messages
• Reviewing Sample Code
• Configuring SERVEROUTPUT
• Update Examples and Style Guide

Variables

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

SELECT Statement

• The SELECT Statement
• Assigning Values to Variables
• %Rowtype Variables
• The CHR Function
• Independent Study
• PL/SQL Record Types
• Sample Declaration Examples

Conditional Statement

• The IF Statement
• Using SELECT in Conditions
• Independent Study
• The Case Statement

Trapping Errors

• Exception Handling
• Handling Internal Errors
• Error Codes and Messages
• Handling No Data Found
• Raising User Exceptions
• Application Error Raising
• Catching Undefined Errors
• Using PRAGMA EXCEPTION_INIT
• Commit and Rollback Operations
• Independent Study
• Nested Code Blocks
• Practical Workshop

Iteration - Looping

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

Cursors

• Cursor Fundamentals
• Cursor Attributes
• Explicit Cursors
• Explicit Cursor Use Cases
• Declaring Cursors
• Declaring Associated Variables
• Opening and Fetching the First Row
• Fetching Subsequent Rows
• Exit Conditions Using %Notfound
• Closing Cursors
• 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

• The Create Statement
• Managing Parameters
• Writing the Procedure Body
• Displaying Error Details
• Inspecting Procedures
• Invoking Procedures
• Calling Procedures via SQL*Plus
• Utilizing Output Parameters
• Passing Output Parameters
• Writing Functions
• Function Examples
• Function Error Display
• Inspecting Functions
• Invoking Functions
• Calling Functions via SQL*Plus
• Principles of Modular Programming
• Procedure Examples
• Function Invocation Patterns
• Using Functions in IF Statements
• Creating Packages
• Package Examples
• Advantages of Packages
• Public vs. Private Sub-programs
• Package Error Display
• Inspecting Packages
• Calling Packages via SQL*Plus
• Invoking Packages from Sub-programs
• Dropping Sub-programs
• Locating Sub-programs
• Creating a Debug Package
• Utilizing the Debug Package
• Positional and Named Notation
• Default Parameter Values
• Recompiling Procedures and Functions
• Practical Workshop

Triggers

• Implementing Triggers
• Statement-Level Triggers
• Row-Level Triggers
• Applying WHEN Restrictions
• Conditional Triggers with IF
• Displaying Trigger Errors
• Committing Within Triggers
• Trigger Limitations
• Mutating Table Issues
• Identifying Triggers
• Removing Triggers
• Auto-Numbering Generation
• Disabling Triggers
• Enabling Triggers
• Naming Conventions for Triggers

Sample Data

• ORDER Table Structures
• FILM Table Structures
• EMPLOYEE Table Structures

Dynamic SQL

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

Using Files

• Handling Text Files
• The UTL_FILE Package
• Write and Append Operations
• Read Operations
• Trigger-Based File Operations
• The DBMS_ALERT Package
• The DBMS_JOB Package

COLLECTIONS

• %Type Variable Collections
• Record Type Collections
• Collection Type Options
• Index-By Tables
• Assigning Values to Collections
• Managing Nonexistent Elements
• Nested Table Concepts
• Initializing Nested Tables
• Using Constructors
• Inserting into Nested Tables
• Var Arrays
• Var Array Initialization
• Adding Elements to Var Arrays
• Multi-Level Collections
• Bulk Binding Techniques
• Bulk Binding Examples
• Transaction Considerations
• The BULK COLLECT Clause
• The RETURNING INTO Clause

Ref Cursors

• Cursor Variables Explained
• Defining REF CURSOR Types
• Declaring Cursor Variables
• Constrained vs. Unconstrained Cursors
• Utilizing Cursor Variables
• Cursor Variable Scenarios