ORACLE PL/SQL Fundamentals Training Course

Introduction

• Goals and Objectives
• Program Schedule
• Participant Introductions
• Prerequisites
• Roles and Responsibilities

SQL Tools

• Learning Objectives
• SQL Developer Overview
• SQL Developer - Establishing Connections
• Reviewing Table Information
• Executing Queries via SQL and SQL Developer
• SQL*Plus Authentication
• Direct Connectivity
• Operating SQL*Plus
• Terminating the Session
• SQL*Plus Command Set
• SQL*Plus Environment Configuration
• SQL*Plus Prompt Usage
• Locating Table Details
• Accessing Assistance
• Utilizing SQL Scripts
• iSQL*Plus and Entity Models
• ORDERS Data Tables
• FILM Data Tables
• Course Table Reference Guide
• SQL Syntax Rules
• SQL*Plus Commands

What is PL/SQL?

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

Variables

• Variable Management
• Data Types
• Variable Assignment
• Constants
• Local vs. Global Variables
• %Type Variables
• Substitution Variables
• Commenting with &
• Verification Options
• && Variables
• Defining and Undefining Variables

SELECT Statement

• SELECT Syntax
• Populating Variables with Data
• %Rowtype Variables
• CHR Function Usage
• Independent Practice
• PL/SQL Records
• Declaration Examples

Conditional Statement

• IF Logic
• SELECT Integration
• Independent Practice
• Case Logic

Trapping Errors

• Exception Handling
• Internal Error Management
• Error Codes and Messages
• Handling No Data Found
• User-Defined Exceptions
• Raising Application Errors
• Catching Undefined Errors
• Applying PRAGMA EXCEPTION_INIT
• Transaction Control: Commit and Rollback
• Independent Practice
• Nested Block Structures
• Practical Workshop

Iteration - Looping

• Infinite Loop (Loop)
• While Loop
• For Loop
• Goto Statements and Labels

Cursors

• Cursor Concepts
• Cursor Properties
• Explicit Cursor Usage
• Explicit Cursor Demonstration
• Cursor Declaration
• Variable Declaration for Cursors
• Opening Cursor and Fetching Initial Row
• Subsequent Row Fetching
• Exit Conditions (%Notfound)
• Closing the Cursor
• For Loop Application I
• For Loop Application II
• Update Scenarios
• FOR UPDATE Clause
• FOR UPDATE OF Clause
• WHERE CURRENT OF Clause
• Managing Commits with Cursors
• Validation Scenario I
• Validation Scenario II
• Cursor Parameters
• Practical Workshop
• Workshop Solutions

Procedures, Functions and Packages

• Create Command
• Parameter Handling
• Procedure Implementation
• Error Visibility
• Procedure Description
• Invoking Procedures
• Calling Procedures via SQL*Plus
• Utilizing Output Parameters
• Invocation with Output Parameters
• Function Creation
• Function Examples
• Error Visibility
• Function Description
• Invoking Functions
• Calling Functions via SQL*Plus
• Modular Development
• Procedure Examples
• Function Invocation
• Functions within IF Statements
• Package Construction
• Package Examples
• Benefits of Packages
• Public and Private Sub-programs
• Error Visibility
• Package Description
• Calling Packages via SQL*Plus
• Inter-package Calls
• Sub-program Deletion
• Locating Sub-programs
• Creating Debug Packages
• Invoking Debug Packages
• Positional and Named Parameter Styles
• Default Parameter Values
• Recompiling Procedures and Functions
• Practical Workshop

Triggers

• Trigger Creation
• Statement-Level Triggers
• Row-Level Triggers
• WHEN Clause Restrictions
• Conditional Triggers (IF)
• Error Visibility
• Committing within Triggers
• Limitations
• Mutating Triggers
• Identifying Triggers
• Trigger Deletion
• Auto-number Generation
• Disabling Triggers
• Enabling Triggers
• Trigger Naming Conventions

Sample Data

• ORDER Dataset
• FILM Dataset
• EMPLOYEE Dataset

Dynamic SQL

• SQL Integration in PL/SQL
• Variable Binding
• Dynamic SQL Concepts
• Native Dynamic SQL
• DDL and DML Operations
• DBMS_SQL Package Usage
• Dynamic SELECT Statements
• Dynamic SELECT Procedures

Using Files

• Text File Operations
• UTL_FILE Package
• Write and Append Scenarios
• Read Scenarios
• Trigger-Based File Operations
• DBMS_ALERT Packages
• DBMS_JOB Package

COLLECTIONS

• %Type Variables
• Record Variables
• Collection Data Types
• Index-By Tables
• Value Assignment
• Handling Nonexistent Elements
• Nested Tables
• Nested Table Initialization
• Constructor Usage
• Inserting into Nested Tables
• Varrays
• Varray Initialization
• Adding to Varrays
• Multilevel Collections
• Bulk Binding
• Bulk Bind Demonstration
• Transaction Considerations
• BULK COLLECT Clause
• RETURNING INTO Clause

Ref Cursors

• Cursor Variables
• REF CURSOR Type Definitions
• Cursor Variable Declarations
• Constrained vs. Unconstrained
• Utilizing Cursor Variables
• Cursor Variable Examples