Oracle Database: PL/SQL Fundamentals

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

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