ORACLE PL/SQL Fundamentals Training Course

Introduction

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

SQL Tools

• Learning Objectives
• SQL Developer Overview
• Connecting via SQL Developer
• Inspecting Table Metadata
• Executing Queries in SQL Developer
• SQL*Plus Authentication
• Establishing Direct Connections
• Navigating SQL*Plus
• Terminating the Session
• Essential SQL*Plus Commands
• Configuring the SQL*Plus Environment
• Understanding Prompts
• Retrieving Table Details
• Accessing Help Resources
• Running SQL Scripts
• iSQL*Plus and Entity Models
• ORDERS Table Structure
• FILM Table Structure
• Course Materials Handout
• SQL Syntax Rules
• Command Reference

What is PL/SQL?

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

Variables

• Variable Concepts
• Data Types
• Variable Assignment
• Defining Constants
• Scope: Local vs. Global
• %Type Usage
• Substitution Variables
• Comment Syntax with &
• Verification Options
• && Variable Syntax
• DEFINE and UNDEFINE Commands

SELECT Statement

• Using SELECT in PL/SQL
• Assigning Query Results to Variables
• %Rowtype Implementation
• Using the CHR Function
• Independent Practice
• PL/SQL Record Types
• Declaration Examples

Conditional Statement

• IF Logic
• Conditional SELECTs
• Independent Practice
• Case Expressions

Trapping Errors

• Exception Handling
• Managing Internal Errors
• Error Codes and Messages
• Handling No Data Found
• Custom User Exceptions
• Raising Application Errors
• Catching Unhandled Exceptions
• PRAGMA EXCEPTION_INIT
• Transaction Control: Commit and Rollback
• Independent Practice
• Nested Code Blocks
• Practical Exercise

Iteration - Looping

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

Cursors

• Cursor Fundamentals
• Cursor Attributes
• Explicit Cursor Definition
• Explicit Cursor Walkthrough
• Declaring Cursor Objects
• Declaring Fetch Variables
• Opening and Fetching Initial Row
• Iterating Through Subsequent Rows
• Termination via %Notfound
• Closing the Cursor
• For Loop Integration I
• For Loop Integration II
• Update Implementation
• FOR UPDATE Clause
• FOR UPDATE OF Clause
• WHERE CURRENT OF Clause
• Managing Commits with Cursors
• Data Validation Scenario I
• Data Validation Scenario II
• Passing Cursor Parameters
• Practical Exercise
• Exercise Solution

Procedures, Functions and Packages

• CREATE Syntax
• Parameter Definition
• Constructing the Procedure Body
• Error Reporting
• Retrieving Procedure Metadata
• Executing Procedures
• Calling from SQL*Plus
• Output Parameters
• Utilizing Output Values
• Function Creation
• Function Walkthrough
• Error Reporting in Functions
• Retrieving Function Metadata
• Executing Functions
• Calling from SQL*Plus
• Principles of Modular Programming
• Procedural Example
• Function Invocation
• Functions within Conditionals
• Package Creation
• Package Walkthrough
• Advantages of Packages
• Public vs. Private Members
• Error Reporting in Packages
• Retrieving Package Metadata
• Calling Packages from SQL*Plus
• Inter-Procedural Calls
• Dropping Sub-Programs
• Locating Sub-Programs
• Building Debug Packages
• Invoking Debug Packages
• Positional vs. Named Notation
• Default Parameter Values
• Recompilation Techniques
• Practical Exercise

Triggers

• Trigger Creation
• Statement-Level Triggers
• Row-Level Triggers
• WHEN Clause Constraints
• Conditional Triggers (IF Logic)
• Error Reporting
• Transaction Control in Triggers
• System Restrictions
• Mutating Table Issues
• Locating Triggers
• Dropping Triggers
• Auto-Number Generation
• Disabling Triggers
• Enabling Triggers
• Naming Conventions

Sample Data

• ORDERS Tables
• FILM Tables
• EMPLOYEE Tables

Dynamic SQL

• SQL Integration with PL/SQL
• Binding Variables
• Dynamic Query Execution
• Native Dynamic SQL
• Handling DDL and DML
• DBMS_SQL Utilization
• Dynamic SELECT Statements
• Dynamic SELECT Procedures

Using Files

• Text File Interaction
• UTL_FILE Package
• Writing and Appending Data
• Reading Data Examples
• Trigger-Based File Operations
• DBMS_ALERT Usage
• DBMS_JOB Scheduling

COLLECTIONS

• Using %Type in Collections
• Record Variables
• Collection Data Types
• Index-By Tables
• Assigning Collection Values
• Handling Missing Elements
• Nested Tables
• Initializing Nested Tables
• Constructor Usage
• Appending to Nested Tables
• VARRAYs
• VARRAY Initialization
• Adding Elements to VARRAYs
• Multilevel Collection Structures
• Bulk Binding Concepts
• Bulk Binding Example
• Transaction Considerations
• BULK COLLECT Clause
• RETURNING INTO Clause

Ref Cursors

• Cursor Variables Explained
• Defining REF CURSOR Types
• Declaration Syntax
• Constrained vs. Unconstrained Types
• Utilizing Cursor Variables
• Practical Examples