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