Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: PLSQL Newbie Qn
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.
Received on Fri Jun 10 2005 - 11:15:53 CDT