ORACLE PL/SQL Fundamentals Training Course

Aims and Objectives, Course Schedule, Introductions, Pre-requisites, Responsibilities

 

Objectives, SQL Developer, SQL Developer - Connection, Viewing Table Information, Using SQL, SQL Developer - Query, SQL*Plus Login, Direct Connection, Using SQL*Plus, Ending the Session, SQL*Plus Commands, SQL*Plus Environment, SQL*Plus Prompt, Finding Information about Tables, Getting Help, Using SQL Files, iSQL*Plus, Entity Models, The ORDERS Tables, The FILM Tables, Course Tables Handout, SQL Statement Syntax, SQL*Plus Commands

 

Objectives, What is PL/SQL?, Why Use PL/SQL?, Block Structure, Displaying a Message, Sample Code, Setting SERVEROUTPUT, Update Example, Style Guide

 

Objectives, Variables, Datatypes, Setting Variables, Constants, Local and Global Variables, %Type Variables, Substitution Variables, Comments with &, Verify Option, && Variables, Define and Undefine

 

Objectives, SELECT Statement, Populating Variables, %Rowtype Variables, CHR Function, Self Study, PL/SQL Records, Example Declarations

 

Objectives, IF Statement, SELECT Statement, Self Study, 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, Self Study, Nested Blocks, Workshop

 

Objectives, Loop Statement, While Statement, For Statement, Goto Statement and Labels

 

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, FOR UPDATE, FOR UPDATE OF, WHERE CURRENT OF, Commit with Cursors, Validation Example I, Validation Example II, Cursor Parameters, Workshop, Workshop Solution

 

Objectives, Create Statement, Parameters, Procedure Body, Showing Errors, Describe a Procedure, Calling Procedures, Calling Procedures in SQL*Plus, Using Output Parameters, Calling with Output Parameters, Creating Functions, Example Function, Showing Errors, Describe a Function, Calling Functions, Calling Functions in SQL*Plus, Modular Programming, Example Procedure, Calling Functions, Calling Functions In An IF Statement, Creating Packages, Package Example, Reasons for Packages, Public and Private Sub-programs, Showing Errors, Describe a Package, Calling Packages in SQL*Plus, Calling Packages From Sub-Programs, Dropping a Sub-Program, Finding Sub-programs, Creating a Debug Package, Calling the Debug Package, Positional and Named Notation, Parameter Default Values, Recompiling Procedures and Functions, Workshop

 

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

 

ORDER Tables, FILM Tables, EMPLOYEE Tables

 

Objectives, SQL in PL/SQL, Binding, Dynamic SQL, Native Dynamic SQL, DDL and DML, DBMS_SQL Package, Dynamic SQL - SELECT, Dynamic SQL - SELECT Procedure

 

Objectives, Using Text Files, UTL_FILE Package, Write/Append Example, Read Example, Trigger Example, DBMS_ALERT Packegs, DBMS_JOB Package

 

Objectives, %Type Variables, Record Variables, Collection Types, Index-By Tables, Setting Values, Nonexistent Elements, Nested Tables, Nested Table Initialisation, Using the Constructor, Adding to a Nested Table, Varrays, Varray Initialization, Adding Elements to a Varray, Multilevel Collections, Bulk Bind, Bulk Bind Example, Transactional Issues, BULK COLLECT Clause, RETURNING INTO

 

Objectives, Cursor Variables, Defining REF CURSOR Types, Declaring Cursor Variables, Constrained and Unconstrained, Using Cursor Variables, Cursor Variable Examples