OR120: Oracle 11g Introduction to PL/SQL

Contact PI


  • Virtual Classroom

  • Onsite

← Continue Searching

Duration: 2 Days

Get a hands-on introduction to the Oracle11g PL/SQL procedural language for SQL in this course where you will learn how to insert, update, and delete data; control data sharing and locking; debug and use error report procedures; and develop database triggers, stored procedures, and functions for future reuse.

What You Will Learn

  • The PL/SQL environment and program structure
  • Update, insert, and delete statements
  • Error report functions
  • Debugging procedures
  • Logical comparisons
  • Define, store, and execute procedures
  • Define, store, and execute Functions
  • Create package specifications and package bodies
  • Create composite datatypes
  • Work with implicit and explicit cursors

Audience

Anyone who needs to extract and organize information from an Oracle database, including adding, updating, and deleting data, and debug and use error report procedures, and develop database triggers, stored procedures and functions for future reuse. I.E. Application designers, developers, database administrators, and End Users

Prerequistes

Oracle 11g Foundation: SQL Basics & SQL*PLUS or equivalent experience

Course Outline

1. PL/SQL Basics

  • SQL and PL/SQL
  • PL/SQL anonymous block structure and datatypes
  • Variable declarations
  • Object naming rules
  • Executable statements
  • Operators in PL/SQL
  • Repetition control
  • LOOP statements - Basic LOOP, WHILE, FOR
  • CASE expressions and statements
  • Nested blocks
  • Labels and block identification
  • Basic coding standards
  • SQL*PLUS development environment

2. Working with Database Data

  • SELECT statements in PL/SQL
  • Selecting single and multiple rows
  • Referencing other variables or a database column's datatypes
  • Modifying database data (DML)
  • Transaction control commands
  • Selecting data into PL/SQL variables
  • Explicit cursors
  • Transaction control commands
  • Referencing other structures datatypes
  • Using the cursor FOR LOOP
  • FOR UPDATE/WHERE CURRENT OF

3. Exception Handling

  • Writing an exception handler section
  • Handling predefined and user-defined exceptions
  • Controlling exception processing
  • Preventing unhandled exceptions
  • RAISE_APPLICATION_ERROR
  • Using SQLCODE and SQLERRM

4. Stored PL/SQL Objects

  • Introduction to procedures and functions stored in the database
  • Creating stored PL/SQL objects
  • Passing values with parameters
  • Creating procedures, functions, packages, and package bodies
  • Introduction to database triggers
  • Viewing and recompiling stored code

Course Labs

Lab 1: Using PL/SQL to create an Anonymous Block

Lab 2: PL/SQL Basics

Lab 3: Selecting and Updating Database Data

Lab 4: Using Explicit Cursors

Lab 5: Handling Exceptions

Lab 6: Creating a Procedure

Lab 7: Creating a Function

Lab 8: Creating a Package

Lab 9: Creating a Database Trigger