php调用存储过程实现分页,Oracle存储过程实现分页

--book表CREATE TABLE book(book_id VARCHAR2(20),book_name VARCHAR2(100),book_publish VARCHAR2(100)); --in表示输入参数，默

--book表

CREATE TABLE book(

book_id VARCHAR2(20),

book_name VARCHAR2(100),

book_publish VARCHAR2(100)

);

--in表示输入参数，默认为in

--out 表示输出参数

CREATE OR REPLACE PROCEDURE sp_pro7(spBookId IN VARCHAR2, spBookName in VARCHAR2, spBookPub VARCHAR2)

IS

BEGIN

INSERT INTO book VALUES(spBookId, spBookName,spBookPub);

END;

--返回单个值

CREATE OR REPLACE PROCEDURE sp_pro8(spBookId IN VARCHAR2, spBookName OUT VARCHAR2)

IS

BEGIN

SELECT t.book_name INTO spBookName FROM book t WHERE t.book_id = spBookId;

END;

--返回结果集

--1 建立一个游标

CREATE OR REPLACE PACKAGE testPackage AS

TYPE test_cursor IS REF CURSOR;

END testPackage;

--2 创建过程

CREATE OR REPLACE PROCEDURE sp_pro9(spBookId VARCHAR2, sp_cursor OUT testPackage.test_cursor) IS

BEGIN

OPEN sp_cursor FOR SELECT t.book_id,t.book_name,t.book_publish FROM book t;

END;

--分页

SELECT *

FROM (SELECT ROWNUM RN, T.* FROM BOOK T WHERE ROWNUM < 5)

WHERE RN > 2;

CREATE OR REPLACE PROCEDURE fenye(

tablename VARCHAR2,

pageSize NUMBER, -- 一页显示的记录数

pageNow NUMBER, --当前页数

myRows OUT NUMBER, --总记录数

myPageCount OUT NUMBER,

sp_cursor OUT testPackage.test_cursor) IS

v_sql VARCHAR2(1000);

v_begin NUMBER:=(pageNow - 1) * pageSize + 1;

v_end NUMBER:=(pageNow) * pageSize;

BEGIN

v_sql:='SELECT * FROM (SELECT ROWNUM RN, T.* FROM '|| tablename || ' T WHERE ROWNUM < '|| v_end || ') WHERE RN > '|| v_begin;

OPEN sp_cursor FOR v_sql;

--计算myrows mypagecount

v_sql := 'SELECT COUNT(*) FROM '|| tablename;

--快速执行sql,并将结果赋值给myrows

EXECUTE IMMEDIATE v_sql INTO myrows;

IF MOD(myrows, pageSize) = 0 THEN

myPageCount:=myrows / pageSize;

ELSE

myPageCount:=myrows / pageSize + 1;

END IF;

--关闭游标

CLOSE sp_cursor;

END;

package com.leeket;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

/**

* @author Administrator

* @description 分页

*/

public class TestProcedure4 {

public static void main(String[] args) {

CallableStatement call = null;

Connection conn = null;

ResultSet rs = null;

try {

Class.forName("Oracle.jdbc.driver.OracleDriver");

conn = DriverManager

.getConnection("jdbc:oracle:thin:pas_perf/pas_perf@localhost:1521:orcl");

call = conn.prepareCall("{call fenye(?,?,?,?,?,?)}");

call.setString(1, "BOOK");

call.setInt(2, 10);

call.setInt(3, 1);

call.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER);

call.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);

call.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);

call.execute();

//取出返回值 要注意问好的顺序

int num = call.getInt(4);

int myPageCount = call.getInt(5);

rs = (ResultSet)call.getObject(6);

if (rs != null)

while (rs.next()) {

System.out.println(num + "\t" + myPageCount + "\t" + rs.getString(2) + "\t" + rs.getString(3) + "\t" + rs.getString(4));

}

} catch (Exception e) {

e.printStackTrace();

} finally {

try {

if (rs != null)

rs.close();

if (call != null)

call.close();

if (conn != null)

conn.close();

} catch (Exception e) {

e.printStackTrace();

}

}

}

}

，

本文原创发布php中文网，转载请注明出处，感谢您的尊重！