Course Description

This Advanced PL/SQL course will cover topics such as Triggers, exceptions, creating packages and procedures and much more

Prerequisites

Required: Basic IT and computer skills.  Basic analytic or programming skills.

Number of Days and Format

Download Outline


Number of Days:  This is 4 day class

Format:  Instructor lead (in person), remote, training on-demand

Course curriculum

  • 1
    Module 00 Advanced PL/SQL Into
    • Advanced 12c PL:SQL Intro
  • 2
    Introduction
    • Introduction
    • PLSQL Review
  • 3
    SQL Joins
    • Displaying Data from Multiple Tables Using Joins
    • Displaying Data from Multiple Tables Using Joins Part 2
    • Labs Displaying Data from Multiple Tables by Using Joins
  • 4
    Subqueries
    • Using Subqueries to Solve Queries Part 1
    • Using Subqueries to Solve Queries Part 2
    • Labs Using Subqueries to Solve Queries
  • 5
    Declaring Variables
    • Defining Variables
    • Types of Variables
  • 6
    Writing Executable Statements
    • Definition of a PL/SQL Block
    • Operators in PL/SQL
  • 7
    Oracle Database Server: SQL Statements in PL/SQL
    • Oracle Database Server: SQL Statements in PL/SQL
  • 8
    Control Structures
    • IF Then Else Statements
    • Looping Structures
  • 9
    Composite Data Types
    • Composite Data Types
    • Composite Array Data Types
  • 10
    Explicit Cursors
    • Explicit Cursors
    • Cursor Parameters
    • For Update Clause
  • 11
    Handling Exceptions
    • Handling Exceptions
    • Exceptions in a Subblock
  • 12
    Creating Procedures
    • Creating Procedures
    • Formal and Actual Parameters
    • Exception Handling in a Procedure
  • 13
    Creating Functions & Debugging Subprograms
    • Creating Functions & Debugging Subprograms
    • Named and Mixed Notation
    • Debugging your code
  • 14
    Creating Packages
    • Creating Packages
    • Viewing Package information in the Data Dictionary
  • 15
    Working with Packages
    • Working with Packages
    • Package Variables
  • 16
    Oracle-Supplied Packages
    • Oracle-Supplied Packages
    • Using Oracle Supplied Packages
  • 17
    Dynamic SQL
    • Dynamic SQL
  • 18
    PL/SQL Design Considerations
    • PL/SQL Design Considerations
    • Deterministic Functions
  • 19
    Creating Triggers
    • Creating Triggers
    • Trigger Variables (:OLD and :NEW)
    • Resources
  • 20
    PL/SQL Profiling/Trace
    • PL/SQL Profiling
    • LAB DBMS Profiling
  • 21
    Resources
    • PL SQL Labs
    • PL SQL Labs2
    • Resources
    • DBMS_TRACE
    • SQL Labs

Instructor(s)