PL/SQL : SELECT INTO

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 - 04:43:09 CDT