Oracle PL/SQL Fundamentals (ORPLSQL) – Outline

Detailed Course Outline

Selection & Setup Of The Database Interface
  • Considering Available Tools
  • Selecting The Appropriate Tool
  • Oracle NET Database Connections
  • Oracle PAAS Database Connections
  • Setup SQL Developer
  • Setup SQL*PLUS
  • Setup JDeveloper
About BIND & SUBSTITUTION Variables
  • Using SQL Developer
  • Using SQL*PLUS
Choosing A Database Programming Language
  • What Is Database Programming?
  • PL/SQL Performance Advantages
  • Integration With Other Languages
PL/SQL Language Fundamentals
  • PL/SQL Program Structure
  • Language Syntax Rules
  • Embedding SQL
  • Writing Readable Code
  • Generating Database Output
  • SQL*PLUS Input Of A Program Block
DECLARE Section
  • About The DECLARE Section
  • DECLARE Primitive Types
  • Declaration Options
  • NOT NULL
  • CONSTANT
  • Data Dictionary Integration
  • %TYPE
  • Declare Simple User-Defined Types
  • TYPE ... TABLE
  • TYPE ... RECORD
  • Extended User-Defined Types
BEGIN Section
  • About The BEGIN Section
  • Manipulating Program Data
  • Logic Control & Branching
  • GOTO
  • LOOP
  • IF-THEN-ELSE
  • CASE
Exception Section
  • About The EXCEPTION Section
  • Isolating The Specific EXCEPTION
  • PRAGMA EXCEPTION_INIT
  • SQLCODE & SQLERRM Example
  • SQL%ROWCOUNT & SELECT...INTO
Beyond The Basics: Explicit Cursors
  • About Explicit Cursors
  • Extended Cursor Techniques
  • FOR UPDATE OF Clause
  • WHERE CURRENT OF Clause
  • Using FOR…LOOP Cursors
Beyond The Basics: Nested Blocks
Beyond The Basics: DECLARED Subprograms
  • Using DECLARED Subprograms
  • DECLARED Procedure
  • DECLARED Function
Introducing Database-Resident Program Units
  • About Database-Resident Programs
  • Physical Storage & Execution
  • Types Of Stored Program Units
  • Stored Program Unit Advantages
  • Modular Design Principles
Creating Stored Procedures & Functions
  • Stored Procedures & Functions
  • CREATE Procedure / CREATE Function
  • Creating Procedures & Functions
  • RAISE_SALARY() Procedure
  • SALARY_VALID() Function
  • The Parameter Specification
  • DEFAULT Clause
  • SYSTEM & OBJECT Privileges
  • Using The Development Tools
Executing Stored Procedures & Functions
  • Calling Procedures & Functions
  • Unit Testing With EXECUTE
  • ANONYMOUS BLOCK Unit Testing
  • Specifying A Parameter Notation
  • SQL Worksheet Unit Testing
  • Calling Functions From SQL
Maintaining Stored Program Units
  • Recompiling Programs
  • Mass Recompilation Using UTL_RECOMP()
  • Dropping Procedures & Functions
  • DROP Procedure / Function
  • Data Dictionary Metadata
  • Using USER_OBJECTS
  • Using USER_SOURCE
  • Using USER_ERRORS
  • Using USER_OBJECT_SIZE
  • Using USER_DEPENDENCIES
Managing Dependencies
  • DEPENDENCY INTERNALS
  • TRACKING DEPENDENCIES
  • The DEPENDENCY TRACKING Utility
  • SQL Developer Dependency Info
  • Dependency Strategy Checklists
Creating & Maintaining Packages
  • About Packages
  • Creating Packages
  • Maintaining Packages
  • Performance Considerations
Advanced Package Capabilities
  • Definer & Invoker Rights
  • White Lists & Accessible By
  • Persistent Global Objects
  • Defining Initialization Logic
  • Object Orientation Support
Advanced Cursor Techniques
  • USING CURSOR VARIABLES
  • Using SYS_REFCURSOR
  • Using CURSOR Expressions
Using System-Supplied Packages
  • DBMS_OUTPUT()
  • UTL_FILE()
  • FOPEN() Example
Database Trigger Concepts
  • About Database Triggers
  • DML EVENT TRIGGER Sub-Types
  • DATABASE TRIGGER Scenario
  • TRIGGER Execution Mechanisms
  • TRIGGERS Within SQL Worksheet
Creating Database Triggers
  • STATEMENT-LEVEL TRIGGERS
  • Using RAISE_APPLICATION_ERROR()
  • ROW-LEVEL TRIGGERS
  • EXAMPLES OF TRIGGERS
  • EMPLOYEE_SALARY_CHECK Example
  • EMPLOYEE_JOURNAL Example
  • BUDGET_EVENT Example
  • INSTEAD OF TRIGGERS
  • Triggers Within An Application
Maintaining Database Triggers
  • CALL Syntax
  • Trigger Maintenance Tasks
  • SHOW ERRORS Trigger
  • DROP Trigger
  • ALTER Trigger
  • Multiple Triggers For A Table
  • Handling Mutating Table Issues
Implementing System Event Triggers
  • What Are System Event Triggers?
  • Defining The Scope
  • Available System Events
  • System Event Attributes