sarangzigi.com

DECLARE

CURSOR c_list IS

SELECT instructor_id,last_name,first_name,zip,street_address,phone
FROM instructor WHERE (last_name,first_name,nvl(zip,'x')) in
(SELECT last_name,first_name,nvl(zip,'x') FROM instructor
GROUP BY last_name,first_name,nvl(zip,'x')
HAVING count(*)>1)
ORDER by 2,3,4,modified_date;

v_pre_instructor_id number;
v_pre_last_name varchar2(30);
v_pre_first_name varchar2(30);
v_pre_street_address instructor.street_address%type;
v_pre_zip instructor.zip%type;
v_pre_phone instructor.phone%type;
v_new_phone instructor.phone%type;
v_new_address instructor.street_address%type;


BEGIN

FOR rec IN c_list LOOP
EXIT WHEN c_list%NOTFOUND;

IF rec.last_name = v_pre_last_name
AND rec.first_name = v_pre_first_name
AND nvl(rec.zip,'x') = nvl(v_pre_zip,'x') THEN

IF rec.street_address IS NOT NULL THEN
v_new_address :=rec.street_address;
ELSE
v_new_address :=v_pre_street_address;
END IF;

IF rec.phone IS NOT NULL THEN
v_new_phone := rec.phone;
ELSE
v_new_phone :=v_pre_phone;
END IF;

-- dbms_output.put_line(rec.instructor_id||'-'||v_new_address||','||v_new_phone);
UPDATE instructor SET street_address = v_new_address, phone = v_new_phone
WHERE instructor_id = rec.instructor_id;

UPDATE section SET instructor_id = rec.instructor_id
WHERE instructor_id = v_pre_instructor_id;

DELETE FROM instructor where instructor_id = v_pre_instructor_id;

END IF;

v_pre_instructor_id := rec.instructor_id;
v_pre_last_name :=rec.last_name;
v_pre_first_name :=rec.first_name;
v_pre_zip := rec.zip;
v_pre_street_address := rec.street_address;
v_pre_phone := rec.phone;

END LOOP;

END;
/