This course introduces students to PL/SQL and helps them understand the benefits of this powerful programming language. In the class, students learn to create PL/SQL blocks of application code that can be shared by multiple forms, reports, and data management applications.

    Oracle Database 11g : Program with PL/SQL Course

    Course Objectives

    • Design PL/SQL anonymous blocks that execute efficiently
    • Write PL/SQL code to interface with the database
    • 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
    • Create simple procedures and functions
    • Design PL/SQL packages to group and contain related constructs
    • Schedule PL/SQL jobs to run independently
    • Create triggers to solve business challenges

    Introduction to PL/SQL

    • What is PL/SQL
    • PL/SQL Environment
    • Benefits of PL/SQL
    • Overview of the Types of PL/SQL blocks
    • Create and Execute a Simple Anonymous Block
    • Generate Output from a PL/SQL Block
    • SQL Developer as PL/SQL Programming Environment

    Declaring PL/SQL Identifiers

    • Identify the Different Types of Identifiers in a PL/SQL subprogram
    • Use the Declarative Section to Define Identifiers
    • List the Uses for Variables
    • Store Data in Variables
    • Declare PL/SQL Variables

    Writing Executable Statements

    • Describe Basic Block Syntax Guidelines
    • Use Literals in PL/SQL
    • Customize Identifier Assignments with SQL Functions
    • Use Nested Blocks as Statements
    • Reference an Identifier Value in a Nested Block
    • Qualify an Identifier with a Label
    • Use Operators in PL/SQL
    • Use Proper PL/SQL Block Syntax and Guidelines

    Interacting with the Oracle Server

    • Identify the SQL Statements You Can Use in PL/SQL
    • Include SELECT Statements in PL/SQL
    • Retrieve Data in PL/SQL with the SELECT Statement
    • Avoid Errors by Using Naming Conventions When Using Retrieval and DML Statements
    • Manipulate Data in the Server Using PL/SQL
    • The SQL Cursor concept
    • Use SQL Cursor Attributes to Obtain Feedback on DML
    • Save and Discard Transactions

    Writing Control Structures

    • Control PL/SQL Flow of Execution
    • Conditional processing Using IF Statements
    • Conditional Processing CASE Statements
    • Handle Nulls to Avoid Common Mistakes
    • Build Boolean Conditions with Logical Operators
    • Use Iterative Control with Looping Statements

    Working with Composite Data Types

    • Learn the Composite Data Types of PL/SQL Records and Tables
    • Use PL/SQL Records to Hold Multiple Values of Different Types
    • Inserting and Updating with PL/SQL Records
    • Use INDEX BY Tables to Hold Multiple Values of the Same Data Type

    Using Explicit Cursors

    • Cursor FOR Loops Using Subqueries
    • Increase the Flexibility of Cursors By Using Parameters
    • Use the FOR UPDATE Clause to Lock Rows
    • Use the WHERE CURRENT Clause to Reference the Current Row
    • Use Explicit Cursors to Process Rows
    • Explicit Cursor Attributes
    • Cursors and Records

    Handling Exceptions

    • Handling Exceptions with PL/SQL
    • Predefined Exceptions
    • Trapping Nonpredefined Oracle Server Errors
    • Functions that Return Information on Encountered Exceptions
    • Trapping User-Defined Exceptions
    • Propagate Exceptions
    • Use The RAISE_APPLICATION_ERROR Procedure To Report Errors To Applications

    Creating Stored Procedures

    • Describe PL/SQL blocks and subprograms
    • Describe the uses of procedures
    • Create procedures
    • Differentiate between formal and actual parameters
    • List the features of different parameter modes
    • Create procedures with parameters and invoke a procedure
    • Handle exceptions in procedures

    Creating Stored Functions

    • Describe stored functions
    • List the CREATE OR REPLACE FUNCTION syntax
    • Identify the steps to create a stored function
    • Create a stored function in SQL Developer and execute a stored function
    • Identify the advantages of using stored functions in SQL statements
    • Identify the restrictions of calling functions from SQL statements
    • Describe how procedures and functions differ

    Creating Packages

    • List the benefits or using PL/SQL packages
    • Differentiate between a package specification and a package body
    • Create packages
    • Include public and private constructs in a package
    • Call public and private constructs in a package
    • Remove packages

    Creating Triggers

    • Describe different types of triggers
    • Describe database triggers and their use
    • Create database triggers
    • Describe database trigger firing rules
    • Remove database triggers

    Applications for Triggers

    • Create database and system event triggers
    • Create triggers on DDL statements
    • Use the CALL statement in triggers to invoke procedures
    • Explain the rules for reading and writing to tables with triggers
    • Describe business application scenarios for implementing with triggers
    • Manage trigger code

    Understanding and Influencing the PL/SQL Compiler

    • Describe native compilation and interpreted compilation
    • List the features of native compilation
    • Switch between native and interpreted compilation for compiled PL/SQL code
    • Set the parameters to control aspects of PL/SQL compilation
    • Explain the compiler warning mechanism
    • List the steps to use the compiler warnings
    • Use DBMS_WARNING to implement compiler warnings