Course Description
This 3-day Oracle PL/SQL Programming Introduction training course provides a solid, hands-on introduction to the Oracle PL/SQL programming language that is used for creating stored procedures, user functions and database triggers in an Oracle database. This course is for students who are new to the Oracle PL/SQL programming language.
While this course utilises the Oracle 11g release for practical purposes, it is highly suitable for users of earlier or more recent releases of the Oracle RDBMS.
The Oracle 11g release has greatly enhanced the features and functionality of PL/SQL. Students will write stored procedures, functions, packages, and triggers, and implement complex business rules in Oracle. Students will learn programming, management, and security issues of working with PL/SQL program units.
Programming topics will include the built-in packages that come with Oracle, the creation of triggers, and stored procedure features.
Pre-requisites
A solid understanding of SQL and 3GL programming is required. It is recommended the student understand Oracle SQL features. Oracle SQL Programming Introduction or equivalent experience.
Further Training
After some further on-the-job programming experience using the Oracle PL/SQL language, we recommend students attend our Oracle PL/SQL Programming Advanced training course to gain higher-level skills in the usage of Oracle PL/SQL.
Course content
Triggers
Trigger Sequencing
Row-Level Triggers
Trigger Predicates
Trigger Conditions
Using Sequences
Cascading Triggers & Mutating Tables
Generating an Error
Maintaining Triggers
PL/SQL Variables & Datatypes
Anonymous Blocks
Declaring Variables
Datatypes
Subtypes
Character Data
Dates & Timestamps
Date Intervals
Anchored Types
Assignment & Conversions
Selecting into a Variable
Returning into a Variable
PL/SQL Syntax & Logic
Conditional Statements – CASE
Comments & Labels
Loops
WHILE and FOR Loops
SQL in PL/SQL
Local Procedures & Functions
Stored Procedures & Functions
Stored Subprograms
Creating a Stored Procedure
Procedure Calls & Parameters
Parameter Modes
Named Parameter Notation
Creating a Stored Function
Calling a Stored Function
Stored Functions & SQL
Invoker’s Rights
Exception Handling
SQLCODE and SQLERRM
Exception Handlers
Nesting Blocks
Scope & Name Resolution
Declaring Named Exceptions
Raising Exceptions
User-Defined Exceptions
Records, Collections and User-Defined Types
Record Variables
Using the %ROWTYPE Attribute
User-defined Row Types
VARRAY & Nested TABLE Collections
Using Nested TABLEs
Using VARRAYs
Collections in Database Tables
Associative Array Collections
Collection Methods
Iterating Through Collections
Cursors
Multi-Row Queries
Declaring & Opening Cursors
Fetching Rows
Closing Cursors
The Cursor FOR Loop
FOR UPDATE Cursors
Cursor Parameters
The Implicit (SQL) Cursor
Bulk Operations
BULK COLLECT Clause
FORALL Statement
FORALL Variations
Bulk Returns
Bulk Fetching with Cursors
Using Packages
Packages
Oracle-Supplied Packages
The DBMS_OUTPUT Package
The DBMS_UTILITY Package
The UTL_FILE Package
Creating Pipes with DBMS_PIPE
Writing to & Reading from a Pipe
The DBMS_METADATA Package
XML Packages
Networking Packages
Other Supplied Packages
Creating Packages
Structure of a Package
The Package Interface & Implementation
Package Variables & Package State
Overloading Package Functions & Procedures
Forward Declarations
Strong REF CURSOR Variables
Weak REF CURSOR Variables
Working with LOBs
Large Object Types
Oracle Directories
LOB Locators
Internal LOBs
LOB Storage & SECUREFILEs
External LOBs
Temporary LOBs
The DBMS_LOB Package
Working with LOBs
Large Object Types
Oracle Directories
LOB Locators
Internal LOBs
LOB Storage & SECUREFILEs
External LOBs
Temporary LOBs
The DBMS_LOB Package
Maintaining PL/SQL Code
Privileges for Stored Programs
Data Dictionary
PL/SQL Stored Program Compilation
Conditional Compilation
Compile-time Warnings
The PL/SQL Execution Environment
Dependencies & Validation
Maintaining Stored Programs
Dynamic SQL
Generating SQL at Runtime
Native Dynamic SQL vs. DBMS_SQL Package
The EXECUTE IMMEDIATE Statement
Using Bind Variables
Multi-row Dynamic Queries
Bulk Operations with Dynamic SQL
Using DBMS_SQL
DBMS_SQL Subprograms
