--定时任务

declare job20 number;

begin

sys.dbms_job.submit(job20,'test1;',sysdate,'sysdate+1');

end;

--存储过程

create or replace procedure test3

as

xh_zgid varchar();

xh_zggh varchar();

xh_xm varchar();

xh_grdh varchar();

xh_count int;

xh_dxxh int;

xh_zfqnr varchar();

--游标

CURSOR z1 is

select zgid,zggh,xm,grdh from RSGL_ZGDA where zxpb= and (rdsj is not null )and zxpb=  AND  to_char(rdsj,'yyyy')!='' and   (to_char(rdsj,'MM-dd')) = (to_char(sysdate,'MM-dd'));

begin

  select  NR into xh_zfqnr  from OA_ZFQ where zfqid='DYSR';

  select to_number(dqz) into xh_dxxh from xt_xhb where bm='SMS_DAIFASONG_DX';

  dbms_output.put_line(xh_dxxh);

   open z1;

    loop

     fetch z1 into xh_zgid,xh_zggh,xh_xm,xh_grdh;

        if z1 %notfound then exit;end if;

        select count(*) into xh_count from BirthDayTip_jl where zgid=xh_zgid and jlsj=to_char(sysdate,'yyyy/MM/dd');

        dbms_output.put_line(xh_count);

        if xh_count< then

          xh_dxxh:=xh_dxxh+;

          insert into BirthDayTip_jl(zgid,phone,Jlsj) values(xh_zgid,xh_grdh,to_char(sysdate,'yyyy/MM/dd'));

          insert into sms_daifasong_dx(WEIYIBM,FASONGR,FASONGSJ,DUANXINNR,FASONGLX,SHOUJIHM) values(xh_dxxh,xh_xm,sysdate,xh_zfqnr,'',xh_grdh);

          dbms_output.put_line(xh_dxxh);

          dbms_output.put_line(xh_xm);

          dbms_output.put_line(sysdate);

          dbms_output.put_line(xh_zfqnr);

          dbms_output.put_line(xh_grdh);

        end if;

     end loop;

    close z1;

    update xt_xhb set dqz=to_char((xh_dxxh)) where bm='SMS_DAIFASONG_DX';

end;

--执行存储过程

begin

 test3;

 commit;

end;