• 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 => '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;

  • 相关阅读:
    sharepoint权限操作(记录以备忘)
    python-----利用filecmp删除重复文件
    python-----自动解压并删除zip文件
    python-----文件自动归类
    python-----模糊搜索文件
    python-----查看显卡gpu信息
    python-----判断文件是否存在
    git 查看、切换用户
    PyCharm创建文件时自动添加头文件
    python-----监控博客园积分动态,有变化发邮件通知
  • 原文地址:https://www.cnblogs.com/zcwry/p/oracle_procedure.html
Copyright © 2020-2023  润新知