ORACLE PL/SQL Fundamentals Training Course

Introduction

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

SQL Tools

• Learning Objectives
• Introducing SQL Developer
• Establishing Connections in SQL Developer
• Reviewing Table Information
• Executing Queries via SQL and SQL Developer
• Logging into SQL*Plus
• Setting Up Direct Connections
• Navigating SQL*Plus
• Terminating Sessions
• Common SQL*Plus Commands
• The SQL*Plus Environment
• Prompts in SQL*Plus
• Retrieving Table Details
• Accessing Help Resources
• Utilizing SQL Scripts
• iSQL*Plus and Entity Models
• The ORDERS Data Set
• The FILM Data Set
• Course Reference Tables
• SQL Statement Syntax
• Essential SQL*Plus Commands

Understanding PL/SQL

• Overview of PL/SQL
• Benefits of Using PL/SQL
• Block Architecture
• Outputting Messages
• Sample Code Snippets
• Configuring SERVEROUTPUT
• Update Example and Style Guidelines

Variables

• Variable Concepts
• Data Types
• Assigning Values to Variables
• Defining Constants
• Local vs. Global Variables
• Using %Type Variables
• Substitution Variables
• Inserting Comments with &
• The Verify Option
• Managing && Variables
• Defining and Undefining Variables

The SELECT Statement

• Using the SELECT Statement
• Populating Variables from Queries
• Using %Rowtype Variables
• The CHR Function
• Independent Study
• PL/SQL Records
• Declaration Examples

Conditional Logic

• Implementing IF Statements
• SELECT Statement Integration
• Independent Study
• Using Case Statements

Error Handling

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

Iteration and Looping

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

Cursors

• Introduction to Cursors
• Cursor Attributes
• Explicit Cursors
• Example: Explicit Cursor Implementation
• Declaring Cursors
• Declaring Variables
• Opening Cursors and Fetching the First Row
• Fetching Subsequent Rows
• Exit Conditions Using %Notfound
• Closing Cursors
• For Loop Implementation I
• For Loop Implementation II
• Update Example
• Using FOR UPDATE
• Using FOR UPDATE OF
• WHERE CURRENT OF Clause
• Committing Changes with Cursors
• Validation Example I
• Validation Example II
• Cursor Parameters
• Practical Workshop
• Workshop Solutions

Procedures, Functions, and Packages

• The Create Statement
• Handling Parameters
• Constructing Procedure Bodies
• Displaying Error Information
• Describing Procedures
• Invoking Procedures
• Calling Procedures via SQL*Plus
• Utilizing Output Parameters
• Calling Procedures with Output
• Creating Functions
• Example Function
• Displaying Error Information
• Describing Functions
• Invoking Functions
• Calling Functions via SQL*Plus
• Modular Programming Concepts
• Example Procedure
• Calling Functions
• Using Functions Within IF Statements
• Creating Packages
• Package Example
• Advantages of Packages
• Public vs. Private Sub-programs
• Displaying Error Information
• Describing Packages
• Calling Packages via SQL*Plus
• Calling Packages from Sub-programs
• Dropping Sub-programs
• Locating Sub-programs
• Creating a Debug Package
• Invoking the Debug Package
• Positional and Named Notation
• Default Parameter Values
• Recompiling Procedures and Functions
• Practical Workshop

Triggers

• Creating Triggers
• Statement-Level Triggers
• Row-Level Triggers
• Using WHEN Restrictions
• Selective Triggers with IF
• Displaying Error Information
• Committing in Triggers
• Trigger Restrictions
• Mutating Trigger Issues
• Locating Triggers
• Dropping Triggers
• Generating Auto-numbering
• Disabling Triggers
• Enabling Triggers
• Naming Conventions for Triggers

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
• The DBMS_SQL Package
• Dynamic SQL for SELECT Statements
• Dynamic SQL SELECT Procedures

File Operations

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

Collections

• %Type Variables
• Record Variables
• Collection Types
• Index-By Tables
• Assigning Values
• Managing Nonexistent Elements
• Nested Tables
• Initializing Nested Tables
• Using the Constructor
• Adding Elements to Nested Tables
• Varrays
• Initializing Varrays
• Adding Elements to Varrays
• Multilevel Collections
• Bulk Binding
• Bulk Binding Example
• Transactional Considerations
• The BULK COLLECT Clause
• RETURNING INTO Clause

Ref Cursors

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