Fundamenten van ORACLE PL/SQL Training Cursus

Introductie

• Doelen en doelstellingen
• Cursusplanning
• Voorstellingen
• Vereiste voorkennis
• Verantwoordelijkheden

SQL-tools

• Doelen
• SQL Developer
• SQL Developer - Connectie
• Tabellinformatie weergeven
• SQL gebruiken, SQL Developer - Query
• SQL*Plus inloggen
• Directe verbinding
• SQL*Plus gebruiken
• De sessie beëindigen
• SQL*Plus-opdrachten
• SQL*Plus-omgeving
• SQL*Plus-prompt
• Informatie over tabellen vinden
• Hulp krijgen
• SQL-bestanden gebruiken
• iSQL*Plus, Entiteitsmodellen
• De ORDER-tabels
• De FILM-tabels
• Hulpmiddel Cursustabellen
• Syntaxis van SQL-statements
• SQL*Plus-opdrachten

Wat is PL/SQL?

• Wat is PL/SQL?
• Waarom PL/SQL gebruiken?
• Blokstructuur
• Een bericht weergeven
• Voorbeelddus
• SERVEROUTPUT instellen
• Bijwerkvoorbeeld, Stijlhandleiding

Variables

• Variables
• Data types
• Variables instellen
• Constants
• Lokale en globale variables
• %Type variables
• Vervangingsvariables
• Comments met &
• Verify Option
• && Variables
• Define and Undefine

SELECT Statement

• SELECT Statement
• Vullen van Variables
• %Rowtype Variables
• CHR Function
• Self Study
• PL/SQL Records
• Example Declarations

Conditional Statement

• IF Statement
• SELECT Statement
• Self Study
• Case Statement

Trapping Errors

• 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

Iteration - Looping

• Loop Statement
• While Statement
• For Statement
• Goto Statement and Labels

Cursors

• 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

Procedures, Functions and Packages

• 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

Triggers

• 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

Sample Data

• ORDER Tables
• FILM Tables
• EMPLOYEE Tables

Dynamic SQL

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

Using Files

• Using Text Files
• UTL_FILE Package
• Write/Append Example
• Read Example
• Trigger Example
• DBMS_ALERT Packages
• DBMS_JOB Package

COLLECTIONS

• %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

Ref Cursors

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