Advanced Oracle Programming

There are several hands-on labs in this course that will provide the student with the opportunity to practice and perfect the knowledge of Oracle that they will gain during these 2 days.

Overview

Advanced topics include the study of Oracle’s database architecture, including schemas, objects, and the Data Dictionary. Students will demonstrate building and executing Stored Procedures and Functions while utilizing Oracle’s PL/SQL. Students will also learn about and practice creation of packages and triggers.

There are several hands-on labs in this course that will provide the student with the opportunity to practice and perfect the knowledge of Oracle that they will gain during these 2 days.

Audience

Programmers and analysts who need to get a full understanding of PL/SQL.

Prerequisites: Experience with Windows operating system. One year of experience with programming and knowledge of Relational Database technology. Six months experience with SQL on an Oracle database.

Length

24 hours

Outline

Introduction to PL/SQL
• Introduction
• Stored Procedures
1. Advantages of Stored Procedures
2. Procedure Structure
3. Function Structure
4. Trigger Structure
5. Procedure Parameters
• Packages
• Tools for Creating Stored Procedures
1. EXERCISE -Create Simple Stored Procedure
• Error Handling in PL/SQL
• Summary

Creating Subprograms with PL/SQL
• PL/SQL Data Types
• Declaring Variables and Constants
• Scope of Variables
• Statements and Assignments
• Using SELECT INTO
1. EXERCISE – Customer Read Procedure
• Using INSERT, UPDATE, and DELETE
• Enhancing Your Error Messages
1. EXERCISE – Create INSERT, UPDATE, and DELETE
 Stored Procedures for the Sales Customer Table
• Control Structures
1. Decision Flow Control
2. Loops
• GOTO and Labels
1. EXERCISE – Create Customer Maintenance Procedure
• Creating and Testing Functions
• Removing Procedures and Functions
• Stored Subprograms and the Data Dictionary
• Summary

Packages and Cursors
• PL/SQL Data Types
• Creating Packages
• Declaring a Cursor
• Opening a Cursor
• Fetching from a Cursor
• Closing a Cursor
• Cursor FOR Loops
1. EXERCISE – Create Sales Customer Cursor
Triggers
• Introduction
• Types of Triggers
• Constraints
• Order of Trigger Firing
• Trigger Components and Restrictions
• Creating Triggers
• Using :old and :new
• Using Trigger Predicates