返回值有游标

1、xml

<resultMap id="caseListCur" class="java.util.HashMap">
    <result property="claimNo" column="claim_no" />
    <result property="caseReporterName" column="case_reporter_name" />
    <result property="caseReportDate" column="case_report_date" />
    <result property="accidentDate" column="accident_date" />
    <result property="caseReporterType" column="case_reporter_type" />
    <result property="accidentAddr" column="accident_addr" />
    <result property="hospitalCode" column="hospital_code" />
    <result property="bedNo" column="bed_no" />
    <result property="departmentCode" column="medical_department_code" />
    <result property="departmentName" column="medical_department_name" />
    <result property="hospitalName" column="hospital_name" />
    <result property="statusCode" column="status_code" />
    <result property="caseStatus" column="case_status" />
    <result property="registUsername" column="regist_username" />
    <result property="areaCode" column="area_code" />
</resultMap>
<parameterMap id="queryCaseByClientNoMap" class="java.util.HashMap">
    <parameter property="clientNo" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"></parameter>
    <parameter property="case_cursor" jdbcType="ORACLECURSOR" javaType="cursor" mode="OUT" resultMap="caseListCur"></parameter>
    <parameter property="flag" jdbcType="VARCHAR" javaType="java.lang.String" mode="OUT" ></parameter>
    <parameter property="message" jdbcType="VARCHAR" javaType="java.lang.String" mode="OUT" ></parameter>       
</parameterMap>   
<procedure id="queryCaseByClientNo" parameterMap="queryCaseByClientNoMap">
    <![CDATA[
        {call l_claim_pub_interface.query_case_by_client_no(?,?,?,?)}       
    ]]>
</procedure>

2、java

public List<Map> queryCaseByClientNo(String clientNo) {
    HashMap pMap = new HashMap();
    pMap.put("clientNo", clientNo);
    getSqlMapClientTemplate().queryForObject(ClaimCommonDao.class.getName() + ".queryCaseByClientNo", pMap);
    List caseList = (List)pMap.get("case_cursor");
    return caseList;
}

3、pkg

  PROCEDURE query_case_by_client_no(
    p_client_no                 IN     clm_claim_info.client_no%TYPE,
    p_claim_info_cursor         OUT    SYS_REFCURSOR,
    p_flag                      OUT    VARCHAR2,
    p_message                   OUT    VARCHAR2)
  IS
  BEGIN
    p_flag := 'Y';
    IF p_client_no IS NULL THEN
      p_flag := 'N';
      p_message := '客户号不能为空';
      RETURN;
    END IF;
    OPEN p_claim_info_cursor FOR
    SELECT a.claim_no                  claim_no,
           a.case_reporter_name        case_reporter_name,
           a.case_report_date          case_report_date,
           a.accident_date             accident_date,
           a.case_reporter_type        case_reporter_type,
           a.accident_addr             accident_addr,
           b.hospital_code             hospital_code,
           b.bed_no                    bed_no,
           b.department_code           medical_department_code,
           d.department_name           medical_department_name,
           c.hospital_name             hospital_name,
           e.status_code               status_code,
           f.description               case_status,
           a.regist_username           regist_username,
           a.area_code
      FROM clm_case_report_info        a,
           clm_case_report_medicalinfo b,
           hospital_information        c,
           medical_department          d,
           clm_claim_info              e,
           clm_case_status_tbl         f
     WHERE a.claim_no = b.claim_no
       AND b.hospital_code = c.hospital_code(+)
       AND b.department_code = d.department_code(+)
       AND f.status_code = e.status_code
       AND e.claim_no = a.claim_no
       AND a.data_source = b.data_source
       AND a.data_source = '2'
       AND e.client_no = p_client_no
    UNION ALL
    SELECT a.claim_no                  claim_no,
           a.case_reporter_name        case_reporter_name,
           a.case_report_date          case_report_date,
           a.accident_date             accident_date,
           a.case_reporter_type        case_reporter_type,
           a.accident_addr             accident_addr,
           b.hospital_code             hospital_code,
           b.bed_no                    bed_no,
           b.department_code           medical_department_code,
           d.department_name           medical_department_name,
           c.hospital_name             hospital_name,
           e.status_code               status_code,
           f.description               case_status,
           a.regist_username           regist_username,
           a.area_code
      FROM clm_case_report_info        a,
           clm_case_report_medicalinfo b,
           hospital_information        c,
           medical_department          d,
           clm_claim_info              e,
           clm_case_status_tbl         f
     WHERE a.claim_no = b.claim_no
       AND b.hospital_code = c.hospital_code(+)
       AND b.department_code = d.department_code(+)
       AND f.status_code = e.status_code
       AND e.claim_no = a.claim_no
       AND a.data_source = b.data_source
       AND a.data_source = '1'
       AND e.client_no = p_client_no
       AND NOT EXISTS(SELECT 'X'
                        FROM clm_case_report_info t
                       WHERE t.claim_no = a.claim_no
                         AND t.data_source = '2')
     ORDER BY accident_date DESC;
  EXCEPTION
    WHEN OTHERS THEN
      DECLARE
         v_error_code      VARCHAR2 (4000);
         v_comments        VARCHAR2 (4000);
         v_program_id      VARCHAR2 (50) := 'query_case_by_client_no';
         v_error_message   VARCHAR2 (4000);
      BEGIN
         v_error_code := NULL;
         v_comments := NULL;
         pub_error_handle.exception_no_raise(v_program_id,
                                             v_error_code,
                                             v_comments,
                                             v_error_message);
         p_flag := 'N';
         p_message := 'query_case_by_client_no发生后台异常，具体原因请查看后台异常处理日志。';
      END;
  END query_case_by_client_no;