Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
 
 
Why choose BC training?
 
Getting started
 
Oracle course catalog
 
Oracle RAC Tuning
 

Oracle PL/SQL Programming and Tuning

© 2009 by Burleson Consulting

 

This is an online course 

 
   
Key Features

* Learn the basics of PL/SQL.

* Use PL/SQL tables.

* Understand PL/SQL structures.

* Understand Dynamic SQL in PL/SQL.

* See Nested blocks in PL/SQL.

* Use Stored procedures, functions and packages.

* See Iteration in PL/SQL.

 

   

Course Description

PL/SQL programming & Tuning is an intense hands-on course that is designed to give the student maximum exposure to Oracle PL/SQL.  The student learns by doing, and this class has dozens of in-class exercises and the student will be guided from very simple SQL commands to increasingly complex PL/SQL coding techniques.

The topics start with the basics of SQL and progress into increasingly complex queries, including table joins, subqueries and creating Oracle views.  The PL/SQL section begins with simple concepts and the student gradually masters PL/SQL through increasingly challenging classroom exercises.

Book Required

  Easy Oracle PL/SQL Programming:

John Garmany

ISBN 0-9759135-7-3
 

Audience

This course is designed for practicing Oracle professionals who have basic experience with SQL and the use of a relational database. Prior experience with Oracle is not required, but experience using SQL with a relational database is highly desirable.

Curriculum Design

This course was designed by Donald K. Burleson, an acknowledged leader in Oracle database administration.  Burleson Consulting instructors offer decades of real world DBA experience in Oracle features, and they will share their Oracle secrets in this intense hands-on Oracle PL/SQL training.

Learning Objectives

By the end of this course the student will be able to write advanced PL/SQL code blocks, and will be able to use advanced features such as ref cursors and bulk fetches. The student will also become familiar with the internals of Oracle9i PL/SQL and will be able to compile PL/SQL for super-fast performance.

 

Oracle PL/SQL Programming and Tuning
Syllabus

© 2009 by Donald K. Burleson

 

This online course consists of these twelve one hour lessons:

  1. Basics of PL/SQL

  2. PL/SQL structures 

  3. Error checking – exception handling

  4. Boolean logic in PL/SQL

  5. Cursors in PL/SQL

  6. Iteration in PL/SQL

  7. PL/SQL tables

  8. Dynamic SQL in PL/SQL 

  9. Nested blocks in PL/SQL

  10. Triggers in PL/SQL

  11. Stored procedures, functions and packages

  12. Bulking in PL/SQL

 

 

Basics of PL/SQL
  • PL/SQL architecture
  • PL/SQL and SQL*Plus
  • PL/SQL Basics, Variables, Constants, data types & error handling
  • PL/SQL wrapper utility

PL/SQL structures 

  • Simple blocks
  • Control structures
  • PL/SQL records
  • Recognizing the Basic PL/SQL Block and Its Sections
  • Describing the Significance of Variables in PL/SQL
  • Distinguishing Between PL/SQL and Non-PL/SQL Variables
  • Declaring Variables and Constants
  • Executing a PL/SQL Block

Error checking – exception handling
  • Defining exceptions
  • Using the when others clause
  • Ensuring complete error checking
  • Passing error messages to calling routine

Boolean logic in PL/SQL
  • Identifying the Uses and Types of Control Structures
  • Constructing an IF Statement
  • Constructing and Identifying Different Loop Statements
  • Controlling Block Flow Using Nested Loops and Labels
  • Using Logic Tables
  • If-then-else structure
  • Testing for numbers characters and Booleans

Cursors in PL/SQL
  • Cursor basics
  • Using a cursor for a multi-row SQL query

Iteration in PL/SQL
  • For loop
  • While loop

 

  

PL/SQL tables
  • Defining PL/SQL tables
  • Reasons to use PL/SQL tables
  • Populating a PL/SQL table
  • Retrieving from a PL/SQL table

Dynamic SQL in PL/SQL 

  • Introduction to the dbms_sql package
  • Creating a dynamic SQL statement

Nested blocks in PL/SQL
  • Creating nested blocks
  • Understanding scope in nested blocks

Triggers in PL/SQL
  • Triggers and database events
  • Defining a trigger
  • Timing a trigger
  • Enabling and disabling a trigger

Stored procedures, functions and packages
  • Basics of stored procedures
  • Basics of functions
  • Basics of packages
  • Defining stored procedures & functions
  • Function and stored procedures prototypes
  • Passing arguments to functions and stored procedures
  • Recompiling functions and stored procedures
  • Pinning packages in the SGA with dbms_shared_pool.keep
  • Package forward declaration
  • Package dependency
  • Package overloading
  • Listing package information

Bulking in PL/SQL
  • Bulk queries
  • Bulk DML (forall statement)
  • Using cursor attributes
  • Analyzing impact of bulk operations

 

 

   
email BC:


Copyright © 1996 -  2009 by Burleson Enterprises, Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.