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;

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