LearnItFirst User Forum

create or replace
PROCEDURE A (tbl_name varchar2 default ' ',
l_name varchar2 default ' ',
f_name varchar2 default ' ',
full_name varchar2 default ' ',
ssn varchar2 default ' ',
gender_col varchar2 default ' ',
employee_id varchar2 default ' ') IS


v_tbl varchar2(30) := tbl_name;
v_lname varchar2(30) := l_name;
v_fname varchar2(30) := f_name;
v_fullname varchar2(30) := full_name;
v_ssn varchar2(30) := ssn;
v_gender varchar2(30) := gender_col;
v_empid varchar2(30) := employee_id;
v_num number(10) := 1;
v_row integer;


proc_id varchar2(30) := 'Change process';

V_ERR_CD VARCHAR2(40);
V_ERR_NAME VARCHAR2(100);

BEGIN





if (v_tbl = ' ') then

dbms_output.put_line('Must put table name');
else

EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||v_tbl INTO v_row;


if (v_lname <> ' ') and (v_empid <> ' ') then

execute immediate '
update
(select a.'||v_lname||' a_lname, a.'||v_empid||' a_empid, b.last_name b_lname, b.employee_id b_emplid
from '||v_tbl||' a, CHANGED b
where b.emplid = a.'||v_empid||')
set a_lname= upper(b_lname) ';

end if;

---

if (v_fname <> ' ') and (v_empid <> ' ') and (v_gender <> ' ')then

execute immediate '
update
(select a.'||v_fname||' a_fname, a.'||v_empid||' a_empid, a.'||v_gender||' a_gender,
b.male_first_name b_mfname, b.female_first_name b_ffname, b.unisex_name b_uname, b.employee_id b_emplid
from '||v_tbl||' a, CHANGED b
where b.emplid = a.'||v_empid||')
set a_fname= decode(a_gender,''U'', upper(b_uname),(decode(a_gender,''F'', upper(b_ffname), upper(b_mfname))))';

end if;

---

if (v_fullname <> ' ') and (v_empid <> ' ') and (v_gender <> ' ')then

execute immediate '
update
(select a.'||v_fullname||' a_full, a.'||v_empid||' a_empid, a.'||v_gender||' a_gender,
b.male_first_name b_mfname, b.female_first_name b_ffname, b.unisex_name b_uname, b.last_name b_lname, b.employee_id b_emplid
from '||v_tbl||' a, CHANGED b
where b.emplid = a.'||v_empid||')
set a_full=
concat( upper(b_lname) ||'' , '',
(decode(a_gender,''U'', upper(b_uname),(decode(a_gender,''F'', upper(b_ffname), upper(b_mfname))))))';

end if;
------------------------------------------------------------------------------------------------------------------------------------- --


if (v_lname <> ' ') and (v_empid <> ' ') then
dbms_output.put_line(v_row||' '||v_lname||' from table '||v_tbl||' have been change');
end if;

if (v_fname <> ' ') and (v_empid <> ' ') and (v_gender <> ' ') then
dbms_output.put_line(v_row||' '||v_fname||' from table '||v_tbl||' have been change');
end if;

if (v_fullname <> ' ') and (v_empid <> ' ') and (v_gender <> ' ') then
dbms_output.put_line(v_row||' '||v_fullname||' from table '||v_tbl||' have been change');
end if;

if (v_ssn <> ' ') then
execute immediate '
update '||v_tbl||' set
'||v_ssn||'= translate('||v_ssn||',''0123456789'',''5678901234'')
where rownum <= '||v_row||'';

dbms_output.put_line(v_row||' '||v_ssn||' from table '||v_tbl||' have been change');
end if;

end if;

EXCEPTION

WHEN OTHERS THEN
dbms_output.put_line('Error Encountered ' || substr(sqlerrm,1,200));
V_ERR_CD := SQLCODE;
V_ERR_NAME :=SQLERRM;
IF V_ERR_CD = 'ORA-01401' THEN
dbms_output.put_line('Procedure Compiled with Errors');
END IF;


END A;