Introduction to Oracle 11g PL/SQL Programming
Overview
A Relational Database Management System (RDBMS) is a software
system that allows you to create and manage a relational database.
Minimum requirements for such a system are defined by both ANSI and
ISO. PL/SQL is Oracle's Procedural Language for SQL. It is Oracle's
database programming language for creating stored procedures,
functions, database triggers, and object methods. PL/SQL can be
used for implementing business rules, computing algorithms,
manipulating data, and for stand-alone programs. 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.
Target Audience:
Application developers and database administrators.
Prerequisites:
Introduction to Oracle 11g SQL Programming is required.
Programming experience in a high-level language, such as COBOL,
Java, or Perl is also required.
Introduction to Oracle 11g PL/SQL Programming Training
Class Objectives
- Create triggers on database tables.
- Use PL/SQL's datatypes for database and program data.
- Use program structure and control flow to design and write
PL/SQL programs.
- Create PL/SQL stored procedures and functions.
- Write robust programs that handle runtime exceptions.
- Use PL/SQL's collection datatypes.
- Use cursors to work with database data.
- Use bulk operations for improved performance.
- Use the packages supplied with Oracle.
- Design and write your own packages.
- Maintain and evolve your PL/SQL programs.
- Manage the security of your stored PL/SQL programs.
1. Course Introduction
- Course Objectives
- Suggested References
2. Triggers
- Beyond Declarative Integrity
- Triggers
- Types of Triggers
- Trigger Sequencing
- Row-Level Triggers
- Trigger Predicates
- Trigger Conditions
- Using Sequences
- Cascading Triggers and Mutating Tables
- Generating an Error
- Maintaining Triggers
3. PL/SQL Variables and
Datatypes
- Anonymous Blocks
- Declaring Variables
- Datatypes
- Subtypes
- Character Data
- Dates and Timestamps
- Date Intervals
- Anchored Types
- Assignment and Conversions
- Selecting into a Variable
- Returning into a Variable
4. PL/SQL Syntax and
Logic
- Conditional Statements - IF/THEN
- Conditional Statements - CASE
- Comments and Labels
- Loops
- WHILE and FOR Loops
- SQL in PL/SQL
- Local Procedures and Functions
5. Stored Procedures and Functions
- Stored Subprograms
- Creating a Stored Procedure
- Procedure Calls and Parameters
- Parameter Modes
- Named Parameter Notation
- Default Arguments
- Creating a Stored Function
- Stored Functions and SQL
- Invoker's Rights
6. Exception Handling
- SQLCODE and SQLERRM
- Exception Handlers
- Nesting Blocks
- Scope and Name Resolution
- Declaring and Raising Named Exceptions
- User-Defined Exceptions
7. Records and Collections
- Record Variables
- Using the %ROWTYPE Attribute
- User-Defined Object Types
- VARRAY and Nested TABLE Collections
- Using Nested TABLEs
- Using VARRAYs
- Collections in Database Tables
- Associative Array Collections
- Collection Methods
- Iterating Through Collections
8. 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
9. Bulk Operations
- Bulk Binding
- BULK COLLECT Clause
- FORALL Statement
- FORALL Variations
- Bulk Returns
- Bulk Fetching with Cursors
10. 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 and Reading from a Pipe
- The DBMS_METADATA Package
- XML Packages
- Networking Packages
- Other Supplied Packages
11. Creating Packages
- Structure of a Package
- The Package Interface and Implementation
- Package Variables and Package State
- Overloading Package Functions and Procedures
- Forward Declarations
- Strong REF CURSOR Variables
- Weak REF CURSOR Variables
12. Working with LOBs
- Large Object Types
- Oracle Directories
- LOB Locators
- Internal LOBs
- LOB Storage and SECUREFILEs
- External LOBs
- Temporary LOBs
- The DBMS_LOB Package
13. 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 and Validation
- Maintaining Stored Programs
14. Appendix A - 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