ibatis 调用oracle存储过程返回cursor

最新推荐文章于 2024-08-31 13:09:07 发布

iteye_5082

最新推荐文章于 2024-08-31 13:09:07 发布

阅读量102

点赞数

分类专栏： java web 文章标签： iBATIS Oracle SQL F# .net

本文链接：https://blog.csdn.net/iteye_5082/article/details/81799968

版权

java web 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

# 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")
# )

1. CREATE TABLE "USER_EXTEND_INFO"
2. ( "USER_ID" NUMBER(10,0) NOT NULL ENABLE,
3. "SEX" VARCHAR2(50 BYTE),
4. "BIRTHDAY" DATE,
5. "RACE" VARCHAR2(50 BYTE),
6. "PROVINCE" VARCHAR2(50 BYTE),
7. "CITY" VARCHAR2(50 BYTE),
8. "CARD_TYPE" VARCHAR2(50 BYTE),
9. "CARD_ID" VARCHAR2(50 BYTE),
10. "TELEPHONE" VARCHAR2(50 BYTE),
11. "MOBILEPHONE" VARCHAR2(50 BYTE),
12. "FAX" VARCHAR2(50 BYTE),
13. "ADDRESS" VARCHAR2(100 BYTE),
14. "POST_CODE" VARCHAR2(50 BYTE),
15. "DEGREE" VARCHAR2(50 BYTE),
16. "VOCATION" VARCHAR2(50 BYTE),
17. "SOURCE" VARCHAR2(100 BYTE),
18. CONSTRAINT "PK_USER_EXTEND_INFO" PRIMARY KEY ("USER_ID")
19. )

1. create or replace PACKAGE Types
2. AS
3. TYPE ref_cursor IS REF CURSOR;
4. END;

1. create or replace PROCEDURE QUERY_USER_BASIC_INFO
2. ( P_USER_NAME IN USER_BASIC_INFO.USER_NAME % TYPE
3. , P_REAL_NAME IN USER_BASIC_INFO.REAL_NAME % TYPE
4. , P_USER_TYPE IN USER_BASIC_INFO.USER_TYPE % TYPE
5. , P_BRANCH_ID IN USER_BASIC_INFO.BRANCH_ID % TYPE
6. , P_BEGIN_DATE IN DATE
7. , P_END_DATE IN DATE
8. , P_BEGIN IN NUMBER
9. , P_END IN NUMBER
10. , P_RESULT OUT NUMBER
11. , P_CURSOR OUT TYPES.ref_cursor
12. ) AS
13. v_sql varchar2(1000);
14. v_sqlCount varchar2(1000);
15. BEGIN
16. v_sql:='SELECT * FROM (
17. SELECT
18. row_.*,
19. ROWNUM rownum_
20. FROM (
21. SELECT e.*,f.SEX,
22. f.BIRTHDAY,
23. f.RACE,
24. f.PROVINCE,
25. f.CITY,
26. f.CARD_TYPE,
27. f.CARD_ID,
28. f.TELEPHONE,
29. f.MOBILEPHONE,
30. f.FAX,
31. f.ADDRESS,
32. f.POST_CODE,
33. f.DEGREE,
34. f.VOCATION,
35. f.SOURCE,g.key_name FROM USER_BASIC_INFO e left join user_extend_info f on e.user_id=f.user_id
36. left join dictionary g on e.user_type=g.key_code and g.parent_id=11
37. where 1=1';
38. IF P_USER_NAME IS NOT NULL THEN
39. v_sql := v_sql || 'and e.USER_NAME LIKE ' || '''' || P_USER_NAME || '''';
40. ELSE IF P_REAL_NAME IS NOT NULL THEN
41. v_sql := v_sql || 'and e.REAL_NAME LIKE ' || '''' || P_REAL_NAME || '''';
42. ELSE IF P_USER_TYPE IS NOT NULL THEN
43. v_sql := v_sql || 'and e.USER_TYPE = ' || '''' || P_USER_TYPE || '''';
44. ELSE IF P_BRANCH_ID IS NOT NULL THEN
45. v_sql := v_sql || 'and e.BRANCH_ID = ' || '''' || P_BRANCH_ID || '''';
46. ELSE IF P_BEGIN_DATE IS NOT NULL THEN
47. v_sql := v_sql || 'and e.CREATE_DATE between ' || P_BEGIN_DATE || 'and ' || P_END_DATE;
48. END IF;
49. END IF;
50. END IF;
51. END IF;
52. END IF;
53. v_sql := v_sql || ') row_ WHERE ROWNUM <= ' || P_END || ') WHERE rownum_ >' || P_BEGIN;
54. --DBMS_OUTPUT.PUT_LINE(v_sql);
55. open P_CURSOR for v_sql;
56.
57. v_sqlCount := 'SELECT count(1) FROM USER_BASIC_INFO e left join user_extend_info f on e.user_id=f.user_id';
58. IF P_USER_NAME IS NOT NULL THEN
59. v_sql := v_sql || 'and e.USER_NAME LIKE ' || '''' || P_USER_NAME || '''';
60. ELSE IF P_REAL_NAME IS NOT NULL THEN
61. v_sql := v_sql || 'and e.REAL_NAME LIKE ' || '''' || P_REAL_NAME || '''';
62. ELSE IF P_USER_TYPE IS NOT NULL THEN
63. v_sql := v_sql || 'and e.USER_TYPE = ' || '''' || P_USER_TYPE || '''';
64. ELSE IF P_BRANCH_ID IS NOT NULL THEN
65. v_sql := v_sql || 'and e.BRANCH_ID = ' || '''' || P_BRANCH_ID || '''';
66. ELSE IF P_BEGIN_DATE IS NOT NULL THEN
67. v_sql := v_sql || 'and e.CREATE_DATE between ' || P_BEGIN_DATE || 'and ' || P_END_DATE;
68. END IF;
69. END IF;
70. END IF;
71. END IF;
72. END IF;
73. EXECUTE IMMEDIATE v_sqlCount into P_RESULT;
74. EXCEPTION
75. WHEN NO_DATA_FOUND THEN
76. P_RESULT := 9006;
77. WHEN OTHERS THEN
78. P_RESULT := 9005;
79. END QUERY_USER_BASIC_INFO;

1. <?xml version="1.0" encoding="UTF-8"?>
2. <!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd">
3.
4. 
5. <sqlMap namespace="com.cssweb.idm.user.pojo.UserBasicInfo">
6. <typeAlias alias="userBasicInfo" type="com.cssweb.idm.user.pojo.UserBasicInfo"/>
7. <resultMap id="detailMap" class="userBasicInfo">
8. <result property="userId" column="USER_ID" jdbcType="Long"/>
9. <result property="userName" column="USER_NAME" jdbcType="String" nullValue=""/>
10. <result property="realName" column="REAL_NAME" jdbcType="String" nullValue=""/>
11. <result property="password" column="PASSWORD" jdbcType="String" nullValue=""/>
12. <result property="email" column="EMAIL" jdbcType="String" nullValue=""/>
13. <result property="userType" column="USER_TYPE" jdbcType="String" nullValue=""/>
14. <result property="userState" column="USER_STATE" jdbcType="String" nullValue=""/>
15. <result property="loginCount" column="LOGIN_COUNT" jdbcType="java.lang.Integer" nullValue="0"/>
16. <result property="question" column="QUESTION" jdbcType="String" nullValue=""/>
17. <result property="answer" column="ANSWER" jdbcType="String" nullValue=""/>
18. <result property="account" column="ACCOUNT" jdbcType="java.lang.Integer" nullValue="0"/>
19. <result property="branchId" column="BRANCH_ID" jdbcType="String" nullValue=""/>
20. <result property="createDate" column="CREATE_DATE" jdbcType="java.util.Date" nullValue="1900/01/01 00:00:00"/>
21. <result property="modifyDate" column="MODIFY_DATE" jdbcType="java.util.Date" nullValue="1900/01/01 00:00:00"/>
22. <result property="lastVisit" column="LAST_VISIT" jdbcType="java.util.Date" nullValue="1900/01/01 00:00:00"/>
23. <result property="userLevel" column="USER_LEVEL" jdbcType="String" nullValue=""/>
24. <result property="experienceTime" column="EXPERIENCE_TIME" jdbcType="java.lang.Integer" nullValue="0"/>
25. <result property="custId" column="CUSTID" jdbcType="Long" nullValue="0"/>
26. <result property="sex" column="SEX" jdbcType="String" nullValue=""/>
27. <result property="birthday" column="BIRTHDAY" jdbcType="java.util.Date" nullValue="1900/01/01 00:00:00"/>
28. <result property="race" column="RACE" jdbcType="String" nullValue=""/>
29. <result property="province" column="PROVINCE" jdbcType="String" nullValue=""/>
30. <result property="city" column="CITY" jdbcType="String" nullValue=""/>
31. <result property="cardType" column="CARD_TYPE" jdbcType="String" nullValue=""/>
32. <result property="cardId" column="CARD_ID" jdbcType="String" nullValue=""/>
33. <result property="telephone" column="TELEPHONE" jdbcType="String" nullValue=""/>
34. <result property="mobilePhone" column="MOBILEPHONE" jdbcType="String" nullValue=""/>
35. <result property="fax" column="FAX" jdbcType="String" nullValue=""/>
36. <result property="address" column="ADDRESS" jdbcType="String" nullValue=""/>
37. <result property="postCode" column="POST_CODE" jdbcType="String" nullValue=""/>
38. <result property="degree" column="DEGREE" jdbcType="String" nullValue=""/>
39. <result property="vocation" column="VOCATION" jdbcType="String" nullValue=""/>
40. <result property="source" column="SOURCE" jdbcType="String" nullValue=""/>
41. <result property="typeName" column="KEY_NAME" jdbcType="String" nullValue=""/>
42. </resultMap>
43. <parameterMap id="procQueryMap" class="java.util.HashMap" >
44. <parameter property="P_USER_NAME" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
45. <parameter property="P_REAL_NAME" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
46. <parameter property="P_USER_TYPE" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
47. <parameter property="P_BRANCH_ID" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
48. <parameter property="P_BEGIN_DATE" jdbcType="DATE" javaType="java.util.Date" mode="IN"/>
49. <parameter property="P_END_DATE" jdbcType="DATE" javaType="java.util.Date" mode="IN"/>
50. <parameter property="P_BEGIN" jdbcType="INTEGER" javaType="java.lang.Integer" mode="IN"/>
51. <parameter property="P_END" jdbcType="INTEGER" javaType="java.lang.Integer" mode="IN"/>
52. <parameter property="P_RESULT" jdbcType="INTEGER" javaType="java.lang.Integer" mode="OUT"/>
53. <parameter property="P_CURSOR" javaType="java.sql.ResultSet" jdbcType="ORACLECURSOR" mode="OUT"/>
54. </parameterMap>
55. <procedure id="queryUserBasicInfoProc" parameterMap="procQueryMap" resultMap="detailMap">
56. {call QUERY_USER_BASIC_INFO (?,?,?,?,?,?,?,?,?,?)}
57. </procedure>
58. </sqlMap>

1. /**
2. * <p>this method is used by people get user list.
3. * @param map
4. * @return List
5. * @throws DataAccessException
6. */
7. @SuppressWarnings("unchecked")
8. public List<UserBasicInfo> getList(Map<String, Object> map)
9. throws DataAccessException {
10. return this.getSqlMapClientTemplate().queryForList(statementName + ".queryUserBasicInfoProc", map);
11. }

出自：http://blog.csdn.net/xiaojunhu/archive/2009/07/24/4375976.aspx

iteye_5082

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
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(3...
复制链接

扫一扫