Oracle Database 12c - PL/SQL Fundamentals

Course Content:

Lesson 1 - 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

Lesson 2 - About Bind & Substitution Variables
Using SQL Developer
Using SQL*Plus

Lesson 3 - Choosing a Database Programming Language
What is Database Programming?
PL/SQL Performance Advantages
Integration wth Other Languages

Lesson 4 - 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

Lesson 5 - 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

Lesson 6 - Begin Section
About the Begin Section
Manipulating Program Data
Logic Control & Branching
GOTO
LOOP
IF-THEN-ELSE
CASE

Lesson 7 - Exception Section
ABOUT THE EXCEPTION SECTION
ISOLATING THE SPECIFIC EXCEPTION
PRAGMA EXCEPTION_INIT
SQLCODE &SQLERRM Example
SQL%ROWCOUNT &SELECT...INTO

Lesson 8 - Beyond the Basics: Explicit Cursors
ABOUT EXPLICIT CURSORS
EXTENDED CURSOR TECHNIQUES
FOR UPDATE OF Clause
WHERE CURRENT OF Clause
Using FOR…LOOP Cursors

Lesson 9 - Beyond the Basics: Nested Blocks

Lesson 10 - Beyond the Basics: Declared Subprograms
USING DECLARED SUBPROGRAMS
DECLARED PROCEDURE
DECLARED FUNCTION

Lesson 11 - Introducing Databse-Resident Program Units
ABOUT DATABASE---RESIDENT PROGRAMS
PHYSICAL STORAGE & EXECUTION
TYPES OF STORED PROGRAM UNITS
STORED PROGRAM UNIT ADVANTAGES
MODULAR DESIGN PRINCIPLES

Lesson 12 - 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

Lesson 13 - 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

Lesson 14 - 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

Lesson 15 - Managing Dependencies
DEPENDENCY INTERNALS
TRACKING DEPENDENCIES
THE DEPENDENCY TRACKING UTILITY
SQL DEVELOPER DEPENDENCY INFO
DEPENDENCY STRATEGY CHECKLISTS

Lesson 16 - Creating & Maintaining Packages
ABOUT PACKAGES
CREATING PACKAGES
MAINTAINING PACKAGES
PERFORMANCE CONSIDERATIONS

Lesson 17 - Advanced Package Capabilities
DEFINER & INVOKER RIGHTS
WHITE LITS & ACCESSIBLE BY
PERSISTENT GLOBAL OBJECTS
DEFINING INITIALIZATION LOGIC
OBJECT ORIENTATION SUPPORT

Lesson 18 - Advanced Cursor Techniques
USING CUSROS VARIABLES
USING SYS_REFCURSOR
USING CURSOR EXPRESSIONS

Lesson 19 - Using System-Supplied Package
DBMS_OUTPUT()
UTL_FILE()
FOPEN() EXAMPLE

Lesson 20 - Database Trigger Concepts
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

Lesson 21 - Maintaining Database Triggers
CALL SYNTAX
TRIGGER MAINTENANCE TASKS
SHOW ERRORS TRIGGER
DROP TRIGGER
ALTER TRIGGER
MULTIPLE TRIGGERS FOR A TABLE
HANDLING MUTATING TABLE ISSUES

Lesson 22 - Implementing System Event Triggers
WHAT ARE SYSTEM EVENT TRIGGERS?
DEFININGTHE SCOPE
AVAILABLE SYSTEM EVENTS
SYSTEM EVENT ATTRIBUTES

Prerequisites:
Oracle Database 12c – SQL Fundamentals