ibatis3.0调用Oracle的存储过程

直接上源码

一，oracle储存过程。 

create or replace procedure proc_get_th(
i_hth in varchar2,
o_ret out sys_refcursor
)
is
begin
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");

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