ORACLE PL/SQL Fundamentals Training Course

Introduction

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

SQL Tools

• Learning Objectives
• SQL Developer Overview
• Connecting with SQL Developer
• Viewing Table Details
• Executing Queries in SQL Developer
• Logging into SQL*Plus
• Establishing Direct Connections
• Working with SQL*Plus
• Terminating Sessions
• SQL*Plus Command Reference
• The SQL*Plus Environment
• SQL*Plus Prompts
• Retrieving Table Metadata
• Accessing Help Resources
• Running SQL Script Files
• iSQL*Plus and Entity Models
• Exploring the ORDERS Tables
• Exploring the FILM Tables
• Course Materials and Table Handout
• SQL Statement Syntax Rules
• 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 Guidelines

Variables

• Variable Concepts
• Data Types
• Assigning Values to Variables
• Using Constants
• Local vs. Global Scope
• Using %TYPE Variables
• Substitution Variables
• Adding Comments using &
• The VERIFY Option
• Handling && Variables
• DEFINE and UNDEFINE Commands

SELECT Statement

• Executing SELECT Queries
• Populating Variables from Queries
• Using %ROWTYPE Variables
• The CHR Function
• Self-Study Practice
• Working with PL/SQL Records
• Record Declaration Examples

Conditional Statement

• Using IF Statements
• Conditional SELECT Logic
• Self-Study Practice
• Implementing CASE Statements

Trapping Errors

• Understanding Exceptions
• Handling Internal Errors
• Interpreting Error Codes and Messages
• Catching NO DATA FOUND
• Raising User-Defined Exceptions
• Raising Application Errors
• Capturing Undefined Errors
• Using PRAGMA EXCEPTION_INIT
• Managing Commit and Rollback
• Self-Study Practice
• Nested Code Blocks
• Practical Workshop

Iteration - Looping

• BASIC LOOP Statements
• WHILE LOOPS
• FOR LOOPS
• GOTO Statements and Labels

Cursors

• Cursor Fundamentals
• Cursor Attributes
• Working with Explicit Cursors
• Explicit Cursor Walkthrough
• Cursor Declaration
• Variable Declaration
• Opening Cursors and Fetching First Row
• Fetching Subsequent Rows
• Checking %NOTFOUND
• Closing Cursors
• FOR Loop Implementation (Part I)
• FOR Loop Implementation (Part II)
• Row Update Example
• Using FOR UPDATE
• Using FOR UPDATE OF
• Using WHERE CURRENT OF
• Committing Changes with Cursors
• Data Validation Example I
• Data Validation Example II
• Passing Cursor Parameters
• Practical Workshop
• Workshop Solution Review

Procedures, Functions and Packages

• The CREATE Statement
• Managing Parameters
• Structuring Procedure Bodies
• Error Reporting
• Inspecting Procedure Details
• Invoking Procedures
• Calling Procedures via SQL*Plus
• Utilizing OUT Parameters
• Passing Output Parameters
• Developing Functions
• Function Implementation Example
• Function Error Handling
• Inspecting Function Details
• Invoking Functions
• Calling Functions via SQL*Plus
• Principles of Modular Programming
• Procedure Implementation Example
• Function Invocation Patterns
• Using Functions in IF Statements
• Developing Packages
• Package Implementation Example
• Benefits of Using Packages
• Public vs. Private Sub-programs
• Package Error Handling
• Inspecting Package Details
• Invoking Packages via SQL*Plus
• Calling Packages from Within Sub-programs
• Dropping Sub-programs
• Locating Sub-programs
• Building a Debug Package
• Testing the Debug Package
• Positional vs. Named Parameter Notation
• Setting Default Parameter Values
• Recompiling Procedures and Functions
• Practical Workshop

Triggers

• Developing Triggers
• Statement-Level Triggers
• Row-Level Triggers
• Applying WHEN Clauses
• Conditional Triggers using IF
• Trigger Error Handling
• Commit Behavior in Triggers
• Trigger Limitations
• Mutating Table Issues
• Identifying Triggers
• Removing Triggers
• Generating Auto-Increment IDs
• 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
• Bind Variables
• Dynamic SQL Overview
• Native Dynamic SQL
• Executing DDL and DML
• Using the DBMS_SQL Package
• Dynamic SELECT Statements
• Building a Dynamic SELECT Procedure

Using Files

• Handling Text Files
• The UTL_FILE Package
• Writing and Appending Data
• Reading File Contents
• File Handling in Triggers
• The DBMS_ALERT Package
• The DBMS_JOB Package

COLLECTIONS

• %TYPE Collections
• Record Collections
• Collection Data Types
• Index-By Tables
• Assigning Collection Values
• Handling Non-Existent Elements
• Nested Tables
• Initializing Nested Tables
• Using Constructors
• Appending to Nested Tables
• VARRAYs
• Initializing VARRAYs
• Adding Elements to VARRAYs
• Multilevel Collections
• Bulk Binding
• Bulk Binding Examples
• Transactional Considerations
• The BULK COLLECT Clause
• Using RETURNING INTO

Ref Cursors

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