oracle存储过程与游标使用实例
来源:互联网 发布:荷兰留学知乎 编辑:程序博客网 时间:2024/05/01 07:28
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;
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;
0 0
- oracle存储过程与游标使用实例
- oracle存储过程与游标的使用
- Oracle 游标 与 存储过程
- Oracle存储过程,游标使用
- <Oracle游标>存储过程中使用游标
- oracle存储过程:游标使用,多重游标
- Oracle存储过程返回游标实例详解
- Oracle存储过程返回游标实例详解
- Oracle与MySQL存储过程以及游标使用的区别
- mysql存储过程和游标使用实例
- MySQL存储过程简单实例--游标使用
- oracle 存储过程中使用游标
- 简单oracle存储过程,使用游标
- oracle存储过程和游标的使用
- Oracle使用游标循环调用存储过程
- oracle存储过程和游标的使用
- oracle 存储过程和游标的使用
- oracle存储过程和游标的使用
- 2012年5月SAT香港真题解析
- unix基础杂谈
- GRE写作必备句型
- Maven项目怎样引用其他项目/或者jar包
- C++面向对象模型
- oracle存储过程与游标使用实例
- JAVA反射学习之——深入研究(反射与泛型)
- 事件委托的个人理解
- 关于“代码区,全局数据区,堆区,栈区”和“ 栈区,堆区,全局/静态存储区,常量存储区”两种不同的说法
- Eclipse maven构建springmvc项目
- 浏览器设置禁用javascript
- chrome浏览器中div被embed标签遮住
- 如何取得文件的扩展名
- 如何解决C#中多个panel重叠问题