• Decrease font size
  • Default font size
  • Increase font size

Oracle Database 11g PL/SQL Programming

 Print E-mail

Public Training Course - Taught Live OnLine

Notify me when Public Technical Training Course is next scheduled!Training Description

This online Oracle Database 11g: PL/SQL Programming technical training course introduces students to PL/SQL and helps them understand the benefits of Oracle’s proprietary database language. Students learn to create PL/SQL blocks of application code that can be shared by multiple users, forms, reports, and data management applications. Students learn to create anonymous PL/SQL blocks as well as named stored procedures, functions, packages and triggers. Students learn to develop, execute, and manage PL\SQL stored program units, subprograms, triggers, declaring identifiers and both user-defined and pre-defined exceptions. Students are introduced to the utilization of some of the more useful Oracle-supplied packages.

Training Objectives

As a result of attending this course, you will be able to:

  • Describe the features and syntax of PL/SQL
  • Use PL/SQL programming constructs and conditionally control code flow (loops, control structures, and explicit cursors)
  • Handle runtime errors
  • Design PL/SQL anonymous block that execute efficiently
  • Create and use stored procedures and functions
  • Design and use PL/SQL packages to group and contain related constructs
  • Manage dependencies between PL/SQL subprograms
  • Use conditional compilation to customize functionality in a PL/SQL block
  • Create overloaded package subprograms
  • Use the Oracle supplied PL/SQL packages to generate screen output, file output, and schedule jobs.
  • Write dynamic SQL for coding flexibility
  • Describe security for stored PL/SQL units
  • Design PL/SQL code for predefined data types, local subprograms, additional pragmas and standardized constants and exceptions
  • Create triggers to solve business requirements including 11g compound triggers
  • Use the 11g PL/SQL function cache

Prerequisites

  • The course, Oracle Database 11g: SQL
  • Previous programming experience

Audience

  • System Analysts, Developers, Database Administrators, Technical Consultants

Training Outline

Introduction to PL/SQL

  • PL/SQL Overview
  • Benefits of PL/SQL Subprograms
  • Overview of the Types of PL/SQL blocks
  • Creating and Executing an Anonymous Block
  • Displaying Output from a PL/SQL Block

PL/SQL Identifiers

  • PL/SQL datatypes
  • Using the Declarative Section of a block
  • Storing Data in Variables
  • Scalar Data Types
  • %TYPE Attribute
  • Using Sequences in PL/SQL Expressions

Executable Statements

  • Describing Basic PL/SQL Block Syntax Guidelines
  • Commenting Code
  • SQL Functions in PL/SQL
  • Data Type Conversion
  • Nested Blocks
  • Operators in PL/SQL
  • Retrieving Data with SELECT Statements in PL/SQL
  • Making Changes to Database Data Using PL/SQL
  • SQL Cursors
  • Using SQL Cursor Attributes
  • Transaction Processing in PL/SQL

Control Structures

  • Using IF Statements
  • Using CASE Statements
  • Simple Loop Statement
  • While Loop Statement
  • For Loop Statement
  • The Continue Statement

Composite Data Types

  • Using PL/SQL Records
  • Using the %ROWTYPE Attribute
  • Inserting and Updating with PL/SQL Records
  • Using PL/SQL Tables (Associative Arrays)

Explicit Cursors

  • Understanding Explicit Cursors
  • Declaring the Cursor
  • Opening the Cursor
  • Fetching data from the Cursor
  • Closing the Cursor
  • Cursor FOR loop
  • Explicit Cursor Attributes
  • FOR UPDATE Clause
  • WHERE CURRENT Clause

Exception Handling

  • Understanding Exceptions and Exception Handling
  • Coding for Predefined Oracle Server Errors
  • Coding for Non-Predefined Oracle Server Errors
  • Coding for User-Defined Exceptions
  • Exception Propogation
  • Calling the RAISE_APPLICATION_ERROR Procedure

Stored Procedures

  • Modular Development
  • Understanding the PL/SQL Execution Environment
  • Advantages of Using PL/SQL Subprograms
  • Anonymous Blocks verses Stored Subprograms
  • Using the CREATE Procedure Command
  • Using Parameters
  • Understanding Parameters Modes
  • Viewing Information About Stored Procedures Using the Data Dictionary Views

Stored Functions

  • Using the CREATE Function Command
  • Calling User-Defined Functions in SQL Statements
  • Controlling Side Effects When Calling Functions from SQL Expressions
  • Viewing Information About Stored Functions Using the Data Dictionary Views

Managing Dependencies

  • Define Schema Object Dependencies
  • Querying information about dependencies from the USER_DEPENDENCIES View
  • Status for a Schema Object
  • Invalidation of Dependent Objects
  • Fine-Grained Dependency Management in Oracle Database 11g
  • Recompiling a PL/SQL Program Unit

Packages

  • Advantages of Packages
  • Describing Packages
  • The Components of a Package
  • Using the CREATE Package and CREATE Package Body Commands
  • The Scope of a Package’s Components
  • Invoking the Elements in a Package
  • Persistent State of Packages
  • Using the Data Dictionary to View Package Source Code
  • Overloading Subprograms in PL/SQL
  • Using Forward Declarations
  • Using Package Functions in SQL
  • Using PL/SQL Tables of Records in Packages

DML Triggers

  • Identifying Scenarios for Implementing Triggers
  • Trigger Events
  • Trigger Bodies
  • Using the CREATE TRIGGER Statement to Create DML Triggers
  • Statement Level Triggers Versus Row Level Triggers
  • Disabling Triggers

Compound, DDL, and Event Database Triggers

  • Working With Compound Triggers
  • Identifying the Timing-Point of a Table Compound Trigger
  • Structure of Compound Triggers
  • Using a Compound Trigger to Resolve the Mutating Table Error
  • Creating Triggers on DDL Statements
  • Creating Database-Event and System-Event Triggers
  • System Privileges Required for Trigger Management

Oracle-Supplied Packages

  • Examples of Some of the Oracle-Supplied Packages
  • Using the DBMS_OUTPUT Package
  • Using the UTL_FILE Package
  • Using the UTL_MAIL Package
  • Using the DBMS_METADATA Package
  • Using the UTL_COMPRESS Package
  • Using the DBMS_CRYPTO Package
  • Using the DBMS_SCHEDULER Package
  • Using the DBMS_PIPE Package

Dynamic SQL and Bulk Processing

  • Using the EXECUTE IMMEDIATE Command
  • Cursor Variables
  • Using Native Dynamic SQL to Compile PL/SQL Code
  • Context Switches
  • Bulk Processing
  • Using the FORALL Statement

Design Considerations

  • Autonomous Transactions
  • The NOCOPY Compiler Hint
  • The PARALLEL_ENABLE Hint
  • Security Options for Stored PL/SQL Units
  • Using the Cross-Session PL/SQL Function Result Cache
  • Using the DETERMINISTIC Clause with Functions
  • Improving Performance Using Bulk Binding

The PL/SQL Compiler

  • Using the PL/SQL Compiler
  • Initialization Parameters for PL/SQL Compilation
  • PL/SQL Compile Time Warnings
  • Compile Time Warning Messages Categories
  • Using PLSQL_WARNINGS Initialization Parameter to Set the Warning Messages Levels
  • Using the DBMS_WARNING Package to Set the Warning Messages Levels
  • Querying the Compiler Warnings from the Data Dictionary Views
  • Conditional Compilation
  • The PLSQL_CCFLAGS Parameter