Oracle Database 12c - PL/SQL Fundamentals

Agenda

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

2 - About Bind & Substitution Variables

• Using SQL Developer
• Using SQL*Plus

3 - Choosing a Database Programming Language

• What is Database Programming?
• PL/SQL Performance Advantages
• Integration wth Other Languages

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

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

6 - Begin Section

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

7 - Exception Section

• ABOUT THE EXCEPTION SECTION
• ISOLATING THE SPECIFIC EXCEPTION
• PRAGMA EXCEPTION_INIT
• SQLCODE &SQLERRM Example
• SQL%ROWCOUNT &SELECT...INTO

8 - Beyond the Basics: Explicit Cursors

• ABOUT EXPLICIT CURSORS
• EXTENDED CURSOR TECHNIQUES
• FOR UPDATE OF Clause
• WHERE CURRENT OF Clause
• Using FOR…LOOP Cursors

9 - Beyond the Basics: Nested Blocks

10 - Beyond the Basics: Declared Subprograms

• USING DECLARED SUBPROGRAMS
• DECLARED PROCEDURE
• DECLARED FUNCTION

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

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

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

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

15 - Managing Dependencies

• DEPENDENCY INTERNALS
• TRACKING DEPENDENCIES
• THE DEPENDENCY TRACKING UTILITY
• SQL DEVELOPER DEPENDENCY INFO
• DEPENDENCY STRATEGY CHECKLISTS

16 - Creating & Maintaining Packages

• ABOUT PACKAGES
• CREATING PACKAGES
• MAINTAINING PACKAGES
• PERFORMANCE CONSIDERATIONS

17 - Advanced Package Capabilities

• DEFINER & INVOKER RIGHTS
• WHITE LITS & ACCESSIBLE BY
• PERSISTENT GLOBAL OBJECTS
• DEFINING INITIALIZATION LOGIC
• OBJECT ORIENTATION SUPPORT

18 - Advanced Cursor Techniques

• USING CUSROS VARIABLES
• USING SYS_REFCURSOR
• USING CURSOR EXPRESSIONS

19 - Using System-Supplied Package

• DBMS_OUTPUT()
• UTL_FILE()
• FOPEN() EXAMPLE

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

22 - Maintaining Database Triggers

• CALL SYNTAX
• TRIGGER MAINTENANCE TASKS
• SHOW ERRORS TRIGGER
• DROP TRIGGER
• ALTER TRIGGER
• MULTIPLE TRIGGERS FOR A TABLE
• HANDLING MUTATING TABLE ISSUES

23 - Implementing System Event Triggers

• WHAT ARE SYSTEM EVENT TRIGGERS?
• DEFININGTHE SCOPE
• AVAILABLE SYSTEM EVENTS
• SYSTEM EVENT ATTRIBUTES