Other Oracle database topics

Author

PLSQL Newbie Qn

simon

2005-06-10, 11:23 am

I am new to PLSQL. Is this a good programming practice to test if there is
0, 1 or > 1 rows returned from a SELECT INTO:

DECLARE
v_number number;
BEGIN
BEGIN
SELECT id INTO v_number FROM table where col=something;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_number:=0;
WHEN TOO_MANY_ROWS THEN
v_number:=99; /*special value for >1 rows*/
END;
IF v_number =0 THEN
..../*no row*/
ELSIF v_number=99 THEN
.../*>1 rows*/
ELSE
....../* 1 row*/
END IF;

END;

I have the feeling that exception should really means something abnormal -
rather than a way test to test normal conditions. But, it seems that SELECT
INTO has to generate exception if rows count is not 1.

Gerard H. Pille

2005-06-10, 11:23 am

simon wrote:
> I am new to PLSQL. Is this a good programming practice to test if there is
> 0, 1 or > 1 rows returned from a SELECT INTO:
>
> DECLARE
> v_number number;
> BEGIN
> BEGIN
> SELECT id INTO v_number FROM table where col=something;
> EXCEPTION
> WHEN NO_DATA_FOUND THEN
> v_number:=0;
> WHEN TOO_MANY_ROWS THEN
> v_number:=99; /*special value for >1 rows*/
> END;
> IF v_number =0 THEN
> ..../*no row*/
> ELSIF v_number=99 THEN
> .../*>1 rows*/
> ELSE
> ...../* 1 row*/
> END IF;
>
> END;
>
> I have the feeling that exception should really means something abnormal -
> rather than a way test to test normal conditions. But, it seems that SELECT
> INTO has to generate exception if rows count is not 1.
>
>
>
Not if id can be 0 or 99, else it is OK. It is abnormal to select nothing or
multiple values in to a single, simple variable.

simon

2005-06-10, 1:23 pm

"Gerard H. Pille" <ghp@skynet.be> ¦b¶l¥ó
> Not if id can be 0 or 99, else it is OK. It is abnormal to select nothing
or
> multiple values in to a single, simple variable.
Ok, assuming id cannot be 0 or 99, or I can just use another variable as the
flag.

What i am trying clarify is that if this way of using exception for testing
row count returned by SELECT INTO a good practice? Because I am thinking
exception should be used to handle abnormal cases, not an IF test of
expected possibilities. However, given the fact that SELECT INTO will raise
exception, it seems this technique have to be used.

Gerard H. Pille

2005-06-10, 8:23 pm

simon wrote:
> "Gerard H. Pille" <ghp@skynet.be> ¦b¶l¥ó
>
>
> or
>
>
> Ok, assuming id cannot be 0 or 99, or I can just use another variable as the
> flag.
>
> What i am trying clarify is that if this way of using exception for testing
> row count returned by SELECT INTO a good practice? Because I am thinking
> exception should be used to handle abnormal cases, not an IF test of
> expected possibilities. However, given the fact that SELECT INTO will raise
> exception, it seems this technique have to be used.
>
>
I wouldn't do it that way, but I don't think your way is worse.

declare
v_number number;
cursor c_test (p_param varchar2) is
select id from table where varchar_col = p_param;
begin
v_number := 0;
for r_test in c_test( something_else) loop
v_number := v_number + 1;
if v_number > 1 then
v_number := 99;
exit;
end if;
end loop;
end;
/

Lex

2005-06-11, 3:23 am

Yes it's good practice. It's fast (low cost) and makes your code easy
to understand. It might be a good idea not to use the flag though,
since flags generally messes up the code.

Lx

William Robertson

2005-06-11, 8:23 pm

Lex wrote:
> Yes it's good practice. It's fast (low cost) and makes your code easy
> to understand. It might be a good idea not to use the flag though,
> since flags generally messes up the code.
>
> Lx

Agree exception handler is fine but flag is not. Why use the exception
handler to set some variable's value, then test that variable all over
again in an IF clause? I would just do whatever needed doing within the
exception handler itself.

Sponsored Links