Oracle PL/SQL Programming Training Class

This course is taught at your Company site with up to 20 students. 

Click here for on-site course prices

Optional supplemental mentoring

* Learn the basics of PL/SQL.

* Understand PL/SQL tables and arrays.

* Understand how to implement PL/SQL structures.

* Create Dynamic SQL in PL/SQL.

* See when to use Nested blocks in PL/SQL.

* Use Stored procedures, functions and packages in PL/SQL.

* See Iteration  and looping in PL/SQL.

* Program complex Boolean logic in PL/SQL

* Learn ref cursors

* See how to use the bulk collect and forall operators.

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

The topics start with the basics of PL/SQL and progress into increasingly complex programmatic structures including array manipulation cursor management and bulking operations.  The PL/SQL training class begins with simple concepts and the student gradually masters PL/SQL through increasingly challenging classroom exercises.

		Easy Oracle PL/SQL Programming: ISBN 0-9759135-7-3
		Oracle PL/SQL Tuning Expert Secrets for High Performance Programming Dr. Tim Hall ISBN 0-9761573-9-X

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 PL/SQL is not required, but experience using Oracle or any other relational database is highly desirable.

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