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

Home -> Community -> Usenet -> c.d.o.server -> Re: PL/SQL : SELECT INTO

Re: PL/SQL : SELECT INTO

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Thu, 25 Apr 2002 22:06:40 +0200
Message-ID: <ucgodfjb20kab@corp.supernews.com>

"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 address
Received on Thu Apr 25 2002 - 15:06:40 CDT

Original text of this message

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