Oracle PL/SQL
Course Overview
This three day course gives participants a sound understanding of working with Oracle PL/SQL. The courses assumes no prior programming knowledge and is hands on in character.
Duration: 3 day course
Course Content
Triggers
Beyond Declarative Integrity
Triggers
Types of Triggers
Row-Level Triggers
Cascading Triggers and Mutating Tables
Generating an Error
Triggers on Views
System Triggers
Maintaining Triggers
PL/SQL Variables and Datatypes
Declaring Variables
Datatypes
Subtypes
Character Data
Dates and Timestamps
Date Intervals
Anchored Types
Assignment and Conversions
Selecting into a Variable
PL/SQL Syntax and Logic
PL/SQL Blocks and Programs
Conditional Statements IF/THEN
Conditional Statements CASE
Comments and Labels
Loops
WHILE and FOR Loops
Stored Procedures and Functions
Stored Subprograms
Procedures and Functions
Creating a Stored Procedure
Calling a Stored Procedure
Passing Parameters and Default Arguments
Parameter Modes
Creating a Stored Function
Calling a Stored Function
Stored Functions and SQL
Local Procedures and Functions
Exception Handling
SQLCODE and SQLERRM
Exception Handlers
Nesting Blocks
Scope and Name Resolution
User-Defined Exceptions
Compile-Time Warnings
Records and Collections
Record Variables
Using the %ROWTYPE Attribute
VARRAY and Nested TABLE Collections
Using Nested TABLEs
Using VARRAYs
Associative Array Collections
Collection Methods
Iterating Through Collections
FORALL Statement
BULK COLLECT Clause
Cursors
Multi-Row Queries
Declaring and Opening Cursors
Fetching Rows
Closing Cursors
The Cursor FOR Loop
FOR UPDATE Cursors
Cursor Parameters
The Implicit (SQL) Cursor
Using Packages
Packages
Oracle-Supplied Packages
The DBMS_OUTPUT Package
The DBMS_UTILITY Package
The UTL_FILE Package
The DBMS_METADATA Package
XML Packages
Networking Packages
Other Supplied Packages
Creating Packages
Structure of a Package
The Package Interface and Implementation
Package Variables
Overloading Package Functions and Procedures
Named Parameter Notation
REF CURSOR Variables
Working with LOBs
Large Object Types
Oracle Directories
LOB Locators
Internal LOBs
External LOBs
Temporary LOBs
The DBMS_LOB Package
Maintaining PL/SQL Code
Privileges for Stored Programs
PL/SQL Stored Program Compilation
The PL/SQL Execution Environment
Dependencies and Validation
Maintaining Stored Programs
Email us about this courses
Telephone on 01952 938014