|
 |
|
Oracle Database Tips by Donald Burleson |
Why Use Functions and
Procedures?
The benefits of functions and procedures are
numerous. Functions and procedures provide a consistent means
of accessing, altering, and deleting database information. They
allow enhanced security by giving the DBA the ability to grant
access to the procedures and functions instead of to the actual
tables or views. The procedure or functions can have elevated
privileges that are in effect while the procedure or function is
active but are disabled when it completes.
Functions and procedures enhance
productivity by allowing a given process to be coded once and then
referenced by all developers. Instead of each form requiring coded
triggers for data access, the stored procedures and functions can be
referenced instead. This drives consistency down to the database
level instead of requiring it from each developer.
Performance is enhanced by allowing multiple
users to access the same shared image. Since the procedures and
functions are loaded into the cached memory area, only one I/O is
required for each procedure or function to be available to all
users. In network situations, a function or procedure can be called
with a single network call; the function or procedure can then
trigger a multitude of database actions and then return, via another
single call, the results, thus greatly reducing network traffic.
Tip
If a
function or procedure affects only one table, then, in Oracle8 and
Oracle8i, perhaps it should be made into a method. Methods should be
used for any internal PL/SQL objects that affect only one table or
are used to obtain values from only one table. Using a method
instead of a procedure or function will implement the concept of
encapsulation in these cases.
See Code Depot
www.dba-oracle.com/oracle_scripts.htm |
and include the URL for the page.
