Oracle 11g Program with PL/SQL

Length: 5 days
Audience: Programmers and Database Administrators responsible for the development or maintenance of application systems which access an Oracle database.
Prerequisites: Oracle 11g SQL Fundamentals and knowledge of a procedural language (e.g., C).
Overview: The course provides a comprehensive introduction to PL/SQL, Oracle's procedural language extension to SQL. PL/SQL is used to write stored procedures, packages, and database triggers. PL/SQL will be of particular interest to individuals developing applications in a client server or multi-tier environment.

The course also provides attendees with the background to develop stored PL/SQL programs including functions, procedures, packages, and triggers. Commonly used predefined packages provided with the Oracle11g DBMS are examined. Discussions include how these packages could be used as part of in-house applications. Additional PL/SQL features such as native dynamic SQL support and autonomous transactions are also presented.

Workshops provide students with the opportunity to use predefined packages and evaluate their potential role in an application as well as to write their own stored PL/SQL objects.
  • Overview of PL/SQL
  • Variables and Constants
    • Identifiers
    • Datatypes
    • Variables
    • Expressions
  • PL/SQL Executable Statements
    • Control Structures
    • Conditional Statements
    • Loops
  • Embedding SQL Within PL/SQL
    • Implicit Cursors
    • Explicit Cursors
    • Parameterized Cursors
    • Transaction Processing
    • Cursor Variables
  • Handling Exceptions
    • Predefined Exceptions
    • User-Defined Exceptions
    • RAISE Statement
    • Propogation of Exceptions
  • PL/SQL Built-in Functions
  • Subprograms
    • Procedures
    • Functions
    • Parameter Modes
    • RETURN Statement
    • Overloading
    • Recursion
  • PL/SQL on the Server
    • Creating and Calling Stored Procedures
    • Creating and Calling Stored Functions
    • Passing Parameters
    • Definer's versus Invoker's Rights
    • Creating and Using Packages
    • Cross-Session PL/SQL Function Result Cache
    • Database Triggers
    • Compound Triggers
  • Advanced Features
    • PL/SQL Associative Arrays
    • BULK COLLECT
    • Native Dynamic SQL
    • Autonomous Transactions
    • PL/SQL Hierarchical Profiler
    • Native Compilation
    • Subprogram Inlining
  • Predefined Packages
    • UTL_FILE
    • DBMS_PIPE
    • DBMS_ALERT
    • DBMS_SQL
Back to Oracle 11g Courses