ORACLE PL/SQL Fundamentals Training Course

Introduction

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

SQL Tools

• Learning Objectives
• Overview of SQL Developer
• Connecting via SQL Developer
• Inspecting Table Information
• Executing Queries in SQL Developer
• Logging into SQL*Plus
• Establishing Direct Connections
• Navigating SQL*Plus
• Terminating Sessions
• SQL*Plus Command Reference
• SQL*Plus Environment Settings
• Understanding the SQL*Plus Prompt
• Retrieving Table Metadata
• Accessing Help Resources
• Utilising SQL Script Files
• iSQL*Plus and Entity Models
• Examining ORDERS Tables
• Examining FILM Tables
• Distribution of Course Tables Handout
• SQL Syntax Conventions
• Review of SQL*Plus Commands

What is PL/SQL?

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

Variables

• Introduction to Variables
• Data Types in PL/SQL
• Assigning Variable Values
• Defining Constants
• Difference Between Local and Global Variables
• Using %Type Variables
• Substitution Variables
• Adding Comments using &
• Verification Options
• && Variables
• Define and Undefine Commands

SELECT Statement

• Using the SELECT Statement in PL/SQL
• Populating Variables from Results
• Utilising %Rowtype Variables
• The CHR Function
• Independent Study
• Working with PL/SQL Records
• Declarations Examples

Conditional Statement

• The IF Statement
• Conditional SELECT Statements
• Independent Study
• The CASE Statement

Trapping Errors

• Concept of Exceptions
• Handling Internal Errors
• Error Codes and Messages
• Catching NO_DATA_FOUND
• Raising User-Defined Exceptions
• Raise Application Error
• Handling Undefined Errors
• Using PRAGMA EXCEPTION_INIT
• Commit and Rollback Strategies
• Independent Study
• Nested Blocks for Exception Handling
• Practical Workshop

Iteration - Looping

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

Cursors

• Introduction to Cursors
• Cursor Attributes
• Explicit Cursors
• Explicit Cursor Implementation Example
• Declaring Cursor Objects
• Declaring Corresponding Variables
• Opening Cursors and Fetching the First Row
• Iterating Through Subsequent Rows
• Termination using %NOTFOUND
• Closing Cursors
• Cursor For Loops (Part I)
• Cursor For Loops (Part II)
• Update Operations Example
• Locking with FOR UPDATE
• Specific Columns with FOR UPDATE OF
• Updating with WHERE CURRENT OF
• Managing Commits with Cursors
• Data Validation Example (I)
• Data Validation Example (II)
• Parameterised Cursors
• Practical Workshop
• Workshop Solutions

Procedures, Functions and Packages

• The CREATE Statement
• Parameter Passing
• Structure of a Procedure Body
• Error Display Techniques
• Describing Procedures
• Invoking Procedures
• Calling Procedures from SQL*Plus
• Using OUT Parameters
• Invoking with Output Data
• Function Creation
• Function Implementation Example
• Function Error Handling
• Describing Functions
• Invoking Functions
• Calling Functions from SQL*Plus
• Principles of Modular Programming
• Procedural Example
• Function Invocation Review
• Using Functions within IF Conditions
• Package Creation
• Package Implementation Example
• Advantages of Using Packages
• Public vs. Private Sub-programs
• Package Error Handling
• Describing Packages
• Invoking Packages from SQL*Plus
• Calling Packages from Within Sub-programs
• Dropping Sub-programs
• Locating Stored Sub-programs
• Creating a Debugging Package
• Invoking the Debug Package
• Positional and Named Parameter Notation
• Default Parameter Values
• Recompiling Procedures and Functions
• Practical Workshop

Triggers

• Creating Database Triggers
• Statement-Level Triggers
• Row-Level Triggers
• Applying WHEN Restrictions
• Conditional Triggers using IF
• Trigger Error Handling
• Commit Behaviour in Triggers
• Trigger Limitations
• Mutating Table Issues
• Locating Triggers
• Dropping Triggers
• Generating Auto-Numbering
• Disabling Triggers
• Enabling Triggers
• Naming Conventions for Triggers

Sample Data

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

Dynamic SQL

• Executing 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 within Procedures

Using Files

• Handling Text Files
• The UTL_FILE Package
• Writing and Appending Data Example
• Reading Data Example
• Trigger-Based File Operations
• DBMS_ALERT Package Overview
• DBMS_JOB Package Overview

COLLECTIONS

• Review of %Type Variables
• Record Variables
• Introduction to Collection Types
• Associative Arrays (Index-By Tables)
• Assigning Collection Values
• Managing Nonexistent Elements
• Nested Table Structures
• Initialising Nested Tables
• Using Constructors
• Adding Entries to Nested Tables
• Variable-Size Arrays (Varrays)
• Initialising Varrays
• Adding Entries to Varrays
• Complex Multi-Level Collections
• The Bulk Bind Mechanism
• Bulk Bind Implementation Example
• Transactional Considerations
• Using the BULK COLLECT Clause
• Using the RETURNING INTO Clause

Ref Cursors

• Concept of Cursor Variables
• Defining REF CURSOR Types
• Declaring Cursor Variables
• Constrained vs. Unconstrained Cursors
• Practical Use of Cursor Variables
• Comprehensive Cursor Variable Examples