Oracle PLSQLTutorial -
An introduction to using PL/SQL with Oracle (Part 7)

This is part 7 of our introductory Oracle PL/SQL tutorial designed to show you how to make effective use of PL/SQL . This article will explore the use of PL/SQL stored procedures in Oracle.

Using Oracle PL/SQL Stored Procedures

 

 

 

 

 

 

 

Let's consider, the advantages and disadvantages of using stored procedures and which, by the way, apply to Java stored procedures as well as Oracle PL/SQL stored procedures.

Advantages of stored procedures in Oracle:

• the procedures are stored in the Oracle database and are, therefore, executed on the database server which is likely to me more powerful than the clients which in turn means that stored procedures should run faster;
• the code is stored in a compiled form which means that it is syntactically valid and does not need to be compiled at run-time, thereby saving resources;
• each user of the stored procedure/function will use exactly the same form of queries which means the queries are reused thereby reducing the parsing overhead and improving the scalability of applications;
• as the procedures/functions are stored in the Oracle database there is no need to transfer the code from the clients to the database server or to transfer intermediate results from the server to the clients. This results in much less network traffic and again improves scalability;
• when using Oracle PL/SQL packages, as soon as one object in the package is accessed, the whole package is loaded into memory which makes subsequent access to objects in the package much faster
• stored procedures/functions can be compiled into "native" machine code making them even faster (available with Oracle 9i and above)

Disadvantages:

• there is an overhead involved in embedding or calling PL/SQL procedures from SQL in Oracle due to the context switching that the Oracle database has to perform. This may be significant in terms of performance but usually this overhead is outweighed by performance advantages of using PL/SQL
• more memory may be required on the Oracle database server when using Oracle PL/SQL packages as the whole package is loaded into memory as soon as any object in the package is accessed
• native compilation can take twice as long as normal compilation

Now that we've looked at the pros and cons of using stored procedures in Oracle, what can we do with them ? The answer to that is almost anything. PL/SQL stored procedures are just named blocks of PL/SQL that are stored in the database. Therefore all of the language elements of PL/SQL and all of the PL/SQL data types are available to stored procedures just as they are to any other PL/SQL block.

In conclusion, stored procedures is one feature of Oracle that you should be using if you want to improve the performance and scalability of your applications. The disadvantages are easily outweighed by the advantages and the use of packages to combine related objects (procedures, functions and variables) into one physical unit enhances these advantages.

For reviews of book on Oracle PL/SQL and other aspects of Oracle as well as links to other resources see our Oracle resources page. You might also be interested in our beginner's SQL tutorial or our other Oracle tutorials.

Oracle PL/SQL tutorial Part 6                                                 Oracle PL/SQL tutorial Part 8

---------------------------------------

Looking for more Oracle tips and tricks ? Take a short cut now and subscribe to our ezine and you'll find plenty of them. Just fill in the form and the next issue will soon be winging its way to you.

Smartsoft Computing Ltd
Bristol, England
Tel: 0845 0031320

Contact Us

Click here to view our privacy policy .

This site uses hitslink.com to gather statistical information about our visitors. View hitslink privacy policy .

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. UNIX is a registered trademark of The Open Group in the United States and other countries.

© Copyright Smartsoft Computing Ltd 2001-2008. All rights reserved.

Search for: