 |
|
A PL/SQL Wish List
Oracle Tips by
Burleson
|
PL/SQL is not a perfect programming language (someone from Oracle
told me in 1992 that it wasn’t really a programming language at all).
If you have any experience with other programming languages, you’ll
find that some really useful things are missing from PL/SQL. While
these aren’t serious deficiencies, their incorporation would make
PL/SQL a bit less tedious to use at times.
The continue Statement
The continue statement in C is a marvelous tool, allowing
you to immediately skip to the next iteration of a loop without
executing any steps that occur inside the loop after the continue
statement. Unfortunately, PL/SQL has only a GOTO statement to
allow you to accomplish this simple task. The GOTO statement is
illustrated by this example:
LOOP
<<START_OF_LOOP>>
...PL/SQL statements..
GOTO <<START_OF_LOOP>>
END LOOP;
You can also mirror this functionality by putting the contents of
your loop inside a stored procedure and using the RETURN
statement to exit out of the stored procedure if you determine that
your data can’t or shouldn’t be processed any further. This example
calls a procedure that holds the internal logic of the loop.
LOOP
MyLoopContents (parameter1 => value);
END LOOP;
Listing 2.21 illustrates a loop that simulates the functionality of
a continue statement by using a stored procedure.
Listing 2.21 Using a stored procedure to simulate a C
continue statement.
PROCEDURE MyLoopContents (parameter1 <datatype>)
IS
<variable declarations>
BEGIN
...PL/SQL statements...
IF some_condition THEN
RETURN;
END IF;
END;
The case/switch Statement
While not an essential part of any language, the case
statement is more elegant than:
IF (x < 100) THEN
<statements>
ELSIF (X <200) THEN
<statements>
ELSIF (x < 300) THEN
<statements>
ELSE
<statements>
END IF;
Unfortunately, PL/SQL allows only the clumsy IF-THEN-ELSE
method of testing multiple conditions.
Incrementing Variables
The ability to increment or decrement a variable in C using the
++, --, +=, and -= operators is something
that I’ve often wished for while coding a procedure or function.
Instead, you’ll have to settle for the less elegant method shown here:
nSum := nSum + 1;
While functionally equivalent, the previous example simply seems
less graceful than a similar C statement:
nSum++;
You can create a stored function that accepts an integer
value and returns the value incremented by one, although it hardly
seems worth the effort.
This is an
excerpt from the book "High Performance Oracle Database
Automation" by Jonathan Ingram and Donald K. Burleson, Series
Editor. |
