存储过程(数组参数、for循环、拼接的动态sql游标、merge into)

create or replace procedure SFGL_XF_ONE_ADD(p_njdm   in varchar2,
                                        p_yxdm   in varchar2,
                                         p_zydm   in varchar2,
                                         p_fy     in varchar2,
                                         p_czr    in varchar2,
                                         o_errMsg out varchar2) is
  /**
  *  生成单个年级专业学费
  *  功能：遍历当前年级的学院专业的学生把每个学生需要缴纳的学费保存到应缴费用表，
           如果该学生的学费已经存在于应缴费用表中则更新，否则插入,同时更新当前学生的学费         

  *  2015年11月1日
  *   p_njdm 年级代码
  *   p_yxdm 院系代码
  *   p_zydm 专业代码
  *   p_fy 住宿费用
  *   p_czr 当前操作人
  *   o_errMsg 返回出错信息
  */

begin
  for xsxx_rec in (SELECT * FROM SFGL_XSXX WHERE nj = p_njdm and yxdm = p_yxdm and zydm = p_zydm) loop
  
      merge into SFGL_YJFY a
        using (SELECT xsxx_rec.xsid xsid, p_njdm ||p_yxdm||p_zydm||'xf' scm, '1' lx FROM dual) b
        on (a.YJYH = b.xsid and a.SCM = b.scm and a.FYLX = b.lx)
        when matched then
          update set a.FY = p_fy,a.modified_time = sysdate,a.modified_by = p_czr  --更新费用,修改时间,修改人
        
        when not matched then
          insert
            (a.YJFYID,a.FYMC,a.FY,a.FYLX,a.YJYH,a.SCM,
            a.modified_Time,a.modified_By,a.create_Time,a.create_By,a.bz)
          values
            (XL_SFGL_YJFY.NEXTVAL,p_njdm ||'年'||xsxx_rec.yxmc||xsxx_rec.zymc||'学费',p_fy,'1',xsxx_rec.xsid,p_njdm ||p_yxdm||p_zydm||'xf',
            sysdate,p_czr,sysdate,p_czr,null); --插入应缴费用表
      
        update SFGL_XSXX set xf = p_fy WHERE XSID = xsxx_rec.xsid; --更新学生信息表'学费'字段
  
  end loop;
exception
  when others then
    o_errMsg := '程序运行出现内部错误，请联系管理员。';
    raise;
end SFGL_XF_ONE_ADD;

 

create or replace procedure SFGL_XF_All_ADD(p_njdmAttr   in type_varchar,
                                         p_czr    in varchar2,
                                         o_errMsg out varchar2) is
  /**
  *  生成全部学费
  *  功能：根据选择的年级生成全部的学费，遍历当前年级的学院专业的学生把每个学生需要缴纳的学费保存到应缴费用表，
           如果该学生的学费已经存在于应缴费用表中则更新，否则插入,同时更新当前学生的学费         

  *  2015年11月1日
  *   p_njdmAttr 年级代码数组
  *   p_czr 当前操作人
  *   o_errMsg 返回出错信息
  */

begin
--循环需要生成的年级
  for i in 1..p_njdmAttr.Count loop 
  --根据年级查找学费表
    for xf_rec in (SELECT * FROM SFGL_XF WHERE nj = p_njdmAttr(i)) loop 
    --根据年级、院系、专业查找学生信息，然后遍历这些学生查看在应缴费用表中是否已经存在，存在则更新，不存在插入；
    --最后更新这个学生的学费字段
      for xsxx_rec in (SELECT * FROM SFGL_XSXX WHERE nj = xf_rec.nj and yxdm = xf_rec.yxdm and zydm = xf_rec.zydm) loop
      
          merge into SFGL_YJFY a
            using (SELECT xsxx_rec.xsid xsid, xsxx_rec.nj ||xsxx_rec.yxdm||xsxx_rec.zydm|| 'xf' scm, '1' lx FROM dual) b
            on (a.YJYH = b.xsid and a.SCM = b.scm and a.FYLX = b.lx)
            when matched then
              update set a.FY = xf_rec.fy,a.modified_time = sysdate,a.modified_by = p_czr --学生信息已经存在，更新应缴费用表中的费用,修改时间,修改人
            
            when not matched then
              insert
                (a.YJFYID,a.FYMC,a.FY,a.FYLX,a.YJYH,a.SCM,
                a.modified_Time,a.modified_By,a.create_Time,a.create_By,a.bz)
              values
                (XL_SFGL_YJFY.NEXTVAL,xsxx_rec.nj ||'年'||xsxx_rec.yxmc||xsxx_rec.zymc||'学费',xf_rec.fy,'1',xsxx_rec.xsid,xsxx_rec.nj ||xsxx_rec.yxdm||xsxx_rec.zydm|| 'xf',
                sysdate,p_czr,sysdate,p_czr,null); --学生信息不已经存在应缴费用表中，插入
          
            update SFGL_XSXX set xf = xf_rec.fy WHERE XSID = xsxx_rec.xsid; --更新学生信息表'学费'字段
      
      end loop;
     end loop;
  end loop;
exception
  when others then
    o_errMsg := '程序运行出现内部错误，请联系管理员。';
    raise;
end SFGL_XF_All_ADD;

 

create or replace procedure SFGL_QTFY_XS(p_njdm   in varchar2,
                                         p_xqdm   in varchar2,
                                         p_dwh    in varchar2,
                                         p_zydm   in varchar2,
                                         p_bjdm   in varchar2,
                                         p_xh     in varchar2,
                                         p_qtfyid     in varchar2,
                                         p_fymc   in varchar2,
                                         p_fy     in varchar2,
                                         p_czr    in varchar2,
                                         o_errMsg out varchar2) is
  /**
  *  生成其他费用(学生类型)
  *  功能：根据查询条件遍历学生把每个学生需要缴纳的其他费用保存到应缴费用表，
           如果该学生的其他费用已经存在于应缴费用表中则更新，否则插入,同时更新当前学生的其他费用
  

  *  2015年11月6日
  *   p_njdm 年级代码
  *   p_xqdm 校区代码
  *   p_dwh 院系代码
  *   p_zydm 专业代码
  *   p_bjdm 班级代码
  *   p_xh 教务学号
  *   p_qtfyid 其他费用主键
  *   p_fymc 费用名称
  *   p_fy 费用
  *   p_czr 当前操作人
  *   o_errMsg 返回出错信息
  */

  v_sql STRING(3000);  --存放查询语句

  TYPE cur_type IS REF CURSOR; --创建学生信息的动态游标，根据查询条件生成相应的游标
  xsxx_cur cur_type;
  xsxx_rec SFGL_XSXX%rowtype; --创建与SFGL_XSXX相同类型的临时集合

begin
  --根据查询条件初始化的游标语句
  v_sql := 'select * FROM SFGL_XSXX where 1=1 and nj = ''' || p_njdm || '''';
  if p_xqdm is not null then
    v_sql := v_sql || ' and xqdm = ''' || p_xqdm || '''';
  end if;
  if p_dwh is not null then
    v_sql := v_sql || ' and yxdm = ''' || p_dwh || '''';
  end if;
  if p_zydm is not null then
    v_sql := v_sql || ' and zydm = ''' || p_zydm || '''';
  end if;
  if p_bjdm is not null then
    v_sql := v_sql || ' and bjdm = ''' || p_bjdm || '''';
  end if;
  if p_xh is not null then
    v_sql := v_sql || ' and xh = ''' || p_xh || '''';
  end if;
  /*dbms_output.put_line('查询语句：'||v_sql);*/

  --打开学生信息游标
  open xsxx_cur for v_sql;
  loop
    fetch xsxx_cur into xsxx_rec; --把游标的值放到xsxx_rec临时集合
    exit when xsxx_cur%notfound;
  
    merge into SFGL_YJFY a
    using (SELECT xsxx_rec.xsid xsid, p_njdm ||p_qtfyid|| 'xsqtfy' scm, '4' lx FROM dual) b
    on (a.YJYH = b.xsid and a.SCM = b.scm and a.FYLX = b.lx)
    when matched then
      update set a.FY = p_fy, a.modified_time = sysdate, a.modified_by = p_czr --更新费用,修改时间,修改人
      
    when not matched then
      insert(a.YJFYID,a.FYMC,a.FY,a.FYLX,a.YJYH,a.SCM,a.modified_Time,a.modified_By,a.create_Time,a.create_By,a.bz)
      values
        (XL_SFGL_YJFY.NEXTVAL,p_fymc || '学生其他费用',p_fy,'4',xsxx_rec.xsid,p_njdm ||p_qtfyid|| 'xsqtfy',sysdate,p_czr,
         sysdate,p_czr,null); --插入应缴费用表
  
    update SFGL_XSXX set qtfy = p_fy WHERE XSID = xsxx_rec.xsid; --更新学生信息表'其他费用'字段
  
  end loop;
  close xsxx_cur;
exception
  when others then
    o_errMsg := '程序运行出现内部错误，请联系管理员。';
    raise;
end SFGL_QTFY_XS;

 

p_njdmAttr   in type_varchar 这是定义的数组类型

 

CREATE OR REPLACE TYPE "TYPE_VARCHAR" AS TABLE OF VARCHAR2(200)

 
--创建一个collection类型，用于格式化输出，主要应用于存储过程传入数组类型参数。