PL/SQL

This Database Program with PL/SQL training shows you how to develop stored procedures, functions, packages and database triggers. You'll learn to manage PL/SQL program units and dependencies, and usage of some of the Oracle-supplied packages.

Overview of PL/SQL

  • Advantages of PL/SQL
  • Tight Integration with SQL
  • Better Performance
  • Higher Productivity
  • Full Portability
  • Tight Security
  • Access to Predefined Packages
  • Support for ObjectOriented Programming
  • Support for Developing Web Applications and Pages
  • Understanding the Main Features of PL/SQL
  • Understanding PL/SQL Block Structure
  • Understanding PL/SQL Variables and Constants
  • Declaring Variables
  • Assigning Values to a Variable
  • Bind Variables
  • Declaring Constants
  • Processing Queries with PL/SQL
  • Declaring PL/SQL Subprograms
  • Declaring Datatypes for PL/SQL Variables
  • %TYPE
  • %ROWTYPE
  • Understanding PL/SQL Control Structures
  • Conditional Control
  • Iterative Control
  • Sequential Control
  • Understanding Conditional Compilation
  • Writing Reusable PL/SQL Code
  • Subprograms: Procedures and Functions
  • Packages: APIs Written in PL/SQL
  • Inputting and Outputting Data with PL/SQL
  • Understanding PL/SQL Data Abstraction
  • Cursors
  • Collections
  • Records
  • Object Types
  • Understanding PL/SQL Error Handling
  • PL/SQL Architecture
  • In the Oracle Database Server
  • Anonymous Blocks
  • Stored Subprograms
  • Database Triggers

Fundamentals of the PL/SQL Language

  • Character Sets and Lexical Units
  • Delimiters
  • Identifiers
  • Reserved Words
  • Predefined Identifiers
  • Quoted Identifiers
  • Literals
  • Numeric Literals
  • Character Literals
  • String Literals
  • BOOLEAN Literals
  • Datetime Literals
  • Comments
  • SingleLine Comments
  • Multiline Comments
  • Restrictions on Comments
  • Declarations
  • Constants
  • Using DEFAULT
  • Using NOT NULL
  • Using the %TYPE Attribute
  • Using the %ROWTYPE Attribute
  • Aggregate Assignment
  • Using Aliases
  • Restrictions on Declarations
  • PL/SQL Naming Conventions
  • Scope and Visibility of PL/SQL Identifiers
  • Assigning Values to Variables
  • Assigning BOOLEAN Values
  • Assigning a SQL Query Result to a PL/SQL Variable
  • PL/SQL Expressions and Comparisons
  • Logical Operators
  • Order of Evaluation
  • ShortCircuit Evaluation
  • Comparison Operators
  • Relational Operators
  • IS NULL Operator
  • LIKE Operator
  • BETWEEN Operator
  • IN Operator
  • Concatenation Operator
  • BOOLEAN Expressions
  • BOOLEAN Arithmetic Expressions
  • BOOLEAN Character Expressions
  • BOOLEAN Date Expressions
  • Guidelines for PL/SQL BOOLEAN Expressions
  • CASE Expressions
  • Simple CASE expression
  • Searched CASE Expression
  • Handling Null Values in Comparisons and Conditional Statements
  • NULLs and the NOT Operator

PL/SQL Datatypes

  • Overview of Predefined PL/SQL Datatypes
  • PL/SQL Number Types
  • BINARY_INTEGER Datatype
  • BINARY_FLOAT and BINARY_DOUBLE Datatypes
  • NUMBER Datatype
  • PLS_INTEGER Datatype
  • PL/SQL Character and String Types
  • CHAR Datatype
  • LONG and LONG RAW Datatypes
  • RAW Datatype
  • ROWID and UROWID Datatype
  • VARCHAR Datatype
  • PL/SQL National Character Types
  • Comparing UTF and ALUTF Encodings
  • NCHAR Datatype
  • NVARCHAR Datatype
  • PL/SQL LOB Types
  • BFILE Datatype
  • BLOB Datatype
  • CLOB Datatype
  • NCLOB Datatype
  • PL/SQL Boolean Types
  • BOOLEAN Datatype
  • PL/SQL Date, Time, and Interval Types
  • DATE Datatype
  • TIMESTAMP Datatype
  • TIMESTAMP WITH TIME ZONE Datatype
  • TIMESTAMP WITH LOCAL TIME ZONE Datatype
  • INTERVAL YEAR TO MONTH Datatype
  • INTERVAL DAY TO SECOND Datatype
  • Datetime and Interval Arithmetic
  • Avoiding Truncation Problems Using Date and Time Subtypes
  • Overview of PL/SQL Subtypes
  • Defining Subtypes
  • Using Subtypes
  • Type Compatibility with Subtypes
  • Constraints and Default Values with Subtypes
  • Converting PL/SQL Datatypes
  • Explicit Conversion
  • Implicit Conversion
  • Choosing Between Implicit and Explicit Conversion
  • DATE Values
  • RAW and LONG RAW Values
  • Differences between the CHAR and VARCHAR Datatypes
  • Assigning Character Values
  • Comparing Character Values
  • Inserting Character Values
  • Selecting Character Values

Using PL/SQL Control Structures

  • Overview of PL/SQL Control Structures
  • Testing Conditions: IF and CASE Statements
  • Using the IFTHEN Statement
  • Using the IFTHENELSE Statement
  • Using the IFTHENELSIF Statement
  • Using CASE Statements
  • Searched CASE Statement
  • Guidelines for PL/SQL Conditional Statements
  • Controlling Loop Iterations: LOOP and EXIT Statements
  • Using the LOOP Statement
  • Using the EXIT Statement
  • Using the EXITWHEN Statement
  • Labeling a PL/SQL Loop
  • Using the WHILELOOP Statement
  • Using the FORLOOP Statement
  • How PL/SQL Loops Iterate
  • Dynamic Ranges for Loop Bounds
  • Scope of the Loop Counter Variable
  • Using the EXIT Statement in a FOR Loop
  • Sequential Control: GOTO and NULL Statements
  • Using the GOTO Statement
  • Restrictions on the GOTO Statement
  • Using the NULL Statement

Using PL/SQL Collections and Records

  • What are PL/SQL Collections and Records?
  • Understanding PL/SQL Collections
  • Understanding Nested Tables
  • Understanding Varrays
  • Understanding Associative Arrays (IndexBy Tables)
  • How Globalization Settings Affect VARCHAR Keys for Associative Arrays
  • Understanding PL/SQL Records
  • Choosing Which PL/SQL Collection Types to Use
  • Choosing Between Nested Tables and Associative Arrays
  • Choosing Between Nested Tables and Varrays
  • Defining Collection Types and Declaring Collection Variables
  • Declaring PL/SQL Collection Variables
  • Initializing and Referencing Collections
  • Referencing Collection Elements
  • Assigning Collections
  • Comparing Collections
  • Using Multilevel Collections
  • Using Collection Methods
  • Checking If a Collection Element Exists (EXISTS Method)
  • Counting the Elements in a Collection (COUNT Method)
  • Checking the Maximum Size of a Collection (LIMIT Method)
  • Finding the First or Last Collection Element (FIRST and LAST Methods)
  • Looping Through Collection Elements (PRIOR and NEXT Methods)
  • Increasing the Size of a Collection (EXTEND Method)
  • Decreasing the Size of a Collection (TRIM Method)
  • Deleting Collection Elements (DELETE Method)
  • Applying Methods to Collection Parameters
  • Avoiding Collection Exceptions
  • Defining and Declaring Records
  • Using Records as Procedure Parameters and Function Return Values
  • Assigning Values to Records
  • Comparing Records
  • Inserting PL/SQL Records into the Database
  • Updating the Database with PL/SQL Record Values
  • Restrictions on Record Inserts and Updates
  • Querying Data into Collections of Records

Performing SQL Operations from PL/SQL

  • Overview of SQL Support in PL/SQL
  • Data Manipulation
  • Transaction Control
  • SQL Functions
  • SQL Pseudocolumns
  • SQL Operators
  • Managing Cursors in PL/SQL
  • Implicit Cursors
  • Attributes of Implicit Cursors
  • Guidelines for Using Attributes of Implicit Cursors
  • Explicit Cursors
  • Declaring a Cursor
  • Opening a Cursor
  • Fetching with a Cursor
  • Fetching Bulk Data with a Cursor
  • Closing a Cursor
  • Attributes of Explicit Cursors
  • Querying Data with PL/SQL
  • Selecting At Most One Row: SELECT INTO Statement
  • Selecting Multiple Rows: BULK COLLECT Clause
  • Looping Through Multiple Rows: Cursor FOR Loop
  • Performing Complicated Query Processing: Explicit Cursors
  • Querying Data with PL/SQL: Implicit Cursor FOR Loop
  • Querying Data with PL/SQL: Explicit Cursor FOR Loops
  • Defining Aliases for Expression Values in a Cursor FOR Loop
  • Using Subqueries
  • Using Correlated Subqueries
  • Writing Maintainable PL/SQL Queries
  • Using Cursor Variables (REF CURSORs)
  • What Are Cursor Variables (REF CURSORs)
  • Why Use Cursor Variables
  • Declaring REF CURSOR Types and Cursor Variables
  • Passing Cursor Variables As Parameters
  • Controlling Cursor Variables: OPENFOR, FETCH, and CLOSE
  • Opening a Cursor Variable
  • Using a Cursor Variable as a Host Variable
  • Fetching from a Cursor Variable
  • Closing a Cursor Variable
  • Reducing Network Traffic When Passing Host Cursor Variables to PL/SQL
  • Avoiding Errors with Cursor Variables
  • Restrictions on Cursor Variables
  • Using Cursor Expressions
  • Restrictions on Cursor Expressions
  • Example of Cursor Expressions
  • Constructing REF CURSORs with Cursor Subqueries
  • Overview of Transaction Processing in PL/SQL
  • Using COMMIT in PL/SQL
  • Using ROLLBACK in PL/SQL
  • Using SAVEPOINT in PL/SQL
  • How Oracle Does Implicit Rollbacks
  • Ending Transactions
  • Setting Transaction Properties with SET TRANSACTION
  • Restrictions on SET TRANSACTION
  • Overriding Default Locking
  • Doing Independent Units of Work with Autonomous Transactions
  • Advantages of Autonomous Transactions
  • Defining Autonomous Transactions
  • Comparison of Autonomous Transactions and Nested Transactions
  • Transaction Context
  • Transaction Visibility
  • Controlling Autonomous Transactions
  • Using Autonomous Triggers
  • Calling Autonomous Functions from SQL

Performing SQL Operations with Native Dynamic SQL

  • Why Use Dynamic SQL with PL/SQL?
  • Using the EXECUTE IMMEDIATE Statement in PL/SQL
  • Specifying Parameter Modes for Bind Variables in Dynamic SQL Strings
  • Using Bulk Dynamic SQL in PL/SQL
  • Using Dynamic SQL with Bulk SQL
  • Examples of Dynamic Bulk Binds
  • Guidelines for Using Dynamic SQL with PL/SQL
  • Building a Dynamic Query with Dynamic SQL
  • When to Use or Omit the Semicolon with Dynamic SQL
  • Improving Performance of Dynamic SQL with Bind Variables
  • Passing Schema Object Names As Parameters
  • Using Duplicate Placeholders with Dynamic SQL
  • Using Cursor Attributes with Dynamic SQL
  • Passing Nulls to Dynamic SQL
  • Using Database Links with Dynamic SQL
  • Using Invoker Rights with Dynamic SQL
  • Using Pragma RESTRICT_REFERENCES with Dynamic SQL
  • Avoiding Deadlocks with Dynamic SQL
  • Backward Compatibility of the USING Clause
  • Using Dynamic SQL With PL/SQL Records and Collections
  • Using PL/SQL Subprograms
  • What Are Subprograms?
  • Advantages of PL/SQL Subprograms
  • Understanding PL/SQL Procedures
  • Understanding PL/SQL Functions
  • Using the RETURN Statement
  • Declaring Nested PL/SQL Subprograms
  • Passing Parameters to PL/SQL Subprograms
  • Actual Versus Formal Subprogram Parameters
  • Using Positional, Named, or Mixed Notation for Subprogram Parameters
  • Specifying Subprogram Parameter Modes
  • Using the IN Mode
  • Using the OUT Mode
  • Using the IN OUT Mode
  • Summary of Subprogram Parameter Modes
  • Using Default Values for Subprogram Parameters
  • Overloading Subprogram Names
  • Guidelines for Overloading with Numeric Types
  • Restrictions on Overloading
  • How Subprogram Calls Are Resolved
  • How Overloading Works with Inheritance
  • Using Invoker's Rights Versus Definer's Rights (AUTHID Clause)
  • Advantages of Invoker's Rights
  • Specifying the Privileges for a Subprogram with the AUTHID Clause
  • Who Is the Current User During Subprogram Execution?
  • How External References Are Resolved in Invoker's Rights Subprograms
  • The Need for Template Objects in Invoker's Rights Subprograms
  • Overriding Default Name Resolution in Invoker's Rights Subprograms
  • Granting Privileges on Invoker's Rights Subprograms
  • Granting Privileges on an Invoker's Rights Subprogram: Example
  • Using Roles with Invoker's Rights Subprograms
  • Using Views and Database Triggers with Invoker's Rights Subprograms
  • Using Database Links with Invoker's Rights Subprograms
  • Using Object Types with Invoker's Rights Subprograms
  • Calling Invoker's Rights Instance Methods
  • Using Recursion with PL/SQL
  • What Is a Recursive Subprogram?
  • Calling External Subprograms
  • Controlling Side Effects of PL/SQL Subprograms
  • Understanding Subprogram Parameter Aliasing

PL/SQL Packages

  • What Is a PL/SQL Package?
  • What Goes In a PL/SQL Package?
  • Advantages of PL/SQL Packages
  • Understanding The Package Specification
  • Referencing Package Contents
  • Restrictions
  • Understanding The Package Body
  • Some Examples of Package Features
  • Private Versus Public Items in Packages
  • How Package STANDARD Defines the PL/SQL Environment
  • Overview of ProductSpecific Packages
  • About the DBMS_ALERT Package
  • About the DBMS_OUTPUT Package
  • About the DBMS_PIPE Package
  • About the HTF and HTP Packages
  • About the UTL_FILE Package
  • About the UTL_HTTP Package
  • Guidelines for Writing Packages
  • Separating Cursor Specs and Bodies with Packages

Handling PL/SQL Errors

  • Overview of PL/SQL Runtime Error Handling
  • Guidelines for Avoiding and Handling PL/SQL Errors and Exceptions
  • Advantages of PL/SQL Exceptions
  • Summary of Predefined PL/SQL Exceptions
  • Defining Your Own PL/SQL Exceptions
  • Declaring PL/SQL Exceptions
  • Scope Rules for PL/SQL Exceptions
  • Associating a PL/SQL Exception with a Number: Pragma EXCEPTION_INIT
  • Defining Your Own Error Messages: Procedure RAISE_APPLICATION_ERROR
  • Redeclaring Predefined Exceptions
  • How PL/SQL Exceptions Are Raised
  • Raising Exceptions with the RAISE Statement
  • How PL/SQL Exceptions Propagate
  • Reraising a PL/SQL Exception
  • Handling Raised PL/SQL Exceptions
  • Exceptions Raised in Declarations
  • Handling Exceptions Raised in Handlers
  • Branching to or from an Exception Handler
  • Retrieving the Error Code and Error Message: SQLCODE and SQLERRM
  • Catching Unhandled Exceptions
  • Tips for Handling PL/SQL Errors
  • Continuing after an Exception Is Raised
  • Retrying a Transaction
  • Using Locator Variables to Identify Exception Locations
  • Overview of PL/SQL CompileTime Warnings
  • PL/SQL Warning Categories
  • Controlling PL/SQL Warning Messages
  • Using the DBMS_WARNING Package

Tuning PL/SQL Applications for Performance

  • Initialization Parameters for PL/SQL Compilation
  • How PL/SQL Optimizes Your Programs
  • When to Tune PL/SQL Code
  • Guidelines for Avoiding PL/SQL Performance Problems
  • Avoiding CPU Overhead in PL/SQL Code
  • Make SQL Statements as Efficient as Possible
  • Make Function Calls as Efficient as Possible
  • Make Loops as Efficient as Possible
  • Do Not Duplicate Builtin String Functions
  • Reorder Conditional Tests to Put the Least Expensive First
  • Minimize Datatype Conversions
  • Use PLS_INTEGER for Integer Arithmetic
  • Use BINARY_FLOAT and BINARY_DOUBLE for FloatingPoint Arithmetic
  • Avoiding Memory Overhead in PL/SQL Code
  • Be Generous When Declaring Sizes for VARCHAR Variables
  • Group Related Subprograms into Packages
  • Pin Packages in the Shared Memory Pool
  • Improve Your Code to Avoid Compiler Warnings
  • Profiling and Tracing PL/SQL Programs
  • Using The Profiler API: Package DBMS_PROFILER
  • Using The Trace API: Package DBMS_TRACE
  • Controlling the Trace
  • Reducing Loop Overhead for DML Statements and Queries with Bulk SQL
  • Using the FORALL Statement
  • How FORALL Affects Rollbacks
  • Counting Rows Affected by FORALL with the %BULK_ROWCOUNT Attribute
  • Handling FORALL Exceptions with the %BULK_EXCEPTIONS Attribute
  • Retrieving Query Results into Collections with the BULK COLLECT Clause
  • Examples of BulkFetching from a Cursor
  • Limiting the Rows for a Bulk FETCH Operation with the LIMIT Clause
  • Retrieving DML Results into a Collection with the RETURNING INTO Clause
  • Using FORALL and BULK COLLECT Together
  • Using Host Arrays with Bulk Binds
  • Writing ComputationIntensive Programs in PL/SQL
  • Tuning Dynamic SQL with EXECUTE IMMEDIATE and Cursor Variables
  • Tuning PL/SQL Procedure Calls with the NOCOPY Compiler Hint
  • Restrictions on NOCOPY
  • Handling Exceptions in Pipelined Table Functions

Quick Enquiry Form