ORACLE PL/SQL Fundamentals Training Course

Introduction

• Course Aims and Objectives
• Session Schedule
• Participant Introductions
• Prerequisites Review
• Role Responsibilities

SQL Tools

• Learning Objectives
• Overview of SQL Developer
• Connecting via SQL Developer
• Reviewing Table Information
• Running Queries in SQL Developer
• Logging into SQL*Plus
• Establishing Direct Connections
• Using SQL*Plus
• Terminating Sessions
• SQL*Plus Command Set
• SQL*Plus Environment Setup
• Prompt Configuration
• Retrieving Table Metadata
• Accessing Help Resources
• Executing SQL Scripts
• iSQL*Plus and Entity Models
• Exploring the ORDERS Tables
• Examining the FILM Tables
• Course Tables Reference Sheet
• SQL Statement Syntax Rules
• SQL*Plus Command Reference

What is PL/SQL?

• Defining PL/SQL
• Benefits of Using PL/SQL
• Understanding Block Structure
• Displaying Output Messages
• Sample Code Walkthrough
• Configuring SERVEROUTPUT
• Update Example and Style Guide

Variables

• Variable Concepts
• Data Types
• Assigning Variables
• Working with Constants
• Local vs. Global Variables
• %Type Variable Usage
• Substitution Variables
• Using Comments with &
• Verification Options
• && Variable Handling
• Defining and Undefining Variables

SELECT Statement

• Using the SELECT Statement
• Populating Variables from Queries
• %Rowtype Variable Implementation
• The CHR Function
• Self-Study Activity
• PL/SQL Records
• Declaration Examples

Conditional Statement

• The IF Statement
• Conditional SELECT Statements
• Self-Study Exercise
• The Case Statement

Trapping Errors

• Exception Handling Basics
• Managing Internal Errors
• Error Codes and Messages
• Handling No Data Found
• Raising User Exceptions
• Raising Application Errors
• Catching Undefined Errors
• Using PRAGMA EXCEPTION_INIT
• Commit and Rollback Strategies
• Self-Study Task
• Nested Blocks in Error Handling
• Practical Workshop

Iteration - Looping

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

Cursors

• Introduction to Cursors
• Cursor Attributes
• Explicit Cursor Declaration
• Explicit Cursor Example
• Declaring Cursor Definitions
• Declaring Cursor Variables
• Opening and Fetching First Row
• Fetching Subsequent Rows
• Exit Conditions (%Notfound)
• Closing Cursors
• For Loop Usage Part I
• For Loop Usage Part II
• Update Examples with Cursors
• FOR UPDATE Clause
• FOR UPDATE OF Clause
• WHERE CURRENT OF Clause
• Managing Commits with Cursors
• Validation Example I
• Validation Example II
• Cursor Parameters
• Workshop Exercise
• Workshop Solution Walkthrough

Procedures, Functions and Packages

• Using the Create Statement
• Defining Parameters
• Procedure Body Structure
• Displaying Errors
• Describing a Procedure
• Invoking Procedures
• Calling Procedures in SQL*Plus
• Utilizing Output Parameters
• Passing Output Parameters
• Creating Functions
• Example Function Implementation
• Error Display in Functions
• Describing a Function
• Invoking Functions
• Calling Functions in SQL*Plus
• Modular Programming Principles
• Example Procedure Code
• Function Invocation Patterns
• Functions within IF Statements
• Creating Packages
• Package Example
• Advantages of Packages
• Public and Private Sub-programs
• Error Handling in Packages
• Describing a Package
• Calling Packages in SQL*Plus
• Invoking Packages from Sub-programs
• Dropping Sub-programs
• Locating Sub-programs
• Creating a Debug Package
• Using the Debug Package
• Positional and Named Notation
• Parameter Default Values
• Recompiling Procedures and Functions
• Practical Workshop

Triggers

• Creating Triggers
• Statement-Level Triggers
• Row-Level Triggers
• WHEN Clauses
• Conditional Triggers (IF)
• Error Reporting in Triggers
• Commit Behaviour in Triggers
• Trigger Restrictions
• Mutating Trigger Issues
• Locating Existing Triggers
• Dropping Triggers
• Generating Auto-numbers
• Disabling Triggers
• Enabling Triggers
• Naming Conventions for Triggers

Sample Data

• ORDER Tables Structure
• FILM Tables Structure
• EMPLOYEE Tables Structure

Dynamic SQL

• Executing SQL within PL/SQL
• Variable Binding
• Dynamic SQL Concepts
• Native Dynamic SQL
• DDL and DML Operations
• Using the DBMS_SQL Package
• Dynamic SELECT Statements
• Dynamic SELECT Procedures

Using Files

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

COLLECTIONS

• %Type Variables in Collections
• Record Variables
• Collection Type Overview
• Index-By Tables
• Assigning Values
• Handling Nonexistent Elements
• Nested Tables
• Initializing Nested Tables
• Using Constructors
• Appending to Nested Tables
• Varrays
• Varray Initialization
• Adding Elements to Varrays
• Multilevel Collections
• Bulk Bind Operations
• Bulk Bind Example
• Transactional Considerations
• BULK COLLECT Clause
• RETURNING INTO Clause

Ref Cursors

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