mybatis调取oracle execute immediate动态拼接存储过程返回结果

小灰灰 2022-06-02 10:28 259阅读 0赞

1:存储过程

\--动态过程

create or replace procedure dynamic_pro(pro in varchar2,res out varchar2)  is
      vSQL   varchar2(200);
    begin
      vSQL := 'BEGIN '||pro||'; END;';
      execute immediate vSQL using out res;
    end dsm_dynamic_pro;

\--调取过程

create or replace procedure test_len (filed varchar2,res out varchar2) is
    /**
    **描述
    **/
    begin
      if(length(filed)>10) then
          res:='error';
          dbms_output.put_line('error');
        else
          res:='success';
          dbms_output.put_line('success');
        end if;
    end test_len;

2:mybatis xml

<resultMap type="java.util.HashMap" id="resultMap">
           <result column="pro" property="pro" javaType="java.lang.String" jdbcType="VARCHAR"/>      
           <result column="res" property="res" javaType="java.lang.String" jdbcType="VARCHAR"/>
    </resultMap>
     
    <select id="ardoData" statementType="CALLABLE" parameterType="java.util.HashMap" resultMap="resultMap">
    {call dynamic_pro(#{pro,mode=IN,jdbcType=VARCHAR},#{res,mode=OUT,jdbcType=VARCHAR})}
    </select>

3:dao

public String ardoData(Map<String, String> paramMap);

4:service实现类

public String ardoData(String pro) {
    Map<String, String> paramMap = new HashMap<String, String>();
    paramMap.put("pro", pro);
    dao.ardoData(paramMap);
    System.out.println(paramMap.get("res"));
    return paramMap.get("res");
    }

5:调取过程

String pro = " test_len (‘铜雀深宫锁二乔’,:res)";
    System.out.println("调用的存储过程："+pro);
    String result = service.ardoData(pro);
    System.out.println("结果："+ result);