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