ORACLE PL/SQL Fundamentals Training Course

Introduction

• Course Goals and Objectives
• Itinerary
• Participant Introductions
• Prerequisites
• Roles and Responsibilities

SQL Tools

• Learning Objectives
• SQL Developer
• SQL Developer - Connection Setup
• Inspecting Table Data
• Querying with SQL in SQL Developer
• Logging In with SQL*Plus
• Establishing Direct Connections
• Using the SQL*Plus Interface
• Terminating a Session
• SQL*Plus Command Reference
• The SQL*Plus Environment
• The SQL*Plus Prompt
• Retrieving Table Metadata
• Accessing Help Resources
• Executing SQL Scripts
• iSQL*Plus and Entity Models
• Review of ORDERS Tables
• Review of FILM Tables
• Distribution of Course Table Documentation
• Syntax of SQL Statements
• Overview of SQL*Plus Commands

Understanding PL/SQL

• Definition of PL/SQL
• Benefits of Using PL/SQL
• Code Block Structure
• Outputting Messages
• Code Samples
• Configuring SERVEROUTPUT
• Update Demonstration and Coding Style

Variables

• Variable Management
• Data Types
• Variable Assignment
• Constant Definitions
• Local vs. Global Scope
• %Type Variable Usage
• Substitution Variables
• Commenting with &
• The Verify Option
• && Variable Handling
• Define and Undefine Commands

SELECT Statement

• Overview of the SELECT Statement
• Populating Variables from Queries
• %Rowtype Variable Usage
• The CHR Function
• Independent Study Activities
• PL/SQL Record Types
• Sample Declarations

Conditional Logic

• The IF Statement
• Conditional SELECT Statements
• Independent Study Activities
• The Case Statement

Error Handling

• Exception Handling Basics
• Internal System Errors
• Error Codes and Messages
• Handling No Data Found
• Custom User Exceptions
• Raising Application Errors
• Catching Undefined Errors
• Using PRAGMA EXCEPTION_INIT
• Transaction Control: Commit and Rollback
• Independent Study Activities
• Nested Code Blocks
• Practical Workshop

Looping Constructs

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

Cursors

• Introduction to Cursors
• Cursor Attributes
• Explicit Cursor Usage
• Example: Explicit Cursor
• Cursor Declaration
• Variable Declaration for Cursors
• Opening a Cursor and Fetching the First Row
• Fetching Subsequent Rows
• Extraction Based on %Notfound
• Closing a Cursor
• For Loop Implementation I
• For Loop Implementation II
• Update Demonstration
• FOR UPDATE Clause
• FOR UPDATE OF Clause
• 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
• Parameter Handling
• Procedure Structure
• Error Visualization
• Describing a Procedure
• Invoking Procedures
• Calling Procedures via SQL*Plus
• Utilizing Output Parameters
• Passing Output Parameters
• Function Creation
• Function Example
• Error Visualization
• Describing a Function
• Invoking Functions
• Calling Functions via SQL*Plus
• Modular Development Practices
• Procedure Example
• Function Invocation
• Using Functions in IF Statements
• Package Creation
• Package Example
• Advantages of Using Packages
• Public vs. Private Subprograms
• Error Visualization
• Describing a Package
• Calling Packages via SQL*Plus
• Invoking Packages from Other Subprograms
• Removing Subprograms
• Locating Subprograms
• Creating a Debug Package
• Using the Debug Package
• Positional and Named Parameter Notation
• Default Parameter Values
• Recompiling Procedures and Functions
• Practical Workshop

Triggers

• Trigger Creation
• Statement-Level Triggers
• Row-Level Triggers
• The WHEN Condition
• Conditional Triggers using IF
• Error Visualization
• Commit Behavior in Triggers
• System Restrictions
• Mutating Triggers
• Identifying Triggers
• Removing a Trigger
• Generating Auto-Numbering
• Disabling Triggers
• Enabling Triggers
• Naming Conventions for Triggers

Sample Data

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

Dynamic SQL

• Executing SQL within PL/SQL
• Variable Binding
• Dynamic SQL Execution
• Native Dynamic SQL
• DDL and DML Statements
• The DBMS_SQL Package
• Dynamic SELECT Statements
• Dynamic SELECT Procedures

File Operations

• Text File Manipulation
• The UTL_FILE Package
• Write and Append Demonstration
• Read Demonstration
• Trigger-Based File Example
• The DBMS_ALERT Package
• The DBMS_JOB Package

COLLECTIONS

• %Type Variable Usage
• Record Variable Usage
• Collection Data Types
• Index-By Tables
• Assigning Values to Collections
• Handling Nonexistent Elements
• Nested Tables
• Nested Table Initialization
• Using Constructor Functions
• Adding Items to Nested Tables
• Varrays
• Varray Initialization
• Appending to Varrays
• Multi-Level Collections
• Bulk Binding Operations
• Bulk Binding Example
• 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