oracle存储过程与游标使用实例

create or replace procedure CLOSE_DIRECT_AUDIT_POINT(pointname     in varchar2,
                                                     new_pointname in varchar2) is
  v_audit_id     NUMBER(38);
  v_new_audit_id NUMBER(38);
  iCnt           number;
  v_zjhm         VARCHAR2(18);
  v_zjlx         char(1);
  v_xm           VARCHAR2(30);
  v_jhrsjhm      varchar2(11);
  --该信息审核点暂缓的学生
  cursor review_student_cur is
    select b.zjlx, b.zjhm, b.xm, c.jhrsjhm
      from T_PSINFO_REV_PUBLIC a, T_PSTU_REGINFO b, t_pstu_reginfo_jhr c
     where a.XXSHDID = v_audit_id
       and a.zjlx = b.zjlx
       and a.zjhm = b.zjhm
       and a.zjhm = c.zjhm
       and b.SHFLG = '6';
begin
 --取旧直审点编号
  select count(*) into iCnt from T_PSINFO_REVIEW where shdmc = pointname;
  if (iCnt = 1) then
    select xxshdid
      into v_audit_id
      from T_PSINFO_REVIEW
     where shdmc = pointname;
  else
    dbms_output.put_line('aduit point named[' || pointname || '] found [' || iCnt ||
                         '] records!');
    return;
  end if;


  --取新直审点编号
  select count(*)
    into iCnt
    from T_PSINFO_REVIEW
   where shdmc = new_pointname;
  if (iCnt = 1) then
    select xxshdid
      into v_new_audit_id
      from T_PSINFO_REVIEW
     where shdmc = new_pointname;
  else
    dbms_output.put_line('new aduit point named[' || new_pointname ||
                         '] found [' || iCnt || '] records!');
    return;
  end if;


  --关闭对应用户的校审核权限
  update T_AUTH_USER_ROLE
  set delflg='1'
   where yhbh in (select a.yhbh
                    from t_auth_user a, T_PSINFO_REVIEW b
                   where a.XXSHDID = b.XXSHDID
                     and a.XXSHDID in (select XXSHDID from t_psinfo_review where qxdm='310105'
                      and glzsxxbh is not null))
                      and jsbh='004';
  --统计审核点内暂存孩子数量
  select count(a.zjhm)
    into iCnt
    from T_PSINFO_REV_PUBLIC a, T_PSTU_REGINFO b
   where a.zjlx = b.zjlx
     and a.zjhm = b.zjhm
     and b.SHFLG = '6'
     and a.XXSHDID = v_audit_id;
  if (iCnt > 0) then
    dbms_output.put_line('There are ' || iCnt ||
                         ' students who is temporarily saved in this aduit point!');
    --修改审核点相关学生状态，暂存(6)——>待补齐材料(3)
    update T_PSTU_REGINFO
       set shflg = '3'
     where zjhm in (select a.zjhm
                      from T_PSINFO_REV_PUBLIC a, T_PSTU_REGINFO b
                     where a.zjlx = b.zjlx
                       and a.zjhm = b.zjhm
                       and b.SHFLG = '6'
                       and a.XXSHDID = v_audit_id);


    --将学生所属信息审核点迁移至新流转点
    update T_PSINFO_REV_PUBLIC
       set xxshdid = v_new_audit_id
     where zjhm in (select a.zjhm
                      from T_PSINFO_REV_PUBLIC a, T_PSTU_REGINFO b
                     where a.zjlx = b.zjlx
                       and a.zjhm = b.zjhm
                       and b.SHFLG = '6'
                       and a.XXSHDID = v_audit_id);


    iCnt := 0; --重置计数器
    open review_student_cur;
    loop
      fetch review_student_cur
        into v_zjlx, v_zjhm, v_xm, v_jhrsjhm;
      exit when review_student_cur%notfound;
      --逐条发短信
      insert into T_NOTICE_INFO
      (TZJLID,TZRZJLX,TZRZJHM,TZRSJHM,TZLB,TZMC,WYYW,WYNY,TZHFBZ,DYBZ,DXYW,DXNY,DXFSZT,
      DXFSSJ,DXFSCS,MTMSGID,TZCKZT,DELFLG)
      values
      (seq_tzjlid.nextval,v_zjlx,v_zjhm,v_jhrsjhm,'2','待补齐材料','0','','','0','1',
      '待定！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！',
      '0',sysdate,'1','','0','0');
    end loop;
    close review_student_cur;
  else
    dbms_output.put_line('There is no students who is temporarily saved in this aduit point,work skipped');
    return;
  end if;


  --commit;


end CLOSE_DIRECT_AUDIT_POINT;