Oracle Database: PL/SQL Fundamentals Course Outline
General Description
This course introduces one to Oracle database programming using the PL/SQL programming language. One will learn the syntax, structure and features of the language. This textbook will also lay the foundation for the entire Sideris Oracle PL/SQL programming series, allowing one to progress from introductory topics to advanced application design and programming and finally onto writing complex high-performance applications.
PL/SQL may be considered as one of the basic skill sets required for any Oracle professional, nearly as important as the SQL language itself.
This course will also continue on to show those who are now ready to employ the language in the development of database applications. In particular, the focus of this course will be on the use of database-resident stored program units such as procedures, functions, packages and database triggers. New features introduced with the Oracle 12c release of the database are also explained and demonstrated.
+++++++++ NOTE ++++++++++
This course content is applicable to versions 12c, 18c, and 19c.
Target Audience
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
Certification
This course considers subjects applicable to certification as both an Oracle PL/SQL Developer Certified Associate (OCA) and an Oracle Advanced PL/SQL Developer Certified Professional (OCP). The topics considered are included within "Exam 1Z0-144: Program With PL/SQL".
Objectives
This course begins with an explanation of the intent and usage of the PL/SQL programming language for database applications. Important reasons why one should incorporate PL/SQL modules within the application architecture right from the initial design and planning phase are presented. Next one learns how to begin building executable PL/SQL program units. One learns about each of the major segments of a working program and how these interact with each other during program execution, including the important error or exception handling capabilities of the language. The final section goes beyond the basics and begins to explore advanced topics that will be useful later in the Sideris Oracle PL/SQL developer textbook series. One learns techniques and features that allow modular and reusable programs to be developed, increasing productivity and maintainability of database applications.
In addition, this course explains how database-resident program units can be used as part of the overall database application architecture and the benefits realized by doing so. It then builds upon one's knowledge of database-resident program units and applies these to the development of PL/SQL packages. In a production environment most PL/SQL program units should be packaged, and these advanced database programming capabilities along with the benefits of using these are discussed. It concludes with extensive demonstrations on how a particular type of database-resident program unit known as a database trigger can be used as part of an advanced database application design.
COURSE OUTLINE
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
© 2014 Sideris Courseware Corporation
View outline in Word
OSP12C
Facebook
LinkedIn