如何實(shí)現(xiàn)PL/SQL中編寫Oracle數(shù)據(jù)庫(kù)分頁(yè)的存儲(chǔ)過程

這篇文章主要為大家展示了“如何實(shí)現(xiàn)PL/SQL中編寫Oracle數(shù)據(jù)庫(kù)分頁(yè)的存儲(chǔ)過程”，內(nèi)容簡(jiǎn)而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領(lǐng)大家一起研究并學(xué)習(xí)一下“如何實(shí)現(xiàn)PL/SQL中編寫Oracle數(shù)據(jù)庫(kù)分頁(yè)的存儲(chǔ)過程”這篇文章吧。

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

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

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

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

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

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

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

在Java中編寫代碼測(cè)試分頁(yè)，代碼如下：

package com.test.oracletest; 
import java.sql.CallableStatement; 
import java.sql.Connection; 
import java.sql.DriverManager; 
import java.sql.ResultSet; 
//測(cè)試分頁(yè) 
public class OraclePaging { 
  public static void main(String[] args) { 
    try { 
      // 加載驅(qū)動(dòng) 
      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ù)庫(kù)的存儲(chǔ)過程 
      CallableStatement cst = connection 
          .prepareCall("{call paging(?,?,?,?,?,?)}"); 
      // 給？賦值 
      cst.setString(1, "emp"); 
      cst.setInt(2, 6); 
      cst.setInt(3, 2); 
      // 注冊(cè)存儲(chǔ)過程的輸出項(xiàng) 
      cst.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER); 
      cst.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER); 
      cst.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR); 
      // 執(zhí)行 
      cst.execute(); 
      // 獲取輸出項(xiàng) 
      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("總頁(yè)數(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é)果如圖所示：

我們還可以通過修改模板最內(nèi)側(cè)的視圖來滿足其他一些基本的排序要求。Oracle的分頁(yè)思想就是如此。。#

以上是“如何實(shí)現(xiàn)PL/SQL中編寫Oracle數(shù)據(jù)庫(kù)分頁(yè)的存儲(chǔ)過程”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道！