Oracle 9i: Program with PL/SQL

5 Day Course
Code OC9IPPLS

This course has been retired. Please view currently available Oracle DBA Training Courses.

Modules

Hide all

Introduction (7 topics)

  • Describing PL/SQL
  • Describing the Use of PL/SQL for the Developer and the Database Administrator
  • Explaining the Benefits of PL/SQL
  • PL/SQL program constructs
  • PL/SQL anonymous block structure
  • Subprogram block structure
  • Course objectives and overview

Declaring Variables (5 topics)

  • Recognizing the Basic PL/SQL Block and Its Sections
  • Describing the Significance of Variables in PL/SQL
  • Distinguishing Between PL/SQL and Non-PL/SQL Variables
  • Declaring Variables and Constants
  • Executing a PL/SQL Block

Writing Executable Statements (5 topics)

  • Recognizing the Significance of the Executable Section
  • Writing Statements Within the Executable Section
  • Describing the Rules of Nested Blocks
  • Executing and Testing a PL/SQL Block
  • Using Coding Conventions

Interacting with the Oracle Server (5 topics)

  • Writing a Successful SELECT Statement in PL/SQL
  • Declaring the Data type and Size of a PL/SQL Variable Dynamically
  • Writing Data Manipulation Language (DML) Statements in PL/SQL
  • Controlling Transactions in PL/SQL
  • Determining the Outcome of SQL DML Statements

Writing Control Structures (5 topics)

  • Identifying the Uses and Types of Control Structures
  • Constructing an IF Statement
  • Constructing and Identifying Different Loop Statements
  • Controlling Block Flow Using Nested Loops and Labels
  • Using Logic Tables

Working with Composite Data types (5 topics)

  • Creating User-Defined PL/SQL Records
  • Creating a PL/SQL Table
  • Creating a PL/SQL Table of Records
  • Differentiating Among Records
  • Tables, and Tables of Records

Writing Explicit Cursors (3 topics)

  • Using a PL/SQL Record Variable
  • Distinguishing Between the Implicit and Explicit Cursor
  • Writing a Cursor FOR Loop

Advanced Explicit Cursor Concepts (4 topics)

  • Writing a Cursor that Uses Parameters
  • Determining When a FOR UPDATE Clause in a Cursor Is Required
  • Using a PL/SQL Table Variable
  • Using a PL/SQL Table of Records

Handling Exceptions (6 topics)

  • Defining PL/SQL Exceptions
  • Recognizing Unhandled Exceptions
  • Listing and Using Different Types of PL/SQL Exception Handlers
  • Trapping Unanticipated Errors
  • Describing the Effect of Exception Propagation in Nested Blocks
  • Customizing PL/SQL Exception Messages

Creating Procedures (5 topics)

  • Describe the uses of procedures
  • Create procedures
  • Create procedures with arguments
  • Invoke a procedure
  • Remove a procedure

Prerequisites

Working with iSQL*Plus Introduction to Oracle9i: SQL

Sections