儲存過程

--申請單的增
procedure loanAdd(p_appcode in varchar2, op_result out number) as
  v_appid varchar2(20) := 'SZ00' || APPLOANAPP_ID.nextval;
begin
  insert into t_app_loanapp (appid, appcode) values (v_appid, p_appcode);
  commit;
  dbms_output.put_line('CURRVAL：' || v_appid);
  op_result := 1;
exception
  when others then
    rollback;
    dbms_output.put_line(sqlcode || '---' || sqlerrm);
    op_result := 0;
end;

--申請單的查
procedure loanGet(op_result out sys_refcursor) as
begin
  open op_result for
    select appid, appcode, contractcode, productno from t_app_loanapp;
end;

Mapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.nbesoft.mapper.imapper.LoanMapper">
    <resultMap id="resultMap" type="com.nbesoft.mapper.model.Loan">
        <id column="APPID" jdbcType="VARCHAR" property="appid"/>
        <result column="APPCODE" jdbcType="VARCHAR" property="appcode"/>
        <result column="CONTRACTCODE" jdbcType="VARCHAR" property="contractcode"/>
        <result column="PRODUCTNO" jdbcType="VARCHAR" property="productno"/>
    </resultMap>

    <!-- 插入貸款資訊 -->
    <insert id="insertLoan" parameterType="java.util.Map" statementType="CALLABLE">
        {call erp_loan.loanadd(#{p_appcode,mode=IN,jdbcType=VARCHAR},#{op_result,mode=OUT,jdbcType=INTEGER})}
    </insert>

    <!-- 獲取貸款資訊 -->
    <select id="selectLoans" parameterType="java.util.Map" statementType="CALLABLE">
        {call erp_loan.loanget(#{op_result,mode=OUT,jdbcType=CURSOR,javaType=java.sql.ResultSet,resultMap=resultMap})}
    </select>
</mapper>

Dao層

import java.util.Map;

public interface LoanMapper {
    void insertLoan(Map<String, Object> map);

    void selectLoans(Map<String, Object> map);
}

Controller層

import com.nbesoft.company.service.TLoanService;
import com.nbesoft.company.util.Tip;
import com.nbesoft.mapper.model.Loan;
import oracle.jdbc.internal.OracleTypes;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.text.ParseException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Controller
@RequestMapping("/tloan")
public class TLoanController extends SuperControl {
    @Resource
    private TLoanService tLoanService;

    @RequestMapping(value = "/add/{appCode}", method = RequestMethod.GET)
    public void doAdd(@PathVariable String appCode, HttpServletResponse response) throws ParseException {
        Map<String, Object> map = new HashMap<>();
        map.put("p_appcode", appCode);
        tLoanService.insertLoan(map);
        int insertLoan = (int) map.get("op_result");
        if (insertLoan > 0) {
            super.writeJson(new Tip("新增成功！", 200, "/loan/1/10.shtml"), response);
        } else {
            super.writeJson(new Tip("新增失敗！", 500), response);
        }
    }

    @RequestMapping(value = "/get", method = RequestMethod.GET)
    public void doGet(HttpServletResponse response) throws ParseException {
        Map<String, Object> map = new HashMap<>();
        map.put("op_result", OracleTypes.CURSOR);
        tLoanService.selectLoans(map);
        List<Loan> app = (List<Loan>) map.get("op_result");
        super.writeJson(app, response);
    }
}