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

-- 先创建包 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;

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