Course 263:
Oracle PL/SQL Programming

(5 days)

 

Course Description

Create database-level applications. Participants will code procedural constructs including control statements, loops and cursors, create PL/SQL object constructs such as stored procedures, functions, packages, and database triggers. Participants will develop techniques to enforce business rules within the database. Exercises and lab sessions reinforce the learning objectives and provide participants the opportunity to gain practical hands-on experience.

Learning Objectives

  • Develop efficient PL/SQL programs to access Oracle databases
  • Conditionally control code flow (loops, control structures)
  • Use some of the Oracle supplied PL/SQL packages to generate screen and file outputs
  • Create stored procedures for maximum reuse and minimum code maintenance
  • Design modular applications using packages
  • Invoke native dynamic SQL to build runtime SQL statements
  • Manage data retrieval with cursors and cursor variables
  • Enhance performance of collection datatypes with bulk operations
  • Manage dependency issues
  • Understand when and how to use overloading
  • Create triggers to solve business challenges and enforce business rules

Who Should Attend

  •  Database Administrators
  •  Developers
  •  Others needing a working knowledge of PL/SQL

Prerequisites

  • A working knowledge of SQL
  • Familiarity of SQL*Plus and SQL Developer
  • A basic understanding of PL/SQL

Course Outline

1. Introduction and Overview

  • Course Objectives
  • Describe the Course Database Schema
  • Introduction to SQL Developer
  • SQL Review

2. PL/SQL Overview

  • What Is PL/SQL?
  • Benefits of PL/SQL
  • Review the Types of PL/SQL Blocks
  • PL/SQL Block Structure
  • How to Generate Output from a PL/SQL Block
  • Create a Simple Anonymous Block
  • Understand and Resolve Compilation Errors
  • Using the SQL Developer Debugger

3. PL/SQL Basics

  • Variable Declaration and Assignment
  • Variable Data Types
  • Variable Scoping
  • Use Variables to Store Data
  • Work with PL/SQL Records
  • The %TYPE and %ROWTYPE Attributes
  • Control Structures

4. Working with Data

  • Using Built-In Functions and Expressions
  • Embedded SQL Statements
  • The RETURNING Statement
  • Understanding Transactions
  • Avoid Errors by Using Proper Naming Conventions

5. Cursors

  • Implicit Cursors
  • Explicit Cursors
  • How to Use Cursor Attributes
  • Cursor Variables
  • Cursor Expressions

6. Procedures and Functions

  • Review of Subprograms
  • Working with Parameters in Procedures and Functions
  • Maintaining Subprogram in the Database
  • Definers Rights/Invokers Rights Subprograms
  • Autonomous Transactions
  • User-Written SQL Functions
  • Scalar and Table-Valued Functions
  • Using the Result Cache

7. Application Development with Packages

  • Define Packages
  • Private and Public Variables
  • Storing Session Data in Packages
  • Initialization
  • Overloading
  • Oracle-Supplied Packages
  • Using PL/Scope for Debugging
  • Dependencies

8. Triggers

  • Describe Triggers
  • Identify the Trigger Event Types and Body
  • Differences between Statement-Level and Row-Level Triggers
  • Instead of Triggers
  • Solving Mutating Table Problem with Compound Triggers
  • How to Maintain Triggers
  • Understanding Advantages and Disadvantages of Using Triggers

9. Error Handling

  • Exception Types
  • Predefined Exceptions
  • Undefined Exceptions
  • User Defined Exceptions
  • Exception Propagation
  • Retrying a Transaction

10. Using Collections

  • About Collections
  • Associative Arrays, Nested Tables, and VARRAYs
  • Coding with the Collection Methods
  • Using the Collection Set Operators
  • Comparing the Collection Types
  • Enhance Performance with Bulk Operations

11. Native Dynamic SQL

  • Basics of NDS
  • Using the EXECUTE IMMEDIATE Statement
  • The Dangers of Injection and How to Avoid

Please Contact Your ROI Representative to Discuss Course Tailoring!