首页 > 代码库 > oracle 存储过程的写法
oracle 存储过程的写法
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 =http://www.mamicode.com/> ‘Freq=DAILY;ByHour=17;ByMinute=30;BySecond=00‘);
end;
oracle 存储过程的写法