带游标的存储过程 ibatis 调用方法

时间:2022-06-25 13:52:43

CREATE OR REPLACE PROCEDURE sp_ypjg_cgdcx( ai_requestid number,
                                           ai_forgid    number,
                                           out_cur      out his_zyjs.ref_cur) AS
  /*
  功能:采购单查询(包括上下级的查询)。
  名称:sp_ypjg_cgdcx
  参数: ai_requestid 申请单ID,ai_forgid   传入的机构ID
  创建:XCK
  创建时间:2012-05-24
  */
 
v_type  varchar2(2);
v_orglevel varchar2(2);

BEGIN
  select t.type,t.orglevel into v_type,v_orglevel from sys_organize_info t where t.id = ai_forgid;
 
  if v_type = '03' and v_orglevel = '04' then
     OPEN out_cur for
      select t1.requcode,--申请单号
             t2.orderid,--序号
             t3.medicode,--项目编码
             t3.mediname||'['||t3.spec||']' as mediname,--项目名称
             t2.unit,--单位
             t2.requnum as sqsl,--申请数量
             t2.spsl,--审批数量
             pkg_bshis_common.F_GET_ADD('dictoper',t1.inputor,'opername') as sqr,--申请人
             t1.requdate as sqrq--申请日期
        from request_total t1,request t2,dictmedi_public t3
      where t1.requestid = t2.requestid
        and t2.mediid = t3.mediid
        and t1.forgid = t2.forgid
        and t1.frcode = t2.frcode
        and t2.forgid = t3.forgid
        and t2.frcode = t3.frcode
        and t1.stage = 1
        --and t1.retu = 0
        and t1.requestid = ai_requestid
        and t1.forgid = ai_forgid;
  elsif v_type = '03' and v_orglevel = '05' then
      OPEN out_cur for
      select t1.requcode,
             t2.orderid,
             t3.medicode,
             t3.mediname||'['||t3.spec||']' as mediname,
             t2.unit,
             t2.requnum as sqsl,
             t2.spsl,
             pkg_bshis_common.F_GET_ADD('dictoper',t1.inputor,'opername') as sqr,
             t1.requdate as sqrq
        from request_total t1,request t2,dictmedi t3
      where t1.requestid = t2.requestid
        and t2.mediid = t3.mediid
        and t1.forgid = t2.forgid
        and t1.frcode = t2.frcode
        and t2.forgid = t3.forgid
        and t2.frcode = t3.frcode
        and t1.stage = 1
        --and t1.retu = 0
        and t1.requestid = ai_requestid
        and t1.forgid = ai_forgid;
  end if;
 
End;

 

sql语句

<sqlMap>

    <resultMap class="java.util.HashMap" id="out_ResultMap">
    <result column="requcode" property="requcode" jdbcType="VARCHAR" />
    <result column="orderid" property="orderid" jdbcType="INTEGER" />
    <result column="medicode" property="medicode" jdbcType="VARCHAR" />
    <result column="mediname" property="mediname" jdbcType="VARCHAR" />
    <result column="unit" property="unit" jdbcType="VARCHAR" />
    <result column="sqsl" property="sqsl" jdbcType="DECIMAL" />
    <result column="spsl" property="spsl" jdbcType="DECIMAL" />
    <result column="sqr" property="sqr" jdbcType="VARCHAR" />
    <result column="sqrq" property="sqrq" jdbcType="VARCHAR" />
  </resultMap>
 
 <parameterMap id="busPrintProcedureParam" class="java.util.HashMap">
   <parameter property="REQUESTID" jdbcType="NUMBER" mode="IN"/>
   <parameter property="FORGID" jdbcType="NUMBER" mode="IN"/>
   <parameter jdbcType="ORACLECURSOR" mode="OUT"
         property="DATA" javaType="java.sql.ResultSet" resultMap="out_ResultMap"/>
 </parameterMap>
 <procedure id="busPrintProcedure" parameterMap="busPrintProcedureParam">
   {call sp_ypjg_cgdcx(?,?,?)}
 </procedure> 
</sqlMap>