现在的位置: 首页 > 综合 > 正文

Oracle存储过程（分页、模糊查找、排序、按字段区间查找）etc.以及JAVA代码

2014年08月16日 ⁄ 综合 ⁄ 共 5117字 ⁄ 字号 小 中 大 ⁄ 评论关闭
--
先创建包
CREATE
OR REPLACE PACKAGE mypackage AS TYPE mycursor IS REF CURSOR;
END;

 

--创建分页存储过程
CREATE
OR REPLACE PROCEDURE splitpage
(
v_tableName
IN VARCHAR2,
v_pageSize
IN NUMBER,
v_pageNow
IN NUMBER,
v_myTotalRows
OUT NUMBER,
v_myTotalPageCount
OUT NUMBER,
v_cursor
OUT mypackage.mycursor
)
IS
v_sql
VARCHAR2(1000);
v_begin
NUMBER:=(v_pageNow-1)*v_pageSize+1;
v_end  
NUMBER:=v_pageNow*v_pageSize;
 
BEGIN
  v_sql:='select
* from(
                 select
rownum rn,a1.* from(
                   select
* from ' ||v_tableName||
                 ')
a1 where rownum<='||v_end||
          ')
where rn>='||v_begin||'';
  OPEN
v_cursor FOR v_sql;
  v_sql:='select
count(*) from ' ||v_tableName||'';
  EXECUTE
IMMEDIATE v_sql INTO v_myTotalRows;
  IF
MOD(v_myTotalRows,v_pageSize)=0 THEN
    v_myTotalPageCount:=v_myTotalRows/v_pageSize;
  ELSE
    v_myTotalPageCount:=v_myTotalRows/v_pageSize+1;
  END
IF;
END;

　　

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
public YOURClass
getSplitPage(int targetPage,
int eachpagenum){
        Connection
conn = DBConnection.getConn();//获取Oraccle
的Connection
                 
                //参数位置要与存储过程对应，其余用法跟pstmt类似
        String
sql = "{call
splitpage(?,?,?,?,?,?)}";
        try{
            cs
= conn.prepareCall(sql);
            cs.setString(1,"tablename");
//
the name of db's table
            cs.setInt(2,eachpagenum);
//
Each page record numbers
            cs.setInt(3,targetPage);
//
Target page number
 
            //
register the output
            cs.registerOutParameter(4,OracleTypes.NUMBER);
            cs.registerOutParameter(5,OracleTypes.NUMBER);
            cs.registerOutParameter(6,OracleTypes.CURSOR);
 
            cs.execute();
            //
get the value from CallableStatement
            totalRowsCount
= cs.getInt(4);
//
获取总记录数
            totalPagesCount
= cs.getInt(5);//
获取总页数
            ResultSet
rs = (ResultSet) cs.getObject(6);
 
            while(rs.next()){
                //
从rs中读取数据
            }
            return YOURClass;
        }catch (SQLException
e){
            e.printStackTrace();
        }
        return null;
    }

　　下面就不给出JAVA代码了，因为都是类似的。用法跟pstmt一样，只是多了注册输出变量。

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
--带模糊查询的分页
CREATE
OR REPLACE PROCEDURE splitpagefuzzy
(
v_tableName
IN VARCHAR2,
v_colName
IN VARCHAR2,
v_keyword
IN VARCHAR2,
v_pageSize
IN NUMBER,
v_pageNow
IN NUMBER,
v_myTotalRows
OUT NUMBER,
v_myTotalPageCount
OUT NUMBER,
v_cursor
OUT mypackage.mycursor
)
IS
v_sql
VARCHAR2(1000);
v_begin
NUMBER:=(v_pageNow-1)*v_pageSize+1;
v_end  
NUMBER:=v_pageNow*v_pageSize;
 
BEGIN
  v_sql:='select
* from(
                 select
rownum rn,a1.* from(
                   select
* from '
||v_tableName||' where
'||v_colName||' like
''%'||
v_keyword ||'%'' '||
                 ')
a1 where rownum<='||v_end||
          ')
where rn>='||v_begin||'';
  OPEN
v_cursor FOR v_sql;
  v_sql:='select
count(*) from ' ||v_tableName||'';
  EXECUTE
IMMEDIATE v_sql INTO v_myTotalRows;
  IF
MOD(v_myTotalRows,v_pageSize)=0 THEN
    v_myTotalPageCount:=v_myTotalRows/v_pageSize;
  ELSE
    v_myTotalPageCount:=v_myTotalRows/v_pageSize+1;
  END
IF;
END;

　　

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
--带模糊查询带排序的分页
CREATE
OR REPLACE PROCEDURE splitpagefuzzyorder
(
v_tableName
IN VARCHAR2,
v_colName
IN VARCHAR2,
v_keyword
IN VARCHAR2,
v_colOrder
IN VARCHAR2,
v_orderType
IN VARCHAR2,
v_pageSize
IN NUMBER,
v_pageNow
IN NUMBER,
v_myTotalRows
OUT NUMBER,
v_myTotalPageCount
OUT NUMBER,
v_cursor
OUT mypackage.mycursor
)
IS
v_sql
VARCHAR2(1000);
v_begin
NUMBER:=(v_pageNow-1)*v_pageSize+1;
v_end  
NUMBER:=v_pageNow*v_pageSize;
 
BEGIN
  v_sql:='select
* from(
                 select
rownum rn,a1.* from(
                   select
* from '
||v_tableName||' where
'||v_colName||' like
''%'||
v_keyword ||'%'' '||
                   '
order by ' ||v_colOrder||'
'||v_orderType||
                 ')
a1 where rownum<='||v_end||
          ')
where rn>='||v_begin||'';
  OPEN
v_cursor FOR v_sql;
  v_sql:='select
count(*) from ' ||v_tableName||'';
  EXECUTE
IMMEDIATE v_sql INTO v_myTotalRows;
  IF
MOD(v_myTotalRows,v_pageSize)=0 THEN
    v_myTotalPageCount:=v_myTotalRows/v_pageSize;
  ELSE
    v_myTotalPageCount:=v_myTotalRows/v_pageSize+1;
  END
IF;
END;

　　

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
--带模糊查询带排序带时间段的分页
CREATE
OR REPLACE PROCEDURE splitpagefuzzyorderdate
(
v_tableName
IN VARCHAR2,
v_colName
IN VARCHAR2,
v_keyword
IN VARCHAR2,
v_colOrder
IN VARCHAR2,
v_orderType
IN VARCHAR2,
v_colDate
IN DATE;
v_dateBegin
IN DATE,
v_dateEnd
IN DATE,
v_pageSize
IN NUMBER,
v_pageNow
IN NUMBER,
v_myTotalRows
OUT NUMBER,
v_myTotalPageCount
OUT NUMBER,
v_cursor
OUT mypackage.mycursor
)
IS
v_sql
VARCHAR2(1000);
v_begin
NUMBER:=(v_pageNow-1)*v_pageSize+1;
v_end  
NUMBER:=v_pageNow*v_pageSize;
 
BEGIN
  v_sql:='select
* from(
                 select
rownum rn,a1.* from(
                   select
* from '
||v_tableName||' where
'||v_colName||' like
''%'||
v_keyword ||'%'' '||
                   '
and ' v_colDate||'
between ' v_dateBegin
||'
and '||v_dateEnd
||
                   '
order by ' ||v_colOrder||'
'||
                 ')
a1 where rownum<='||v_end||
          ')
where rn>='||v_begin||'';
  OPEN
v_cursor FOR v_sql;
  v_sql:='select
count(*) from ' ||v_tableName||'';
  EXECUTE
IMMEDIATE v_sql INTO v_myTotalRows;
  IF
MOD(v_myTotalRows,v_pageSize)=0 THEN
    v_myTotalPageCount:=v_myTotalRows/v_pageSize;
  ELSE
    v_myTotalPageCount:=v_myTotalRows/v_pageSize+1;
  END
IF;
END;

　　这几个下来想必大家也都清楚该怎么做了。如果需要添加什么条件，只需要在最内层字句中添加相应

返回
【上篇】
【下篇】

作者:

抱歉!评论已关闭.

返回首页

Copyright © 2013-2018 学步园  保留所有权利.
软文销售 QQ客服：2265327166 点击这里给我发消息（其他合作也可洽谈）
必威体育
必威电竞