Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Creating Procedures Using PL/SQL
Oracle Tips by Burleson
 

Stored Procedure

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.

    
  
This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter