Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL Default Question (Resent)
abc (abc_at_abc.com.au) wrote:
: Hi All,
: I have a PL/SQL to do following calculation:
: Total = NUM1 + NUM2;
: SELECT a INTO NUM1 FROM T1;
: SELECT b INTO NUM2 FROM T2;
If you know you can never get more than a single row, then the trick to avoid exceptions is to use a group function, such as max. It returns the value you want (i.e. the single value, or a null). A comment pointing out this trick is warranted to avoid programmer confusion later, and you must know that the query really does return just one row, cause otherwise you can get unreported errors on multiple rows.
(The reason to avoid exceptions is simply to keep the code uncluttered when the situation is not really an exception from the point of view of the algorithm. If the situation is really an error then don't do this.)
You can add an nvl to automatically use a default value, so for you perhaps
or test the null'ness later with IF numX IS NULL .
Also the SQL implicit cursor will tell you the number of rows returned, so something like
SELECT max(a) INTO NUM1 FROM T1;
IF SQL%NOTFOUND THEN ...
works when you need it.
Received on Thu Feb 27 2003 - 16:00:32 CST