ORACLE PL/SQL Fundamentals Training Course

Overview

• Learning Goals and Objectives
• Course Itinerary
• Participant Introductions
• Prerequisite Requirements
• Participant and Instructor Responsibilities

SQL Utilities

• Learning Objectives
• SQL Developer
• Establishing a Connection in SQL Developer
• Reviewing Table Information
• Executing Queries using SQL Developer
• Logging in to SQL*Plus
• Establishing a Direct Connection
• Navigating SQL*Plus
• Terminating the Session
• SQL*Plus Command Syntax
• SQL*Plus Environment Setup
• Understanding the SQL*Plus Prompt
• Retrieving Table Metadata
• Accessing Help Resources
• Running SQL Scripts
• iSQL*Plus and Entity Models
• ORDERS Table Structure
• FILM Table Structure
• Course Reference Tables
• SQL Syntax Standards
• SQL*Plus Command Recap

Introduction to PL/SQL

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

Variables

• Variable Concepts
• Data Types
• Assigning Values to Variables
• Defining Constants
• Scope: Local vs Global Variables
• %Type Variables
• Substitution Variables
• Adding Comments using &
• The Verify Option
• && Variables
• Define and Undefine Commands

SELECT Statements

• Using the SELECT Statement
• Populating Variables from Queries
• %Rowtype Variables
• The CHR Function
• Independent Practice
• PL/SQL Records
• Record Declaration Examples

Conditional Logic

• IF Statements
• Conditional SELECT Statements
• Independent Practice
• CASE Statements

Error Handling

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

Iteration and Loops

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

Cursors

• Cursor Overview
• Cursor Attributes
• Explicit Cursors
• Explicit Cursor Examples
• Declaring a Cursor
• Declaring the Variable
• Opening the Cursor and Fetching the First Row
• Fetching Subsequent Rows
• Termination Condition: Exit When %Notfound
• Closing the Cursor
• For Loop Integration I
• For Loop Integration II
• Update Scenarios
• FOR UPDATE Clause
• FOR UPDATE OF Clause
• WHERE CURRENT OF Clause
• Committing Changes within Cursor Operations
• Data Validation Example I
• Data Validation Example II
• Passing Cursor Parameters
• Practical Workshop
• Workshop Solutions

Procedures, Functions, and Packages

• The Create Statement
• Parameter Management
• Procedure Logic
• Displaying Compilation Errors
• Inspecting Procedure Details
• Executing Procedures
• Running Procedures via SQL*Plus
• Utilizing Output Parameters
• Handling Output Parameters in Calls
• Function Creation
• Function Examples
• Displaying Function Errors
• Inspecting Function Details
• Invoking Functions
• Running Functions via SQL*Plus
• Principles of Modular Programming
• Procedure Examples
• Function Integration
• Using Functions within IF Conditions
• Package Creation
• Package Examples
• Advantages of Packages
• Public vs Private Sub-programs
• Displaying Package Errors
• Inspecting Package Details
• Invoking Packages via SQL*Plus
• Calling Packages from Other Sub-programs
• Removing Sub-programs
• Locating Existing Sub-programs
• Creating Debugging Packages
• Using the Debug Package
• Positional vs Named Notation
• Setting Default Parameter Values
• Recompiling Procedures and Functions
• Practical Workshop

Triggers

• Trigger Creation
• Statement-Level Triggers
• Row-Level Triggers
• Applying WHEN Conditions
• Conditional Triggers using IF
• Displaying Trigger Errors
• Transaction Control in Triggers
• Trigger Limitations
• Mutating Trigger Issues
• Identifying Triggers
• Removing Triggers
• Automatic Number Generation
• Disabling Triggers
• Enabling Triggers
• Naming Conventions for Triggers

Sample Datasets

• ORDER Tables
• FILM Tables
• EMPLOYEE Tables

Dynamic SQL

• Embedding SQL in PL/SQL
• Binding Variables
• Dynamic SQL Concepts
• Native Dynamic SQL
• Executing DDL and DML Dynamically
• The DBMS_SQL Package
• Dynamic SELECT Statements
• Dynamic SELECT Procedures

File Handling

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

Collections

• %Type Variables in Collections
• Record Variables
• Collection Data Types
• Index-By Tables
• Assigning Collection Values
• Handling Missing Elements
• Nested Tables
• Initializing Nested Tables
• Using the Constructor
• Inserting Elements into Nested Tables
• Varrays
• Initializing Varrays
• Adding Elements to Varrays
• Multilevel Collection Structures
• Bulk Binding
• Bulk Bind Examples
• Transaction Considerations
• The BULK COLLECT Clause
• Using RETURNING INTO

Ref Cursors

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