Overview
PL/SQL allows developers to extend the basic data query and manipulation of SQL into complete applications and shared program units. This class provides the technical expertise necessary to utilize this powerful component of Oracle.
The content of this course applies to developing PL/SQL blocks for stand alone use, stored procedures, functions, packages and triggers or in the Oracle Forms or Reports development tools.
One of the most powerful features of PL/SQL is the ability to create ‘program units’ that are stored within the database. This allows for robust solutions to be developed that can be shared and re-used. Students will learn to write, debug and manage all types of program units: procedures, functions, packages and triggers.
Oracle provides dozens of PL/SQL packages with the core RDBMS product. Utilization of many of the most useful of these pre-supplied PL/SQL packages is covered allowing developers to accomplish sophisticated tasks such as job scheduling, interprocess communication and utilizing the FLASHBACK feature.
Intended audience
Intermediate Oracle SQL developers and DBAs
Intermediate Oracle PL/SQL developers and DBAs wishing to ‘back fill’ gaps in their expertise
Technical managers needing Oracle expertise for project administration
Prerequisites
Skill with GUI interfaces
Data processing background
A solid understanding of SQL (6 months of experience) is required to succeed in this class. Persons attending without this will experience difficulty.
Course Topics
SQL tools
The Petsaver database
PL/SQL language fundamentals
PL/SQL scalar variables
SELECT statements in PL/SQL
DML statements in PL/SQL
Transaction control in PL/SQL
PL/SQL control structures
PL/SQL composite variables
PL/SQL cursors
PL/SQL block hierarchies
PL/SQL exception handling
Advanced exception handling
Basic Oracle supplied packages
PL/SQL performance tuning
New features of PL/SQL in Oracle10g
Creating stored procedures
Creating stored functions
Advanced features of procedures and functions
Creating packages
Advanced features of packages
Creating database triggers
Advanced features of triggers
Maintaining program units
Dynamic SQL in PL/SQL
Advanced compilation techniques
Advanced Oracle supplied packages
Working with large objects (LOBs)
Topic contents
1. SQL tools
SQL*Plus
iSQL*Plus
SQL*Developer
PL/SQL Developer
2. The Petsaver database
3. PL/SQL language fundamentals
What is PL/SQL?
Reasons to use PL/SQL
PL/SQL block structure
Types of PL/SQL blocks
Using DBMS_OUTPUT
PL/SQL scalar variables
4. Defining scalar variables
Anchoring to database definitions
Assigning defaults
Creating constants
PL/SQL value assignment
5. SELECT statements in PL/SQL
Single row queries
Avoiding SELECT errors
6. DML statements in PL/SQL
INSERT
UPDATE
DELETE
7. Transaction control in PL/SQL
COMMIT
ROLLBACK
SAVEPOINT
8. PL/SQL control structures
IF THEN ELSE
LOOP
CASE
9. PL/SQL composite variables
Records
Associative arrays (PL/SQL tables)
Nested tables
VARRAYs
10. PL/SQL cursors
Defining cursors
Cursor attributes
Cursor FOR loops
Using DML with cursors
11. PL/SQL block hierarchies
Variable scope
Creating sub-blocks
Referencing block labels
12. PL/SQL exception handling
Defining exceptions
Predefined exceptions
Other Oracle exceptions
User-defined exceptions
13. Advanced exception handling
Capturing unexpected exceptions
Error trapping functions
Controlling cursor error handling
14. Basic Oracle supplied packages
UTL_FILE
DBMS_JOB
DBMS_PIPE
DBMS_LOCK
DBMS_FLASHBACK
15. PL/SQL performance tuning
The RETURN clause
Using BULK operators
Using DBMS_TRACE
Using the DBMS_PROFILER
NOCOPY hint
Using DBMS_SHARED_POOL
16. New features of PL/SQL in Oracle 10g
Boolean variables
CASE statement
MERGE statement
New date datatypes
CAST function
Regular expressions
17. Creating stored procedures
Understanding procedures
Client-side vs. server-side procedures
Invoking procedures in SQL and PL/SQL
Managing stored procedures
Resolving compile errors
Removing a procedure
18. Creating stored functions
Understanding functions
Client-side vs. server-side functions
Using the RETURN clause
Invoking functions in SQL and PL/SQL
Procedures and functions compared
19. Advanced features of procedures and functions
Advanced parameter topics
Local modules
DETERMINISTIC functions
Autonomous transactions
Transaction visibility
Wrapping PL/SQL program units
20. Creating packages
Package overview
Public vs. private package constructs
21. Advanced features of packages
Overloading
Forward referencing
Using 'start up' code
22. Creating database triggers
Trigger overview
Specifying triggering events
Managing triggers
23. Advanced features of triggers
INSTEAD OF triggers
DML trigger directives
Autonomous triggers
Calling procedures from triggers
24. Maintaining program units
Program units in the data dictionary
Program unit dependencies
Invokers rights vs. definers rights security models
25. Dynamic SQL in PL/SQL
26. Working with large objects (LOBs)
Understanding LOBs
Creating LOBs
Manipulating LOBs with DBMS_LOB
LOBs and NULL values
