ibatis 调用oracle存储过程 返回cursor

  CREATE TABLE "USER_BASIC_INFO" 
   (	"USER_ID" NUMBER(10,0) NOT NULL ENABLE, 
	"USER_NAME" VARCHAR2(50 BYTE) NOT NULL ENABLE, 
	"REAL_NAME" VARCHAR2(50 BYTE), 
	"PASSWORD" VARCHAR2(32 BYTE) NOT NULL ENABLE, 
	"EMAIL" VARCHAR2(50 BYTE) NOT NULL ENABLE, 
	"USER_TYPE" VARCHAR2(50 BYTE), 
	"USER_STATE" VARCHAR2(50 BYTE), 
	"LOGIN_COUNT" NUMBER(8,0) DEFAULT 0, 
	"QUESTION" VARCHAR2(200 BYTE), 
	"ANSWER" VARCHAR2(200 BYTE), 
	"ACCOUNT" VARCHAR2(50 BYTE) DEFAULT -1, 
	"BRANCH_ID" VARCHAR2(50 BYTE) DEFAULT -1, 
	"CREATE_DATE" DATE DEFAULT sysdate, 
	"MODIFY_DATE" DATE DEFAULT sysdate, 
	"LAST_VISIT" DATE DEFAULT sysdate, 
	"USER_LEVEL" VARCHAR2(50 BYTE), 
	"EXPERIENCE_TIME" NUMBER(4,0) DEFAULT 0, 
	"CUSTID" NUMBER(20,0), 
	 CONSTRAINT "PK_USER_BASIC_INFO" PRIMARY KEY ("USER_ID")
   )

  CREATE TABLE "USER_EXTEND_INFO" 
   (	"USER_ID" NUMBER(10,0) NOT NULL ENABLE, 
	"SEX" VARCHAR2(50 BYTE), 
	"BIRTHDAY" DATE, 
	"RACE" VARCHAR2(50 BYTE), 
	"PROVINCE" VARCHAR2(50 BYTE), 
	"CITY" VARCHAR2(50 BYTE), 
	"CARD_TYPE" VARCHAR2(50 BYTE), 
	"CARD_ID" VARCHAR2(50 BYTE), 
	"TELEPHONE" VARCHAR2(50 BYTE), 
	"MOBILEPHONE" VARCHAR2(50 BYTE), 
	"FAX" VARCHAR2(50 BYTE), 
	"ADDRESS" VARCHAR2(100 BYTE), 
	"POST_CODE" VARCHAR2(50 BYTE), 
	"DEGREE" VARCHAR2(50 BYTE), 
	"VOCATION" VARCHAR2(50 BYTE), 
	"SOURCE" VARCHAR2(100 BYTE), 
	 CONSTRAINT "PK_USER_EXTEND_INFO" PRIMARY KEY ("USER_ID")
   )

create or replace PACKAGE Types
AS
    TYPE ref_cursor IS REF CURSOR;
END;

create or replace PROCEDURE QUERY_USER_BASIC_INFO
( P_USER_NAME IN USER_BASIC_INFO.USER_NAME % TYPE
, P_REAL_NAME IN USER_BASIC_INFO.REAL_NAME % TYPE
, P_USER_TYPE IN USER_BASIC_INFO.USER_TYPE % TYPE
, P_BRANCH_ID IN USER_BASIC_INFO.BRANCH_ID % TYPE
, P_BEGIN_DATE IN DATE
, P_END_DATE IN DATE
, P_BEGIN IN NUMBER
, P_END IN NUMBER
, P_RESULT OUT NUMBER
, P_CURSOR OUT TYPES.ref_cursor
) AS
v_sql varchar2(1000);
v_sqlCount varchar2(1000);
BEGIN
  	    v_sql:='SELECT * FROM (
	    	SELECT
	    		row_.*,
	    		ROWNUM rownum_
	    	FROM (
	    		SELECT e.*,f.SEX,
          f.BIRTHDAY,
          f.RACE,
          f.PROVINCE,
          f.CITY,
          f.CARD_TYPE,
          f.CARD_ID,
          f.TELEPHONE,
          f.MOBILEPHONE,
          f.FAX,
          f.ADDRESS,
          f.POST_CODE,
          f.DEGREE,
          f.VOCATION,
          f.SOURCE,g.key_name FROM USER_BASIC_INFO e left join user_extend_info f on e.user_id=f.user_id
          left join dictionary g on e.user_type=g.key_code and g.parent_id=11
        where 1=1';
        IF P_USER_NAME IS NOT NULL THEN
            v_sql := v_sql || 'and e.USER_NAME LIKE ' || '''' || P_USER_NAME || '''';
        ELSE IF P_REAL_NAME IS NOT NULL THEN
            v_sql := v_sql || 'and e.REAL_NAME LIKE ' || '''' || P_REAL_NAME || '''';
        ELSE IF P_USER_TYPE IS NOT NULL THEN
            v_sql := v_sql || 'and e.USER_TYPE = ' || '''' || P_USER_TYPE || '''';
        ELSE IF P_BRANCH_ID IS NOT NULL THEN
            v_sql := v_sql || 'and e.BRANCH_ID = ' || '''' || P_BRANCH_ID || '''';
        ELSE IF P_BEGIN_DATE IS NOT NULL THEN
            v_sql := v_sql || 'and e.CREATE_DATE between ' || P_BEGIN_DATE || 'and ' || P_END_DATE;
        END IF;
        END IF;
        END IF;
        END IF;
        END IF;
	    	v_sql := v_sql || ') row_ WHERE ROWNUM <= ' || P_END || ') WHERE rownum_ >' || P_BEGIN;
        --DBMS_OUTPUT.PUT_LINE(v_sql);
        open P_CURSOR for v_sql;

        v_sqlCount := 'SELECT count(1) FROM USER_BASIC_INFO e left join user_extend_info f on e.user_id=f.user_id';
        IF P_USER_NAME IS NOT NULL THEN
            v_sql := v_sql || 'and e.USER_NAME LIKE ' || '''' || P_USER_NAME || '''';
        ELSE IF P_REAL_NAME IS NOT NULL THEN
            v_sql := v_sql || 'and e.REAL_NAME LIKE ' || '''' || P_REAL_NAME || '''';
        ELSE IF P_USER_TYPE IS NOT NULL THEN
            v_sql := v_sql || 'and e.USER_TYPE = ' || '''' || P_USER_TYPE || '''';
        ELSE IF P_BRANCH_ID IS NOT NULL THEN
            v_sql := v_sql || 'and e.BRANCH_ID = ' || '''' || P_BRANCH_ID || '''';
        ELSE IF P_BEGIN_DATE IS NOT NULL THEN
            v_sql := v_sql || 'and e.CREATE_DATE between ' || P_BEGIN_DATE || 'and ' || P_END_DATE;
        END IF;
        END IF;
        END IF;
        END IF;
        END IF;
        EXECUTE IMMEDIATE v_sqlCount into P_RESULT;
        EXCEPTION
        WHEN NO_DATA_FOUND THEN
            P_RESULT := 9006;
        WHEN OTHERS THEN
            P_RESULT := 9005;
END QUERY_USER_BASIC_INFO;

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd">
		
<!-- iBatis SQL Map 文件 -->
<sqlMap namespace="com.cssweb.idm.user.pojo.UserBasicInfo">
	<typeAlias alias="userBasicInfo" type="com.cssweb.idm.user.pojo.UserBasicInfo"/>
		 <resultMap id="detailMap" class="userBasicInfo">
		<result property="userId" column="USER_ID" jdbcType="Long"/>
		<result property="userName" column="USER_NAME" jdbcType="String" nullValue=""/>
		<result property="realName" column="REAL_NAME" jdbcType="String" nullValue=""/>
		<result property="password" column="PASSWORD" jdbcType="String" nullValue=""/>
		<result property="email" column="EMAIL" jdbcType="String" nullValue=""/>
		<result property="userType" column="USER_TYPE" jdbcType="String" nullValue=""/>
		<result property="userState" column="USER_STATE" jdbcType="String" nullValue=""/>
		<result property="loginCount" column="LOGIN_COUNT" jdbcType="java.lang.Integer" nullValue="0"/>
		<result property="question" column="QUESTION" jdbcType="String" nullValue=""/>
		<result property="answer" column="ANSWER" jdbcType="String" nullValue=""/>
		<result property="account" column="ACCOUNT" jdbcType="java.lang.Integer" nullValue="0"/>
		<result property="branchId" column="BRANCH_ID" jdbcType="String" nullValue=""/>
		<result property="createDate" column="CREATE_DATE" jdbcType="java.util.Date" nullValue="1900/01/01 00:00:00"/>
		<result property="modifyDate" column="MODIFY_DATE" jdbcType="java.util.Date" nullValue="1900/01/01 00:00:00"/>
		<result property="lastVisit" column="LAST_VISIT" jdbcType="java.util.Date" nullValue="1900/01/01 00:00:00"/>
		<result property="userLevel" column="USER_LEVEL" jdbcType="String" nullValue=""/>
		<result property="experienceTime" column="EXPERIENCE_TIME" jdbcType="java.lang.Integer" nullValue="0"/>
		<result property="custId" column="CUSTID" jdbcType="Long" nullValue="0"/>
		<result property="sex" column="SEX" jdbcType="String" nullValue=""/>
		<result property="birthday" column="BIRTHDAY" jdbcType="java.util.Date" nullValue="1900/01/01 00:00:00"/>
		<result property="race" column="RACE" jdbcType="String" nullValue=""/>
		<result property="province" column="PROVINCE" jdbcType="String" nullValue=""/>
		<result property="city" column="CITY" jdbcType="String" nullValue=""/>
		<result property="cardType" column="CARD_TYPE" jdbcType="String" nullValue=""/>
		<result property="cardId" column="CARD_ID" jdbcType="String" nullValue=""/>
		<result property="telephone" column="TELEPHONE" jdbcType="String" nullValue=""/>
		<result property="mobilePhone" column="MOBILEPHONE" jdbcType="String" nullValue=""/>
		<result property="fax" column="FAX" jdbcType="String" nullValue=""/>
		<result property="address" column="ADDRESS" jdbcType="String" nullValue=""/>
		<result property="postCode" column="POST_CODE" jdbcType="String" nullValue=""/>
		<result property="degree" column="DEGREE" jdbcType="String" nullValue=""/>
		<result property="vocation" column="VOCATION" jdbcType="String" nullValue=""/>
		<result property="source" column="SOURCE" jdbcType="String" nullValue=""/>
		<result property="typeName" column="KEY_NAME" jdbcType="String" nullValue=""/>
	 </resultMap>
	<parameterMap id="procQueryMap" class="java.util.HashMap" >   
		<parameter property="P_USER_NAME" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
		<parameter property="P_REAL_NAME" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
		<parameter property="P_USER_TYPE" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
		<parameter property="P_BRANCH_ID" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
		<parameter property="P_BEGIN_DATE" jdbcType="DATE" javaType="java.util.Date" mode="IN"/>
		<parameter property="P_END_DATE" jdbcType="DATE" javaType="java.util.Date" mode="IN"/>
		<parameter property="P_BEGIN" jdbcType="INTEGER" javaType="java.lang.Integer" mode="IN"/>
		<parameter property="P_END" jdbcType="INTEGER" javaType="java.lang.Integer" mode="IN"/>
		<parameter property="P_RESULT" jdbcType="INTEGER" javaType="java.lang.Integer" mode="OUT"/>
		<parameter property="P_CURSOR" javaType="java.sql.ResultSet" jdbcType="ORACLECURSOR" mode="OUT"/>
	</parameterMap>
	<procedure id="queryUserBasicInfoProc" parameterMap="procQueryMap" resultMap="detailMap">   
		{call QUERY_USER_BASIC_INFO (?,?,?,?,?,?,?,?,?,?)}   
	</procedure>  	
</sqlMap>

	/**
	 * <p>this method is used by people get user list.
	 * @param map
	 * @return List
	 * @throws DataAccessException
	 */
	@SuppressWarnings("unchecked")
	public List<UserBasicInfo> getList(Map<String, Object> map) 
			throws DataAccessException {
		return this.getSqlMapClientTemplate().queryForList(statementName +  ".queryUserBasicInfoProc", map);
	}