Introduction to Oracle® PL/SQL Programming

Public Training Price : $1950 per student
Private Training Price : Request a quote
Print this outline
Enroll in this Public Class

 

 

 

Introduction to Oracle PL/SQL Programming Training Class Summary

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 10g 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.

Audience: Application developers and database administrators.

Prerequisites: Introduction to Oracle SQL Programming is required. Programming experience in a high-level language, such as COBOL, Java or Perl is also required.

Class Length: 3 days

Introduction to Oracle 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 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.
Introduction to Oracle PL/SQL Programming Training Class Detailed Outline
  1. Course Introduction
    • Course Objectives
    • Overview
    • Suggested References
  2. Triggers
    • Beyond Declarative Integrity
    • Triggers
    • Types of Triggers
    • 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
    • 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, Collections, and User-Defined Types
    • Record Variables
    • Using the %ROWTYPE Attribute
    • VARRAY and Nested TABLE Collections
    • Using Nested TABLEs
    • Using VARRAYs
    • Collection 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
    • 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
  15. Appendix B - PL/SQL Versions, Datatypes, and Language Limits
  16. Appendix C – Oracle 10g Supplied Packages