Return in a PL/SQL Procedure – Just one more thing Mum never told me

Functions return values. Procedures pass out parameters. That’s the way it is, that’s the way it’s always been. Or that’s what I thought up until recently.
Turns out that procedures can return too. I don’t know why I hadn’t realised this before. Maybe I’m just too set in my ways, or maybe it’s not just me.
Yes, I hear you cry, but RETURN in a procedure won’t pass back a value, so why would it be useful ? Time for another trivial example…
We’ve got a procedure that always gets called as part of some processing or other, but only performs an update under certain conditions…

 
CREATE TABLE trivial ( letter VARCHAR2(1)) 
/ 
CREATE OR REPLACE PROCEDURE ins_trivial( pa_input IN VARCHAR2) AS 
   BEGIN 
      IF ASCII( UPPER( pa_input)) BETWEEN 65 AND 90 THEN 
         INSERT INTO trivial( letter) VALUES( pa_input); 
      END IF; 
   END; 
/ 

So far, so what.
OK, imagine that the procedure does the test and then does a whole load of other processing before finally finishing several hundred lines later. The END IF is way down at the bottom of the program. You’ve got to read all the way down to find out what happens if the input isn’t a character ( i.e. absolutely nothing). Wouldn’t it be much more readable if you knew that at the start ?

Well, we could reverse the logic of the test and then raise an exception :

 
CREATE OR REPLACE PROCEDURE ins_trivial( pa_input IN VARCHAR2) 
AS 
   e_no_error EXCEPTION; 
BEGIN 
   IF ASCII( UPPER( pa_input)) NOT BETWEEN 65 AND 90 THEN 
      RAISE e_no_error; 
   END IF; 
   INSERT INTO trivial( letter) VALUES( pa_input); 
EXCEPTION WHEN e_no_error THEN 
   NULL; 
END; 
/ 

That’s OK, but it’s a bit misleading – we’re raising an exception but it’s not because of an error.

How about

 
CREATE OR REPLACE PROCEDURE ins_trivial( pa_input IN VARCHAR2) 
AS 
BEGIN 
   IF ASCII( UPPER( pa_input)) NOT BETWEEN 65 AND 90 THEN 
      GOTO no_action; 
   END IF; 
   INSERT INTO trivial( letter) VALUES( pa_input);
 <<no_action>> 
   NULL; 
END; 
/ 

At this point I’m reminded of the scene in Blazing Saddles where Hedley Lamarr is discussing how to chase the townsfolk out of Rock Ridge. When his henchmen suggest that they “Kill the first male born child of every family”, his assessment is “Hmmm, too Jewish”. I don’t know why, but this seems appropriate here. Using GOTO doesn’t really give us much more than the EXCEPTION handler.

Incidentally, anyone reading who subscribes to the widely held belief that using GOTO and writing spaghetti code are synonymous, you can do something just as stupid with exceptions if you put your mind to it. If you’re that interested, here’s an example of GOTO used in a more meaningful context…and there’s not a shred of pasta anywhere in sight !

OK back to the matter at hand – RETURN comes to the rescue

 
CREATE OR REPLACE PROCEDURE ins_trivial( pa_input IN VARCHAR2) 
AS 
BEGIN 
   IF ASCII( UPPER( pa_input)) NOT BETWEEN 65 AND 90 THEN 
      RETURN; 
   END IF; 
   INSERT INTO trivial( letter) VALUES( pa_input); 
END; 
/ 

In this context, it simply causes the procedure to exit without error – exactly what we were after. The logic of the test is compact and readable. Job done.

As I said before, I didn’t realise you could do this. Judging by much of the code I’ve seen over the years, I didn’t think many other PL/SQL programmers did either. Feel free to point and laugh if you’re one of the ones that did.

About these ads

2 thoughts on “Return in a PL/SQL Procedure – Just one more thing Mum never told me

  1. Or you could simply test for IF NOT and have the smaller block at the top and leave the “real deal” for the ELSE:

    CREATE OR REPLACE PROCEDURE ins_trivial( pa_input IN VARCHAR2) AS
    BEGIN

    IF NOT
    (ASCII( UPPER( pa_input)) NOT BETWEEN 65 AND 90) THEN
    /* the smaller exceptional situation block*/
    RETURN;
    ELSE
    /* the real deal juicy code*/
    INSERT INTO trivial( letter) VALUES( pa_input);
    END IF;

    END;

    But I did get your point. No problem with GOTOs regarding there’s only one tag at the end of the function, like we’d have with an expection approach.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s