Oracle Consulting and Training
For Better, Faster, Smarter,
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
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
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
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
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their
© Copyright Smartsoft Computing Ltd 2014. All rights reserved.