ORACLE PL/SQL Fundamentals Training Course

Introduction

• Course Goals and Objectives
• Training Schedule
• Participant Introductions
• Prerequisites Review
• Participant Roles and Responsibilities

SQL Tools

• Learning Objectives
• Introduction to SQL Developer
• Connecting via SQL Developer
• Inspecting Table Metadata
• Executing Queries in SQL Developer
• Logging in to SQL*Plus
• Establishing Direct Connections
• Navigating SQL*Plus
• Terminating the Session
• Key SQL*Plus Commands
• The SQL*Plus Environment
• The SQL*Plus Prompt
• Retrieving Table Information
• Accessing Help Resources
• Executing SQL Scripts
• iSQL*Plus and Entity Models
• ORDERS Table Structure
• FILM Table Structure
• Course Tables Reference Sheet
• SQL Syntax Fundamentals
• Essential SQL*Plus Commands

What is PL/SQL?

• Understanding PL/SQL
• Benefits of Using PL/SQL
• PL/SQL Block Architecture
• Outputting Messages
• Code Samples
• Configuring SERVEROUTPUT
• Update Examples and Style Guidelines

Variables

• Variable Concepts
• Data Types Overview
• Assigning Values to Variables
• Defining Constants
• Scope: Local vs. Global Variables
• %TYPE Variable Declaration
• Substitution Variables
• Adding Comments using &
• The Verify Option
• && Variable Syntax
• DEFINE and UNDEFINE Commands

SELECT Statement

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

Conditional Statement

• The IF Conditional
• Conditional SELECT Logic
• Independent Practice
• The CASE Statement

Trapping Errors

• Exception Handling Basics
• Internal System Errors
• Error Codes and Descriptive Messages
• Handling NO_DATA_FOUND
• Raising User-Defined Exceptions
• Raising Application Errors
• Catching Unhandled Exceptions
• Using PRAGMA EXCEPTION_INIT
• COMMIT and ROLLBACK Operations
• Independent Practice
• Nested Exception Blocks
• Hands-on Workshop

Iteration - Looping

• The LOOP Construct
• The WHILE Loop
• The FOR Loop
• GOTO Statement and Label Usage

Cursors

• Cursor Concepts
• Inspecting Cursor Attributes
• Explicit Cursor Definition
• Explicit Cursor Walkthrough
• Declaring Cursor Definitions
• Declaring Associated Variables
• Opening Cursor and Fetching First Record
• Fetching Subsequent Records
• Loop Termination using %NOTFOUND
• Closing the Cursor
• FOR Loop Implementation I
• FOR Loop Implementation II
• Update Scenarios
• FOR UPDATE Clause
• FOR UPDATE OF Clause
• WHERE CURRENT OF Clause
• Managing Commits with Cursors
• Data Validation Scenarios I
• Data Validation Scenarios II
• Passing Parameters to Cursors
• Hands-on Workshop
• Workshop Solutions

Procedures, Functions and Packages

• The CREATE Statement
• Parameter Definitions
• Procedure Implementation
• Diagnosing Errors
• Describing Procedure Structure
• Invoking Procedures
• Calling Procedures from SQL*Plus
• Leveraging Output Parameters
• Execution with Output Parameters
• Function Creation Process
• Function Implementation Example
• Diagnosing Errors
• Describing Function Structure
• Invoking Functions
• Calling Functions from SQL*Plus
• Principles of Modular Programming
• Procedure Example
• Function Invocation
• Functions within IF Conditions
• Package Development
• Package Implementation Example
• Advantages of Using Packages
• Public vs. Private Procedures
• Diagnosing Errors
• Describing Package Structure
• Invoking Packages in SQL*Plus
• Calling Packages from Subroutines
• Removing Subprograms
• Locating Subprograms
• Developing Debug Packages
• Executing the Debug Package
• Positional vs. Named Parameter Notation
• Default Parameter Values
• Recompiling Procedures and Functions
• Hands-on Workshop

Triggers

• Developing Triggers
• Statement-Level Triggers
• Row-Level Triggers
• WHEN Clause Restrictions
• Conditional Triggers using IF
• Diagnosing Errors
• COMMIT Operations inside Triggers
• Trigger Limitations
• Mutating Table Triggers
• Locating Existing Triggers
• Removing Triggers
• Generating Auto-Increment Values
• Disabling Triggers
• Enabling Triggers
• Naming Conventions for Triggers

Sample Data

• ORDER Table Structures
• FILM Table Structures
• EMPLOYEE Table Structures

Dynamic SQL

• Integrating SQL within PL/SQL
• Parameter Binding
• Dynamic SQL Concepts
• Native Dynamic SQL
• Handling DDL and DML
• The DBMS_SQL Package
• Dynamic SELECT Statements
• Dynamic SELECT via Procedure

Using Files

• Working with Text Files
• The UTL_FILE Package
• Write and Append Operations
• File Read Operations
• Trigger-Based File Examples
• The DBMS_ALERT Package
• The DBMS_JOB Package

COLLECTIONS

• %TYPE Variable Usage
• Record Variable Handling
• Collection Type Overview
• Index-By Tables
• Assigning Collection Values
• Handling Missing Elements
• Nested Table Concepts
• Initializing Nested Tables
• Using the Constructor Function
• Appending to Nested Tables
• VARRAY Structures
• VARRAY Initialization
• Adding Elements to VARRAYs
• Multi-Level Collections
• Bulk Binding Techniques
• Bulk Bind Implementation Example
• Transaction Management Considerations
• The BULK COLLECT Clause
• RETURNING INTO Clause

Ref Cursors

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