Intro to PL/ SQL Programming

Introduction to PL/SQL Programming

Length: 3 Day
Prerequisites: You must possess strong programming skills to benefit from this class. We will not teach you
the basics of programming such as logic flow and conditional logic. We will, however, teach you how to do it
in PL/SQL. Experience with a procedural language (e.g. java, C, COBOl, .NET, etc.) and SQL is required.

Overview

This class will teach you how to write efficient and scalable PL/SQL programs to create database-intensive PL/SQL applications. You will learn the critical and fundamental aspects of the PL/SQL language.

Learning how to create efficient, scalable PL/SQL programs is an important objective of this course. To meet the objective, the course includes instruction on such things as the proper use of bind variables, bulk processing, pipelining, and benchmarking different formulations of a routine to help identify the code that should be tuned.

Hands-on workshops constitute approximately 50% of the class. Overview of relational concepts and Oracle architecture are also provided. As with all of our courses, this lass is highly customizable to your specific training requirements.

As with all our courses, this class is highly customizable to your specific training requirements.

Key Benefits: After successfully completing this course, you will be able to:

Code efficient, scalable PL/SQL programs that include common programming constructs such as data-typing, variable assignment, flow control, cursor handling, bulk processing, array processing and error handling
Create PL/SQL stored procedures that accept and return values or sets of values
Manage stored procedure dependencies and privileges
Create PL/SQL functions, including powerful table functions and pipelined table functions
Create database triggers for auditing, complex business rule support, simple replication and more
Create and maintain Oracle packages
Use the UTL_FILE package to read and write to operating system files.
Use the UTL_MAIL package to send email from an Oracle database
Do simple benchmarking of PL/SQL code with the DBMS_UTITLITY package

Audience: Developers, Analysts, DBAs who know any programming language will benefit from this class.

Outline

Chapter 1. Introduction to PL/SQL

What is PL/SQL?
Why Use PL/SQL?
Pl/SQL Program Structure
Anonymous Blocks
Compile Errors
Output to SLQ *Plus
Procedures
Functions
Packages
Data Dictionary
Triggers
Tools for Development
Working in SQL *Plus

Chapter 2. Language Fundamentals

Statements
PL/SQL Symbols
Quoting Mechanism
Common PL/SQL Datatypes
Declaring Variables
%Type
PL/SQL Records
Records: %ROWTYPE
Programmer Defined Records
Variable Scope
Nested Blocks
Functions
Regular Expressions
IF Statement
CASE Statement
Simple Expression
Loops
Numeric FOR Loop
PL/SQL Arrays
Simple Array Example

Chapter 3. PL/SQL and SQL Basics

SELECT INTO Statement
Implicit Cursor Loops
Basics Workshop A (Cursor Loop)
DML in PL/SQL
Cursor Attributes
Native Dynamic SQL (NDS)
Embedding DDL
Basics Workshop B (NDS)

Chapter 4. PL/SQL and SQL : Cursors

What is a Cursor?
Cursor Processing
Cursor Attributes
Dynamic Cursors
Variable Scope Cursors
Cursor Parameters
Cursor Record
Cursor FOR LOOP
Referencing the Current Row

Chapter 5. PL/SQL: Bulk Processing

Lesson Objectives
BULK Processing
BULK Collect: Multiple Arrays, SELECT INTO, FETCH with LIMIT
BULK DELETE
BULK INSERT
FORALL Improvements
Returning into Arrays

Chapter 6. Procedures

What is a Procedure?
Abbreviated Syntax
Review: Compile Errors
Compiler Warnings
Optimizing Compiler
Native Compilation
Procedure Signatures
Calling Procedures
Procedure Synonyms
Referencing Parameters by Name
Returning Sets: REF CURSOR
Dropping Procedures
Dependencies
ALTER COMPILE
Privileges

Chapter 7. Functions

What is a Function?
Abbreviated Syntax
Calling Functions
Table Functions
Building & Using Table Functions
Pipelined Example
Test Data Generator

Chapter 8. Error Handling

Types of Errors
Runtime Errors
Exception Concepts
Recovering from Errors
Logging Exceptions
RAISE_APPPLICATION _ERROR
Raising Exceptions
EXCEPTION_INIT

Chapter 9. Packages

Concepts
Benefits
Package Contents
Recovering from Errors
Package Body Syntax
Privileges
Calling Packaged Objects
Initialization Code
Session Variables
Package Variables
Package Overloading
Compiling Packages
DROP Package
Supplied Package

Chapter 10. Triggers

Trigger Concepts
Trigger Execution
Row Trigger
OLD Reference Variable
NEW Reference Variable
Trigger Attributes
Derived Value Trigger
Restrictions
Security Privileges
Statement Level Triggers
Autonomous Transactions
Autonomous Triggers
INSTEAD OF Triggers

Chapter 11. File I/O Using UTL_FILE

UTL_FILE Concepts
Setup
Unix File Permissions
Opening, closing, reading and Writing Files
Write Files - PUT, PUT_LINE, NEW_LINE, PUTF

Chapter 12. Overview UTL_MAIL and other PL/SQL Built-in Packages

Introduction UTL_MAIL
PUTL_MAIL Setup
Other PL/SQL Build-in Packages

Back to Course Listing