Oracle® PL/SQL®

Click for printer-friendly version
Summary
This course gives an introduction to ORACLE® PL/SQL®, an application development environment which enables the writing of programs and stored procedures using both SQL and PL/SQL commands.

It is based on version 8 of the ORACLE relational database but the principles learnt may be applied to both version 7.3 and version 8i.

Prerequisites
Attendance on the Introduction to SQL course or equivalent or extensive experience of another Relational Database, such as Informix or Sybase.

Next Steps

Length
2 days

Format
Instructor-led course, with many practical computer-based exercises.

Course Outline

Day 1

  • Introduction
    Objectives. Prerequisites. Bibliography.
  • Review of SQL*Plus®
    Some SQL*Plus Commands, The SQL*Plus Environment, The Sample Database.
  • What is PL/SQL?
    Block Structure. Sample Code. DMBS_OUTPUT Statement.
  • Variables
    Declaration and Data Types. Value Assignment, Constants. Local and Global. Variable Typing, Substitution Variables.
  • SELECT Statement
    Selecting into Variables, Rowtype Variables.
  • IF Statement
    Flow Control.
  • Iteration - Looping
    The LOOP statement, WHILE Statement, FOR Statement, GOTO and Labels.

Day 2

  • Cursors
    Implicit Cursors. Using Explicit Cursors. Cursor Attributes. Cursors with Loops. Use of Select for Update, Where Current Of.
  • Exceptions
    Trapping Errors. Raising Internal Exceptions. Using SQLCODE and SQLERRM. Raising User Exceptions. Using the PRAGMA EXCEPTION.
  • Procedures and Functions
    Creating and Executing Procedures. Creating and Executing Functions.
  • Packages
    Creating and Executing Packages. Public and Private Sub-programs. Examining Dependencies.
  • Triggers
    Types of Trigger. Creating and Firing Triggers. Administration of Triggers.
Hardware and Software Requirements
Each student should have access to a PC running Windows 95/98/ME or NT Workstation/2000 Professional with at least 500Mb of free space on the C drive and a CDROM. However, the PCs do NOT have to be on a network.
Note to Oracle licensees
Your Oracle license may not allow the use of the software for providing training to people outside your organisation. Contact Oracle if you are planning to provide third-party training and have any doubts about your license in this regard.


Notice: Undefined index: HTTP_REFERER in /var/www/datadeliverance/inc/trademarks.inc on line 3

Notice: Undefined index: HTTP_REFERER in /var/www/datadeliverance/inc/trademarks.inc on line 4

Notice: Undefined variable: REMOTE_USER in /var/www/datadeliverance/inc/trademarks.inc on line 7

Notice: Undefined index: HTTP_REFERER in /var/www/datadeliverance/inc/trademarks.inc on line 7

Notice: Undefined index: printer in /var/www/datadeliverance/inc/trademarks.inc on line 11

Go to course catalogue

Oracle and Java are registered trademarks of Oracle and/or its affiliates. PL/SQL and SQL*Plus are trademarks or registered trademarks of Oracle Corporation. Microsoft is either a registered trademark or trademark of Microsoft Corporation in the United States and/or other countries. MVS is a registered trademark of the International Business Machines Corp. in the U.S. or other countries or both. Motif, OSF/1, UNIX, and the "X Device" are registered trademarks and IT DialTone and The Open Group are trademarks of The Open Group in the U.S. and other countries. All other trademarks are the property of their respective owners.