Re: Request PL/SQL Advice

nigel_puntridge wrote:

> 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;
> /

I have to confess that it seems to me you have chosen about as difficult and convoluted a way to accomplish your goas as I can imagine. I'd write out a solution but from your code and explanation I still not really sure what you are trying to do.

But here's something that may help you part way

SELECT COUNT(*)
INTO x
FROM emp
WHERE emp_id = parameter

IF x = 0 THEN

   RETURN 0; -- indicating nothing found ELSE
   do whatever else it is you are doing
   RETURN other_stuff
END IF; But it seems to me that a procedure with multiple out parameters would make more sense than function in which you concatenate stuff together and, I presume, have to then break it up again later.

Daniel Morgan Received on Thu Aug 01 2002 - 16:13:50 CDT