Oracle Database 11g Programming with PL/SQL

Course number: OD11-CGI002

Course Description:

A good Oracle database administrator must gain and maintain a mastery of his or her environment, its physical structures, and its maintenance routines. An administrator must understand the data being stored well enough to tune and improve performance, to anticipate and prevent data failure, and to recover lost data should a failure occur. In this course, you will develop an advanced understanding of complex Oracle database concepts, maintenance and recovery routines, and database management. You have an Oracle installation and need to broaden your knowledge of advanced Oracle administrative functionality so that you can maintain the healthiest possible database.

Course Objective:
You will use PL/SQL to create programs with program units such as procedures, functions, packages, and database triggers to enhance data manipulations.

Performance-Based Objectives
Upon successful completion of this course, students will be able to:

write a simple PL/SQL program to familiarize themselves with the fundamentals and structure of PL/SQL blocks.
create a PL/SQL program using variables and lexical units.
use control structures to help them incorporate decision-making approach to programming.
handle PL/SQL exceptions.
work with PL/SQL cursors to embed SQL in PL/SQL code or call PL/SQL functions within SQL structures.
create PL/SQL subprograms.
use PL/SQL collections and records to enhance database performance during data processing.
develop and use PL/SQL packages.
create triggers to automatically trigger validation procedures when a table is modified.
use the PL/SQL compiler.
manage dependencies.
use dynamic SQL to handle changes in data definitions without recompiling.

Target Student:

This course is designed for IT Professionals with programming experience. The target student will also have experience in using SQL with Oracle 10g or 11g.

Prerequisites

Students enrolling in this class should have knowledge of working with Oracle databases. To ensure your success, we recommend that you first complete the following Element K courses or have equivalent knowledge.

Oracle Database 11g: Introduction, and
Oracle Database 11g: SQL Fundamentals I & II

Course Outline

Lesson 1: Getting Started with PL/SQL Programming

Topic 1A: Examine PL/SQL Basics
Topic 1B: Write a Simple PL/SQL Program Block

Lesson 2: Using Variables and Lexical Units

Topic 2A: Use PL/SQL Variables and Data Types
Topic 2B: Use PL/SQL Lexical Units

Lesson 3: Using Control Structures

Topic 3A: Use PL/SQL Operators and Expressions
Topic 3B: Use Conditional Statements
Topic 3C: Use Iterative Statements

Lesson 4: Handling PL/SQL Exceptions

Topic 4A: Handle System-Defined Exceptions
Topic 4B: Handle User-Defined Exceptions

Lesson 5: Working with PL/SQL Cursors

Topic 5A: Manage Data Using Cursors
Topic 5B: Retrieve Data Using the Cursor with Looping Mechanisms

Lesson 6: Creating PL/SQL Subprograms

Topic 6A: Use Stored Subprograms
Topic 6B: Create User-Defined Subprograms

Lesson 7: Improving Database Performance with PL/SQL Collections and Records

Topic 7A: Work with Collections
Topic 7B: Create Record Variables

Lesson 8: Using PL/SQL Packages

Topic 8A: Create Packages
Topic 8B: Work with Product-Specific Packages

Lesson 9: Creating Triggers

Topic 9A: Create a Simple Trigger
Topic 9B: Create a Compound Trigger

Lesson 10: Using the PL/SQL Compiler

Topic 10A: Apply Conditional Compilation
Topic 10B: Hide PL/SQL Code

Lesson 11: Managing Dependencies

Topic 11A: Work with Trigger Dependencies
Topic 11B: Manage Package Dependencies

Lesson 12: Use Dynamic SQL

Topic 12A: Use Native Dynamic SQL
Topic 12B: Work with the DBMS_SQL Package

Available Formats

Classroom

Live Online

Courses