Re: PL/SQL : Alternative way of doing if any

From: Billy <vslabs_at_onwe.co.za>
Date: 17 Aug 2005 22:40:29 -0700
Message-ID: <1124343629.431825.28530@f14g2000cwb.googlegroups.com>

baka wrote:

> i have one simple procedure to get some code if a string match exists.
> written procedure to achive this. but feel it is not an efficient one.
> can any one give altenative approch to this problem if any.

I've just raised this issue (again) on the PL/SQL Forum in Metalink. So I'm going to rant a bit again. Don't mind the spittle and foaming at the mouth, and don't take it personal as I'm making general statements about the quality of PL/SQL developers of today.

Do not code explicit cursors if they are not needed. Explicit cursors are often used because of the row-by-row processing mindset. Which is simply wrong. Explicit cursors require additional exception handling code. Use an implicit cursor - i.e. a plain vanilla SELECT in PL/SQL. Use explicit cursors, *with* bulk processing, when the requirements demand it.

Do no treat Oracle as file-with-records system in PL/SQL using things like explicit cursors, for loops and single row fetches.

Consider the following:
==
SQL> declare

 2     cursor c is select object_id from user_objects where 1=2;
 3     i integer;
 4   begin
 5     open c;
 6     fetch c into i;
 7     close c;

 8 end;
 9 /

PL/SQL procedure successfully completed. ==

4 lines of code are required to assign the value to the variable. There are *no* raised exceptions when there's no data found. Which means having to add even more code to test whether or not that cursor fetch did indeed returned data.

More code = more moving parts = [decreased readability + increased likelihood of errors + more maintenance + slower performance]

Now compare it with the following:
==
SQL> declare
 2 i integer;
 3 begin
 4 select object_id into i from user_objects where 1=2;  5 end;
 6 /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4
==

Single line of code. Easy to read. Easy to understand. And when there is no data, you get an exception. No need to write code to check if there was data. No mysterious runtime errors because the cursor does not return data as expected and never tested. You get an exception. Automatically. Exceptions Are Good (tm). Doing in-line testing and evalution for runtime results are idiotic.

There are no "alternative" approaches in programming. There is The Single Correct Approach. It goes something like this:

- know WHAT you write
- know WHY you write
- modularise
- drinks lots of coffee

If you cannot explain why you are using an explicit cursor, you do not know WHAT you are writing. Which raises serious question about if you know WHY you're writing it.

Modularisation.. that should be very obvious, but is kind of a lost art these days. Think modules. Think building blocks when writing code. Something like a RAISE_APPLICATION_ERROR should be coded once and once only. (yeah - that means write a darn exception handler module that raises all your application exceptions in a *single* controllable place)

The last one, drinking lots of coffee, should be ovious. To any real programmer at least.

--
Billy

Received on Thu Aug 18 2005 - 00:40:29 CDT