CREATE OR REPLACE PROCEDURE PROC_NBYXSMDGL_SJZB(VCZR IN VARCHAR2,
VMBBH IN VARCHAR2,
VND OUT NUMBER) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('----start0----');
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE, 'yyyy-MM-dd HH:MI:ss'));
TY_TASKSCORE_1 icdc_xs.CJ_SCORE.TASKSCORE%type:=0;
xuewei_nr varchar2(100);
xuewei_count number(10, 2) := 0;
IF VMBBH IS NULL THEN
RETURN;
END IF;
DECLARE
CURSOR CURRENT_CURSOR IS
--select * from sis_xj_xsjbxx t where Vnd = to_number(t.nj) + to_number(t.xz) ; -- 学生基本信息中的 将要毕业的人
SELECT T.*
FROM ICDC_XS.VIEW_BYXWXX S, SIS_XJ_XSJBXX T
WHERE S.XH = T.XH
AND BYZH IS NOT NULL;
MY_CURSOR CURRENT_CURSOR%ROWTYPE;
BEGIN
-- 存储过程功能：初始化 插入到学生拟毕业生信息表 和 学生离校数据准备(离校办理结果表) 中信息
--删除当前模板下的 拟毕业生学生名单
DELETE FROM SIS_LX_NBYSMDGL T WHERE T.DYMB = VMBBH;
--删除当前模板下的 数据准备的学生名单
DELETE FROM SIS_LX_LXSXBLJG T1 WHERE T1.LXMBBH = VMBBH;
-- 修改离校事项 数据准备状态：准备完毕
UPDATE SIS_LX_LXSX T SET SJZBQR = '1' WHERE T.LXMBBH = VMBBH;
IF CURRENT_CURSOR%ISOPEN THEN
CLOSE CURRENT_CURSOR;
END IF;
OPEN CURRENT_CURSOR;
LOOP
FETCH CURRENT_CURSOR
INTO MY_CURSOR;
EXIT WHEN CURRENT_CURSOR%NOTFOUND;
INSERT INTO SIS_LX_NBYSMDGL T
(T.RESOURCE_ID,
T.XH,
T.KSH,
T.XM,
T.XMPY,
CYM,
XBM,
CSRQ,
MZM,
ZZMMM,
YXSH,
ZYH,
ZYKLM,
NJ,
BH,
JGM,
SYDM,
ZJLB,
SFZH,
ZJXY,
TC,
LXDH,
TXDZ,
YZBM,
DZXX,
ZP,
GATQM,
GBM,
HYZKM,
XSLBM,
RXNY,
PYFSM,
XSCCDM,
YJFXM,
DSZGH,
HDXLFSM,
XZ,
XXM,
CSDM,
JKZKM,
XZZ,
HKSZD,
HKXZM,
ZYDZ,
ZXZT,
XNXJBZ,
GJXJBZ,
BYZGBZ,
XWZGBZ,
DYMB,
CZSJ,
CZR)
VALUES
(SEQ_SIS_RESOURCE_ID.NEXTVAL,
MY_CURSOR.XH,
MY_CURSOR.KSH,
MY_CURSOR.XM,
MY_CURSOR.XMPY,
MY_CURSOR.CYM,
MY_CURSOR.XBM,
MY_CURSOR.CSRQ,
MY_CURSOR.MZM,
MY_CURSOR.ZZMMM,
MY_CURSOR.YXSH,
MY_CURSOR.ZYH,
MY_CURSOR.ZYKLM,
MY_CURSOR.NJ,
MY_CURSOR.BH,
MY_CURSOR.JGM,
MY_CURSOR.DQDM,
MY_CURSOR.ZJLB,
MY_CURSOR.ZJHM,
MY_CURSOR.ZJXY,
MY_CURSOR.TC,
MY_CURSOR.LXDH,
MY_CURSOR.TXDZ,
MY_CURSOR.YZBM,
MY_CURSOR.DZXX,
MY_CURSOR.ZP,
MY_CURSOR.GATQM,
MY_CURSOR.GBM,
MY_CURSOR.HYZKM,
MY_CURSOR.XSLBM,
MY_CURSOR.RXNY,
MY_CURSOR.PYFSM,
MY_CURSOR.XSCCDM,
MY_CURSOR.YJFXM,
MY_CURSOR.DSZGH,
MY_CURSOR.HDXLFSM,
MY_CURSOR.XZ,
MY_CURSOR.XXM,
MY_CURSOR.CSDM,
MY_CURSOR.JKZKM,
MY_CURSOR.XZZ,
MY_CURSOR.HKSZD,
MY_CURSOR.HKXZM,
MY_CURSOR.ZYDZ,
MY_CURSOR.ZXZT,
MY_CURSOR.XNXJBZ,
MY_CURSOR.GJXJBZ,
'0',
'0',
VMBBH,
SYSDATE,
VCZR);
-- 插入到学生拟毕业信息表中了
--0 还要插入到离校学生办理结果表中 SIS_LX_LXSXBLJG
--1 查询模板下有多少个事件
--2 循环事件将信息插入到 办理结果表中
--3 默认都是办理状态是 0
DECLARE
CURSOR CURRENT_CURSOR_GETLXSX IS
SELECT * FROM SIS_LX_LXSX T WHERE T.LXMBBH = VMBBH; -- 通过模板编号，查找事项编号
C_GETLXSX CURRENT_CURSOR_GETLXSX%ROWTYPE;
BEGIN
IF CURRENT_CURSOR_GETLXSX%ISOPEN THEN
CLOSE CURRENT_CURSOR_GETLXSX;
END IF;
OPEN CURRENT_CURSOR_GETLXSX; --判断游标是否打开.如果开了将其关闭，然后在打开
--commit;
LOOP
FETCH CURRENT_CURSOR_GETLXSX
INTO C_GETLXSX;
EXIT WHEN CURRENT_CURSOR_GETLXSX%NOTFOUND;
INSERT INTO SIS_LX_LXSXBLJG
(RESOURCE_ID,
LXSXID,
LXMBBH,
XS_RESOURCE_ID,
XH,
LXSXNRSM,
SXJG,
BZ1,
BZ2,
CJRQ,
CJFS,
CZRBH,
BLDW,
BLRQ,
BLCZR,
BLFS)
VALUES
(SEQ_SIS_RESOURCE_ID.NEXTVAL,
C_GETLXSX.LXSXID,
VMBBH,
NULL,
MY_CURSOR.XH,
NULL,
'0',
NULL,
NULL,
SYSDATE,
NULL,
VCZR,
C_GETLXSX.BLDW,
NULL,
NULL,
NULL);
END LOOP;
--commit;
CLOSE CURRENT_CURSOR_GETLXSX;
END; -- 里层游标结束
END LOOP; --外层游标结束
COMMIT; -- 提交事务
CLOSE CURRENT_CURSOR; --关闭外层游标
END;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('sqlcode : ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('sqlerrm : ' || SQLERRM);
ROLLBACK;
END PROC_NBYXSMDGL_SJZB;
-- and 和 or 配合使用
select cj.STUDENTNO ,
cj.COURSENO,
NVL(cj.TASKSCORE,0) as TASKSCORE,
NVL(cj.TOTALSCORE,0) as TOTALSCORE,
cj.STUDENTNAME,
cj.TERMNO
from icdc_xs.CJ_SCORE cj
where cj.studentno = XH_STUDENTNO and cj.FIRSTYEAR= r_startyear and cj.ENDYEAR = r_endyear and scoretypeno = '01'
and (cj.TERMNO = decode( v_xq,NULL,1,v_xq) or
cj.TERMNO = decode( v_xq,NULL,2 ,v_xq) or
cj.TERMNO = decode( v_xq,NULL,3 ,v_xq) or
cj.TERMNO = decode( v_xq,NULL,4 ,v_xq) or
cj.TERMNO = decode( v_xq,NULL,5 ,v_xq) or
cj.TERMNO = decode( v_xq,NULL,6 ,v_xq) ) ; --定义游标
-- 查询出来 德智体方面 （加分和减分的总和）
select
nvl ( sum(case when z.dztfm = '1' and z.cpbz = '1' then to_number(s.cpfs) when z.dztfm = '1' and z.cpbz = '2' then -to_number(s.cpfs) else 0 end) ,0)
, nvl ( sum(case when z.dztfm = '2' and z.cpbz = '1' then to_number(s.cpfs) when z.dztfm = '2' and z.cpbz = '2' then -to_number(s.cpfs) else 0 end) ,0)
, nvl ( sum(case when z.dztfm = '3' and z.cpbz = '1' then to_number(s.cpfs) when z.dztfm = '3' and z.cpbz = '2' then -to_number(s.cpfs) else 0 end) ,0)
into
deyu_add
, zhiyu_add
, tiyu_add
from sis_cp_sssqzhjfcp s,sis_cp_zhjfcp z where s.cpbh =z.resource_id and s.xh = XH_STUDENTNO and s.xnd = v_xnd_val ;
