Procedures and Functions

A procedure or function is a named PL/SQL block – they are normally stored in the database within package specifications (which is a wrapper for a group of named blocks) but they may be stored on the database individually. The advantage of this is that when a block is placed on the database it is parsed at the time it is stored. When it is subsequently executed Oracle already has the block compiled and it is therefore much faster. It is also a good way of grouping application functionality together and exposing only function calls (not the code itself). It is possible to invoke a stored procedure or function from most Oracle tools including SQL *Plus. It is also possible to attach a block to a database or Form trigger

Benefits of using procedures and functions include :-

  1. Improved data security and integrity.
    • Control indirect access to objects from non privileged users with security privileges.
    • Ensure that related actions are performed together, or not at all, by funnelling actions for related tables through a single path.
  2. Improved performance.
    • Avoid reparsing for multiple users by exploiting shared SQL.
    • Avoid PL/SQL parsing at run time by parsing at compile time.
    • Reduce the number of calls to the database and decrease network traffic by bundling commands.

 Improved maintenance.

    • Modify routines online without interfering with other users.
    • Modify one routine to affect multiple applications. Modify one routine to eliminate duplicate testing.
Advertisements

About Oracle Technology Blogs
Oracle Application Development

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: