Oracle PL/SQL Programming

Learn via : Virtual Classroom / Online
Duration : 5 Days
Download PDF
  1. Home
  3. Oracle PL/SQL Programming

Description

    This Oracel 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.

     

    Delegates 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

Outline

Introduction and Overview

  • PL/SQL fundamentals
  • Declaring and anchoring variables to database definitions
  • Flow control constructs
  • Oracle 12c PL/SQL features
  • 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

Prerequisites

Knowledge of Introduction to SQL