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); }
扫一扫
1783
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
