Oracle PLSQL Tutorial (Part 4)

This is the 4th of a series of Oracle PLSQL tutorials showing how you can use PL/SQL to simplify your Oracle systems.

Using Oracle PLSQL

So far in this tutorial we've covered the data types, the language elements and discussed some of the pros and cons of using PLSQL.

These topics haven't been covered in great detail as this Oracle PLSQL tutorial just covers some of the fundamental aspects of the language which brings us nicely to the next topic - using Oracle 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 manipulate data in the database, so in a sense PL/SQL could be regarded as a wrapper for SQL. Therefore we need to know how to use SQL within PL/SQL to manipulate data.

Fortunately for us, Oracle have made the integration of SQL and PL/SQL extremely easy and straightforward - we can either embed SQL statements directly into PL/SQL blocks (known as implicit cursors) or we can define queries as an explicit cursor.

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 BOOLEAN) IS

2. max_sal emp.salary%TYPE;

3. BEGIN

... <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 that piece of PL/SQL to explain what's going on and to highlight the important bits. By the way, the line numbers are not part of the PLSQL 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 parameter type).

Next, 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.

Anchoring the definition like this ensures that the datatype of this variable is matched to the datatype in the table definition whenever this piece of code is compiled.

The next line of interest (line 4) 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).

By using the INTO clause, the result of the query is stored in the max_sal variable declared previously 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 form 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.

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 optional but good practice..

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, 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.

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 (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>

EXCEPTION

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.

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

Why not take advantage of one of our classroom-based Oracle training courses to extend, enhance and expand your knowledge of PL/SQL? With our partners we are able to offer courses across the country and we also provide on-site and customised training. See the Oracle training page for details of our scheduled courses or contact us for more information about on-site or customised Oracle training.

If you're looking for a good book on PL/SQL to take you from beginner to expert, take a look at Oracle PL/SQL Programming by Steven Feuerstein and Bill Pribyl.

See Oracle PLSQL tutorial part 3 for a discussion of Oracle PL/SQL Language Elements

This series continues with PL/SQL tutorial Part 5 - Using explicit cursors in Oracle PL/SQL

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

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 ezine.

Send your questions direct to us 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.