oracle 存储过程代码

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 ;