Oracle Consulting and Training
For Better, Faster, Smarter,
Oracle PL/SQL Tutorial Part 4 - PL/SQL in the real world
This is part 4 of our Oracle PLSQL tutorial showing how you can use PL/SQL to simplify your Oracle
systems. So far we've covered the data types, the language elements and discussed some of its pros and
cons . Now let’s look at using PL/SQL in the real world.
One of the main reasons for using PL/SQL is to provide some structure to our SQL statements that are
retrieving or modifying data in the database. In one sense therefore, you could regard PL/SQL as a wrapper
for SQL which means we need to know how to use SQL within PL/SQL.
Fortunately for us, Oracle have made this easy and straightforward and provides tow methods for doing so.
We can either embed SQL statements directly into PL/SQL
blocks (as implicit cursors) or we can define explicit
cursors for SQL SELECT statements.
Let's look at an example to make things clear. Let's
suppose that we want to fetch the result from a query and
we know that this query can only ever return one row (this
last factor is important as we'll see in a minute). To do this
we simply embed the query within our PLSQL code.
1. PROCEDURE check_max_sal
(max_sal_limit_breached IN OUT
2. max_sal emp.salary%TYPE;
... <preceding logic> ...
4. SELECT MAX(salary) INTO max_sal FROM emp;
5. max_sal_limit_breached := max_sal > 2000000 ;
... <more logic> ...
6. END check_max_sal;
Now let's dissect this code to explain what's going on and to highlight the important bits. By the way, the
line numbers are not part of the PL/SQL block - they are just there to make the discussion easier.
Line 1 declares a procedure called check_max_sal which takes one parameter called
max_sal_limit_breached which is of type Boolean. This parameter can both be read from and
written to by the procedure (this is signified by specifying "IN OUT" between the parameter name and the
On line 2 we declare a local variable max_sal which we've anchored to the definition of salary in the
emp table by use of the construct emp.salary%TYPE. This has two advantages:
we don't need to go and find out whether salary is integer or floating point
if the definition of salary in the emp table changes from fixed to floating or even to varchar2, we
don't have to worry - all we need to do is recompile our procedure and Oracle handles the rest.
Line 4 is the next line of interest and this is a single-row query. We know that this query will return only
one row because there's no group by clause and we're using the MAX function to find the maximum
value of this column in the table. By using the MAX function we ensure that the query will always return
exactly one row, even if the table is empty (in which case it returns NULL).
The result of the query is stored in the local variable max_sal by use of the INTO clause, thereby
making it accessible to the rest of our PLSQL block Note that use of the INTO clause is compulsory with
implicit cursors that retrieve data from the database.
Line 5 sets the value of the output parameter max_sal_limit_breached by comparing the value of
max_sal returned from the query with a constant. If max_sal is higher than 20000 then
max_sal_limit_breached is set to TRUE. Note that Boolean variables in Oracle PL/SQL can have
3 values: TRUE, FALSE or NULL (undefined). This means that, in this case, if max_sal is null then
max_sal_limit_breached will also be set to null (because if you compare something known with
something unknown the result is unknown),
Line 6 closes this PL/SQL block, in this case the procedure check_max_sal. Labeling the end of the
procedure or function or package is not compulsory but is good practice as it makes your code easier to
read and therefore easier to maintain.
Now let's go back to the query. This particular query always returns one row (because we're using a group
function without a group by clause), and one column into a single variable, but what would happen if
we wanted to fetch several columns at once? Or the query returned more than one row?
The first situation is easy to resolve - we just need to declare one variable for each column returned from
the query and include them in the INTO clause.
However if the query returned more than one row, (unless we’re doing a bulk collect which we’ll
cover in another tutorial) this would cause an error and Oracle would raise an exception - in this case the
error would be ORA-01422 which would generate a TOO_MANY_ROWS exception. Unless we have an
exception handler (which we don’t in this case), execution of the code stops at that point and control is
returned to the invoking environment.
An implicit cursor is also at risk of generating a NO_DATA_FOUND exception which would be caused if
the SQL query returned no data (again, which can't happen in this case).
These errors and others can be trapped within the Oracle PL/SQL block by the use of an exception handler
which will be explored in detail in another tutorial, but for completeness, the exception handler must be the
last section before the end of the Oracle PL/SQL block. The general syntax is ...
BEGIN /* start of anonymous block or procedure/function */
< PL/SQL statements>
WHEN <exception> THEN <PL/SQL statements> ;
END ; /* of anonymous block or procedure/function */
We've only looked at querying the database in this PL/SQL tutorial because that's the most common type of
SQL statement. UPDATE/INSERT/DELETE statements follow the same rules as in SQL but can access
PL/SQL variables - they just need to be referenced in the relevant part of the SQL statement.
Oracle PLSQL tutorial Part 3 Oracle PLSQL tutorial Part 5
For more help with PL/SQL why not consider taking one of our formal training courses? We offer
instructor-led training at centres across the country. See the Oracle training page for details or contact us
for more information.
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