PL/SQL中編寫Oracle數(shù)據(jù)庫分頁的存儲過程

2024-08-29 14:00:14

字體：大中小

供稿：網(wǎng)友

其實 Oracle數(shù)據(jù)庫的分頁還是比較容易理解的。此文以oracle數(shù)據(jù)庫中的SCOTT用戶的EMP表為例，用PL/SQL Developer編寫一個分頁存儲過程，要求是：可以輸入表名，每頁顯示記錄數(shù)，當前頁，返回總記錄數(shù)，總頁數(shù)和返回的結(jié)果集。

由于需要返回查詢出來的結(jié)果集，需要在PL/SQL中創(chuàng)建一個package，這個包里面定義一個refcursor類型，用于記錄sql語句查詢出來的結(jié)果集。創(chuàng)建包的代碼如下：

create or replace package pagingPackage as type paging_cursor is ref cursor; end pagingPackage;

接下來開始Oracle的分頁過程，我們可以用select emp.*,rownum from emp;來顯示地表示出每行的行標。然后可以根據(jù)行標對內(nèi)容進行分頁，下面這個SQL語句可以作為Oracle分頁的模板。

select * from  (select t1.*,rownum rn from (select * from emp) t1 where rownum<=12) where rn>=8;

有了上面的refcursor類型和分頁模板，下面開始編寫分頁的存儲過程，代碼如下：

create procedure paging (tableName in varchar2 ,--表名 pageSizes in number,--每頁顯示記錄數(shù) pageNow in number,--當前頁 rowNums out number,--總記錄數(shù) pageNum out number,--總頁數(shù) paging_cursor out pagingPackage.paging_cursor) is  --定義部分 --定義sql語句，字符串 v_sql varchar2(1000); --定義兩個整數(shù)，用于表示每頁的開始和結(jié)束記錄數(shù) v_begin number:=(pageNow-1)*pageSizes+1; v_end number:=pageNow*pageSizes; begin  --執(zhí)行部分  v_sql:='select * from (select t1.*,rownum rn from (select * from '||tableName||') t1 where rownum<='||v_end||') where rn>='||v_begin;  --把游標和sql語句關(guān)聯(lián)  open paging_cursor for v_sql;  --計算rowNums和pageNum  --組織一個sql語句  v_sql:='select count(*) from '||tableName;  --執(zhí)行該sql語句,并賦給rowNums  execute immediate v_sql into rowNums;  --計算pageNum  if mod(rowNums,pageSizes)=0 then   pageNum := rowNums/pageSizes;   else    pageNum := rowNums/pageSizes+1;    end if;  end;

在Java中編寫代碼測試分頁，代碼如下：

package com.test.oracletest; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; //測試分頁 public class OraclePaging {   public static void main(String[] args) {     try {       // 加載驅(qū)動       Class.forName("oracle.jdbc.driver.OracleDriver");       // 獲取連接       Connection connection = DriverManager.getConnection(           "jdbc:oracle:thin:@127.0.0.1:1521:ORCL", "SCOTT", "tiger");       // 創(chuàng)建CallableStatement，調(diào)取數(shù)據(jù)庫的存儲過程       CallableStatement cst = connection           .prepareCall("{call paging(?,?,?,?,?,?)}");       // 給？賦值       cst.setString(1, "emp");       cst.setInt(2, 6);       cst.setInt(3, 2);       // 注冊存儲過程的輸出項       cst.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER);       cst.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);       cst.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);       // 執(zhí)行       cst.execute();       // 獲取輸出項       int rowNums = cst.getInt(4);       int pageNum = cst.getInt(5);       ResultSet rs = (ResultSet) cst.getObject(6);       System.out.println("總記錄數(shù)rowNums = " + rowNums);       System.out.println("總頁數(shù)pageNum = " + pageNum);       System.out.println("EMPNO" + '/t' + "ENAME" + '/t' + "ROWNUM");       while (rs.next()) {         System.out.println(rs.getInt("EMPNO") + " " + '/t'             + rs.getString("ENAME") + '/t' + rs.getInt("RN"));       }     } catch (Exception e) {       e.printStackTrace();     } finally {       // 關(guān)閉資源     }   } }

執(zhí)行的結(jié)果如圖所示：

oracle分頁存儲過程,sql,分頁存儲過程