ORACLE PL/SQL Fundamentals
Duration 2-3 days
This course gives an introduction to ORACLE PL/SQL, an application development environment that enables the writing of stored packages, procedures, functions and triggers using both SQL and PL/SQL commands.
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 1 day is needed to cover the core elements.
Full course notes are provided along with sample database files, example SQL files and free software tools for use in accessing an ORACLE database.
Desktop and ORACLE Version
The course may be run on a Windows or Linux desktop and may be customised to run on ORACLE version 9i, 10g, 11g or 12c. The principles learnt may also be applied to older versions,
The course is only suitable for those who have some knowledge of SQL. Previous experience with an interactive computer system is desirable but not essential.
For those who will be undertaking the task of Database Administration, the follow-up to this course would be the ORACLE Database Administration course.
There should be no more than 12 delegates on the course and all delegates should meet the above pre-requisites.
Each delegate should have access to a PC running Microsoft Windows with at least 1GB of memory, 20GB of free disc space and a DVD drive. The course uses the free software ORACLE Virtual Box, which may be installed before the course starts if preferred, and a supplied image file. For the trainer, a whiteboard, flipchart and pens are required and an overhead projector that can link to a laptop would be a great help.
Overview, Aims and Objectives, Sample Data, Schedule, Introductions, Pre-requisites, Getting Value, Responsibilities, Self Study Sections, Contents
What is PL/SQL?
Objectives, What is PL/SQL?, Why Use PL/SQL?, Block Structure, Displaying a Message, Sample Code, Setting SERVEROUTPUT, Update Example, Style Guide, Course Tables Handout
Objectives, Variables, Datatypes, Setting Variables, Constants, Using Functions, Using Operators, Using Sequence Values, Local and Global Variables, Guidelines For Variables, %Type Variables, Substitution Variables, Comments with &, Verify Option, && Variables, Define and Undefine
Objectives, SELECT Statement, Populating Variables, %Rowtype Variables, %Rowtype Advantages, INSERT Example, UPDATE Example, PL/SQL Records, Example Declarations, PLSQL Tables, Bind Variables, Using Bind Variables, SQL Example, PL/SQL Example Conditional Statement Objectives, IF Statement, SELECT Statement, Case Statement
Objectives, Exception, Internal Errors, Error Code and Message, Using No Data Found, User Exceptions, Raise Application Error, Trapping Non-defined Errors, Using PRAGMA EXCEPTION_INIT, Commit and Rollback, Nested Blocks, Workshop
Iteration - Looping
Objectives, Loop Statement, While Statement, For Statement, For Loop Rules, Continue Statement, Goto Statement and Labels, Use of Loops
Objectives, Cursors, Cursor Attributes, Explicit Cursors, Explicit Cursor Example, Declaring the Cursor, Declaring the Variable, Open, Fetching the First Row, Fetching the Next Row, Exit When %Notfound, Close, For Loop I, For Loop II, Update Example, BOOLEAN Example, RETURNING Clause, FOR UPDATE, FOR UPDATE WAIT, FOR UPDATE OF, WHERE CURRENT OF, Commit with Cursors, Nested Blocks, Validation Example I, Validation Example II, Cursor Parameters, Workshop, Workshop Solution
Procedures, Functions and Packages
Objectives, Create Procedure Statement, Parameters, Procedure Body, Calling Procedures, Positional and Named Notation, Parameter Default Values, Using Output Parameters, Calling Procedures with Output Parameters, Create Function Statement, Example Function, Calling Functions, Calling Procedures in SQL*Plus, Calling Functions In SQL*Plus, Modular Programming, Example Procedure, Setting Variables With Functions, Calling Functions In An IF Statement, Propagation of Exceptions, Create Package Statement, Package Example, Advantages of PL/SQL Packages, Public and Private Sub-programs, Calling Packages From Sub-Programs, Calling Packages in SQL*Plus, Finding Sub-programs, Dropping a Sub-Program, Recompiling Sub-programs, SQL Developer Debugger, Workshop I, Workshop II
Objectives, Creating Triggers, Statement Triggers, Row Level Triggers, WHEN Restriction, Selective Triggers - IF, Showing Errors, Commit in Triggers, Restrictions, Mutating Triggers, Finding Triggers, Dropping a Trigger, Generating an Auto-number, Disabling Triggers, Enabling Triggers, Trigger Names
More on Sub-programs
Objectives, Functions in SQL Statements, Example Functions in SQL, Local Sub-Programs, Definers vs Invokers Rights, Autonomous Transactions, Forward Declarations, Overloading, Initialisation Block, Bodiless Package, Wrap Utility, Running the Wrap Utility, Compound Triggers, Designing Triggers, System Triggers, Trigger Restrictions, System Event Trigger, DDL Event Trigger, Instead-of Triggers, Modifiable versus Non-modifiable Views, Instead-of Example
ORDER Tables, FILM Tables, EMPLOYEE Tables
Optional Advanced Content
Objectives, Using SQL in PL/SQL, Binding, Dynamic SQL, Native Dynamic SQL, Execute Immediate, Execute Immediate Using, Execute Immediate Into, Execute Immediate Returning Into, DBMS_SQL
Objectives, Using Text Files, Create Directory, UTL_FILE Write/Append Example, UTL_FILE Write Example, UTL_FILE Read Example
Objectives, Cursor Variables, REF CURSOR Types, Declaring Cursor Variables, Constrained and Unconstrained, Using Cursor Variables, Cursor Variable Examples, Bind Variables Are Good
Objectives, %Type Variables, Record Variables, PL/SQL Records, Variables vs Collections, Collections, Why Use Collections?, Collection Types, Methods, Associative Arrays, Setting Values, Nonexistent Elements, Example, Non-Numeric Index, NOCOPY Hint, Varrays, Nested Tables, TABLE Command, When to Use What
Objectives, Bulk SQL, Dynamic SQL with BULK COLLECT INTO Clause, Dynamic SQL with RETURNING BULK COLLECT INTO Clause, Dynamic SQL Inside FORALL Statement, DBMS_SQL Package.
Objectives, DBMS_SCHEDULER Package, DBMS_METADATA, Retrieving Metadata, Examples, DBMS_UTILITY, UTL_MAIL, HTP, DBMS_APPLICATION_INFO