This code was anonymised by the sender and some of the original logic may be a bit harder to make sense of as a result. Instead of EMP records, you should probably imagine it checking billion-dollar financial deals or nuclear reactor core temperature readings. Anyway, I think I've figured out what it's supposed to do.
You pass in an EMP record containing first name, last name, email address and so on, and it calls OTHER_PROC(email_address, the_emp_record_as_passed_in) - but only if the email address is not null, and it is unique for employees with that name. For some reason. But how to check? Simple:
- Use a cursor to load all the rows for that name into an array. (Apparently there were 130+ columns in the original table.)
- Check array.COUNT to see how many rows there are.
- If there is only one row, use it, taking care to use an NVL expression because we definitely don't want a NULL email address.
- If there is more than one row, open the same cursor again, and this time loop through it comparing each row's email address with the previous one. If it's the same, set lv_email_same = 1, otherwise set it to 0. That way, at the end of the loop we'll know whether they were all the same or not, right?
PROCEDURE unleash_havoc (p_emp_rec emp%rowtype)
IS
lv_email_same NUMBER(1) :=0; -- 0: no, 1: yes
lv_email_null NUMBER(1) :=0; -- 0: no, 1: yes
lv_email emp.email%TYPE := NULL;
ln_row NUMBER;
TYPE emp_tab IS TABLE OF emp%ROWTYPE;
lt_emp_data emp_tab;
CURSOR c_emp (p_last_name VARCHAR2, p_first_name VARCHAR2) IS
SELECT *
FROM emp e
WHERE e.last_name = p_last_name
AND e.first_name = p_first_name;
BEGIN
OPEN c_emp (p_emp_rec.last_name, p_emp_rec.first_name);
FETCH c_emp BULK COLLECT INTO lt_emp_data;
CLOSE c_emp;
IF lt_emp_data.COUNT = 1 THEN
ln_row := lt_emp_data.FIRST;
lv_email := NVL(lt_emp_data(ln_row).email,NULL);
other_proc(lv_email,p_emp_rec);
ELSIF lt_emp_data.COUNT > 1 THEN
FOR r IN c_emp (p_emp_rec.last_name, p_emp_rec.first_name) LOOP
IF NVL(r.email,'X') = NVL(lv_email,'X') THEN
lv_email := r.email;
lv_email_same := 1;
ELSE
lv_email := r.email;
lv_email_same := 0;
END IF;
IF r.email IS NULL THEN
lv_email_null := 1;
ELSE
lv_email_null := 0;
END IF;
END LOOP;
IF lv_email_same = 1
AND lv_email_null = 0
THEN
lv_email := NVL(lt_emp_data(ln_row).email,NULL);
other_proc(lv_email,p_emp_rec);
ELSE
...
END IF;
END IF;
END;
