Oracle PL/SQLTutorial Part 7 - Using Stored Procedures

This is the 7th in our series of introductory Oracle PL/SQL tutorials. We've already looked at data types, language elements, pros and cons of using PL/SQL. cursors and cursor FOR LOOPs. (See here for previous articles).  This tutorial explores the use of PL/SQL stored procedures in Oracle.

Oracle PL/SQL Stored Procedures

Let's consider the advantages and disadvantages of using stored procedures (and functions and packages) 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 database has to perform which may have a significant effect on performance • 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 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 whether they’re written in .NET, Java or PL/SQL. 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. The next part of this tutorial continues exploring the use of PL/SQL stored procedures in Oracle. PL/SQL tutorial Part 6                                                                       PL/SQL tutorial Part 8  --------------------------------------- Looking to sky-rocket productivity, save time and reduce costs? Training is a highly cost-effective and proven method of boosting productivity. Smartsoft offers instructor- led training in Oracle and related technologies on or off site in cities across the UK as well as self-study online training. See our scheduled Oracle training courses, or let us know your requirements. Looking for more Oracle tips and tricks? For no frills, no fluff, just solid, reliable technical information, take a short cut now and subscribe to our newsletter. Jam-packed full of tips and tricks, it will help you make your Oracle systems faster and more reliable and save you hours searching for information. Subscribe today - there's no charge - and your first issue will soon be winging its way to your mailbox. Smartsoft Computing Ltd, Bristol, England Tel: 0845 003 1320 Need help with Oracle? Contact Us View our privacy policy This site uses woopra.com to gather statistical information about our visitors. This data is aggregated to show industry trends (such as browser share). However, this data shall be the average of many thousands of visits and is in no way linked to individuals. View woopra privacy policy.  Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. © Copyright Smartsoft Computing Ltd 2014. All rights reserved.