Learning the Basics of PL/SQL

• What Is PL/SQL?

• Why Learn PL/SQL?

• SQL, SQL*Plus, PL/SQL: What's the Difference?

  • SQL

  • PL/SQL

  • SQL*Plus

  • What You Need to Finish This Book

• How to Obtain Personal Oracle

• Getting Started with PL/SQL

  • PL/SQL Is Block Structured

  • Compiling and Executing a Simple Block

  • What About Some Output?

  • Alternatives to Retyping

  • Writing Your First Function

  • Finding Compilation Errors

  • Displaying the Function's Return Value

  • Can Even This Simple Function Be Useful?

• Summary

• Q&A

• Workshop

  • Quiz

  • Exercises

• Selecting a Development Tool

  • SQL*Plus

    • Environment Commands

    • Session Commands

    • Display Controls for Headers and Footers

    • Display Controls for Columns

    • Bind Variables

    • Substitution Variables

    • Building a Simple Break Report

    • Summary Computations

  • Developer/2000 Tools (Forms, Reports, and Graphs)

  • Oracle Precompiler

  • Summary

  • Q&A

  • Workshop

    • Quiz

    • Exercise

• Writing Declarations and Blocks

  • Datatypes

    • VARCHAR2

    • CHAR

    • NUMBER

    • BINARY_INTEGER

    • PLS_INTEGER

    • DATE

    • BOOLEAN

    • LONG

    • RAW

    • LONG RAW

    • ROWID

    • MSLABEL

  • Block Structure

    • Anonymous Blocks

    • Function and Procedure Blocks

    • Trigger Blocks

    • Nested Blocks

    • Nesting Functions and Procedures

    • Scope Rules

  • Summary

  • Q&A

  • Workshop

    • Quiz

    • Exercises

• Writing PL/SQL Expressions

  • PL/SQL Basics: Operators, Expressions, and Conversion

  • Operators

    • Arithmetic Operators

    • Comparison Operators

    • Logical Operators

    • String Operators

  • Use of Comparison Operators with Strings

    • The Effect of Character Set on String Comparisons

    • The Datatype's Effect on String Comparisons

    • The Effect of Case on String Comparisons

  • Use of Comparison Operators with Dates

  • Expressions

    • Expressions Defined

    • Operator Precedence

    • Types of Expressions

    • Null Values in Expressions

  • Datatype Conversions

    • Implicit Conversion

    • Explicit Conversion

  • Summary

  • Q&A

  • Workshop

    • Quiz

    • Exercise

• Using Functions, IF Statements, and Loops

  • PL/SQL Functions

    • Defining Formal Parameters

    • Return Types

    • Exception Handling

    • Creating a Stored Function

    • Finding Errors

  • The NULL Statement

    • Why Use the NULL Statement?

    • How to Call a Function from PL/SQL With or Without Parameters

  • PL/SQL Statements

    • The IF Statement

    • The IF...THEN...ELSE Statement

    • Nested IF Statements

    • Using IF...ELSIF

    • Nested IF Versus ELSIF

    • The Formatting of IF Statements

    • Avoiding Common Mistakes When Using IF

    • Looping Statements

    • Reversing the Loop

    • Different Incrementing Through a Loop

  • Summary

  • Q&A

  • Workshop

    • Quiz

    • Exercises

• Implementing Loops and GOTOs

  • Labels and the GOTO Statement

    • Statement Labels

    • The GOTO Statement

    • Scoping Rules for the GOTO Statement

    • An Example of the GOTO Statement in Action

    • Why Use the GOTO Statement?

  • WHILE Loops

    • Examples of WHILE Loops

  • The EXIT and EXIT WHEN Statements

    • Examples Using EXIT and EXIT WHEN

    • Using Labels and EXIT Statements with Loops

    • Sample Simple LOOPs

    • Creating a REPEAT...UNTIL Loop

    • An Example of a Simulated REPEAT...UNTIL Loop

  • What Loop Should I Use?

  • Loop Guidelines

    • Style Tips for Loops

  • Summary

  • Q&A

  • Workshop

    • Quiz

    • Exercises

• Using Oracle's Built-in Functions

  • SQL Functions Versus PL/SQL Functions

  • The Major Categories of Functions

  • Experimenting with Functions

  • Conversion Functions

    • Using TO_DATE

    • Using TO_CHAR for Dates

    • Using TO_NUMBER

    • Using TO_CHAR for Numbers

  • DATE Functions

    • SYSDATE

    • The TRUNC Function

    • The ADD_MONTHS Function

    • The NEXT_DAY Function

    • The LAST_DAY Function

    • The MONTHS_BETWEEN Function

    • The NEW_TIME Function

    • The ROUND Function

  • Summary

  • Q&A

  • Workshop

    • Quiz

    • Exercises

• WEEK 2

• At a Glance

  • Where You Are Going

  • Week 2

  Day 8

• Procedures, Packages, Errors, and Exceptions

  • Procedures

    • Why Use Procedures?

    • Procedures Versus Functions

    • Creating Procedures

    • The RETURN Statement

    • Procedure Dependencies

    • Discovering Dependencies

    • Recompiling a Stored Procedure

    • Re-creating and Modifying Procedures

    • Invoking Stored Procedures

    • Parameters

    • Parameter Definitions

    • Listing Stored Procedure Information

    • Dropping a Stored Procedure

    • Overloading

    • Recursion

    • Why Use Packages?

    • Package Helpful Hints

    • Package Specification

    • Creating Packages

    • Calling Package Subprograms

    • Recompiling Packages

    • Private Versus Public Package Objects

    • Variables, Cursors, and Constant Persistence

    • Package State

    • Package Dependency

  • Trapping Errors and Exceptions

    • Exception-Handling Structures

    • SQLCODE and SQLERRM

    • Reraising an Exception

    • Propagate Exceptions

  • Summary

  • Q&A

  • Workshop

    • Quiz

    • Exercises

• Using SQL: INSERT, SELECT, Advanced Declarations, and Tables

  • Types of SQL Statements

  • Creating Some Oracle Tables

  • The INSERT Statement

    • Inserting Some Data

  • Singleton SELECT Statement

    • Some SELECT Examples

  • Types of Exceptions

  • The UPDATE and DELETE Statements

  • Methods of Declaring Variables

    • Variables Based on Database Columns

    • Record Variables

    • Declarations at the Package Level

  • PL/SQL Tables

    • Declaring a PL/SQL Table

    • Referencing Values in a PL/SQL Table

    • Using a PL/SQL Table

    • Nested Tables

  • Summary

  • Q&A

  • Workshop

    • Quiz

    • Exercises

• Manipulating Data with Cursors, DELETE, and UPDATE

  • What Is a Cursor?

  • Explicit Cursors

    • Declaring a Cursor

    • Opening the Cursor

    • Fetching Data in a Cursor

    • Closing the Cursor

    • Explicit Cursor Attributes

  • Explicit Cursor Example

    • Automated Explicit Cursors

  • Implicit Cursors

    • Implicit Cursor Attributes

  • Summary

  • Q&A

  • Workshop

    • Quiz

    • Exercise

• Writing Database Triggers

  • What Is a Trigger?

    • An Example of a Trigger

    • Types of Triggers

    • Syntax

  • Uses for Triggers

    • Enforcing a Business Rule

    • Maintaining Data Integrity

    • Enforcing Security

    • Maintaining History

  • Managing Triggers

    • Listing Triggers

    • Viewing Trigger Code

    • Enabling and Disabling Triggers

  • Trigger Limitations

    • Triggers and Mutating Tables

  • Summary

  • Q&A

  • Workshop

    • Quiz

    • Exercises

• Using Cursors for Complex Processing

  • Passing Parameters to Cursors

    • Declaring Cursor Parameters

    • Using Cursor Parameters

  • Scope of Cursor Parameters

  • Cursor Return Clause and Packages

  • Cursor Variables

    • The Cursor Variable Declaration

    • Cursor Usage with Cursor Variables

    • Cursor Variable Assignments

    • Cursor Variables as Arguments

  • The Current Row of Parameter and Cursors

  • Cursor Scoping

  • Cursor Alias

  • Summary

  • Q&A

  • Workshop

    • Quiz

    • Exercise

• Using Oracle8 Objects for Object-Oriented Programming

  • A Quick Primer on Object-Oriented Programming

    • Encapsulation

    • Inheritance

    • Polymorphism

    • Classes, Objects, Attributes, and Methods

    • Advantages over Traditional Methods

  • How Oracle Implements Objects

    • Object Types

    • Object Tables

    • Object Views

  • Defining an Object Type

    • Constructor Methods

    • Accessor Methods

    • Mutator Methods

  • Instantiating and Using an Object

  • Storing and Retrieving Objects

    • Storing Objects as Table Columns

    • Retrieving and Updating Objects in a Table Column

  • Object Tables

    • Storing Objects in an Object Table

    • Retrieving and Updating Objects in an Object Table

    • Deleting Objects from an Object Table

  • Nesting and Sharing Objects

    • Dot Notation

    • Object References

  • The SELF Parameter

  • Overloading

  • Comparing Objects

    • The ORDER Method

    • The MAP Method

  • Limitations of Oracle's Implementation

  • Summary

  • Q&A

  • Workshop

    • Quiz

    • Exercises

• Debugging Your Code and Preventing Errors

  • Syntax Errors

  • Logic Errors

    • Order of Operations

    • Nonterminating Loops

  • The Nontool Approach

    • Setting Up a Test Environment

    • Setting Up Test Data

    • Narrowing Down the Location of a Bug

  • Using Tools to Help in Debugging a Program

    • Writing a Debugging Package

    • Using DBMS_OUTPUT as a Debugging Tool

  • Error Prevention and Planning for Debugging in the Future

    • Defining Requirements and Project Planning

    • Using a Modular Approach to Coding

    • Commenting Your Code

    • Formatting Your Code

  • Summary

  • Q&A

  • Workshop

    • Quiz

    • Exercises

Week 3 At a Glance

• Where You Are Going

• Week 3

  Day 15

Exploring Advanced Topics

• What Is Recursion?

• Practicing Recursion

• Why Use Recursion?

• Review of Large Object Datatypes

• Using External Files in Your Oracle8 Database

  • Creating the Directory Object

• Limitations of BFILE

• The DBMS_LOB Package with BFILE

  • The BFILENAME Function

  • The COMPARE Function

  • The FILECLOSE Procedure

  • The FILECLOSEALL Procedure

  • The FILEEXISTS Function

  • The FILEGETNAME Procedure

  • The FILEISOPEN Function

  • The FILEOPEN Procedure

  • The GETLENGTH Function

  • The INSTR Function

  • The READ Procedure

  • The SUBSTR Function

• Examples of BFILEs Using the DBMS_LOB Package

  • Accessing BFILEs

  • Comparing Files

• Working with Locators

• The DBMS_LOB Package with Internal LOBs

  • The APPEND Procedure

  • The COMPARE Function

  • The COPY Procedure

  • The EMPTY_BLOB Function

  • The EMPTY_CLOB Function

  • The ERASE Procedure

  • The GETLENGTH Function

  • The INSTR Function

  • The READ Procedure

  • The SUBSTR Function

  • The TRIM Procedure

  • The WRITE Procedure

• Examples of Internal LOBs Using the DBMS_LOB Package

  • Creating the Table

  • Adding Data to the Table

  • Populating the LOBS Table with the COPY Procedure

  • Manipulating Internal LOBs with APPEND and WRITE

  • Analyzing the Contents of an Internal LOB

  • Using TRIM and ERASE to Edit CLOBs

• Summary

• Q&A

• Workshop

  • Quiz

  • Exercise

• Managing Transactions and Locks

  • Types of Transactions

  • Starting a Transaction

  • Ending a Transaction

  • Canceling a Transaction

  • Two-Phase Commit

  • Savepoints

  • Locking

    • Table Locking

    • Row Locking

    • Other Locks

    • Monitoring Locks

  • Summary

  • Q&A

  • Workshop

    • Quiz

    • Exercise

• Generating Dynamic SQL

  • Steps for Using DBMS_SQL

  • Three Types of Statements Processed with DBMS_SQL

    • Using the DBMS_SQL Package with Non-Query DDL and DML Statements

    • Using the DBMS_SQL Package with Queries

    • Using the DBMS_SQL Package with Anonymous PL/SQL Blocks

  • Error Handling

    • IS_OPEN

    • LAST_ROW_COUNT

    • LAST_ROW_ID

    • LAST_ERROR_POSITION

    • LAST_SQL_FUNCTION_CODE

  • Fetching Long Data

    • DEFINE_COLUMN_LONG

    • COLUMN_VALUE_LONG

  • Summary

  • Q&A

  • Workshop

    • Quiz

    • Exercises

• Writing to Files and the Display

  • UTL_FILE Definition

    • File Input

    • File Output

    • Closing Files

    • Examples Utilizing the UTL_FILE Package

  • TEXT_IO

  • The DBMS_OUTPUT Package

    • Enabling the DBMS_OUTPUT Package

    • Disabling the DBMS_OUTPUT Package

    • Using PUT with the DBMS_OUTPUT Package

    • The NEW_LINE Procedure

    • Working with PUT_LINE

    • Testing PUT, NEW_LINE, and PUT_LINE

    • GET_LINE and the DBMS_OUTPUT Package

    • Using GET_LINES

    • Exceptions Raised from the DBMS_OUTPUT Package

  • Summary

  • Q&A

  • Workshop

    • Quiz

    • Exercises

• Managing Database Jobs

  • The DBMS_JOB Package

  • Background Processes

  • Execution of Jobs

    • Submitting Jobs to the Job Queue with SUBMIT

    • Submitting Jobs to the Job Queue with ISUBMIT

    • Using RUN to Execute Jobs Immediately

    • The Job Environment

  • Viewing Jobs

    • Samples for Viewing Jobs

  • Job Management

    • Removing a Job

    • Altering a Job

    • Importing and Exporting Jobs

    • Handling Broken Jobs

  • Hands-On Practice in Job Management

    • Creating Procedures to Submit as Jobs

    • Submitting All Jobs to the Job Queue

    • Running All Three Jobs Immediately

    • Viewing Information About the Jobs

    • Altering the Job Running the Procedure HELLO

    • Removing the Hello Job

  • The DBMS_PIPE Package

    • Public Versus Private Pipes

    • Steps in Using Pipes

    • The Functions and Procedures of DBMS_PIPE

    • An Example of Using Pipes

    • Other Pipe Functions and Procedures

    • Other Uses for Pipes

  • Summary

  • Q&A

  • Workshop

    • Quiz

    • Exercises

• Alerting Other Procedures: The DBMS_ALERT Package

  • Using SIGNAL to Issue an Alert

  • Registering for an Alert

  • Waiting for a Specific Alert

  • Waiting for Any Registered Alert

  • Removing One Alert

  • Removing All Alerts

  • Polling Versus Events with the SET_DEFAULTS Procedure

  • Alerts Versus Pipes

  • A Demonstration of the DBMS_ALERT Package

    • Creating the Backup Database

    • Creating the Trigger to Signal an Alert

    • Waiting for the Alert

  • Q&A

  • Workshop

    • Quiz

    • Exercises

• Implementing J/SQL for Web Applications

  • J/SQL

    • J/SQL Coding Standards

  • JDBC

  • PL/SQL Versus Java

  • The World Wide Web and SQL

  • Summary

  • Q&A

  • Workshop

    • Quiz

• WEEK 3 In Review

  • Day 15

  • Day 16

  • Day 17

  • Day 18

  • Day 19

  • Day 20

  • Day 21