Course duration: 3 Days
Overview
This 3 day course gives an introduction to ORACLE PL/SQL, an application development environment that enables the writing of stored procedures, functions and triggers using both SQL and PL/SQL commands.
It may be based on ORACLE version 9i, 10g or 11g of the relational database and the principles learnt may be applied to versions 8 and 8i.
Full course notes are provided along with sample database files, example PL/SQL files and free software tools for use in accessing an ORACLE database.
Pre-requisites
It is recommended that the delegate has attended the Oracle SQL Fundamentals course or equivalent or has extensive experience of the use of SQL with another Relational Database, such as MySQL, Informix or Sybase.
Follow-up
For those who will be undertaking the task of Database Administration, the follow-up to this course would be the ORACLE Database Administration course.
On-site Requirements
Course Length
The course takes the format of a workshop, with a mix of lecture, working examples and practical exercises.
Although the content may be customised, at least 2 days are needed to cover the core elements.
Course Content
Introduction
Overview, Aims and Objectives, Schedule, Introductions, Pre-requisites, Bibliography, Responsibilities
SQL*Plus
Objectives, What is SQL*Plus?, Starting SQL*Plus, iSQL*Plus, Using SQLTools, SQL*Plus Commands, SQL*Plus Environment, LOGIN.SQL File, Documentation, Entity Models, The ORDERS Tables, The FILM Tables, Course Tables Handout
PL/SQL
Objectives, What is PL/SQL?, Why Use PL/SQL?, Block Structure, Displaying a Message, Setting SERVEROUTPUT, Sample Code, PL/SQL Code, Update Example, Variables
Variables
Objectives, Datatypes, Setting Variables, Constants, Local and Global Variables, %Type Variables, Substitution Variables, Comments with &, Verify Option, && Variables, Define and Undefine
Select Statement
Objectives, SELECT Statement, Populating Variables, %Rowtype Variables, Record Types
Conditional Statement
Objectives, IF Statement, SELECT Statement, Case Statement
Iteration – Looping
Objectives, Loop Statement, While Statement, For Statement, Goto Statement and Labels
Trapping Errors
Objectives, Exception, Internal Errors, Error Code and Message, User Exceptions, Using No Data Found, Nested Blocks, EXCEPTION, EXCEPTION, Trapping Non-defined Errors, Using PRAGMA EXCEPTION_INIT