mybatis调取oracle execute immediate动态拼接函数返回结果

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/ardo_pass/article/details/79027685

1:编写简单函数:

create or replace function fun_dynamic(cusfun varchar2) return varchar2 is
  vSQL varchar2(200);
  Result varchar2(200);
 
begin
  --vSQL := 'BEGIN :1:=FNC_TEST(2,3,:res); END;';
  vSQL := 'BEGIN :1:='||cusfun||'; END;';
 
  execute immediate vSQL using out Result;
  return(Result);
end fun_dynamic;
2:xml

<resultMap type="java.util.HashMap" id="resultMapFun">
       <result column="res" property="res" javaType="java.lang.String" jdbcType="VARCHAR"/>
</resultMap>
 
<!-- 定义函数 -->
    <select id="callFunction" parameterType="map" statementType="CALLABLE" resultMap="resultMapFun">
          {#{res,mode=OUT,jdbcType=VARCHAR}=call fun_dynamic(#{cusfun,mode=IN,jdbcType=VARCHAR})}
</select>
3:dao

public String callFunction(Map<String, String> paramMap);
4:service实现类

@Override
public String callFunction(Map<String, String> paramMap) {
checkDataDao.callFunction(paramMap);
return paramMap.get("res");
}

5:controller

Map<String, String> paramMap = new HashMap<String, String>();
paramMap.put("cusfun", "fun_test('写的什么函数?','动态函数!')");
String result = checkDataService.callFunction(paramMap);
System.out.println("函数返回结果:"+result);
6:输出
函数返回结果:写的什么函数?动态函数!



展开阅读全文

execute immediate用法

04-05

plsql存储过程中execute immediate为什么这样用不行呢,求解rn[code=SQL]rnexecute immediate 'select count(*) from partranslog where merch_id=' || mcht || ' and sett_postdate>=' || to_char(startdate) || ' and sett_postdate<=' || to_charrnrn(enddate) || strsql into recordcount using mcht,startdate,enddate;rn[/code]rn如果不加后边部分就可以:|| strsql into recordcount using mcht,startdate,enddaternrn存储过程如下:rn[code=SQL]rncreate or replace procedure transrn(rnmcht in varchar,rnsett in varchar,rnstartcardno in varchar,rnendcardno in varchar,rntx_code in varchar,rnbanlace in varchar,rnstartdate in varchar,rnenddate in varchar,rntraceno in varchar,rnbatchno in varchar,rntermid in varchar,rndocument in varchar,rncardhold in varchar,rnlxname in varchar,rntelphone in varchar,rngkdate in varchar,rnpagesize in number,rncurrpage in number,rnrecordcount out numberrn)rnasrnstrsql varchar(1000);rnv_con number(8);rnbeginrn recordcount:=88888;rn strsql:=' where merch_id=mcht and sett_postdate>=to_char(startdate) and sett_postdate<=to_char(enddate)';rn if length(startcardno)>0 and length(endcardno)>0 thenrn strsql:=' and cardno>=startcartno and cardno<=endcardno';rn end if;rn execute immediate 'select count(*) from partranslog where merch_id=' || mcht || ' and sett_postdate>=' || to_char(startdate) || ' and sett_postdate<=' || to_charrnrn(enddate) || strsql into recordcount using mcht,startdate,enddate;rn update proce set name=recordcount;rnend trans;rn/rn[/code] 论坛

没有更多推荐了,返回首页