River Flows In You
0%

发表于 更新于 分类于

环境及版本：

  • SpringBoot： 2.x
  • Oracle : 11c

  1. 无参数的存储过程

    创建存储过程

    1
    2
    3
    4
    5
    create or replace procedure TEST01
    as
    begin
      dbms_output.put_line('hello word');
    end;

    Mapper.xml

    1
    2
    3
    <select id="show1" statementType="CALLABLE" >
       {call TEST01}
    </select>

    Mapper.java

    1
    void show1()

    Controller.java

    1
    2
    3
    4
    @Test
    public void show1() {
    	mapper.show1();
    }

  2. 有参数的存储过程
    创建存储过程

    1
    2
    3
    4
    5
    6
    7
       CREATE OR REPLACE 
       procedure TEST02(name in varchar,res out varchar)
       as
       begin
         dbms_output.put_line('name='||name);
         res:='name='||name;
    end;

    Mapper.xml

    1
    2
    3
       <select id="show2" statementType="CALLABLE" resultType="java.util.Map">
           {call TEST02 (#{name,mode=IN,jdbcType=VARCHAR},#{res,mode=OUT,jdbcType=VARCHAR})}
    </select>

    Mapper.java

    1
    void show2()

    Controller.java

    1
    2
    3
    4
    5
    6
    7
    @Test
    public void show2() {
    	Map<String,Object> map=new HashMap<>();
    	map.put("name","wahhh");
        batchManageMapper.show2(map);
        System.out.println(map.get("res"));
     }

  3. 存储过程的结果集调用
    创建存储过程

    1
    2
    3
    4
    5
    6
    CREATE OR REPLACE 
    Procedure TEST03(res out sys_refcursor)
    As
    begin
    	open res for select * from BOND_INFO WHERE BOND_CODE='111799852.IB';
    End;

    Mapper.xml

    1
    2
    3
    4
    5
    6
    7
    8
    <resultMap id="resultMap" type="cn.com.pojo.BondInfo">
            <result property="bondCode" column="BOND_CODE"/>
            <result property="bondName" column="BOND_NAME"/>
    </resultMap>

    <select id="show3" statementType="CALLABLE" resultType="java.util.Map">
            {call TEST03 (#{res,mode=OUT,jdbcType=CURSOR,javaType=java.sql.ResultSet,resultMap=resultMap})}
    </select>

    Mapper.java

    1
    List<BondInfo> show3(Map<String, Object> map);

    Controller.java

    1
    2
    3
    4
    5
    6
    7
    @Test
    public void show3() {
      Map<String,Object> map=new HashMap<>();
      batchManageMapper.show3(map);
      List<BondInfo> bondInfos1 = (List<BondInfo>) map.get("res");
      System.out.println(bondInfos1.size());
    }