106-110

Table of Contents

Chapter 4
Procedures

A stored procedure is a piece of code that performs a specific task. The procedure is compiled by Oracle and stored within the data dictionary.

This chapter discusses the creation of procedures using PL/SQL. I have provided several detailed examples, and will guide you through the entire process of creating a new procedure. By the end of this chapter, you will be familiar with how stored procedures are used and how to design, create, and test your own procedures.

Advantages Of Procedures

By allowing you to store the executable and source code for your application within the data dictionary, Oracle allows you to create applications that reach new heights in performance, modularity, maintainability, and reliability. Code no longer has to be generated within the front end of a system; instead, logic can be centralized and called from every part of a system s interface.

Embedded SQL

Stored procedures can execute any DML statement that can be executed in SQL*Plus, as shown in Listing 4.1.

Listing 4.1 Embedded SQL within a stored procedure.

 PROCEDURE Annual_Review . .    SELECT base_salary    INTO   nBaseSalary    FROM   EMPLOYEES    WHERE  CURRENT OF Employees_cur; . . END Annual_Review;

Maintainability

The logic of a stored procedure is more easily maintained than individual copies of the same piece of code spread throughout a system. If a logic error is discovered or a business rule changes, only the one stored procedure has to be changed and tested .

If the same logic is coded into several different applications, changing the code could easily take several times longer than changing a single piece of code. There are also reliability issues because making several changes increases the likelihood of a defect being introduced into the code.

Modularity

By coding logic for a specific task into a stored procedure, the logic for the task becomes readily available to any code that needs to perform the specific task. The procedure will accept a defined set of input values and will process those values in exactly the same manner every time the procedure executes. Figure 4.1 illustrates the concepts behind modular code.

Figure 4.1 Modular versus non-modular code.

Performance Improvement

There are several reasons why stored procedures provide some performance improvements over code that is implemented in multiple locations to perform the same task, including:

On large projects, code to perform a similar task in multiple locations is rarely written by the same developer. Calling a stored procedure to perform a task increases the likelihood that the DML statements inside the stored procedure are already cached in the SGA because those statements are written only once.

The implementation of a stored procedure does not change depending on which part of the system calls the procedure. The parameter values are the only differences in the procedure s execution.

Oracle maintains a copy of the executable version of the stored procedure ( p-code ) and executes this copy rather than recompiling the procedure.

Reusability

A stored procedure only has to be written once and can be called from many different parts of a system. Even if a logic error is found or a business rule changes, the change only has to be made once and every part of the system that calls the stored procedure is corrected.

Creating And Dropping Procedures

Procedures are created in SQL*Plus using a command like the one shown in Listing 4.2.

Listing 4.2 Creating a stored procedure.

 CREATE OR REPLACE PROCEDURE Annual_Review IS   <declarations> BEGIN   <statements>; END Annual_Review; /

Using the OR REPLACE clause indicates that Oracle should replace an existing procedure of the same name if it exists. If the object exists and the OR REPLACE clause isn t used, an error will occur.

Procedures can be dropped using the DROP command inside SQL*Plus, as follows :

 DROP PROCEDURE Annual_Review;

Procedures can also be created and dropped using Oracle s Procedure Builder or one of several available third-party editors. These products have become quite sophisticated.

It s very rare to compile a procedure (or other stored object) successfully on the first time through; very often you will receive compilation errors. Resolving compilation errors is discussed in Chapter 8.

Recompiling Procedures

While procedures (and other objects) can be recompiled by simply reissuing the CREATE OR REPLACE command for the object, there s another method of recompiling stored objects without having a copy of the source code in a standalone file.

A stored PL/SQL object can be recompiled by using an ALTER command in SQL*Plus, like the commands shown here:

 ALTER PROCEDURE Annual_Review COMPILE; ALTER FUNCTION Raise_Salary COMPILE; ALTER PACKAGE End_Of_Year COMPILE; ALTER PACKAGE BODY End_Of_Year COMPILE;

In order to recompile objects using this method, you must be running under an account with the ALTER ANY OBJECT privilege. This method of recompiling stored PL/SQL objects is useful when objects become marked as invalid due to changes to their dependent objects. Dependencies are discussed in detail a bit later in this chapter.

Automatically Recompiling Invalid Stored Objects
When an object that your procedure references is altered in some manner (a column in a table is modified, a new column is added, or a stored PL/SQL object called by your procedure is recompiled), your procedure will be marked as invalid because Oracle is uncertain of its status. The next time your procedure is called, Oracle will generate an error message and remove the p-code for the invalid procedure. When your procedure is called again, Oracle will automatically attempt to recompile the p-code.
A package spec isn t marked as invalid when a package body is altered. However, altering the package spec does invalidate the package body.

There isn t any way that I ve found to force Oracle to automatically recompile the p-code for an invalid stored procedure or function without first generating an error. This minor annoyance is a relatively common complaint among PL/SQL developers.