Oracle

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