今天遇到一个bug, mybatis 调用oracle的function,总结几点如下:
1.function的参数是没有out 参数的,举例如下:
FUNCTION get_result(p1 IN VARCHAR2, p2 IN VARCHAR2)
RETURN NUMBER IS
result table1.somecolumn%TYPE;
BEGIN
IF (p2= '01') THEN
UPDATE table1 t
SET t.somecolumn= CASE WHEN somecolumn>= 127 OR somecolumn IS NULL THEN 1 ELSE somecolumn+1 END
WHERE t1.keyColumn= p1 RETURNING
somecolumn INTO result;
ELSIF (p2= '02') THEN
UPDATE table1
SET somecolumn= CASE WHEN somecolumn>= 15 OR somecolumn IS NULL THEN 0 ELSE somecolumn+1 END
WHERE table1.keyColumn= p1
RETURNING somecolumn INTO result;
END IF;
COMMIT;
RETURN reslult;
END get_result
;
<span style="font-family: Arial, Helvetica, sans-serif; background-color: rgb(255, 255, 255);">2.针对oracle function , mybatis的mapper,不要设置out参数,没必要.正确的方式举例如下:</span>
<select id="procedule_getxxResult" parameterType="java.util.Map" statementType="CALLABLE">
{
#{result,mode=OUT,jdbcType=NUMERIC,javaType=Integer} = call pkg_services.get_result(
#{p1,mode=IN,jdbcType=VARCHAR},
#{p2,mode=IN,jdbcType=VARCHAR}
)
}
</select>
<span style="font-size:14px;">public int getxxxSeq(String p1,String p2){
Map<String,Object> map = new HashMap<String,Object>(4);
map.put("p_zdljdz", strRtua);
map.put("p_zdgylx", rtuPotocType);
try{
testMapper.<span style="font-family: Arial, Helvetica, sans-serif;">procedule_getxxResult</span><span style="font-family: Arial, Helvetica, sans-serif;">(map);</span>
String res= map.get("result").toString();
Integer value = Integer.valueOf(</span><span style="font-family: Arial, Helvetica, sans-serif; font-size: 14px;">res</span><span style="font-family: Arial, Helvetica, sans-serif;">);</span><span style="font-size:14px;">
return value;
}catch(Exception e){
System.out.println(e.getLocalizedMessage());
}
return -1;
}</span>