ORACLE PL/SQL Fundamentals Training Course

Introduction

• Course Aims and Objectives
• Itinerary and Schedule
• Participant Introductions
• Prerequisites
• Responsibilities

SQL Tools

• Learning Objectives
• SQL Developer Overview
• Connecting via SQL Developer
• Inspecting Table Information
• Running Queries in SQL Developer
• Logging into SQL*Plus
• Establishing Direct Connections
• Operating SQL*Plus
• Terminating the Session
• SQL*Plus Command Reference
• The SQL*Plus Environment
• Understanding the SQL*Plus Prompt
• Retrieving Table Details
• Accessing Help Resources
• Utilizing SQL Files
• iSQL*Plus and Entity Models
• Exploring the ORDERS Tables
• Exploring the FILM Tables
• Distributing the Course Tables Handout
• SQL Syntax Conventions
• Advanced SQL*Plus Commands

What is PL/SQL?

• Overview of PL/SQL
• Benefits of Using PL/SQL
• Understanding Block Structure
• Outputting 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 Variables
• Substitution Variables
• Adding Comments with &
• Verifying Options
• Managing && Variables
• Defining and Undefining Values

SELECT Statement

• The SELECT Command
• Populating Variables with Data
• Utilizing %Rowtype Variables
• The CHR Function
• Self-Study Assignment
• Working with PL/SQL Records
• Reviewing Example Declarations

Conditional Statement

• Implementing IF Statements
• Conditional SELECT Statements
• Self-Study Assignment
• Using Case Statements

Trapping Errors

• Handling Exceptions
• Managing Internal Errors
• Interpreting Error Codes and Messages
• Leveraging No Data Found
• Raising User Exceptions
• Raising Application Errors
• Catching Undefined Errors
• Applying PRAGMA EXCEPTION_INIT
• Managing Commits and Rollbacks
• Self-Study Assignment
• Working with Nested Blocks
• Practical Workshop

Iteration - Looping

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

Cursors

• Introduction to Cursors
• Understanding Cursor Attributes
• Explicit Cursors
• Reviewing Explicit Cursor Examples
• Cursor Declaration
• Variable Declaration for Cursors
• Opening and Fetching the First Row
• Fetching Subsequent Rows
• Exiting When %Notfound
• Closing the Cursor
• For Loop Implementation I
• For Loop Implementation II
• Update Examples
• Using FOR UPDATE
• Specifying FOR UPDATE OF
• Applying WHERE CURRENT OF
• Committing Changes with Cursors
• Validation Example I
• Validation Example II
• Cursor Parameters
• Practical Workshop
• Workshop Solutions

Procedures, Functions and Packages

• Using the Create Statement
• Defining Parameters
• Constructing the Procedure Body
• Displaying Errors
• Describing Procedures
• Invoking Procedures
• Calling Procedures via SQL*Plus
• Utilizing Output Parameters
• Calling with Output Parameters
• Developing Functions
• Reviewing Example Functions
• Displaying Errors
• Describing Functions
• Invoking Functions
• Calling Functions via SQL*Plus
• Principles of Modular Programming
• Reviewing Example Procedures
• Function Invocation
• Using Functions Within IF Statements
• Creating Packages
• Reviewing Package Examples
• Advantages of Using Packages
• Managing Public and Private Sub-programs
• Displaying Errors
• Describing Packages
• Calling Packages via SQL*Plus
• Invoking Packages from Sub-Programs
• Removing Sub-programs
• Locating Sub-programs
• Building a Debug Package
• Executing the Debug Package
• Positional vs. Named Notation
• Setting Default Parameter Values
• Recompiling Procedures and Functions
• Practical Workshop

Triggers

• Creating Triggers
• Statement-Level Triggers
• Row-Level Triggers
• Applying WHEN Restrictions
• Selective Triggers using IF
• Displaying Errors
• Managing Commits within Triggers
• Understanding Restrictions
• Addressing Mutating Triggers
• Locating Triggers
• Removing Triggers
• Generating Auto-incrementing Numbers
• Disabling Triggers
• Enabling Triggers
• Naming Conventions for Triggers

Sample Data

• Exploring ORDER Tables
• Exploring FILM Tables
• Exploring EMPLOYEE Tables

Dynamic SQL

• Integrating SQL in PL/SQL
• Concepts of Binding
• Implementing Dynamic SQL
• Utilizing Native Dynamic SQL
• Handling DDL and DML
• Using the DBMS_SQL Package
• Dynamic SQL for SELECT
• Creating Dynamic SQL SELECT Procedures

Using Files

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

COLLECTIONS

• Using %Type Variables
• Record Variables
• Types of Collections
• Index-By Tables
• Assigning Values
• Handling Nonexistent Elements
• Nested Tables
• Initializing Nested Tables
• Utilizing the Constructor
• Adding Elements to Nested Tables
• Varrays
• Initializing Varrays
• Adding Elements to Varrays
• Multilevel Collections
• Bulk Bind Techniques
• Bulk Bind Examples
• Addressing Transactional Issues
• The BULK COLLECT Clause
• Using RETURNING INTO

Ref Cursors

• Cursor Variables
• Defining REF CURSOR Types
• Declaring Cursor Variables
• Constrained vs. Unconstrained Cursors
• Working with Cursor Variables
• Examples of Cursor Variables