ORACLE PL/SQL Fundamentals Training Course

Introduction

• Course Aims and Objectives
• Training Schedule
• Participant Introductions
• Pre-requisites
• Roles and Responsibilities

SQL Tools

• Learning Objectives
• Overview of SQL Developer
• Connecting via SQL Developer
• Examining Table Information
• Executing Queries in SQL Developer
• SQL*Plus Login Process
• Establishing Direct Connections
• Navigating SQL*Plus
• Session Termination
• Essential SQL*Plus Commands
• The SQL*Plus Environment
• Understanding the SQL*Plus Prompt
• Retrieving Table Metadata
• Accessing Help Resources
• Utilizing SQL Scripts
• iSQL*Plus and Entity Models
• The ORDERS Data Set
• The FILM Data Set
• Course Reference Tables
• SQL Syntax Standards
• Advanced SQL*Plus Commands

What is PL/SQL?

• Defining PL/SQL
• Benefits of Using PL/SQL
• Block Structure Concepts
• Outputting Messages
• Code Samples
• Configuring SERVEROUTPUT
• Update Examples and Style Guides

Variables

• Variable Concepts
• Data Types
• Assigning Variable Values
• Defining Constants
• Local vs. Global Scope
• %Type Variable Definitions
• Substitution Variables
• Comments Involving Special Characters
• The Verify Option
• Double Ampersand Variables
• Defining and Undefining Variables

SELECT Statement

• The SELECT Command
• Assigning Data to Variables
• %Rowtype Variables
• The CHR Function
• Independent Study Modules
• PL/SQL Record Types
• Declarations and Examples

Conditional Statement

• IF Logic
• Integration with SELECT
• Independent Study Modules
• CASE Statement Logic

Trapping Errors

• Exception Handling Basics
• Internal Error Management
• Interpreting Error Codes and Messages
• Handling No Data Found Scenarios
• Raising User-Defined Exceptions
• Raising Application Errors
• Catching Undefined Errors
• Using PRAGMA EXCEPTION_INIT
• Managing Commits and Rollbacks
• Independent Study Modules
• Nested Block Logic
• Practical Workshop

Iteration - Looping

• Basic Loop Constructs
• While Loop Logic
• For Loop Logic
• Goto Statements and Labels

Cursors

• Cursor Fundamentals
• Cursor Attributes
• Explicit Cursor Definitions
• Explicit Cursor Examples
• Cursor Declaration Syntax
• Variable Declaration for Cursors
• Opening Cursors and Fetching Initial Rows
• Fetching Subsequent Rows
• Termination Conditions (%Notfound)
• Closing Cursors
• For Loop Integration I
• For Loop Integration II
• Update Operations Example
• FOR UPDATE Clause
• FOR UPDATE OF Clause
• WHERE CURRENT OF Clause
• Committing Changes with Cursors
• Validation Scenarios I
• Validation Scenarios II
• Passing Cursor Parameters
• Practical Workshop
• Workshop Solutions

Procedures, Functions and Packages

• Create Statement Syntax
• Parameter Definitions
• Writing Procedure Bodies
• Diagnosing Errors
• Procedure Documentation
• Invoking Procedures
• Calling Procedures in SQL*Plus
• Leveraging Output Parameters
• Invoking with Output Parameters
• Function Creation
• Function Examples
• Diagnosing Errors
• Function Documentation
• Invoking Functions
• Calling Functions in SQL*Plus
• Principles of Modular Programming
• Procedure Examples
• Function Calls
• Using Functions Within IF Blocks
• Package Creation
• Package Examples
• Benefits of Using Packages
• Public vs. Private Sub-programs
• Diagnosing Errors
• Package Documentation
• Invoking Packages in SQL*Plus
• Calling Packages from Sub-programs
• Dropping Sub-programs
• Locating Sub-programs
• Creating Debug Packages
• Invoking Debug Packages
• Positional and Named Notation
• Default Parameter Values
• Recompiling Procedures and Functions
• Practical Workshop

Triggers

• Trigger Creation
• Statement-Level Triggers
• Row-Level Triggers
• WHEN Clause Restrictions
• Selective Triggers with IF Logic
• Diagnosing Errors
• Committing Within Triggers
• Trigger Limitations
• Mutating Trigger Issues
• Locating Triggers
• Dropping Triggers
• Auto-numbering Generation
• Disabling Triggers
• Enabling Triggers
• Naming Conventions for Triggers

Sample Data

• ORDER Data Sets
• FILM Data Sets
• EMPLOYEE Data Sets

Dynamic SQL

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

Using Files

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

COLLECTIONS

• %Type Variables in Collections
• Record Variables
• Collection Type Definitions
• Index-By Tables
• Assigning Values
• Handling Nonexistent Elements
• Nested Tables
• Nested Table Initialization
• Constructor Usage
• Appending to Nested Tables
• Varrays
• Varray Initialization
• Adding Elements to Varrays
• Multilevel Collections
• Bulk Binding Techniques
• Bulk Binding Examples
• Transactional Considerations
• The BULK COLLECT Clause
• RETURNING INTO Clause

Ref Cursors

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