ibatis 调用oracle存储过程 返回cursor

nx404h6sj9

view plaincopy to clipboardprint?
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")  
  )  

view plaincopy to clipboardprint?
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")  
  )  

view plaincopy to clipboardprint?
create or replace PACKAGE Types  
AS  
    TYPE ref_cursor IS REF CURSOR;  
END;  

view plaincopy to clipboardprint?
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;  

view plaincopy to clipboardprint?
<?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>  

view plaincopy to clipboardprint?
/**
* <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);  
}