ORACLE PL/SQL Fundamentals Training Course

Introduction

• Goals and Learning Outcomes
• Course Timeline
• Meet and Greet
• Entry Requirements
• Roles and Duties

SQL Utilities

• Objectives
• SQL Developer
• Connecting via SQL Developer
• Inspecting Table Metadata
• Executing Queries in SQL Developer
• Logging into SQL*Plus
• Establishing Direct Connections
• Using SQL*Plus
• Terminating the Session
• SQL*Plus Commands
• SQL*Plus Environment Setup
• SQL*Plus Prompts
• Retrieving Table Details
• Accessing Help Resources
• Executing SQL Scripts
• iSQL*Plus and Entity Models
• The ORDERS Tables
• The FILM Tables
• Course Tables Reference Guide
• SQL Statement Structure
• SQL*Plus Commands

Understanding PL/SQL

• Overview of PL/SQL
• Benefits of Using PL/SQL
• Block Architecture
• Message Output
• Code Samples
• Configuring SERVEROUTPUT
• Update Example and Coding Standards

Variables

• Variable Usage
• Data Types
• Assigning Variables
• Constants
• Local vs. Global Variables
• %Type Variables
• Substitution Variables
• Comments using &
• Verification Options
• && Variables
• Defining and Clearing Variables

SELECT Statements

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

Conditional Logic

• IF Statements
• SELECT Statements in Logic
• Independent Practice
• Case Statements

Error Handling

• Exceptions
• System-Generated Errors
• Error Codes and Messages
• Handling No Data Found
• Custom Exceptions
• Raising Application Errors
• Catching Undefined Errors
• Using PRAGMA EXCEPTION_INIT
• Commit and Rollback Strategies
• Independent Practice
• Nested Blocks
• Workshop Activity

Iteration and Loops

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

Cursors

• Introduction to Cursors
• Cursor Attributes
• Explicit Cursors
• Explicit Cursor Demonstrations
• Cursor Declaration
• Variable Declaration
• Opening and Fetching the First Row
• Fetching Subsequent Rows
• Exit Conditions (%Notfound)
• Closing Cursors
• For Loop I
• For Loop II
• Update Example
• FOR UPDATE Clause
• FOR UPDATE OF Clause
• WHERE CURRENT OF Clause
• Committing Changes with Cursors
• Validation Example I
• Validation Example II
• Cursor Parameters
• Workshop Activity
• Workshop Solutions

Procedures, Functions, and Packages

• The CREATE Statement
• Parameter Handling
• Procedure Body Structure
• Identifying Errors
• Describing Procedures
• Invoking Procedures
• Calling Procedures in SQL*Plus
• Output Parameters
• Invoking with Output Parameters
• Defining Functions
• Function Examples
• Identifying Errors
• Describing Functions
• Invoking Functions
• Calling Functions in SQL*Plus
• Modular Programming Principles
• Procedure Examples
• Function Invocation
• Using Functions in IF Statements
• Creating Packages
• Package Examples
• Advantages of Packages
• Public vs. Private Sub-programs
• Identifying Errors
• Describing Packages
• Calling Packages in SQL*Plus
• Invoking Packages from Sub-programs
• Removing Sub-programs
• Locating Sub-programs
• Building a Debug Package
• Using the Debug Package
• Positional and Named Notation
• Default Parameter Values
• Recompiling Procedures and Functions
• Workshop Activity

Triggers

• Building Triggers
• Statement-Level Triggers
• Row-Level Triggers
• WHEN Conditions
• Conditional Triggers - IF
• Identifying Errors
• Commits within Triggers
• Trigger Limitations
• Mutating Trigger Issues
• Locating Triggers
• Removing Triggers
• Auto-numbering Generation
• Disabling Triggers
• Enabling Triggers
• Naming Conventions for Triggers

Sample Data

• ORDER Tables
• FILM Tables
• EMPLOYEE Tables

Dynamic SQL

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

File Operations

• Working with Text Files
• UTL_FILE Package
• Write/Append Examples
• Read Examples
• Trigger Integration Example
• DBMS_ALERT Packages
• DBMS_JOB Package

COLLECTIONS

• %Type Variables
• Record Variables
• Collection Data Types
• Index-By Tables
• Assigning Values
• Handling Missing Elements
• Nested Tables
• Nested Table Initialization
• Using Constructors
• Adding to Nested Tables
• Varrays
• Varray Initialization
• Adding Elements to Varrays
• Multilevel Collections
• Bulk Binding
• Bulk Binding Examples
• Transactional Considerations
• BULK COLLECT Clause
• RETURNING INTO Clause

Ref Cursors

• Cursor Variables
• Defining REF CURSOR Types
• Declaring Cursor Variables
• Constrained vs. Unconstrained Types
• Utilizing Cursor Variables
• Cursor Variable Examples