This course offers students an intensive lecture and lab environment to master Oracle’s Data Server technology. The class covers the use of PL/SQL, creating stored procedures and functions as well as packages and objects. Participants will learn how to write simple to complex PL/SQL queries and will be able to modify database objects using SQL in PL/SQL. Advanced techniques – such as cursor processing, exception processing, calling procedures from using packages within PL/SQL, collections, oracle table objects, nested tables, user-defined data types and arrays – will also be discussed.

Audience: This class is for application developers, database administrators, systems analysts and technical support professionals.
Course Duration: 5 days
Prerequisites:

Working knowledge of SQL is highly recommended.

Hardware and Software Requirements:
  • Minimal processor speed of 2 ghz or higher
  • Memory requirements – 4 gb (giga byte of memory)
  • Video: 16 color (800 x 600 resolution minimum)
  • 50 mb of storage space per student
  • MS Windows or UNIX/LINUX
Course Objectives:
  • Create, retrieve and Manipulate database objects through PL/SQL
  • Utilize PL/SQL to control transactions
  • Understand exception processing capabilities
  • Identify when to use cursors versus singleton selects
  • Utilize BASIC IF-THEN-ELSE logic
  • Use PL/SQL to retrieve data from OS files
  • Use triggers to implement business rules and referential integrity
  • Evaluate PL/SQL statements for efficiency
  • Utilizing collections and arrays
Course Outline:
  • Introduction to PL/SQL
  • Processing in the PL/SQL Environment
    • Scalar Variables
    • Scoping Variables
  • Creating Records & PL/SQL Tables
    • Using Composite records
    • %ROWTYPE
    • Arrays as PL/SQL tables
  • Modifying Records in PL/SQL Blocks
    • Inserting Records
    • Updating Records
    • Deleting Records
  • Execution Block
    • Calculations with Variable
      • Singleton Selects
    • Decision Statements
    • Loops
  • Using Exception Block
    • Predefined Oracle Blocks
    • Non-Predefined Oracle Blocks
    • User Defined Blocks
  • Cursor Processing
    • Standard Cursors
    • For Loop Cursors
    • Passing Parameters with Cursors
  • Creating Procedures and Functions
    • Use of IN variables
    • Use of OUT variables
    • Use of IN/OUT variables
    • Using Parameters with Procedures and Functions
    • Debugging Procedures and Functions
  • Defining Triggers
    • Before and After Row Triggers
    • Before and After Statement Triggers
    • Triggers Using Referential Integrity
    • Triggers Using Business Rules
  • Creating Packages
    • Specification of a Package
    • Creating a Package body
    • Use of Global versus Local Variables
  • Creating Object Types
    • User-Defined Object Types
    • Initializing Objects
    • Nested Tables
  • Collections
    • Creating Varrays
    • Traversing Collections Through EXIST, NEXT, TRIM etc.