|Previous||Table of Contents||Next|
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.
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 systems interface.
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;
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.
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.
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:
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.
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 isnt 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 Oracles Procedure Builder or one of several available third-party editors. These products have become quite sophisticated.
Its 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.
While procedures (and other objects) can be recompiled by simply reissuing the CREATE OR REPLACE command for the object, theres 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 isnt marked as invalid when a package body is altered. However, altering the package spec does invalidate the package body.
There isnt any way that Ive 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.
|Previous||Table of Contents||Next|