• Decrease font size
  • Default font size
  • Increase font size

Introduction to Oracle9i: PL/SQL Language

 Print E-mail
Course Length - 2 Days
 
Course Description

The Oracle PL/SQL language provides for programming logic features not contained within SQL. The grammar, syntax and benefits of using the PL/SQL language within an Oracle database environment are considered.

 

You Will Learn How To:

The primary objective of this course is to provide an introduction to the PL/SQL database programming language. Major subject areas to be explored are:

  • Declaring variables and complex datatypes.
  • Developing logic within PL/SQL program blocks.
  • Fetching data from the database into program variables.
  • Returning program output to users.
  • Handling program exceptions.
  • Using explicit and implicit database cursors.
  • Using the advanced features of nested blocks and subprograms.
 

Course Prerequisites

Mandatory Prerequisites:

  • Introduction to Oracle9i: SQL
  • Introduction to Oracle9i: Advanced SQL
 

Who Should Attend?

All Oracle professionals including developers, database administrators, designers and analysts.

 

Next Suggested Course

Develop Oracle9i PL/SQL Program Units.

 

Course Outline

LANGUAGE FEATURES

  • WHY USE PL/SQL?
  • Where PL/SQL Program Units May Be Deployed
  • The Java Alternative To PL/SQL
  • STRUCTURE OF A PL/SQL PROGRAM BLOCK
  • PL/SQL FROM INTERACTIVE TOOLS
  • GENERATING OUTPUT

DECLARE CLAUSE

  • DATATYPES
  • DECLARE Example
  • SPECIAL DATATYPES
  • %TYPE VARIABLE
  • %ROWTYPE RECORD
  • TYPE TABLE

BEGIN CLAUSE

  • DATA MANIPULATION
  • CHAR and VARCHAR
  • Numeric
  • Date
  • Boolean
  • Comparison Operators
  • Functions
  • Date Calculations
  • SELECTINTO
  • LOGIC CONTROL & BRANCHING
  • GOTO
  • LOOP
  • IF-THEN-ELSE
  • CASE

EXCEPTION

  • HANDLING EXCEPTION
  • SPECIAL FEATURES
  • SYSTEM DEFINED EXCEPTIONS
  • USING SQLCODE & SQLERRM
  • IMPLICIT CURSORS
  • USER-DEFINED EX_INED
  • PRAGMA EXCEPTION_INIT

EXPLICIT CURSORS

  • UNDERSTANDING CURSORS
  • EXPLAINING THE CURSOR EXAMPLE
  • DECLARE Cursor E
  • OPEN Cursor Example
  • FETCH Cursor Example
  • CLOSE Cursor Example
  • INCLUDING CURSOR PARAMETERS
  • PERFORMING UPDATES FROM CURSORS
  • USING FORLOOP CURSORS

ADVANCED

  • USING NESTED BLOCKS
  • USING DECLARED SUBPROGRAMS
  • DISPLAY_MESSAGE() Procedure Example
  • GET_SPOUSE_NAME() Function Example