Time:  40 Hours

 

Prerequisite:  

 

Prior database basic concepts such as store/read/update/delete records. Prior exposure to object-oriented programming concepts is not required, but definitely beneficial.

 

Course overview:

 

The Oracle PL/SQL language is a flexible procedural extension to SQL and increases productivity, performance, scalability, portability and security. In this course, you will gain the practical knowledge to write PL/SQL programs. You will learn to build stored procedures, design and execute modular applications, and increase the efficiency of data movement.

 

Course Content:

Introduction and Overview

 

PL/SQL fundamentals

• Declaring and anchoring variables to database definitions

• Flow control constructs

• DML commands : Select, Insert, Update, Delete

• DDL commands: Create, Alter Tables/Views/Sequence

• TCL commands: Grant, Revoke

• Joins

• Trigger

• Stored procedure

•  

 

Oracle 11g and 12c PL/SQL features

• PL/SQL in Oracle 11g

• Returning implicit cursor results from stored procedures in Oracle 12c

• Declaring local functions within SELECT statements in Oracle 12c

 

Data Manipulation Techniques

• Maintaining data with DML statements

• Employing the RETURNING INTO clause

• Solving the fetch-across-commit problem

 

Managing data retrieval with cursors

• Implications of explicit and implicit cursors

• Simplifying cursor processing with cursor FOR LOOPs

• Embedding cursor expressions in SELECT statements

 

Cursor variables

• Strong vs. weak cursor variables

• Passing cursor variables to other programs

• Defining REF CURSORS in packages

• Developing Well-Structured and Error-Free Code

 

Error handling using EXCEPTIONs

• Propagation and scope

• "Retrying" problem transactions with EXCEPTION processing

 

Debugging PL/SQL blocks

• Simplifying testing and debugging with conditional compilation

• Interpreting compiler messages

• Applying structured testing techniques

• Building and applying a test bed

• Leveraging the debugging facilities in SQL Developer

• Achieving Maximum Reusability

 

Writing stored procedures and functions

• Calling and invoking server-side logic

• Passing input and output parameters

• Implementing an autonomous transaction

 

Coding user-written functions

• Calling PL/SQL functions from SQL

• Building table-valued functions

 

Developing safe triggers

• Employing :OLD and :NEW variables

• Avoiding unreliable trigger constructs

• Exploiting schema and database triggers

 

Exploiting Complex Datatypes

• Collection types

• PL/SQL tables, nested tables, VARRAYs

• Stepping through dense and nonconsecutive collections

 

Bulk binding for high performance

• Moving data into and out of PL/SQL blocks

• BULK COLLECT INTO and FORALL

• BULK cursor attributes

• BULK EXCEPTION handling

• Invoking Native Dynamic SQL

 

Finessing the compiler

• The EXECUTE IMMEDIATE statement

• The RETURNING INTO clause

 

Types of dynamic SQL

• Building SQL statements during runtime

• Autogenerating standard code

•  

Package Tips and Techniques

• Package structure: SPEC and BODY

• Eliminating dependency problems

• Overloading for polymorphic effects

• Evaluating application frameworks

• Declaring and using persistent global variables

 

Who should go for this course?

 

Programmers and others working with PL/SQL who have a working knowledge of SQL

 

Special notes:

 

• Hands-on labs for each topic

• Real life projects at the course completion

 

 

For more info contact ITEXPS