Course Description
In this course, students will learn the foundation for the programming series and the use of database-resident stored program units for Oracle 12c.
Audience Profile
The target audience for this textbook is all Oracle professionals. Among the specific groups for whom this textbook will be helpful are:
- Application designers and database developers
- Database administrators
- Web server administrators
Prerequisites
Oracle Database 12c: SQL Fundamentals
Learning Objectives
Upon successful completion of this course, students will be able to work with Oracle database programming using the PL/SQL programming language. They will learn the syntax, structure and features of the language.
Course Outline
Module 1: Selection & Setup of the Database Interface
Lessons
• Considering Available Tools
• Selecting the Appropriate Tool
• Oracle Net Database Connections
• Oracle PAAS Database Connections
• Setup SQL Developer
• Setup SQL*Plus
• Setup Jdeveloper
Module 2: About Bind & Substitution Variables
Lessons
• Using SQL Developer
• Using SQL*Plus
Module 3: Choosing a Database Programming Language
Lessons
• What is Database Programming?
• PL/SQL Performance Advantages
• Integration with Other Languages
Module 4: PL/SQL
Lessons
• PL/SQL Program Structure
• LANGUAGE SYNTAX RULES
• EMBEDDING SQL
• WRITING READABLE CODE
• GENERATING DATABASE OUTPUT
• SQL*PLUS INPUT OF A PROGRAM BLOCK
Module 5: Declare Section
Lessons
• 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
Module 6: Begin Section
Lessons
• About the Begin Section
• Manipulating Program Data
• Logic Control & Branching
• GOTO
• LOOP
• IF-THEN-ELSE
• CASE
Module 7: Exception Section
Lessons
• ABOUT THE EXCEPTION SECTION
• ISOLATING THE SPECIFIC EXCEPTION
• PRAGMA EXCEPTION_INIT
• SQLCODE &SQLERRM Example
• SQL%ROWCOUNT &SELECT…INTO
Module 8: Beyond the Basics: Explicit Cursors
Lessons
• ABOUT EXPLICIT CURSORS
• EXTENDED CURSOR TECHNIQUES
• FOR UPDATE OF Clause
• WHERE CURRENT OF Clause
• Using FOR…LOOP Cursors
Module 9: Beyond the Basics: Nested Blocks
Lessons
Module 10: Beyond the Basics: Declared Subprograms
Lessons
• USING DECLARED SUBPROGRAMS
• DECLARED PROCEDURE
• DECLARED FUNCTION
Module 11: Introducing Database-Resident Program Units
Lessons
• ABOUT DATABASE—RESIDENT PROGRAMS
• PHYSICAL STORAGE & EXECUTION
• TYPES OF STORED PROGRAM UNITS
• STORED PROGRAM UNIT ADVANTAGES
• MODULAR DESIGN PRINCIPLES
Module 12: Creating Stored Procedures & Functions
Lessons
• 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
Module 13: Executing Stored Procedures & Functions
Lessons
• CALLING PROCEDURES & FUNCTIONS
• UNIT TESTING WITH EXECUTE
• ANONYMOUS BLOCK UNIT TESTING
• SPECIFYING A PARAMETER NOTATION
• SQL WORKSHEET UNIT TESTING
• CALLING FUNCTIONS FROM SQL
Module 14: Maintaining Stored Program Units
Lessons
• 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
Module 15: Managing Dependencies
Lessons
• DEPENDENCY INTERNALS
• TRACKING DEPENDENCIES
• THE DEPENDENCY TRACKING UTILITY
• SQL DEVELOPER DEPENDENCY INFO
• DEPENDENCY STRATEGY CHECKLISTS
Module 16: Creating & Maintaining Packages
Lessons
• ABOUT PACKAGES
• CREATING PACKAGES
• MAINTAINING PACKAGES
• PERFORMANCE CONSIDERATIONS
Module 17: Advanced Package Capabilities
Lessons
• DEFINER & INVOKER RIGHTS
• WHITE LITS & ACCESSIBLE BY
• PERSISTENT GLOBAL OBJECTS
• DEFINING INITIALIZATION LOGIC
• OBJECT ORIENTATION SUPPORT
Module 18: Advanced Cursor Techniques
Lessons
• USING CUSROS VARIABLES
• USING SYS_REFCURSOR
• USING CURSOR EXPRESSIONS
Module 19: Using System-Supplied Package
Lessons
• DBMS_OUTPUT()
• UTL_FILE()
• FOPEN() EXAMPLE
Module 20: Database Trigger Concepts
Lessons
• ABOUT DATABASE TRIGGERS
• DML EVENT TRIGGER SUB—TYPES
• DATABASE TRIGGER SCENARIO
• TRIGGER EXECUTION MECHANISMS
• TRIGGERS WITHIN SQL WORKSHEET
Module 21: Creating Database Triggers
Lessons
• 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
Module 22: Maintaining Database Triggers
Lessons
• CALL SYNTAX
• TRIGGER MAINTENANCE TASKS
• SHOW ERRORS TRIGGER
• DROP TRIGGER
• ALTER TRIGGER
• MULTIPLE TRIGGERS FOR A TABLE
• HANDLING MUTATING TABLE ISSUES
Module 23: Implementing System Event Triggers
Lessons
• WHAT ARE SYSTEM EVENT TRIGGERS?
• DEFININGTHE SCOPE
• AVAILABLE SYSTEM EVENTS
• SYSTEM EVENT ATTRIBUTES
