Oracle PL/SQL Tutorial #8 - Stored Procedures (ctd)

In our series of introductory Oracle PL/SQL tutorials so far we've already looked at data types, language elements, pros and cons. cursors, cursor FOR LOOPs and in the previous tutorial we looked at the advantages and disadvantages of  stored procedures. (See here for previous articles).  This tutorial continues with PL/SQL stored procedures.

Using Oracle PL/SQL Stored Procedures

We're going to dive a bit deeper into stored procedures and show how and where they can be used. Before we get too deep though, we need to consider the differences between using stand-alone procedures or functions and procedures/functions within packages. As a general rule you should put all your procedures in PL/SQL packages. Why? Well, if nothing else this enables you to group your procedures into one physical file, but used properly PL/SQL packages give you other benefits as well: • they can contain items such as cursors, variables and type declarations which can be shared amongst many procedures thereby reducing the amount of code that has to be maintained • variables declared outside a procedure/function in an Oracle PL/SQL package maintain their value from one invocation to another (as the long as the Oracle database remains running) which means they can be used to maintain data across several invocations of a procedure/function • 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 • each package can have an initialisation section which is run once the first time the package is loaded into memory (i.e. the first time any object in the package is accessed) Now let's look at how and where you can use Oracle PL/SQL stored procedures. In a nutshell they're ideal for encapsulating pieces of business logic where only the final result is required not the intermediate results. PL/SQL stored procedures are also good for breaking up complex SQL statements into more manageable chunks. However, they are not good for interacting with the end-user (use Oracle Application Express or some other client-side tool for that) neither are they really suited to lots of file i/o. So how do you use them ? The first step obviously is to define the problem and to design and code the solution (this might be a large step!). The next step is to invoke your Oracle PL/SQL stored procedures. This can be done by: • calling them from another stored procedure (in another package or even in the same package) • embedding a call to a function (i.e. it always returns a value) in a SQL statement (which in turn may be called from another PL/SQL procedure/function) • calling them from a program written in another language such as Java or C/C++ (which is outside the scope of this tutorial) • or calling them from a trigger or PL/SQL procedure/function or SQL query in an Oracle Form or Report or Apex module Let's expand a little more on calling function from an SQL statement. Note that we can't call a PL/SQL procedure directly from a SQL statement as we need to return a value (but we can call as many procedures as we like from the function). The syntax is exactly the same as for calling a standard SQL function. We are used to seeing statements such as SELECT AVG(sal) FROM employees WHERE DEPARTMENT_ID=1; In this case AVG is a built-in SQL function, suppose instead we wanted to call our own function MY_AVG then the statement becomes: SELECT my_avg(sal) FROM employees WHERE DEPARTMENT_ID=1; If this function were in a package called (for example) EMP_STATS we would need to prefix the function name with the PL/SQL package name like this SELECT emp_stats.my_avg(sal) FROM employees WHERE DEPARTMENT_ID=1; Invoking a PL/SQL procedure from another PL/SQL procedure or function is even easier - you just provide the name of the procedure (prefixed with the package name if necessary) and any parameters that it requires, as follows emp_stats.compute_avg_sal(dept_id => 1); The syntax is slightly different to invoke a PL/SQL function as we need to receive the returned value dept_1_avg_sal := emp_stats.compute_avg_sal(dept_id => 1); This tutorial has barely scratched the surface of the subject, if you would like more PL/SQL training just click on the link for details.  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. For more SQL and PL/SQL tutorials see the Oracle tips and tricks page. The next part of this tutorial introduces PL/SQL collections. PL/SQL tutorial Part 7                                                                       PL/SQL tutorial Part 9  --------------------------------------- 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.