超难存储过程，谁能为我优化，立即给分，谢谢！

[apboy]

create or replace procedure check_dis_cooperation
is
userida varchar2(10);
deptida varchar2(10);
headshipa char(1);
tableida char(2);
cursor user_list is select userid,deptid,headship from check_user WHERE length(deptid)=6 AND deptid!='000101' AND substr(deptid,0,4)='0001';
cursor table_list is select tableid from check_table WHERE objecttype = '1';
deptid0 varchar2(10):='000102';
deptid1 varchar2(10):='000103';
deptid2 varchar2(10):='000104';
deptid3 varchar2(10):='000105';
deptid4 varchar2(10):='000106';
deptid6 varchar2(10):='000107';
deptid7 varchar2(10):='000108';
deptid8 varchar2(10):='000109';
deptid9 varchar2(10):='000110';
deptid10 varchar2(10):='000111';
deptid11 varchar2(10):='000112';
BEGIN
OPEN user_list;
OPEN table_list;

LOOP
FETCH user_list INTO userida,deptida,headshipa;
EXIT WHEN user_list%NOTFOUND;

IF deptida = deptid1 THEN
LOOP
FETCH table_list INTO tableida;
EXIT WHEN table_list%NOTFOUND;
INSERT INTO check_dis(CHECK_MAN,CHECKED_OBJECT,CHECK_TABLE,CHECK_FLAG,CHECK_SUM)
SELECT userid,deptida,tableida,'0',null FROM hres_user WHERE deptid != deptida AND length(deptid)=6 AND substr(deptid,0,4)='0001';
INSERT INTO check_dis_tmp(CHECK_MAN,CHECKED_OBJECT,CHECK_TABLE,CHECK_FLAG,CHECK_SUM)
SELECT userid,deptida,tableida,'0',null FROM hres_user WHERE deptid != deptida AND length(deptid)=6 AND substr(deptid,0,4)='0001';
END LOOP;
ELSIF deptida = deptid2 THEN
LOOP
FETCH table_list INTO tableida;
EXIT WHEN table_list%NOTFOUND;
INSERT INTO check_dis(CHECK_MAN,CHECKED_OBJECT,CHECK_TABLE,CHECK_FLAG,CHECK_SUM)
SELECT userid,deptida,tableida,'0',null FROM hres_user WHERE deptid != deptida AND length(deptid)=6 AND substr(deptid,0,4)='0001';
INSERT INTO check_dis_tmp(CHECK_MAN,CHECKED_OBJECT,CHECK_TABLE,CHECK_FLAG,CHECK_SUM)
SELECT userid,deptida,tableida,'0',null FROM hres_user WHERE deptid != deptida AND length(deptid)=6 AND substr(deptid,0,4)='0001';
END LOOP;
ELSIF deptida = deptid3 THEN
LOOP
FETCH table_list INTO tableida;
EXIT WHEN table_list%NOTFOUND;
INSERT INTO check_dis(CHECK_MAN,CHECKED_OBJECT,CHECK_TABLE,CHECK_FLAG,CHECK_SUM)
SELECT userid,deptida,tableida,'0',null FROM hres_user WHERE deptid != deptida AND length(deptid)=6 AND substr(deptid,0,4)='0001';
INSERT INTO check_dis_tmp(CHECK_MAN,CHECKED_OBJECT,CHECK_TABLE,CHECK_FLAG,CHECK_SUM)
SELECT userid,deptida,tableida,'0',null FROM hres_user WHERE deptid != deptida AND length(deptid)=6 AND substr(deptid,0,4)='0001';
END LOOP;
ELSIF deptida = deptid4 THEN
LOOP
FETCH table_list INTO tableida;
EXIT WHEN table_list%NOTFOUND;
INSERT INTO check_dis(CHECK_MAN,CHECKED_OBJECT,CHECK_TABLE,CHECK_FLAG,CHECK_SUM)
SELECT userid,deptida,tableida,'0',null FROM hres_user WHERE deptid != deptida AND length(deptid)=6 AND substr(deptid,0,4)='0001';
INSERT INTO check_dis_tmp(CHECK_MAN,CHECKED_OBJECT,CHECK_TABLE,CHECK_FLAG,CHECK_SUM)
SELECT userid,deptida,tableida,'0',null FROM hres_user WHERE deptid != deptida AND length(deptid)=6 AND substr(deptid,0,4)='0001';
END LOOP;
ELSIF deptida = deptid5 THEN
LOOP
FETCH table_list INTO tableida;
EXIT WHEN table_list%NOTFOUND;
INSERT INTO check_dis(CHECK_MAN,CHECKED_OBJECT,CHECK_TABLE,CHECK_FLAG,CHECK_SUM)
SELECT userid,deptida,tableida,'0',null FROM hres_user WHERE deptid != deptida AND length(deptid)=6 AND substr(deptid,0,4)='0001';
INSERT INTO check_dis_tmp(CHECK_MAN,CHECKED_OBJECT,CHECK_TABLE,CHECK_FLAG,CHECK_SUM)
SELECT userid,deptida,tableida,'0',null FROM hres_user WHERE deptid != deptida AND length(deptid)=6 AND substr(deptid,0,4)='0001';
END LOOP;
ELSIF deptida = deptid6 THEN
LOOP
FETCH table_list INTO tableida;
EXIT WHEN table_list%NOTFOUND;
INSERT INTO check_dis(CHECK_MAN,CHECKED_OBJECT,CHECK_TABLE,CHECK_FLAG,CHECK_SUM)
SELECT userid,deptida,tableida,'0',null FROM hres_user WHERE deptid != deptida AND length(deptid)=6 AND substr(deptid,0,4)='0001';
INSERT INTO check_dis_tmp(CHECK_MAN,CHECKED_OBJECT,CHECK_TABLE,CHECK_FLAG,CHECK_SUM)
SELECT userid,deptida,tableida,'0',null FROM hres_user WHERE deptid != deptida AND length(deptid)=6 AND substr(deptid,0,4)='0001';
END LOOP;
ELSIF deptida = deptid7 THEN
LOOP
FETCH table_list INTO tableida;
EXIT WHEN table_list%NOTFOUND;
INSERT INTO check_dis(CHECK_MAN,CHECKED_OBJECT,CHECK_TABLE,CHECK_FLAG,CHECK_SUM)
SELECT userid,deptida,tableida,'0',null FROM hres_user WHERE deptid != deptida AND length(deptid)=6 AND substr(deptid,0,4)='0001';
INSERT INTO check_dis_tmp(CHECK_MAN,CHECKED_OBJECT,CHECK_TABLE,CHECK_FLAG,CHECK_SUM)
SELECT userid,deptida,tableida,'0',null FROM hres_user WHERE deptid != deptida AND length(deptid)=6 AND substr(deptid,0,4)='0001';
END LOOP;
ELSIF deptida = deptid8 THEN
LOOP
FETCH table_list INTO tableida;
EXIT WHEN table_list%NOTFOUND;
INSERT INTO check_dis(CHECK_MAN,CHECKED_OBJECT,CHECK_TABLE,CHECK_FLAG,CHECK_SUM)
SELECT userid,deptida,tableida,'0',null FROM hres_user WHERE deptid != deptida AND length(deptid)=6 AND substr(deptid,0,4)='0001';
INSERT INTO check_dis_tmp(CHECK_MAN,CHECKED_OBJECT,CHECK_TABLE,CHECK_FLAG,CHECK_SUM)
SELECT userid,deptida,tableida,'0',null FROM hres_user WHERE deptid != deptida AND length(deptid)=6 AND substr(deptid,0,4)='0001';
END LOOP;
ELSIF deptida = deptid9 THEN
LOOP
FETCH table_list INTO tableida;
EXIT WHEN table_list%NOTFOUND;
INSERT INTO check_dis(CHECK_MAN,CHECKED_OBJECT,CHECK_TABLE,CHECK_FLAG,CHECK_SUM)
SELECT userid,deptida,tableida,'0',null FROM hres_user WHERE deptid != deptida AND length(deptid)=6 AND substr(deptid,0,4)='0001';
INSERT INTO check_dis_tmp(CHECK_MAN,CHECKED_OBJECT,CHECK_TABLE,CHECK_FLAG,CHECK_SUM)
SELECT userid,deptida,tableida,'0',null FROM hres_user WHERE deptid != deptida AND length(deptid)=6 AND substr(deptid,0,4)='0001';
END LOOP;
ELSIF deptida = deptid10 THEN
LOOP
FETCH table_list INTO tableida;
EXIT WHEN table_list%NOTFOUND;
INSERT INTO check_dis(CHECK_MAN,CHECKED_OBJECT,CHECK_TABLE,CHECK_FLAG,CHECK_SUM)
SELECT userid,deptida,tableida,'0',null FROM hres_user WHERE deptid != deptida AND length(deptid)=6 AND substr(deptid,0,4)='0001';
INSERT INTO check_dis_tmp(CHECK_MAN,CHECKED_OBJECT,CHECK_TABLE,CHECK_FLAG,CHECK_SUM)
SELECT userid,deptida,tableida,'0',null FROM hres_user WHERE deptid != deptida AND length(deptid)=6 AND substr(deptid,0,4)='0001';
END LOOP;
ELSIF deptida = deptid11 THEN
LOOP
FETCH table_list INTO tableida;
EXIT WHEN table_list%NOTFOUND;
INSERT INTO check_dis(CHECK_MAN,CHECKED_OBJECT,CHECK_TABLE,CHECK_FLAG,CHECK_SUM)
SELECT userid,deptida,tableida,'0',null FROM hres_user WHERE deptid != deptida AND length(deptid)=6 AND substr(deptid,0,4)='0001';
INSERT INTO check_dis_tmp(CHECK_MAN,CHECKED_OBJECT,CHECK_TABLE,CHECK_FLAG,CHECK_SUM)
SELECT userid,deptida,tableida,'0',null FROM hres_user WHERE deptid != deptida AND length(deptid)=6 AND substr(deptid,0,4)='0001';
END LOOP;
END IF;

END LOOP;
COMMIT;
CLOSE table_list;
CLOSE user_list;

END;
/
上面存储过程如果多了判断，编译不通过，有哪位兄弟能帮我优化，或者写成程序块或者两个存储过程，一个调用一个？谢谢

[shuipipi]

WK，这么一大堆，建议泥最好先简单说明一下泥的实现目的，不然怎么让人看下去？

[scorcer]

create or replace procedure check_dis_cooperation(dept in varchar2)
is
userida varchar2(10);
deptida varchar2(10);
headshipa char(1);
tableida char(2);
cursor user_list is select userid,deptid,headship from check_user WHERE length(deptid)=6 AND deptid!='000101' AND substr(deptid,0,4)='0001';
cursor table_list is select tableid from check_table WHERE objecttype = '1';

BEGIN
OPEN user_list;
OPEN table_list;

LOOP
FETCH user_list INTO userida,deptida,headshipa;
EXIT WHEN user_list%NOTFOUND;

IF deptida = dept THEN
LOOP
FETCH table_list INTO tableida;
EXIT WHEN table_list%NOTFOUND;
INSERT INTO check_dis(CHECK_MAN,CHECKED_OBJECT,CHECK_TABLE,CHECK_FLAG,CHECK_SUM)
SELECT userid,deptida,tableida,'0',null FROM hres_user WHERE deptid != deptida AND length(deptid)=6 AND substr(deptid,0,4)='0001';
INSERT INTO check_dis_tmp(CHECK_MAN,CHECKED_OBJECT,CHECK_TABLE,CHECK_FLAG,CHECK_SUM)
SELECT userid,deptida,tableida,'0',null FROM hres_user WHERE deptid != deptida AND length(deptid)=6 AND substr(deptid,0,4)='0001';
END LOOP;
END IF;

END LOOP;
COMMIT;
CLOSE table_list;
CLOSE user_list;

end check_dis_cooperation;


create or replace procedure test as
v_dept varchar2(20);
v_num number;
v_st varchar2(20);
begin
v_num:=1;
v_st:='000101';
loop
exit when v_num>11;
v_st:=lpad(v_st + 1,6,0);
check_dis_cooperation(v_st);
v_num:=v_num + 1;
end loop;
end;