Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: PL/SQL : SELECT INTO
"Rudy Susanto" <rsusanto_at_atlas-sp.com> wrote in message
news:1a928d0b.0204250143.cb847b0_at_posting.google.com...
> Hi all,
>
> I have two similar programs but i don't know which is better. Actually
> i try to avoid the error when i use SELECT INTO with return no row.
> Which the method should i use? Is there any solution instead of two of
> my programs?
>
> Help me, please.
>
> Thank in advance,
> Rudy Susanto
>
>
> --1st
> FUNCTION get_name(vEmpno NUMBER)
> RETURN VARCHAR2
> AS
> vName scott.emp.ename%TYPE;
> BEGIN
> BEGIN
> SELECT ename INTO vName FROM emp WHERE empno=vEmpno;
> EXCEPTION
> WHEN NO_DATA_FOUND THEN
> vName := 'UNKNOWN';
> END;
> RETURN vName;
> END;
>
> --2nd
> FUNCTION get_name(vEmpno NUMBER)
> RETURN VARCHAR2
> AS
> vName scott.emp.ename%TYPE;
> vRec NUMBER;
> BEGIN
> SELECT COUNT(empno) INTO vRec
> FROM emp
> WHERE empno = vEmpno;
> IF vRec = 1 THEN
> SELECT ename INTO vName FROM emp WHERE empno=vEmpno;
> ELSE
> vName := 'UNKNOWN';
> END IF;
> RETURN vName;
> END;
You should, IMO, *always* use exceptions and never explicit code.
The reason for this is quite simple: whether you like it or not your select
into checks for these exceptions (no data found and single row query returns
more than one row) anyway.
So your second attempt is much more inefficient, and I would definitely
reject this code, when I have to judge it for a production system.
Regards
-- Sybrand Bakker Senior Oracle DBA to reply remove '-verwijderdit' from my e-mail addressReceived on Thu Apr 25 2002 - 15:06:40 CDT