Introduction to Oracle 11g PL/SQL Programming - ORA503

In this Oracle PL/SQL training class is designed to give students a hands on Oracle PL/SQL learning experience.  This course is designed with 40% lecture and demos and 55% hand on labs and Q&A.   Dozens on labs and examples are delivered and student for from easy SQL to more complex PL/SQL coding.

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.


Next public classes scheduled:
 Feb 29 2012 
 Mar 28 2012 
 Apr 25 2012 
Course ID: ORA503

Duration: 3 days

Audience: Application developers and database administrators

Prerequisites: A good working knowledge of Oracle 11g SQL features is required. Suggest taking Introduction to Oracle 11g SQL training class. Programming experience in a high-level language, such as COBOL, Java, or Perl is also required.

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.

Topics:

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

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

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

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

Exception Handling

  • SQLCODE and SQLERRM
  • Exception Handlers
  • Nesting Blocks
  • Scope and Name Resolution
  • Declaring and Raising Named Exceptions
  • User-Defined Exceptions

Records, Collections, and User-Defined Types

  • 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

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
  • Bulk Operations
  • Bulk Binding
  • BULK COLLECT Clause
  • FORALL Statement
  • FORALL Variations
  • Bulk Returns
  • Bulk Fetching with Cursors

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

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

Working with LOBs

  • Large Object Types
  • Oracle Directories
  • LOB Locators
  • Internal LOBs
  • LOB Storage and SECUREFILEs
  • External LOBs
  • Temporary LOBs
  • The DBMS_LOB Package

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

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

Appendix B: PL/SQL Versions, Datatypes, and Language Limits

Appendix C: Oracle 11g Supplied Packages

 

Call Toll-free 877-245-6815 or 303-416-3056 to set up your IT Training today.