Oracle® PL/SQL Training

This Oracle® PL/SQL training course will teach you to increase the productivity, performance, scalability, portability, and security of your Oracle database. With this training, you will learn how to execute PL/SQL programs in Oracle, build stored procedures, design, and execute modular applications, and increase the efficiency of data movement — essential skills for any Oracle developer.

Oracle® PL/SQL Training Benefits

• In this Oracle PL/SQL Programming training, you will learn how to:

  • Develop efficient PL/SQL programs to access Oracle databases. 
  • Create stored procedures and functions for reuse and maintenance. 
  • Design modular applications using packages. 
  • Manage data retrieval for front-end applications. 
  • Invoke native dynamic SQL to develop high-level abstract code. 

• Oracle PL/SQL Programming Training Course Prerequisites

  Students taking this course should have basic knowledge of SQL as taught in Learning Tree course 925, Introduction to SQL Course.

Oracle PL/SQL Course Outline

Module 1: Introduction and Overview

PL/SQL fundamentals

• Declaring and anchoring variables to database definitions
• Flow control constructs

Oracle 11g and 12c PL/SQL features

• PL/Scope in Oracle 11g
• Returning implicit cursor results from stored procedures in Oracle 12c
• Declaring local functions within SELECT statements in Oracle 12c

Module 2: 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

Module 3: 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

Module 4: 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

Module 5: 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

Module 6: 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

Module 7: 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