Oracle Database 12c: PL/SQL Fundamentals I and II

Course Details

• 12c PL/SQL I
• 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
• PL/SQL II – Intermediate: Develop Program
• 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
• 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