java調用Oracle分頁預存程序

標籤：

Java程式

package com.test;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Types;import com.jdbc.BaseJdbcDAO;import oracle.jdbc.OracleTypes;public class Test {/*** @param args*/public static void main(String[] args) {Connection conn = BaseJdbcDAO.getConnection2("com");String sqlSel = "SELECT * FROM POSITION";String sqlCount = "SELECT COUNT(*) FROM POSITION";String sql = "{ call SP_PAGE(?,?,?,?,?,?,?) }";ResultSet rs = null;try {CallableStatement call = conn.prepareCall(sql);call.setInt(1, 20);call.setInt(2, 100);call.setString(3, sqlSel);call.setString(4, sqlCount);call.registerOutParameter(5, Types.INTEGER);call.registerOutParameter(6, Types.INTEGER);call.registerOutParameter(7, OracleTypes.CURSOR);call.execute();// 取出結果集int pageCount = call.getInt(5);//總頁數int total = call.getInt(6);//總記錄數rs = (ResultSet) call.getObject(7);//資訊列表集System.out.println("頁總數：" + pageCount);System.out.println("記錄總數：" + total);int i = 1;while (rs.next()) {System.out.println(">>" + i + "號碼：" + rs.getString(2) + "名稱："+ rs.getString(3) + "序號:" + rs.getInt(1));i++;}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}/* 何問起 hovertree.com */

 

--建立遊標包

create or replace PACKAGE "PKG_QUERY" AStype refCursorType is REF CURSOR; --遊標類型定義，用於返回資料集END;/* 何問起 hovertree.com */

--預存程序

create or replace procedure sp_Page(p_PageSize int, --每頁記錄數p_PageNo int, --當前頁碼,從 1 開始p_SqlSelect varchar2, --查詢語句,含排序部分p_SqlCount varchar2, --擷取記錄總數的查詢語句p_pageCount out int,--總共多少頁p_OutRecordCount out int,--返回總記錄數p_OutCursor out PKG_QUERY.refCursorType)asv_sql varchar2(3000);v_count int;v_heiRownum int;v_lowRownum int;  begin  ----取記錄總數      execute immediate p_SqlCount into v_count;      p_OutRecordCount := v_count;      --計算mypageCount--    if mod(v_count,p_PageSize)=0 then        p_pageCount:=v_count/p_PageSize;    else        p_pageCount:=v_count/p_PageSize+1;    end if;         ----執行分頁查詢      v_heiRownum := p_PageNo * p_PageSize;      v_lowRownum := v_heiRownum - p_PageSize + 1;      v_sql := ‘SELECT B.* FROM (SELECT A.*, rownum rn FROM (‘|| p_SqlSelect ||‘) A WHERE rownum <= ‘|| to_char(v_heiRownum) || ‘ ) B WHERE rn >= ‘ || to_char(v_lowRownum) ;    --注意對rownum別名的使用,第一次直接用rownum,第二次一定要用別名rnOPEN p_OutCursor FOR v_sql;end sp_Page;/* 何問起 hovertree.com */

推薦:http://www.cnblogs.com/roucheng/p/3504465.html

java調用Oracle分頁預存程序