Request PL/SQL Advice

From: nigel_puntridge <someone_at_microsoft.com>
Date: Thu, 01 Aug 2002 19:00:45 GMT
Message-ID: <xzf29.176653$%%2.7289091@news2.east.cox.net>

I have written the following procedure to generate a userid for an employee who is hired into an HR system. It first checks for the existence lastname_firstname, if it exists, then lastname_firstname_mi, then it uses a numeric index as a tie breaker.

The logic seems ok, and everything works, but I would like some critiques from the experts. I write PL/SQL about once a year. Is there a better way to do this?

Another problem I have is that if someone enters and invalid empid (e.g. select gen_user_id('bad emp id') from dual), I get an error saying that the function is returning a null value. Is this normal, and is there a way to make it more graceful?

CREATE OR REPLACE FUNCTION gen_user_id(p_empid IN emp.empid%TYPE)

   RETURN VARCHAR2
IS
BEGIN
    FOR x IN (SELECT

              n.last_name || '_' || n.first_name) AS LNFN,
              n.last_name || '_' || n.first_name || n.middle_initial) AS
LNFNMI,
              FROM emp n
              WHERE n.empid = p_empid)
    LOOP
       IF x.lnfn = NULL THEN
          RETURN NULL;
       END IF;

       FOR a IN (SELECT count(*) acount
                 FROM system_users a
                 WHERE a.user_id = x.lnfn)
       LOOP
          IF a.acount = 0 THEN
              RETURN x.lnfn;    -- No pre-existing LASTNAME || FIRSTNAME, so
return it
          END IF;
       END LOOP;

       FOR b IN (SELECT count(*)  bcount
                 FROM system_users b
                 WHERE b.user_id = x.lnfnmi)
       LOOP
          IF b.bcount = 0 THEN
             RETURN x.lnfnmi;    -- No pre-existing LASTNAME || FIRSTNAME ||
MI, so return it
          END IF;
       END LOOP;

       FOR c IN (SELECT to_char(count(*) + 1) ccount
                 FROM system_users c
                 WHERE c.user_id like  x.lnfnmi || '%')
       LOOP
          RETURN x.lnfnmi || c.ccount;
       END LOOP;

    END LOOP;
END;
/ Received on Thu Aug 01 2002 - 14:00:45 CDT