PLSQL實現分頁

 分頁是任何一個網站(bbs,網上商城，blog)都會使用到的技術，因此學習pl/sql編程開發就一定要掌握該技術。如：

1.  編寫無傳回值的預存程序

     首先是掌握最簡單的預存程序，無傳回值的預存程序。

案例：現有一張表book，表結構如下：

請寫一個過程，可以向book表添加書，要求通過java程式調用該過程。
--in:表示這是一個輸入參數，預設為in
--out:表示一個輸出參數
預存程序代碼如下：

 create or replace procedure sp_pro7(spBookId in number,spbookName in varchar2,sppublishHouse in varchar2) is<br />begin<br /> insert into book values(spBookId,spbookName,sppublishHouse);<br />end;

在java中調用中調用該過程代碼如下：

 //調用一個無傳回值的過程<br />import java.sql.*;<br />public class Test2{<br /> public static void main(String[] args){</p><p> try{<br /> //1.載入驅動<br /> Class.forName("oracle.jdbc.driver.OracleDriver");<br /> //2.得到串連<br /> Connection ct = DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");</p><p> //3.建立CallableStatement<br /> CallableStatement cs = ct.prepareCall("{call sp_pro7(?,?,?)}");<br /> //4.給?賦值<br /> cs.setInt(1,10);<br /> cs.setString(2,"笑傲江湖");<br /> cs.setString(3,"人民出版社");<br /> //5.執行<br /> cs.execute();<br /> } catch(Exception e){<br /> e.printStackTrace();<br /> } finally{<br /> //6.關閉各個開啟的資源<br /> cs.close();<br /> ct.close();<br /> }<br /> }<br />}

     執行，記錄被加進去了。

2.  有傳回值的預存程序（非列表）
案例：編寫一個過程，可以輸入僱員的編號，返回該僱員的姓名。
案例擴張：編寫一個過程，可以輸入僱員的編號，返回該僱員的姓名、工資和崗位。

預存程序代碼如下：

--有輸入和輸出的預存程序<br />create or replace procedure sp_pro8<br />(spno in number, spName out varchar2) is<br />begin<br /> select ename into spName from emp where empno=spno;<br />end; 

java調用過程代碼如下：

import java.sql.*;<br />public class Test2{<br /> public static void main(String[] args){</p><p> try{<br /> //1.載入驅動<br /> Class.forName("oracle.jdbc.driver.OracleDriver");<br /> //2.得到串連<br /> Connection ct = DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");</p><p> //3.建立CallableStatement<br /> /*CallableStatement cs = ct.prepareCall("{call sp_pro7(?,?,?)}");<br /> //4.給?賦值<br /> cs.setInt(1,10);<br /> cs.setString(2,"笑傲江湖");<br /> cs.setString(3,"人民出版社");*/</p><p> //看看如何調用有傳回值的過程<br /> //建立CallableStatement<br /> /*CallableStatement cs = ct.prepareCall("{call sp_pro8(?,?)}");</p><p> //給第一個？賦值<br /> cs.setInt(1,7788);<br /> //給第二個？賦值<br /> cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);</p><p> //5.執行<br /> cs.execute();<br /> //取出傳回值,要注意？的順序<br /> String name=cs.getString(2);<br /> System.out.println("7788的名字"+name);<br /> } catch(Exception e){<br /> e.printStackTrace();<br /> } finally{<br /> //6.關閉各個開啟的資源<br /> cs.close();<br /> ct.close();<br /> }<br /> }<br />}  
運行，成功得出結果。

案例擴張：編寫一個過程，可以輸入僱員的編號，返回該僱員的姓名、工資和崗位。

--有輸入和輸出的預存程序<br />create or replace procedure sp_pro8<br />(spno in number, spName out varchar2,spSal out number,spJob out varchar2) is<br />begin<br /> select ename,sal,job into spName,spSal,spJob from emp where empno=spno;<br />end;

java調用過程代碼如下：

import java.sql.*;<br />public class Test2{<br /> public static void main(String[] args){</p><p> try{<br /> //1.載入驅動<br /> Class.forName("oracle.jdbc.driver.OracleDriver");<br /> //2.得到串連<br /> Connection ct = DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");</p><p> //3.建立CallableStatement<br /> /*CallableStatement cs = ct.prepareCall("{call sp_pro7(?,?,?)}");<br /> //4.給?賦值<br /> cs.setInt(1,10);<br /> cs.setString(2,"笑傲江湖");<br /> cs.setString(3,"人民出版社");*/</p><p> //看看如何調用有傳回值的過程<br /> //建立CallableStatement<br /> /*CallableStatement cs = ct.prepareCall("{call sp_pro8(?,?,?,?)}");</p><p> //給第一個？賦值<br /> cs.setInt(1,7788);<br /> //給第二個？賦值<br /> cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);<br /> //給第三個？賦值<br /> cs.registerOutParameter(3,oracle.jdbc.OracleTypes.DOUBLE);<br /> //給第四個？賦值<br /> cs.registerOutParameter(4,oracle.jdbc.OracleTypes.VARCHAR);</p><p> //5.執行<br /> cs.execute();<br /> //取出傳回值,要注意？的順序<br /> String name=cs.getString(2);<br /> String job=cs.getString(4);<br /> System.out.println("7788的名字"+name+" 工作："+job);<br /> } catch(Exception e){<br /> e.printStackTrace();<br /> } finally{<br /> //6.關閉各個開啟的資源<br /> cs.close();<br /> ct.close();<br /> }<br /> }<br />}

     運行，成功找出記錄。
3.  有傳回值的預存程序（列表[結果集])

     案例：編寫一個過程，輸入部門號，返回該部門所有僱員資訊。
對該題分析如下： 
     由於oracle預存程序沒有傳回值，它的所有傳回值都是通過out參數來替代的，列表同樣也不例外，但由於是集合，所以不能用一般的參數，必須要用pagkage了。所以要分兩部分：
(1). 建立一個包，在該包中，我定義類型test_cursor,是個遊標。代碼如下：
create or replace package testpackage as<br /> TYPE test_cursor is ref cursor;<br />end testpackage;

(2). 建立預存程序，代碼如下：

create or replace procedure sp_pro9(spNo in number,p_cursor out testpackage.test_cursor) is<br />begin<br /> open p_cursor for<br /> select * from emp where deptno = spNo;<br />end sp_pro9;

(3). 在java中調用該過程，代碼如下：

import java.sql.*;<br />public class Test2{<br /> public static void main(String[] args){</p><p> try{<br /> //1.載入驅動<br /> Class.forName("oracle.jdbc.driver.OracleDriver");<br /> //2.得到串連<br /> Connection ct = DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");</p><p> //看看如何調用有傳回值的過程<br /> //3.建立CallableStatement<br /> /*CallableStatement cs = ct.prepareCall("{call sp_pro9(?,?)}");</p><p> //4.給第？賦值<br /> cs.setInt(1,10);<br /> //給第二個？賦值<br /> cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);</p><p> //5.執行<br /> cs.execute();<br /> //得到結果集<br /> ResultSet rs=(ResultSet)cs.getObject(2);<br /> while(rs.next()){<br /> System.out.println(rs.getInt(1)+" "+rs.getString(2));<br /> }<br /> } catch(Exception e){<br /> e.printStackTrace();<br /> } finally{<br /> //6.關閉各個開啟的資源<br /> cs.close();<br /> ct.close();<br /> }<br /> }<br />}

運行，成功得出部門號是10的所有使用者。

4.  編寫分頁過程 
     要求，請大家編寫一個預存程序，要求可以輸入表名、每頁顯示記錄數、當前頁。返回總記錄數，總頁數，和返回的結果集。
(1). oracle中的分頁實現：

select t1.*, rownum rn from (select * from emp) t1 where rownum<=10;<br />--在分頁時，大家可以把下面的sql語句當做一個模板使用<br />select * from<br /> (select t1.*, rownum rn from (select * from emp) t1 where rownum<=10)<br />where rn>=6;

(2). 開發一個包

      建立一個包，在該包中，我定義類型test_cursor,是個遊標。代碼如下：

create or replace package testpackage as<br /> TYPE test_cursor is ref cursor;<br />end testpackage;<br />--開始編寫分頁的過程<br />create or replace procedure fenye<br /> (tableName in varchar2,<br /> Pagesize in number,--一頁顯示記錄數<br /> pageNow in number,<br /> myrows out number,--總記錄數<br /> myPageCount out number,--總頁數<br /> p_cursor out testpackage.test_cursor--返回的記錄集<br /> ) is<br />--定義部分<br />--定義sql語句 字串<br />v_sql varchar2(1000);<br />--定義兩個整數<br />v_begin number:=(pageNow-1)*Pagesize+1;<br />v_end number:=pageNow*Pagesize;<br />begin<br />--執行部分<br />v_sql:='select * from (select t1.*, rownum rn from (select * from '||tableName||') t1 where rownum<='||v_end||') where rn>='||v_begin;<br />--把遊標和sql關聯<br />open p_cursor for v_sql;<br />--計算myrows和myPageCount<br />--組織一個sql語句<br />v_sql:='select count(*) from '||tableName;<br />--執行sql,並把返回的值，賦給myrows；<br />execute inmediate v_sql into myrows;<br />--計算myPageCount<br />--if myrows%Pagesize=0 then這樣寫是錯的<br />if mod(myrows,Pagesize)=0 then<br /> myPageCount:=myrows/Pagesize;<br />else<br /> myPageCount:=myrows/Pagesize+1<br />end if;<br />--關閉遊標<br />close p_cursor;<br />end;

(3). 使用java測試該分頁過程，代碼如下：

import java.sql.*;<br />public class FenYe{<br /> public static void main(String[] args){</p><p> try{<br /> //1.載入驅動<br /> Class.forName("oracle.jdbc.driver.OracleDriver");<br /> //2.得到串連<br /> Connection ct = DriverManager.getConnection("jdbc:oracle:thin@127.0.0.1:1521:MYORA1","scott","m123");</p><p> //3.建立CallableStatement<br /> CallableStatement cs = ct.prepareCall("{call fenye(?,?,?,?,?,?)}");</p><p> //4.給第？賦值<br /> cs.seString(1,"emp");<br /> cs.setInt(2,5);<br /> cs.setInt(3,2);</p><p> //註冊總記錄數<br /> cs.registerOutParameter(4,oracle.jdbc.OracleTypes.INTEGER);<br /> //註冊總頁數<br /> cs.registerOutParameter(5,oracle.jdbc.OracleTypes.INTEGER);<br /> //註冊返回的結果集<br /> cs.registerOutParameter(6,oracle.jdbc.OracleTypes.CURSOR);</p><p> //5.執行<br /> cs.execute(); </p><p> //取出總記錄數 /這裡要注意，getInt(4)中4，是由該參數的位置決定的<br /> int rowNum=cs.getInt(4);</p><p> int pageCount = cs.getInt(5);<br /> ResultSet rs=(ResultSet)cs.getObject(6); </p><p> //顯示一下，看看對不對<br /> System.out.println("rowNum="+rowNum);<br /> System.out.println("總頁數="+pageCount);</p><p> while(rs.next()){<br /> System.out.println("編號："+rs.getInt(1)+" 名字："+rs.getString(2)+" 工資："+rs.getFloat(6));<br /> }<br /> } catch(Exception e){<br /> e.printStackTrace();<br /> } finally{<br /> //6.關閉各個開啟的資源<br /> cs.close();<br /> ct.close();<br /> }<br /> }<br />}

     運行，控制台輸出：
     rowNum=19
     總頁數：4
     編號：7369 名字：SMITH 工資：2850.0
     編號：7499 名字：ALLEN 工資：2450.0
     編號：7521 名字：WARD 工資：1562.0
     編號：7566 名字：JONES 工資：7200.0
     編號：7654 名字：MARTIN 工資：1500.0

新的需要，要求按照薪水從低到高排序，然後取出6-10。

代碼如下：

begin<br />--執行部分<br />v_sql:='select * from (select t1.*, rownum rn from (select * from '||tableName||' order by sal) t1 where rownum<='||v_end||') where rn>='||v_begin;

      重新執行一次procedure,java不用改變，運行，控制台輸出：
      rowNum=19
      總頁數：4
      編號：7900 名字：JAMES 工資：950.0
      編號：7876 名字：ADAMS 工資：1100.0
      編號：7521 名字：WARD 工資：1250.0
      編號：7654 名字：MARTIN 工資：1250.0 
      編號：7934 名字：MILLER 工資：1300.0