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

This is part 8 of our introductory Oracle PL/SQL tutorial designed to show you how to use PL/SQL to improve the performance, scalability and availability of your Oracle systems as well as show you how to develop your Oracle systems better, faster and smarter.

 

 

 

 

 

 

 

stand-alone procedures/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 stored outside a procedure 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 such as validation etc., 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 Forms 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), by 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), by calling them from a program written in another language such as Visual Basic or Java or C/C++ (which is outside the scope of this tutorial) or by calling them from a trigger or PL/SQL procedure/function or SQL query in an Oracle Form or Report.

Let's expand a little more on calling a PL/SQL function from an SQL statement in Oracle.. 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 emp WHERE dept_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 emp WHERE dept_id = 1

If this function was 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 emp WHERE dept_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.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 books on 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

PL/SQL tutorial Part 7                                                            Oracle PL/SQL tutorial Part 9

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

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.

Questions ? Problems ? Need help ? Contact us with your questions and we'll answer them in a future newsletter.

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: