oracle 預存程序的寫法

標籤：ar   sp   for   on   bs   as   tt   nbsp   br   

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;

oracle 預存程序的寫法