ORACLE PL/SQL Fundamentals Training Course

Introduction

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

SQL Tools

• Learning Objectives
• Overview of SQL Developer
• Establishing SQL Developer Connections
• Inspecting Table Metadata
• Executing Queries in SQL Developer
• Logging in via SQL*Plus
• Direct Database Connections
• Operational Use of SQL*Plus
• Terminating Sessions
• Core SQL*Plus Commands
• SQL*Plus Environment Setup
• Understanding the SQL*Plus Prompt
• Retrieving Table Information
• Accessing Help Resources
• Running SQL Scripts
• iSQL*Plus and Entity Models
• The ORDERS Tables Structure
• The FILM Tables Structure
• Course Tables Reference Guide
• SQL Syntax Fundamentals
• Advanced SQL*Plus Commands

Understanding PL/SQL

• Definition and Scope of PL/SQL
• Benefits of Using PL/SQL
• Anatomy of a PL/SQL Block
• Displaying Output Messages
• Code Samples
• Configuring SERVEROUTPUT
• Update Example and Coding Style Guidelines

Variables

• Variable Concepts
• Data Types Overview
• Assigning Variable Values
• Defining Constants
• Local vs. Global Variable Scope
• Using %Type Declarations
• Substitution Variables
• Commenting with Special Characters
• The VERIFY Option
• Advanced Variable Syntax
• DEFINE and UNDEFINE Commands

SELECT Statements

• Using SELECT in PL/SQL
• Populating Variables with Data
• %Rowtype Variable Declarations
• The CHR Function
• Independent Study Activity
• PL/SQL Record Types
• Declaration Examples

Conditional Logic

• The IF Statement
• Conditional SELECT Statements
• Independent Study Activity
• The CASE Statement

Error Handling

• Exception Handling Basics
• Internal Errors
• Error Codes and Messages
• Handling No Data Found
• Raising User-Defined Exceptions
• Raising Application Errors
• Catching Undefined Errors
• Using PRAGMA EXCEPTION_INIT
• Transaction Control: Commit and Rollback
• Independent Study Activity
• Nested Blocks
• Practical Workshop

Iteration and Looping

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

Cursors

• Introduction to Cursors
• Cursor Attributes
• Explicit Cursors
• Explicit Cursor Use Case
• Cursor Declaration
• Variable Declaration for Cursors
• Opening and Fetching the First Record
• Fetching Subsequent Records
• Exit Condition: %NOTFOUND
• Closing the Cursor
• For Loop Integration I
• For Loop Integration II
• Update Operations with Cursors
• FOR UPDATE Clause
• FOR UPDATE OF Clause
• WHERE CURRENT OF Clause
• Committing Changes with Cursors
• Validation Scenarios I
• Validation Scenarios II
• Cursor Parameters
• Practical Workshop
• Workshop Solutions

Procedures, Functions, and Packages

• The CREATE Statement
• Managing Parameters
• Writing the Procedure Body
• Diagnostic Error Reporting
• Describing Procedure Definitions
• Invoking Procedures
• Calling Procedures via SQL*Plus
• Utilizing Output Parameters
• Executing Calls with Output Parameters
• Function Creation
• Function Examples
• Diagnostic Error Reporting for Functions
• Describing Function Definitions
• Invoking Functions
• Calling Functions via SQL*Plus
• Principles of Modular Programming
• Procedure Examples
• Function Integration
• Using Functions within Conditional Statements
• Package Creation
• Package Examples
• Benefits of Using Packages
• Public and Private Sub-programs
• Diagnostic Error Reporting for Packages
• Describing Package Definitions
• Invoking Packages via SQL*Plus
• Calling Packages from Other Sub-programs
• Dropping Sub-programs
• Locating Sub-programs
• Creating a Debugging Package
• Using the Debug Package
• Positional vs. Named Parameter Notation
• Default Parameter Values
• Recompiling Procedures and Functions
• Practical Workshop

Triggers

• Creating Triggers
• Statement-Level Triggers
• Row-Level Triggers
• WHEN Clauses for Restriction
• Conditional Triggers using IF
• Diagnostic Error Reporting
• Commit Operations in Triggers
• Trigger Limitations
• Mutating Table Issues
• Identifying Triggers
• Dropping Triggers
• Generating Auto-numbering
• Disabling Triggers
• Enabling Triggers
• Naming Conventions for Triggers

Sample Data

• ORDER Tables Dataset
• FILM Tables Dataset
• EMPLOYEE Tables Dataset

Dynamic SQL

• Integrating SQL into PL/SQL
• Variable Binding
• Dynamic SQL Concepts
• Native Dynamic SQL
• Executing DDL and DML Dynamically
• The DBMS_SQL Package
• Dynamic SELECT Statements
• Dynamic SELECT Procedures

File Operations

• Handling Text Files
• The UTL_FILE Package
• Writing and Appending Examples
• Reading File Examples
• Trigger-Based File Operations
• The DBMS_ALERT Package
• The DBMS_JOB Package

COLLECTIONS

• %Type Variable Collections
• Record Variables
• Collection Types Overview
• Index-By Tables
• Assigning Values to Collections
• Handling Nonexistent Elements
• Nested Tables
• Initializing Nested Tables
• Using Constructor Functions
• Appending to Nested Tables
• Varrays
• Initializing Varrays
• Adding Elements to Varrays
• Multilevel Collections
• Bulk Binding Techniques
• Bulk Binding Examples
• Considerations for Transactions
• The BULK COLLECT Clause
• RETURNING INTO Clause

Ref Cursors

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