ORACLE PL/SQL Fundamentals Training Course

Introduction

• Course Aims and Objectives
• Course Schedule Overview
• Participant Introductions
• Prerequisite Review
• Roles and Responsibilities

SQL Tools

• Tool Objectives
• Overview of SQL Developer
• Connecting via SQL Developer
• Viewing Table Metadata
• Executing Queries with SQL Developer
• Logging into SQL*Plus
• Establishing Direct Connections
• Working with SQL*Plus
• Terminating Sessions
• Essential SQL*Plus Commands
• The SQL*Plus Environment
• Understanding the SQL*Plus Prompt
• Locating Table Information
• Accessing Help Resources
• Executing SQL Scripts
• iSQL*Plus and Entity Models
• The ORDERS Table Structure
• The FILM Table Structure
• Course Tables Reference Handout
• SQL Statement Syntax Rules
• Review of SQL*Plus Commands

What is PL/SQL?

• Definition of PL/SQL
• Benefits of Using PL/SQL
• Understanding Block Structure
• Displaying Output Messages
• Reviewing Sample Code
• Configuring SERVEROUTPUT
• Update Examples and Style Guide

Variables

• Introduction to Variables
• Data Types in PL/SQL
• Assigning Variable Values
• Working with Constants
• Distinguishing Local and Global Variables
• Using %Type Variables
• Substitution Variables Explained
• Adding Comments with &
• The Verify Option
• Managing && Variables
• Define and Undefine Operations

SELECT Statement

• Using the SELECT Statement
• Populating Variables with Data
• Utilizing %Rowtype Variables
• The CHR Function
• Self-Study Module
• PL/SQL Records
• Example Variable Declarations

Conditional Statement

• Implementing IF Statements
• Conditional SELECT Statements
• Self-Study Module
• Using Case Statements

Trapping Errors

• Understanding Exceptions
• Handling Internal Errors
• Interpreting Error Codes and Messages
• Managing 'No Data Found' Errors
• Raising User Exceptions
• Raising Application Errors
• Catching Undefined Errors
• Utilizing PRAGMA EXCEPTION_INIT
• Managing Commit and Rollback
• Self-Study Module
• Nested Blocks
• Practical Workshop

Iteration - Looping

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

Cursors

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

Procedures, Functions and Packages

• The Create Statement
• Managing Parameters
• Structure of Procedure Bodies
• Displaying Errors
• Describing Procedures
• Invoking Procedures
• Calling Procedures in SQL*Plus
• Utilizing Output Parameters
• Calling with Output Parameters
• Creating Functions
• Example Function Implementation
• Displaying Function Errors
• Describing Functions
• Calling Functions
• Calling Functions in SQL*Plus
• Principles of Modular Programming
• Example Procedure
• Function Calls
• Calling Functions Within IF Statements
• Building Packages
• Package Example
• Advantages of Using Packages
• Public and Private Sub-programs
• Displaying Package Errors
• Describing Packages
• Calling Packages in 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
• Parameter Default Values
• Recompiling Procedures and Functions
• Practical Workshop

Triggers

• Building Triggers
• Statement-Level Triggers
• Row-Level Triggers
• Applying WHEN Restrictions
• Selective Triggers using IF
• Displaying Trigger Errors
• Committing Transactions in Triggers
• Trigger Restrictions
• Handling Mutating Triggers
• Locating Triggers
• Dropping Triggers
• Generating Auto-numbers
• Disabling Triggers
• Enabling Triggers
• Naming Conventions for Triggers

Sample Data

• ORDER Tables
• FILM Tables
• EMPLOYEE Tables

Dynamic SQL

• Executing SQL within PL/SQL
• Data Binding
• Concepts of Dynamic SQL
• Native Dynamic SQL
• Executing DDL and DML
• The DBMS_SQL Package
• Dynamic SQL for SELECT
• Dynamic SQL SELECT Procedures

Using Files

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

COLLECTIONS

• Revisiting %Type Variables
• Record Variables
• Collection Types Overview
• Index-By Tables
• Assigning Collection Values
• Managing Nonexistent Elements
• Nested Tables
• Initializing Nested Tables
• Using Constructors
• Adding Elements to Nested Tables
• Working with Varrays
• Varray Initialization
• Inserting Elements into Varrays
• Multilevel Collections
• The Bulk Bind Technique
• Bulk Bind Example
• Considerations for Transactional Issues
• The BULK COLLECT Clause
• Using RETURNING INTO

Ref Cursors

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