ORACLE PL/SQL Fundamentals Training Course

Introduction

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

SQL Tooling

• Learning Objectives
• Oracle SQL Developer
• SQL Developer - Establishing Connections
• Inspecting Table Metadata
• Executing Queries in SQL Developer
• SQL*Plus Login
• Direct Connection Methods
• SQL*Plus Operations
• Terminating Sessions
• SQL*Plus Command Set
• SQL*Plus Environment Configuration
• SQL*Plus Prompt Usage
• Retrieving Table Information
• Accessing Help Resources
• Utilizing SQL Scripts
• iSQL*Plus and Entity Models
• The ORDERS Table Structure
• The FILM Table Structure
• Course Tables Reference
• SQL Statement Syntax
• Advanced SQL*Plus Commands

Introduction to PL/SQL

• Overview of PL/SQL
• Benefits of Using PL/SQL
• Block Structure
• Outputting Messages
• Sample Code Walkthrough
• Configuring SERVEROUTPUT
• Update Example and Style Guidelines

Variables

• Variable Concepts
• Data Types
• Assigning Values to Variables
• Constants
• Local vs. Global Variables
• %Type Variables
• Substitution Variables
• Comments using the Ampersand
• Verify Option
• && Variables
• Defining and Undefining Variables

SELECT Statements

• Understanding SELECT Statements
• Populating Variables from Queries
• %Rowtype Variables
• The CHR Function
• Self-Study Exercise
• PL/SQL Records
• Example Record Declarations

Conditional Logic

• IF Statements
• SELECT in Conditionals
• Self-Study Exercise
• CASE Statements

Error Handling

• Exception Concepts
• Internal Error Types
• 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
• Self-Study Exercise
• Nested Blocks
• Workshop Activity

Iteration and Looping

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

Cursors

• Cursor Basics
• Cursor Attributes
• Explicit Cursors
• Explicit Cursor Demonstration
• Cursor Declaration
• Variable Declaration for Cursors
• Opening and Fetching First Row
• Fetching Subsequent Rows
• Exit on %NOTFOUND
• Closing the Cursor
• For Loop Integration I
• For Loop Integration II
• Update via Cursor Example
• FOR UPDATE Clause
• FOR UPDATE OF
• WHERE CURRENT OF
• Committing with Cursors
• Validation Example I
• Validation Example II
• Cursor Parameters
• Workshop Activity
• Workshop Solution

Procedures, Functions, and Packages

• CREATE Statement Syntax
• Parameter Definitions
• Procedure Body Structure
• Error Display Techniques
• Inspecting Procedure Details
• Invoking Procedures
• Calling Procedures via SQL*Plus
• Using OUT Parameters
• Calling with OUT Parameters
• Creating Functions
• Function Example
• Error Display in Functions
• Inspecting Function Details
• Invoking Functions
• Calling Functions via SQL*Plus
• Principles of Modular Programming
• Procedure Example
• Function Invocation
• Functions within IF Statements
• Creating Packages
• Package Example
• Advantages of Packages
• Public and Private Sub-programs
• Error Display in Packages
• Inspecting Package Details
• Calling Packages via SQL*Plus
• Calling Packages from Sub-programs
• Dropping Sub-programs
• Locating Sub-programs
• Creating a Debug Package
• Invoking the Debug Package
• Positional and Named Notation
• Default Parameter Values
• Recompiling Procedures and Functions
• Workshop Activity

Triggers

• Creating Triggers
• Statement-Level Triggers
• Row-Level Triggers
• WHEN Clause Restrictions
• Conditional Triggers using IF
• Error Display in Triggers
• Commit Restrictions in Triggers
• General Trigger Restrictions
• Mutating Table Issues
• Identifying Triggers
• Dropping Triggers
• Generating Auto-increment Values
• Disabling Triggers
• Enabling Triggers
• Trigger Naming Conventions

Sample Data

• ORDER Tables
• FILM Tables
• EMPLOYEE Tables

Dynamic SQL

• Executing SQL within PL/SQL
• Binding Variables
• Concepts of Dynamic SQL
• Native Dynamic SQL
• DDL and DML Operations
• DBMS_SQL Package
• Dynamic SQL for SELECT
• Dynamic SQL SELECT Procedure

File Handling

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

Collections

• %Type Variables
• Record Variables
• Collection Types Overview
• Index-By Tables
• Assigning Values
• Handling Nonexistent Elements
• Nested Tables
• Nested Table Initialization
• Using the Constructor
• Extending Nested Tables
• VARRAYs
• VARRAY Initialization
• Adding Elements to VARRAYs
• Multilevel Collections
• Bulk Binding
• Bulk Binding 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