oracle存储过程分页代码是怎么实现的我们来看下实验小编为您整理的方法吧!
/*******存储过程分页代码**********/
包头
create or replace package pck_my is
type c_my is ref cursor;
procedure page_moed(
v_table in varchar 表名
current_page in out number当前页
pageSize in out number页行数
total out number总行数
countPage out number总页数
c_cursor out pck_myc_my游标
);
end pck_my;
body
create or replace package body pck_my as
procedure page_moed(
v_table in varchar
current_page in out number
pageSize in out number
total out number
countPage out number
c_cursor out pck_myc_my
)is
v_sql varchar();
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:=;
end if;
if pageSize<= then
pageSize:=;
end if;
计算 最大行 最小行
v_max:=(current_page+)*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(totalpageSize)= then
countPage:=total/pageSize;
else
countPage:=total/pageSize+;
end if;
exception
exception
when e_table then
dbms_outputput_line(表名不能为空);
end;
end pck_my;
exet
select * from filminfo
java测试代码
[html]
package comrui;
import javasqlCallableStatement;
import javasqlConnection;
import javasqlDriverManager;
import javasqlResultSet;
import javasqlSQLException;
public class Pckage {
/**
* @param args
*/
public static void main(String[] args) {
Connection con;
ResultSet rs;
CallableStatement cs;
try {
ClassforName(oraclejdbcdriverOracleDriver);
con=DriverManagergetConnection(jdbc:oracle:thin:@localhost::ABCtenementrui);
String sql={call pck_mypage_moed(??????)};
cs=conprepareCall(sql);
//指定类型
/* v_table in varchar
current_page in out number
pageSize in out number
total out number
countPage out number
c_cursor out pck_myc_my
*/
//cssetString( null);
cssetString( filminfo);
cssetInt( );
cssetInt();
csregisterOutParameter(oraclejdbcOracleTypesNUMBER);
csregisterOutParameter( oraclejdbcOracleTypesNUMBER);
csregisterOutParameter( oraclejdbcOracleTypesCURSOR);
csexecute();
int total=csgetInt();//总行数
int countPage=csgetInt();//总页数
rs=(ResultSet)csgetObject();//result
Systemoutprintln(总行数+total+\t总页数+countPage);
Systemoutprintln();
while(rsnext()){
Systemoutprintln(FILMNAME:+rsgetString(FILMNAME)+\tFILMID:+rsgetInt(FILMID));
}
} catch (ClassNotFoundException e) {
// TODO Autogenerated catch block
eprintStackTrace();
} catch (SQLException e) {
// TODO Autogenerated catch block
eprintStackTrace();
}
}
}
