Oracle – for when it was like that when you got there

Main menu

Post navigation

CONTINUE statement in PL/SQL – or how GOTO can be a useful member of society

Coding Standards, Best Practices, generally accepted truth – these almost always have something to say on the subject of the evil “GOTO” statement.
Invariably, what’s said is don’t use it. Ever. On pain of pain.
It’s the absolute and unequivocal nature of these strictures that I find annoying.
It may be the case that, in the primordial times of Computer Science, before young Larry Ellison dropped out of College, grew a beard and started getting ideas about applying this Relational Theory stuff to a working database, GOTO was abused to create the dreaded “spaghetti code”.
Since that time, any tentative mention of this structure outside of a Basic Program has been met with revulsion.
However, it’s worth noting that, even such luminaries as Kernighan and Ritchie, whilst describing the goto statement in C as “infinitely abusable” do concede that there are times when gotos may find a place.
As things stand right now (at least, if you’re in one of those shops still waiting for 11g), one of those places can be found in PL/SQL.
I think it’s reasonable to compare PL/SQL ( based on Ada, which in turn is based on Pascal) with C due to they’re shared 3-GL ancestry.
Prior to 11g, PL/SQL is missing one of the features common to many 3-GLs – including C – the continue keyword, which allows the programmer the option to skip a single iteration of a loop without all that mucking about with IF-THEN-ELSE.
For example – say we want to loop through an array of numbers but we only want to process the positive numbers in that array and ignore any negative numbers.
In ‘C’, this is fairly straightforward ….

for( i = 0; i < n; i++)
{
if( a[i] <= 0)
continue;
... /* if we get here then the element is positive
so do stuff to it */
}

Contrast this with PL/SQL

FOR i IN a.FIRST..a.LAST LOOP
IF a(i) > 0 THEN
-- do stuff to the positive element
END IF;
-- not immediately obvious that we do anything with
-- the negative elements – you have to read through
-- all of the code to work this out.
END LOOP;

So, no continue statement, a bit less clarity. However, now GOTO is out on parole, we have a job for it …

FOR i IN a.FIRST..a.LAST LOOP
IF a(i) < 0 THEN
GOTO continue;
END IF;
--
-- If we get here then the element is positive
-- so do stuff
--