ORACLE PL/SQL Fundamentals Training Course

Introduction

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

SQL Tools

• Session Objectives
• Introduction to SQL Developer
• Establishing Connections in SQL Developer
• Inspecting Table Information
• Executing Queries via SQL Developer
• Logging into SQL*Plus
• Direct Connection Setup
• Basics of SQL*Plus Usage
• Terminating the Session
• Essential SQL*Plus Commands
• The SQL*Plus Environment
• Understanding the SQL*Plus Prompt
• Retrieving Table Details
• Accessing Help Resources
• Executing SQL Scripts
• iSQL*Plus and Entity Models
• The ORDERS Table Set
• The FILM Table Set
• Course Table Reference Handout
• SQL Statement Syntax
• Review of SQL*Plus Commands

What is PL/SQL?

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

Variables

• Concept of Variables
• Data Types
• Assigning Variable Values
• Constants
• Local vs. Global Variables
• %Type Variables
• Substitution Variables
• Comments using &
• Verification Option
• && Variables
• Define and Undefine Commands

SELECT Statement

• The SELECT Command
• Populating Variables from Data
• %Rowtype Variables
• The CHR Function
• Independent Practice
• PL/SQL Records
• Declaration Examples

Conditional Statement

• The IF Statement
• SELECT within Conditionals
• Independent Practice
• The CASE Statement

Trapping Errors

• Exception Handling
• Internal Errors
• Error Codes and Messages
• Handling No Data Found
• User-Defined Exceptions
• Raising Application Errors
• Catching Undefined Errors
• Using PRAGMA EXCEPTION_INIT
• Commit and Rollback Operations
• Independent Practice
• Nested Blocks
• Workshop Exercise

Iteration - Looping

• The LOOP Statement
• The WHILE Statement
• The FOR Statement
• GOTO Statement and Labels

Cursors

• Introduction to Cursors
• Cursor Attributes
• Explicit Cursors
• Explicit Cursor Example
• Declaring a Cursor
• Declaring a Variable
• Opening and Fetching the First Row
• Fetching Subsequent Rows
• Exit Condition with %Notfound
• Closing the Cursor
• FOR Loop Part I
• FOR Loop Part II
• Update Example
• FOR UPDATE Clause
• FOR UPDATE OF Clause
• WHERE CURRENT OF Clause
• Committing with Cursors
• Validation Example I
• Validation Example II
• Cursor Parameters,
• Workshop Exercise
• Workshop Solutions

Procedures, Functions and Packages

• The CREATE Statement
• Managing Parameters
• The Procedure Body
• Displaying Errors
• Describing a Procedure
• Invoking Procedures
• Calling Procedures in SQL*Plus
• Utilizing Output Parameters
• Calling with Output Parameters
• Creating Functions
• Function Example
• Displaying Errors
• Describing a Function
• Invoking Functions
• Calling Functions in SQL*Plus
• Modular Programming Concepts
• Procedure Example
• Invoking Functions
• Using Functions in IF Statements
• Creating Packages
• Package Example
• Advantages of Packages
• Public and Private Sub-programs
• Displaying Errors
• Describing a Package
• Calling Packages in SQL*Plus
• Calling Packages from Sub-Programs
• Dropping a Sub-Program
• Locating Sub-programs
• Creating a Debug Package
• Invoking the Debug Package
• Positional and Named Notation
• Default Parameter Values
• Recompiling Procedures and Functions
• Workshop Exercise

Triggers

• Creating Triggers
• Statement-Level Triggers
• Row-Level Triggers
• WHEN Clause Restrictions
• Selective Triggers using IF
• Displaying Errors
• Commit Behavior in Triggers
• Trigger Restrictions
• Mutating Triggers
• Locating Triggers
• Dropping a Trigger
• Auto-Number Generation
• Disabling Triggers
• Enabling Triggers
• Naming Conventions for Triggers

Sample Data

• ORDER Tables
• FILM Tables
• EMPLOYEE Tables

Dynamic SQL

• SQL within PL/SQL
• Binding Concepts
• Dynamic SQL Overview
• Native Dynamic SQL
• DDL and DML Operations
• DBMS_SQL Package
• Dynamic SQL - SELECT
• Dynamic SQL - SELECT Procedure

Using Files

• Working with Text Files
• UTL_FILE Package
• Write and Append Example
• Read Example
• Trigger Example
• DBMS_ALERT Packages
• DBMS_JOB Package

COLLECTIONS

• %Type Variables
• Record Variables
• Collection Types
• Index-By Tables
• Assigning Values
• Nonexistent Elements
• Nested Tables
• Initializing Nested Tables
• Using the Constructor
• Adding Elements to a Nested Table
• Varrays
• Varray Initialization
• Adding Elements to a Varray
• Multilevel Collections
• Bulk Bind
• Bulk Bind Example
• Transactional Considerations
• BULK COLLECT Clause
• RETURNING INTO Clause

Ref Cursors

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