Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: PL/SQL : SELECT INTO
Hi
You can use this:
begin
SELECT ... INTO ... FROM ...
...
Exception when no_data_found then
null;
end;
Epicentre Team A
"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;
Received on Thu Apr 25 2002 - 08:32:01 CDT