create or replace procedure Getyc is
v_id VARCHAR2(36);
v_date VARCHAR2(4);
begin
declare
begin
for i in (select
c.xqbm
from T_BAS_GCJBXX c
where c.id in (select a.id
from T_BAS_GCJBXX a
where a.scszbg = 1
minus
select b.gcid
from T_BAS_SZRBJCXX b
where TO_CHAR(b.bgrq, 'YYYY/MM/DD') =
TO_CHAR(SYSDATE, 'YYYY/MM/DD')) group by c.xqbm) LOOP
v_id := SYS_GUID();
insert into T_BGYCSBJL (ID, SSXQ, BGRQ, DQZT, SBSJ, DXFSZT, BGLX) VALUES(v_id,i.xqbm,SYSDATE,0,SYSDATE,0,0);
insert into T_RBYCSBMX(ID, SBJLID, DQZT, SBRQ, GCID) select SYS_GUID() ID,
v_id SBJLID,
0 DQZT,
SYSDATE SBRQ,
c.id GCID
from T_BAS_GCJBXX c where c.id in (select a.id
from T_BAS_GCJBXX a
where a.scszbg = 1
minus
select b.gcid
from T_BAS_SZRBJCXX b
where TO_CHAR(b.bgrq, 'YYYY/MM/DD') =
TO_CHAR(SYSDATE, 'YYYY/MM/DD')
) and c.xqbm=i.xqbm;
END LOOP;
end;
declare
begin
v_date := TO_CHAR(SYSDATE,'DD');
if(v_date='25') then
insert into T_BGYCSBJL(ID,SSXQ,BGRQ,DQZT,SBSJ,DXFSZT,BGLX)
select SYS_GUID() ID,
c.xqbm SSXQ,
SYSDATE BGRQ,
0 DQZT,
SYSDATE SBSJ,
0 DXFSZT,
1 BGLX
from T_BAS_GCJBXX c
where c.xqbm in (select a.xqbm
from T_BAS_GCJBXX a
where a.scszbg=1
minus
select b.sbdw
from T_SZYBMBXX b
where TO_CHAR(b.bgny,'YYYY/MM')=
TO_CHAR(SYSDATE,'YYYY/MM')) group by c.xqbm;
end if;
end;
commit;
end Getyc;
begin
sys.dbms_scheduler.set_attribute(name => 'WFNCYS.JOB_YC', attribute => 'repeat_interval', value => 'Freq=DAILY;ByHour=17;ByMinute=30;BySecond=00');
end;
使用游标的存储过程：
create or replace procedure Getyc is
v_id VARCHAR2(36);
v_date VARCHAR2(4);
y_date VARCHAR2(4);
needd number;
/*定义查询结果集游标，注意游标存储的是结果集的快照*/
cursor gcjbxx_cur is
select c.xqbm from T_BAS_GCJBXX c
where c.id in(
select a.id from T_BAS_GCJBXX a
where a.scszbg = 1
minus
select b.gcid from T_BAS_SZRBJCXX b
where TO_CHAR(b.bgrq, 'YYYY/MM/DD')=TO_CHAR(SYSDATE, 'YYYY/MM/DD')) group by c.xqbm;
/*定义与游标类型匹配的行记录对象*/
gcjbxx_rec gcjbxx_cur%rowtype;
cursor jqtemp_cur is
select sjsj from t_bas_jqtemp;
jqtemp_rec jqtemp_cur%rowtype;
begin
select needduty into needd from workattendence
where TO_CHAR(datetime, 'YYYY/MM/DD')=TO_CHAR(SYSDATE, 'YYYY/MM/DD');
if(needd=1) then
/*打开游标*/
open gcjbxx_cur;
LOOP
fetch gcjbxx_cur into gcjbxx_rec;
/*当未找到记录时退出循环*/
exit when gcjbxx_cur%notfound;
v_id := SYS_GUID();
insert into T_BGYCSBJL (ID, SSXQ, BGRQ, DQZT, SBSJ, DXFSZT, BGLX)
VALUES(v_id,gcjbxx_rec.xqbm,SYSDATE,0,SYSDATE,0,0);
insert into T_RBYCSBMX(ID, SBJLID, DQZT, SBRQ, GCID)
select SYS_GUID() ID,v_id SBJLID,0 DQZT,SYSDATE SBRQ,c.id GCID from T_BAS_GCJBXX c
where c.id in (select a.id from T_BAS_GCJBXX a where a.scszbg = 1
minus
select b.gcid from T_BAS_SZRBJCXX b
where TO_CHAR(b.bgrq, 'YYYY/MM/DD') = TO_CHAR(SYSDATE, 'YYYY/MM/DD')) and c.xqbm=gcjbxx_rec.xqbm;
/*关闭游标*/
END LOOP;
close gcjbxx_cur;
v_date := TO_CHAR(SYSDATE,'DD');
if(v_date='25') then
insert into T_BGYCSBJL(ID,SSXQ,BGRQ,DQZT,SBSJ,DXFSZT,BGLX)
select SYS_GUID() ID,
c.xqbm SSXQ,
SYSDATE BGRQ,
0 DQZT,
SYSDATE SBSJ,
0 DXFSZT,
1 BGLX
from T_BAS_GCJBXX c
where c.xqbm in (
select a.xqbm from T_BAS_GCJBXX a where a.scszbg=1
minus
select b.sbdw from T_SZYBMBXX b
where TO_CHAR(b.bgny,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM')) group by c.xqbm;
end if;
/*开始遍历t_bas_jqtemp*/
open jqtemp_cur;
loop
fetch jqtemp_cur into jqtemp_rec;
exit when jqtemp_cur%notfound;
for i in (
select c.xqbm from T_BAS_GCJBXX c
where c.id in (
select a.id from T_BAS_GCJBXX a where a.scszbg = 1
minus
select b.gcid from T_BAS_SZRBJCXX b
where TO_CHAR(b.bgrq, 'YYYY/MM/DD')=TO_CHAR(jqtemp_rec.sjsj, 'YYYY/MM/DD')) group by c.xqbm)
LOOP
v_id := SYS_GUID();
insert into T_BGYCSBJL (ID, SSXQ, BGRQ, DQZT, SBSJ, DXFSZT, BGLX)
VALUES(v_id,i.xqbm,jqtemp_rec.sjsj,0,jqtemp_rec.sjsj,0,0);
insert into T_RBYCSBMX(ID, SBJLID, DQZT, SBRQ, GCID)
select SYS_GUID() ID,v_id SBJLID,0 DQZT,jqtemp_rec.sjsj SBRQ,c.id GCID from T_BAS_GCJBXX c
where c.id in (select a.id from T_BAS_GCJBXX a where a.scszbg = 1
minus
select b.gcid from T_BAS_SZRBJCXX b
where TO_CHAR(b.bgrq, 'YYYY/MM/DD') = TO_CHAR(jqtemp_rec.sjsj, 'YYYY/MM/DD')) and c.xqbm=i.xqbm;
END LOOP;
y_date := TO_CHAR(jqtemp_rec.sjsj,'DD');
if(y_date='25') then
insert into T_BGYCSBJL(ID,SSXQ,BGRQ,DQZT,SBSJ,DXFSZT,BGLX)
select SYS_GUID() ID,
c.xqbm SSXQ,
jqtemp_rec.sjsj BGRQ,
0 DQZT,
jqtemp_rec.sjsj SBSJ,
0 DXFSZT,
1 BGLX
from T_BAS_GCJBXX c
where c.xqbm in (
select a.xqbm from T_BAS_GCJBXX a where a.scszbg=1
minus
select b.sbdw from T_SZYBMBXX b
where TO_CHAR(b.bgny,'YYYY/MM')=TO_CHAR(jqtemp_rec.sjsj,'YYYY/MM')) group by c.xqbm;
end if;
end loop;
close jqtemp_cur;
DELETE FROM t_bas_jqtemp;
else
insert into t_bas_jqtemp (sjsj,id) VALUES(SYSDATE,sys_guid());
end if;
commit;
end Getyc;