Oracle PL-SQL

Course Overview

Prerequisites

• • Prerequisite course : Oracle SQL
  • A working knowledge of SQL*Plus or SQL Developer and Oracle SQL is required along with practical experience in writing SQL statements. This can be obtained by attendance on the pre-requisite course. Some previous programming experience will also prove advantageous.

Target Audience for this Course

• • Oracle database administrators, software development personnel and database support staff who need to write PL/SQL scripts to enhance their productivity in manipulating data.

Skills Gained from this Training

• • Writing and running PL/SQL programs from SQL*Plus or SQL Developer
  • Declaring and using variables and constants
  • Assigning and manipulating values
  • Using SQL statements within PL/SQL
  • Implementing conditional execution and loop constructs
  • Declaring and using cursors with and without parameters
  • Updating data with cursors
  • Trapping and handling errors and exceptions in code
  • Implementing nested and labelled loops and blocks
  • Raising exceptions
  • Handling PL/SQL records and tables
  • Using PL/SQL table built-in functions

Course Content

• DAY 1

  
  

• Course Introduction

  Administration and Course Materials
  Course Structure and Agenda
  Delegate and Trainer Introductions

• PL/SQL FUNDAMENTALS

  What is PL/SQL?
  Basic Elements
  Variables and Constants
  Data Types
  Initialising Variables and Assigning Values
  Using SQL Statements in Code
  Generating Output to SQL or SQL Developer

• PROGRAM LOGIc# #IF THEN ELSIF ELSE Statements
  CASE Statements
  The Basic Loop Construct
  WHILE and FOR Loops
  Nested and Labelled Loops
  The GOTO Statement
  The CONTINUE Statement
• USING CURSORS

  What is a Cursor?
  Implicit and Explicit Cursors
  Cursor Operations
  Declaring, Opening and Closing Cursors
  Fetching Rows
  Status Checking
  Using Cursors FOR UPDATE
  The Cursor FOR Loop
  Parameterised Cursors

• Day 2
• EXCEPTIONS AND NESTED BLOCKS

  The EXCEPTION Section
  Types of Exception
  Handling Named System-Raised Exceptions
  Handling Un-named System-Raised Exceptions
  User-Declared Exceptions and Application Errors
  WHEN OTHERS THEN NULL
  Nested and Labelled Blocks
  Propagation of Exceptions
  Scope of Variables and Cursors
  Scope of Goto Statements

• PL/SQL RECORDS AND INDEX-BY TABLES

  Declaring Record Types
  Handling PL/SQL Records
  Nested Records
  Declaring PL/SQL Index-By Tables or Associative Arrays
  PL/SQL Table Built-in Functions
  Manipulating PL/SQL Tables or Associative Arrays

• Note

  The 2 Day Oracle PL/SQL course can be combined with the 3 Day Stored Program Units course, this is equivalent to attending the 5 Day Oracle Program with PL/SQL course.
  

Included with this course

• • Comprehensive course materials and exercises