Oracle PL/SQL with Objects

Course Number:

N/A

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.

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.

Related Posts

About Us
Ntier company logo

IT Training, Agile Ways of Working and High Impact Talent Development Strategies

Let Us Come to You!

Classes recently delivered in: Atlanta, Boston, Chicago, Columbus, Dallas, Detroit, Indianapolis, Jerusalem, London, Milan, New York, Palo Alto, Phoenix, Pittsburgh, Portland, Raleigh, San Antonio, San Diego, San Francisco, San Jose, Seattle, Springfield, Mass., St. Louis, Tampa and more!