oracle儲存過程分頁程式碼

oracle儲存過程分頁程式碼

/*******儲存過程分頁程式碼**********/
--包頭
create or replace package pck_my is
type c_my is ref cursor;
procedure page_moed(
  v_table in varchar2, --表名
  current_page in out number,--當前頁
  pageSize in out number,--頁行數
  total out number,--總行數
  countPage out number,--總頁數
  c_cursor out pck_my.c_my--遊標
  );
end pck_my;

--body
create or replace package body pck_my as
procedure page_moed(
  v_table in varchar2,
  current_page in out number,
  pageSize in out number,
  total out number,
  countPage out number,
  c_cursor out pck_my.c_my
  )is
 
v_sql varchar2(1000);
v_max number;
v_min number;
e_table exception;

begin  
--判斷引數
if v_table is null then
  raise e_table;
  --return;
  end if;
if current_page is null then
current_page:=1;
end if;
if pageSize<=0 then
pageSize:=5;
end if; 
--計算 最大行 最小行
v_max:=(current_page+1)*pageSize;
v_min:=current_page*pageSize;

--獲取資料
v_sql:= 'select *
  from (select filminfo.*, rownum as t from '|| v_table ||' where rownum <='|| v_max||')
 where t > ' ||v_min;
open c_cursor for v_sql;

--計算總行數
v_sql:='select count(*)  from '|| v_table;
execute immediate v_sql into total;

--計算總頁數
if mod(total,pageSize)=0 then
  countPage:=total/pageSize;
else
  countPage:=total/pageSize+1;
end if;

--exception
exception
  when e_table then
   dbms_output.put_line('表名不能為空');

end;
end pck_my;

--  exet
select * from filminfo

java測試程式碼

package com.rui;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Pckage {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		Connection con;
		ResultSet rs;
		CallableStatement cs;
		
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ABC","tenement","rui");

		String sql="{call pck_my.page_moed(?,?,?,?,?,?)}";
		cs=con.prepareCall(sql);
		//指定型別
		
		/* v_table in varchar2,
		  current_page in out number,
		  pageSize in out number,
		  total out number,
		  countPage out number,
		  c_cursor out pck_my.c_my
		*/
		//cs.setString(1, null);
		cs.setString(1, "filminfo");
		cs.setInt(2, 3);
		cs.setInt(3,5);
		cs.registerOutParameter(4,oracle.jdbc.OracleTypes.NUMBER);
		cs.registerOutParameter(5, oracle.jdbc.OracleTypes.NUMBER);
		cs.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);
		cs.execute();
		
		int total=cs.getInt(4);//總行數
		int countPage=cs.getInt(5);//總頁數
		rs=(ResultSet)cs.getObject(6);//result
		System.out.println("總行數:"+total+"\t總頁數"+countPage);
		System.out.println("------------------------------------");
		while(rs.next()){
	   System.out.println("FILMNAME:"+rs.getString("FILMNAME")+"\tFILMID:"+rs.getInt("FILMID"));
			
		}
		
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

}