Re: PL/SQL : SELECT INTO

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:<ucgodfjb20kab_at_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 and Epicentre Team A thank you for your helps.

Regards,
Rudy Susanto Received on Thu Apr 25 2002 - 20:46:41 CDT