Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL Default Question (Resent)

Re: SQL Default Question (Resent)

From: Malcolm Dew-Jones <yf110_at_vtn1.victoria.tc.ca>
Date: 27 Feb 2003 14:00:32 -0800
Message-ID: <3e5e8a80@news.victoria.tc.ca>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US