Java調用Oracle資料庫的分頁預存程序

 最近項目進度比較緩慢，初步研究了一下Oracle的預存程序，將代碼貼出來。

 

 首先先貼出來Oracle的分頁分頁預存程序代碼吧。

 1.這是包的代碼

 

 create or replace package DB_Oper is

  -- Author  : LIUBING
  -- Created : 2009-10-28 15:01:30
  -- Purpose : 測試Oracle的分頁預存程序
 
  -- Public type declarations
  type ref_DataSet is REF CURSOR;

  -- Public function and procedure declarations
  function GetCount(mTableName varchar2, mTerm varchar2) return number;
  procedure return_DataSet(
     mTableName in varchar2,    --表名
     mTerm in varchar2,         --條件
     mPageSize in number,       --每頁顯示記錄數
     mPageIndex in number,      --當前頁
     mOrderField in varchar2,   --排序欄位
     mOrderStyle in number,    --排序方式 0 升序 1 降序
     mTotalRecords out number,  --總記錄數
     mDateSet out ref_DataSet    --記錄
     );

end DB_Oper;

 

2.這是包體的代碼

 

 create or replace package body DB_Oper is

 -----  擷取記錄數  ------
  function GetCount(mTableName varchar2, mTerm varchar2) return number is
    i number;
    vSql varchar2(1000);
  begin
    i := 0;
    vSql := 'select count(*)  from ' || mTableName ;

    if length(mTerm) > 0 then
      vSql := vSql || ' where ' || mTerm;
    end if;

    execute IMMEDIATE  vSql into i;

    return(i);
  end;

  -----  資料分頁  ----------
  procedure return_DataSet(
     mTableName in varchar2,    --表名
     mTerm in varchar2,         --條件
     mPageSize in number,       --每頁顯示記錄數
     mPageIndex in number,      --當前頁
     mOrderField in varchar2,   --排序欄位
     mOrderStyle in number,    --排序方式  0 升序 1 降序
     mTotalRecords out number,  --總記錄數
     mDateSet out ref_DataSet    --記錄集
     ) is
  begin
    declare
      Invalid_Input Exception;
      vSql varchar2(1000);
      Start_page number;
      End_page number;
      n_PageIndex number;
      n_PageSize number;
      --mTotalRecords number;
    begin
      mTotalRecords := GetCount(trim(mTableName), trim(mTerm));
      vSql := 'select * from ' || mTableName;
      if length(trim(mTerm)) > 0 then
        vSql := vSql || ' where ' || trim(mTerm) ;
      end if;
      if length(trim(mOrderField)) > 0 then
       begin
          vSql := vSql || ' order by ' || trim(mOrderField);
          if morderStyle > 0 then
            vSql := vSql || ' desc ';
          end if;
       end;
      end if;

      --
      n_PageSize := mPageSize;
      if mPageSize <= 0 then
        n_PageSize := 10;
      end if;
      n_PageIndex := mPageIndex;
      if mPageIndex <= 0 then
        n_PageIndex := 1;
      end if;
      if mTotalRecords > 0 and n_PageIndex > 1 then
      begin
            --如果n_pageindex大於實際的頁數，則取實際頁數
        if n_PageIndex > round((mTotalRecords / n_PageSize) + 0.5) then
          n_PageIndex := round((mTotalRecords / n_PageSize) + 0.5);
        end if;
      end;
      end if;
      Start_page := (n_PageIndex - 1) * n_PageSize + 1;
      End_page := n_PageIndex * n_PageSize;
      vSql := 'SELECT * FROM (SELECT A.*, rownum r FROM ( ' || vSql || ' ) A WHERE rownum <= ' || End_page || ' ) B WHERE r >= '|| Start_page;

      Open mDateSet for vSql;

    exception
      When Invalid_Input Then
        open mDateSet for select null from dual;
    end;
  end return_DataSet;
end DB_Oper;

3.這是Java的測試代碼

 

  public class ConnToOracle {<br /> final static Logger logger = Logger.getLogger(ConnToOracle.class);<br /> public static Connection getConnection(String url, String userName, String password) {<br /> Connection conn = null;<br /> try {<br /> DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());<br /> conn = DriverManager.getConnection(url, userName, password);</p><p> System.out.println("Connection Starting.......");<br /> } catch (SQLException e) {<br /> logger.error("Connection error......");<br /> }</p><p> return conn;<br /> }</p><p> public static void select() throws Exception {<br /> Connection conn = null;<br /> try {<br /> conn = getConnection("jdbc:oracle:thin:@192.168.6.80:1521:C2SServer", "d1xn_cmc","d1xn_cmc");<br />// String call= "{call DB_Oper.return_DataSet(?,?,?,?,?,?,?,?)}";這種條用方式也是OK的<br /> String call= "begin DB_Oper.return_DataSet(?,?,?,?,?,?,?,?);end;";<br /> CallableStatement proc = conn.prepareCall(call);<br /> proc.setString(1, "game_goods");<br /> proc.setString(2, null);<br /> proc.setString(3, "20");<br /> proc.setString(4, "1");<br /> proc.setString(5, "n_ID");<br /> proc.setString(6, "0");<br /> proc.registerOutParameter(7, OracleTypes.NUMBER);//註冊返回的總記錄數的資料類型<br /> proc.registerOutParameter(8, OracleTypes.CURSOR);//註冊返回的資料結果集的資料類型<br /> proc.execute();</p><p> int a1 = proc.getInt(7);//獲得總的記錄集<br /> System.out.println(a1);<br /> ResultSet rs = (ResultSet)proc.getObject(8);//獲得返回的資料集<br /> while (rs.next()) {<br /> System.out.println(rs.getString(1));<br /> }<br /> } catch (SQLException e) {<br /> e.printStackTrace();<br /> } finally {<br /> if (conn != null) {<br /> conn.close();<br /> }<br /> }<br /> }<br />}<br />