Home About Courses Schedule Services Webinars Contact Search

Introduction to Oracle PL/SQL Programming

SEE SCHEDULE

Duration: 3 Days

Method: Instructor led, Hands-on workshops

Price: $1625.00

Course Code: OR5460



View Related Video

Audience

Programmers and Database Administrators responsible for the development or maintenance of application systems which access an Oracle database.

Description

The course provides a comprehensive introduction to PL/SQL, Oracle’s procedural language extension to SQL. PL/SQL is used to write stored procedures, packages, and database triggers. PL/SQL will be of particular interest to individuals developing applications in a client-server or multi-tier environment.This course can be taught for any Oracle version.

Prerequisites

OR5450 Oracle SQL or equivalent experience

Topics

  1. Overview of PL/SQL
  2. Variables and Constants
    • Identifiers
    • Datatypes
    • Variables
    • Using the %TYPE Attribute
    • Using the %ROWTYPE Attribute
    • Declaring PL/SQL Records
    • Declaring an Array
  3. PL/SQL Executable Statements
    • Control Structures
      • IF-THEN-ELSE
      • IF-THEN-ELSIF
      • Nested IF Statements
      • Simple CASE Statement
      • Searched CASE Statement
    • Loops
      • Basic (Simple) Loop
      • FOR Loop
      • WHILE Loop
  4. Embedding SQL Within PL/SQL
    • Implicit Cursors
    • SELECT … INTO
    • INSERT, UPDATE, DELETE
    • COMMIT, ROLLBACK
    • Using SQL Attributes
      • SQL%FOUND, SQL%NOTFOUND
      • SQL%ROWTYPE
  5. Explicit Cursors
    • Cursor Steps
      • DECLARE, OPEN, FETCH, CLOSE
    • Choosing a Loop Structure
    • The FOR Loop Cursor
  6. Handling Exceptions
    • Predefined Exceptions
    • User-Defined Exceptions
      • DECLARE, RAISE, HANDLE
    • RAISE Statement
    • Propagation of Exceptions
  7. PL/SQL Stored Programs
    • Stored Procedures
    • Using Parameters
    • Functions
      • The RETURN Statement
    • Executing Stored Programs
    • Stored Procedures vs. Functions
    • Packages
      • Benefits of Packages
      • The Package Specification
      • The Package Body
    • Triggers
      • Trigger Events
      • Trigger Types
      • Row Level Triggers
      • Statement Level Triggers
      • Trigger Behavior