ibatis3.0调用Oracle的存储过程

直接上源码

一，oracle储存过程。 

create or replace procedure proc_get_th(i_hth in varchar2,o_ret out sys_refcursor)isbegin for v_rec in (select ggd_id,th from gms_ggd_zt where hth=i_hth) loop insert into tmp_gms_plan_th(ggd_id,th) select ggd_id,th from table(f_get_th(v_rec.ggd_id,v_rec.th)); end loop;

open o_ret for select gpt.ggd_id,gpt.th from tmp_gms_plan_th gpt where (gpt.ggd_id,gpt.th) not in (select ggd_id,th from gms_plans); delete from tmp_gms_plan_th; commit;end;

二，ibatis的xml配置。

 

<resultMap id="ResultGmsTH" type="com.pisx.app.gms.entity.GmsPlans" > <result column="GGD_ID" property="ggdId" jdbcType="VARCHAR" /> <result column="th" property="th" jdbcType="VARCHAR" /> </resultMap>

<!--第一种写法 查询语句 --> 缺点：参数值的顺序一定要和数据库对应 <select id="queryPlansByHthProc" parameterType="java.util.Map" statementType="CALLABLE">

<![CDATA[

call proc_get_th(#{hth,mode=IN,jdbcType=VARCHAR}, #{result,mode=OUT,jdbcType=CURSOR,javaType=java.sql.ResultSet,resultMap=ResultGmsTH} )

]]>

</select>

<!--第二种写法 查询语句 --> 

<select id="queryPlansByHthProc" parameterType="java.util.Map" statementType="CALLABLE">

<![CDATA[

call proc_get_th( hth => #{hth,mode=IN,jdbcType=VARCHAR}, result => #{result,mode=OUT,jdbcType=CURSOR,javaType=java.sql.ResultSet,resultMap=ResultGmsTH})

]]>

</select>

 

三，获取返回值。

 gmsPlansSerivice.queryPlansByHthProc(map);

(List<GmsPlans>) map.get("result");

说明：返回值会自动装在你的参数里面。

 

转载于:https://www.cnblogs.com/luocheng/p/4387696.html