ORACLE PL/SQL Fundamentals Training Course

Introduction

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

SQL Tools

• Learning Objectives
• SQL Developer
• Establishing Connections in SQL Developer
• Inspecting Table Details
• Running Queries via SQL and SQL Developer
• Logging In with SQL*Plus
• Direct Connection Setup
• Basic SQL*Plus Usage
• Terminating Sessions
• SQL*Plus Command Reference
• SQL*Plus Environment Setup
• Understanding the SQL*Plus Prompt
• Discovering Table Information
• Accessing Help Resources
• Executing SQL Files
• iSQL*Plus and Entity Models
• ORDERS Table Structure
• FILM Table Structure
• Course Reference Tables Handout
• SQL Syntax Fundamentals
• Advanced SQL*Plus Commands

What is PL/SQL?

• Definition of PL/SQL
• Benefits of Using PL/SQL
• Block Structure Explanation
• Outputting Messages
• Code Samples
• Configuring SERVEROUTPUT
• Update Examples and Style Guidelines

Variables

• Variable Basics
• Data Types Overview
• Variable Assignment
• Constant Definitions
• Scope: Local vs. Global Variables
• %Type Variable Declarations
• Substitution Variables
• Using & for Comments
• Verify Option Usage
• && Variable Specifics
• DEFINE and UNDEFINE Commands

SELECT Statement

• SELECT Statement Mechanics
• Assigning Values to Variables
• %Rowtype Variable Usage
• CHR Function Application
• Independent Study Exercises
• PL/SQL Record Types
• Declaration Examples

Conditional Statement

• IF Statement Logic
• Conditional SELECT Statements
• Independent Study Exercises
• CASE Statement Implementation

Trapping Errors

• Exception Handling Basics
• Internal Error Types
• Interpreting Error Codes and Messages
• Handling No Data Found
• Defining User Exceptions
• Raising Application Errors
• Catching Undefined Errors
• Using PRAGMA EXCEPTION_INIT
• Commit and Rollback Strategies
• Independent Study Exercises
• Nested Block Handling
• Practical Workshop

Iteration - Looping

• Basic Loop Statement
• While Loop Conditions
• For Loop Iterations
• Goto Statement and Label Usage

Cursors

• Cursor Fundamentals
• Cursor Attributes Explanation
• Explicit Cursor Creation
• Explicit Cursor Example
• Cursor Declaration Process
• Variable Declaration for Cursors
• Opening and Fetching the First Row
• Fetching Subsequent Rows
• Exit Condition with %Notfound
• Closing the Cursor
• For Loop Implementation I
• For Loop Implementation II
• Data Update Examples
• FOR UPDATE Clause
• FOR UPDATE OF Clause
• WHERE CURRENT OF Clause
• Committing Changes with Cursors
• Validation Scenario I
• Validation Scenario II
• Cursor Parameterization
• Practical Workshop
• Workshop Solutions

Procedures, Functions and Packages

• CREATE Statement Syntax
• Parameter Definitions
• Writing Procedure Bodies
• Error Display Techniques
• Procedure Description
• Executing Procedures
• Calling Procedures via SQL*Plus
• Utilizing Output Parameters
• Invoking with Output Parameters
• Function Creation Process
• Sample Function Implementation
• Error Display Techniques
• Function Description
• Function Execution
• Calling Functions via SQL*Plus
• Modular Programming Principles
• Sample Procedure Implementation
• Function Execution Review
• Functions within IF Statements
• Package Creation Process
• Sample Package Implementation
• Advantages of Using Packages
• Public vs. Private Sub-programs
• Error Display Techniques
• Package Description
• Calling Packages via SQL*Plus
• Invoking Packages from Sub-programs
• Dropping Sub-programs
• Locating Sub-programs
• Creating Debug Packages
• Executing Debug Packages
• Positional and Named Notation
• Default Parameter Values
• Recompiling Procedures and Functions
• Practical Workshop

Triggers

• Trigger Creation Basics
• Statement-Level Triggers
• Row-Level Triggers
• WHEN Clause Restrictions
• Conditional Triggers using IF
• Error Display Techniques
• Committing within Triggers
• Trigger Limitations
• Mutating Trigger Issues
• Locating Triggers
• Dropping Triggers
• Auto-Number Generation
• Disabling Triggers
• Enabling Triggers
• Trigger Naming Conventions

Sample Data

• ORDER Table Structure
• FILM Table Structure
• EMPLOYEE Table Structure

Dynamic SQL

• Embedding SQL in PL/SQL
• Variable Binding
• Dynamic SQL Concepts
• Native Dynamic SQL
• Handling DDL and DML
• DBMS_SQL Package Usage
• Dynamic SELECT Statements
• Dynamic SELECT Procedures

Using Files

• Text File Operations
• UTL_FILE Package Overview
• Write and Append Examples
• File Reading Examples
• Trigger-Based File Operations
• DBMS_ALERT Package
• DBMS_JOB Package

COLLECTIONS

• %Type Variable Review
• Record Variable Usage
• Collection Type Overview
• Index-By Tables
• Assigning Values to Collections
• Handling Nonexistent Elements
• Nested Table Structures
• Initializing Nested Tables
• Using Constructor Methods
• Appending to Nested Tables
• VARRAY Structures
• VARRAY Initialization
• Adding Elements to VARRAYs
• Multilevel Collections
• Bulk Binding Techniques
• Bulk Binding Examples
• Transaction Management in Collections
• BULK COLLECT Clause
• RETURNING INTO Clause

Ref Cursors

• Cursor Variable Basics
• Defining REF CURSOR Types
• Cursor Variable Declarations
• Constrained vs. Unconstrained Cursors
• Implementing Cursor Variables
• Cursor Variable Examples