USE [Db_8za8za_2]   
GO   
 
SET ANSI_NULLS ON   
GO   
SET QUOTED_IDENTIFIER ON   
GO   
-- =============================================   
-- Description:    <Description,,通用分頁(yè)存儲(chǔ)過(guò)程>   
-- =============================================   
ALTER PROCEDURE [dbo].[paging ]   
    -- Add the parameters for the stored procedure here   
    --傳入?yún)?shù)   
    @SqlStr nvarchar(4000), --查詢(xún)字符串   
    @CurrentPage int, --第N頁(yè)(當(dāng)前頁(yè)數(shù))   
    @PageSize int --每頁(yè)行數(shù)   
AS   
BEGIN   
    -- SET NOCOUNT ON added to prevent extra result sets from   
    -- interfering with SELECT statements.   
    SET NOCOUNT ON;   
    --定義變量   
    DECLARE @CursorId int --CursorId是游標(biāo)的id   
    DECLARE @Rowcount int --總記錄(行)數(shù)   
    DECLARE @pageCount int --總頁(yè)數(shù)   
    -- Insert statements for procedure here   
      
    EXEC sp_cursoropen @CursorId output,@SqlStr,   
        @Scrollopt=1,@Ccopt=1,@Rowcount=@Rowcount OUTPUT   
      
    SET @pageCount=CEILING(1.0*@Rowcount/@PageSize)--設(shè)置總頁(yè)數(shù)   
      
    SELECT @pageCount   
        AS 總頁(yè)數(shù),@Rowcount AS 總行數(shù),@CurrentPage AS 當(dāng)前頁(yè) --提示頁(yè)數(shù)   
 
    IF(@CurrentPage>@pageCount)--如果傳入的當(dāng)前頁(yè)碼大入總頁(yè)碼數(shù)則把當(dāng)前頁(yè)數(shù)設(shè)為最后一頁(yè)   
        BEGIN   
            SET @CurrentPage = @pageCount--設(shè)置當(dāng)前頁(yè)碼數(shù)   
        END   
    IF(@CurrentPage<=0)--如果傳入的當(dāng)前頁(yè)碼大入總頁(yè)碼數(shù)則把當(dāng)前頁(yè)數(shù)設(shè)為第一頁(yè)   
        BEGIN   
            SET @CurrentPage = 1--設(shè)置當(dāng)前頁(yè)碼數(shù)   
        END   
    SET @CurrentPage=(@CurrentPage-1)*@PageSize+1 --設(shè)置當(dāng)前頁(yè)碼數(shù)   
 
    EXEC sp_cursorfetch @CursorId,16,@CurrentPage,@PageSize   
    EXEC sp_cursorclose @CursorId    --關(guān)閉游標(biāo)   
 
    SET NOCOUNT OFF   
END 

Java調(diào)用儲(chǔ)存過(guò)程：

package test;  
 
import java.sql.*;  
 
public class Study3 {  
    private Connection con;  
    public ResultSet rs;  
    private CallableStatement callsta;  
    private String use = "sa";  
    private String pwd = "sa";  
 
    public Study3() {  
        try {  
            // 連接數(shù)據(jù)庫(kù)驅(qū)動(dòng)  
            Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");  
            String str = "jdbc:microsoft:sqlserver://localhost:1433;databasename=test";  
            con = DriverManager.getConnection(str, use, pwd);  
 
            // 設(shè)置存儲(chǔ)過(guò)程參數(shù)  
            String st = "{call Paging(?,?,?)}";  
            callsta = con.prepareCall(st);  
            callsta.setString(1, "select * from T_employee");  
            callsta.setInt(2, 1);  
            callsta.setInt(3, 3);  
 
            // 循環(huán)輸出調(diào)用存儲(chǔ)過(guò)程的記錄結(jié)果  
            StringBuffer sb=new StringBuffer();  
            int rsNum=0;//統(tǒng)計(jì)結(jié)果集的數(shù)量  
            int updateCount = -1;  
            boolean flag = callsta.execute();// 這個(gè)而爾值只說(shuō)明第一個(gè)返回內(nèi)容是更新計(jì)數(shù)還是結(jié)果集。  
            do {   胸圍
                updateCount = callsta.getUpdateCount();  
                if (updateCount != -1) {// 說(shuō)明當(dāng)前行是一個(gè)更新計(jì)數(shù)  
                    // 處理.  
                    System.out.println("..說(shuō)明當(dāng)前行是一個(gè)更新計(jì)數(shù)..");  
                    callsta.getMoreResults();  
                    continue;// 已經(jīng)是更新計(jì)數(shù)了,處理完成后應(yīng)該移動(dòng)到下一行  
                    // 不再判斷是否是ResultSet  
                }  
                rs = callsta.getResultSet();  
                if (rs != null) {// 如果到了這里,說(shuō)明updateCount == -1  
                    // 處理rs  
                    rsNum++;  
                    System.out.println("統(tǒng)計(jì)結(jié)果集的數(shù)量:"+rsNum);  
                    if (rs != null) {  
                        ResultSetMetaData rsmd = rs.getMetaData(); // 獲取字段名  
                        int numberOfColumns = rsmd.getColumnCount(); // 獲取字段數(shù)  
                        int i = 0;  
                        while (rs.next()) { // 將查詢(xún)結(jié)果取出  
                            for (i = 1; i <= numberOfColumns; i++) {  
//                              System.out.println(rs.getInt("總頁(yè)數(shù)"));  
                                String date = rs.getString(i);  
                                sb.append(date+" ");  
                            }  
                        }  
                        rs.close();  
                    }  
                    callsta.getMoreResults();  
                    continue;  
                    // 是結(jié)果集,處理完成后應(yīng)該移動(dòng)到下一行  
                }  
                // 如果到了這里,說(shuō)明updateCount == -1 && rs == null,什么也沒(méi)的了  
                System.out.println(sb.toString());  
            } while (!(updateCount == -1 && rs == null));  
            // callsta.getXXX(int);//獲取輸出參數(shù)  
        } catch (Exception e) {  
            e.printStackTrace();  
        }  
    }  
 
    public static void main(String[] age) {  
        Study3 study = new Study3();  
    }  
}