ORACLE PL/SQL Fundamentals Training Course

Introduction

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

SQL Tools

• Learning Objectives
• Overview of SQL Developer
• Connecting via SQL Developer
• Inspecting Table Metadata
• Running Queries in SQL Developer
• Logging into SQL*Plus
• Establishing Direct Connections
• Navigating SQL*Plus
• Terminating Sessions
• Core SQL*Plus Commands
• SQL*Plus Environment Setup
• Understanding Prompts
• Retrieving Table Information
• Accessing Help Resources
• Executing SQL Scripts
• iSQL*Plus and Entity Models
• The ORDERS Tables
• The FILM Tables
• Course Tables Handout
• SQL Statement Syntax
• Advanced SQL*Plus Commands

What is PL/SQL?

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

Variables

• Variable Concepts
• Data Types
• Assigning Values to Variables
• Defining Constants
• Local vs. Global Variables
• Using %Type
• Substitution Variables
• Comments using &
• Verify Option Usage
• Handling && Variables
• Define and Undefine Commands

SELECT Statement

• Using the SELECT Statement
• Populating Variables
• Working with %Rowtype
• The CHR Function
• Self-Study Activity
• PL/SQL Records
• Example Declarations

Conditional Statement

• Implementing IF Statements
• SELECT within Conditionals
• Self-Study Activity
• Case Statements

Trapping Errors

• Understanding Exceptions
• Handling Internal Errors
• Interpreting Error Codes and Messages
• Managing No Data Found
• Defining User Exceptions
• Raising Application Errors
• Handling Undefined Errors
• Using PRAGMA EXCEPTION_INIT
• Committing and Rolling Back
• Self-Study Activity
• Nested Blocks
• Workshop Exercise

Iteration - Looping

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

Cursors

• Cursor Fundamentals
• Cursor Attributes
• Explicit Cursors
• Explicit Cursor Examples
• Declaring Cursors
• Declaring Associated Variables
• Opening and Fetching the First Row
• Fetching Subsequent Rows
• Termination with %Notfound
• Closing Cursors
• For Loop Part I
• For Loop Part II
• Update Examples
• FOR UPDATE Clause
• FOR UPDATE OF
• WHERE CURRENT OF
• Committing with Cursors
• Validation Example I
• Validation Example II
• Cursor Parameters
• Workshop Exercise
• Workshop Solutions

Procedures, Functions and Packages

• The CREATE Statement
• Handling Parameters
• Procedure Bodies
• Error Handling
• Describing Procedures
• Invoking Procedures
• Calling Procedures in SQL*Plus
• Using Output Parameters
• Invocations with Output
• Creating Functions
• Function Examples
• Error Management
• Describing Functions
• Invoking Functions
• Calling Functions in SQL*Plus
• Modular Programming
• Procedure Examples
• Function Invocation
• Functions in IF Statements
• Creating Packages
• Package Examples
• Advantages of Packages
• Public and Private Sub-programs
• Error Inspection
• Describing Packages
• Calling Packages in SQL*Plus
• Invoking from Sub-programs
• Dropping Sub-programs
• Locating Sub-programs
• Creating Debug Packages
• Using Debug Packages
• Positional and Named Notation
• Default Parameter Values
• Recompiling Code
• Workshop Exercise

Triggers

• Creating Triggers
• Statement-Level Triggers
• Row-Level Triggers
• WHEN Clauses
• Conditional Triggers
• Error Detection
• Commit Behavior in Triggers
• Trigger Restrictions
• Mutating Tables
• Locating Triggers
• Removing Triggers
• Auto-Numbering
• Disabling Triggers
• Enabling Triggers
• Naming Conventions

Sample Data

• ORDER Tables
• FILM Tables
• EMPLOYEE Tables

Dynamic SQL

• Executing SQL in PL/SQL
• Binding Variables
• Dynamic SQL Concepts
• Native Dynamic SQL
• DDL and DML Operations
• Using the DBMS_SQL Package
• Dynamic SELECT
• Dynamic SELECT Procedures

Using Files

• Handling Text Files
• UTL_FILE Package
• Writing and Appending
• Reading Files
• Trigger Integration
• DBMS_ALERT Package
• DBMS_JOB Package

COLLECTIONS

• %Type Usage
• Record Variables
• Collection Data Types
• Index-By Tables
• Assigning Values
• Handling Missing Elements
• Nested Tables
• Initializing Nested Tables
• Using Constructors
• Inserting into Nested Tables
• VARRAYs
• VARRAY Initialization
• Adding to VARRAYs
• Multilevel Collections
• Bulk Binding
• Bulk Bind Examples
• Transaction Considerations
• BULK COLLECT Clause
• RETURNING INTO

Ref Cursors

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