Oracle Database 10g:

Program With PL/SQL

Course Outline

Duration: 5 Days

 Prerequisites: The following courses or equivalent knowledge

Objectives:

Part 1:

The primary objective of this course is to provide an introduction to the PL/SQL database programming language, based upon the latest features available with the Oracle10g Release 2 (R2) revision.

Major subject areas to be explored are:
• Understand the environment and context in which PL/SQL operates.
• Consider the advantages and benefits of PL/SQL within a database environment.
• Declaring program variables and complex data types.
• Developing logic within PL/SQL program blocks.
• Fetching data from the database into program variables.
• Returning program output to users.
• Handling program exceptions.
• Using explicit and implicit database cursors.
• Using the advanced features of nested blocks and subprograms.
• How to take advantage of new features such as regular expression support and the handling of string literals within program code.

Part 2:

The Oracle PL/SQL language provides for programming logic features not contained within SQL. The grammar, syntax and benefits of using the PL/SQL language within an Oracle database environment are considered.

The objective of this course is to equip students to develop database server-side PL/SQL program units within an Oracle database. Major subject areas to be explored and secondary objectives are:
• Understanding application partitioning within a client/server or multi-tiered web-based architecture.
• Understanding the basic form and structure of program units stored within the database.
• Building and maintaining database stored program units.
• Encapsulating stored units within packages and taking advantage of accompanying advanced programming techniques such as cursor variables and cursor expressions.
• Handling intricate theoretical challenges, such as mutating tables.
• Building and maintaining DML-event and system-event database triggers.
• Discuss the storage and execution model for database programs and how one can write efficient programs to maximize performance.
• Using system-supplied packages to extend the power of your SQL statements and PL/SQL applications.

 

Course Content:

Part 1:

LANGUAGE FEATURES
• UNDERSTANDING PL/SQL
• STRUCTURE OF A PL/SQL PROGRAM BLOCK
• PL/SQL FROM INTERACTIVE TOOLS
• GENERATING OUTPUT WITHIN SQL*PLUS

DECLARE CLAUSE
• ABOUT THE DECLARE CLAUSE
• DECLARE SIMPLE TYPES
• COMPLEX TYPES
• TYPE ... TABLE
• TYPE ... RECORD

BEGIN CLAUSE
• ABOUT THE BEGIN CLAUSE
• PERFORMING DATA MANIPULATION
• LOGIC CONTROL & BRANCHING
• IF-THEN-ELSE
• CASE

EXCEPTION CLAUSE
• ABOUT THE EXCEPTION CLAUSE
• ISOLATING THE SPECIFIC EXCEPTION
• PRAGMA EXCEPTION_INIT
• SQL%ROWCOUNT & SELECT...INTO

EXPLICIT CURSORS
• ABOUT EXPLICIT CURSORS
• ADVANCED CURSOR TECHNIQUES

ADVANCED PROGRAMMING: NESTED BLOCKS

ADVANCED PROGRAMMING: DECLARED SUBPROGRAMS

Part 2:

INTRODUCING DATABASE PROGRAM UNITS
• ABOUT DATABASE PROGRAM UNITS
• TYPES OF PL/SQL PROGRAM UNITS
• TYPES OF STORED PROGRAM UNITS
• ADVANTAGES OF USING STORED PROGRAM UNITS
• DEVELOPMENT TOOLS

CREATING STORED PROCEDURES & FUNCTIONS
• ABOUT STORED PROCEDURES & FUNCTIONS
• CREATING PROCEDURES & FUNCTIONS
• EXECUTING PROCEDURES & FUNCTIONS
• HANDLING COMPILATION ERRORS

MAINTAINING STORED PROCEDURES & FUNCTIONS
• RECOMPILING & DROPPING PROGRAMS
• DATA DICTIONARY STORAGE
• MANAGING DEPENDENCIES

CREATING & MAINTAINING PACKAGES
• ABOUT PACKAGES
• CREATING PACKAGES
• ADVANCED PROGRAMMING TECHNIQUES
• MAINTAINING PACKAGES

ADVANCED CURSOR TECHNIQUES
• USING CURSOR VARIABLES
• USING CURSOR EXPRESSIONS

USING SYSTEM-SUPPLIED PACKAGES
• DBMS_OUTPUT()
• UTL_FILE()

CREATING DATABASE TRIGGERS
• ABOUT DATABASE TRIGGERS
• STATEMENT-LEVEL TRIGGERS
• ROW-LEVEL TRIGGERS
• EXAMPLES OF TRIGGERS
• INSTEAD OF TRIGGERS
• EMPLOYING TRIGGERS WITHIN AN APPLICATION

MAINTAINING DATABASE TRIGGERS
• CALL SYNTAX
• MULTIPLE TRIGGERS PER EVENT
• TRIGGER MAINTENANCE TASKS
• SHOW ERRORS TRIGGER
• DROP TRIGGER
• ALTER TRIGGER
• HANDLING MUTATING TABLE ISSUES

IMPLEMENTING SYSTEM EVENT TRIGGERS
• WHAT ARE SYSTEM EVENT TRIGGERS?
• DEFINING THE SCOPE
• AVAILABLE SYSTEM EVENTS
• SYSTEM EVENT ATTRIBUTES