/*create or replace package my_cursor as
type my_type is ref CURSOR;
end my_cursor;*/
create or replace procedure pager(
tableName in varchar2,
condition in varchar2:=' WHERE 1=1 ',
orders in varchar2,
pageIndex in integer:=1,
pageSize in integer:=5,
pageCount out integer,
recordCount out integer,
lresult out my_cursor.my_type
) as
count_sql varchar2(2000) :='select count(*) from {TABLE_NAME} {CONDITION}';
pager_sql varchar2(2000):='select * from(select rownum rn,models.* from(SELECT * from {TABLE_NAME} {CONDITION} {ORDERS})models where rownum<={TOP_COUNT} ) where rn>{PASS_COUNT}';
begin
--替换查询总条数语句
count_sql:=replace(count_sql,'{TABLE_NAME}',tableName);
count_sql:=replace(count_sql,'{CONDITION}',condition);
--执行查询总条数语句
EXECUTE IMMEDIATE count_sql into recordCount;
dbms_output.put_line(count_sql);
--输出总页数
if recordCount mod pageSize=0 then
pageCount:=recordCount/pageSize;
else
pageCount:=recordCount/pageSize+1;
end if;
--替换分页语句
pager_sql:=replace(pager_sql,'{TABLE_NAME}',tableName);
if condition<>'' then
pager_sql:=replace(pager_sql,'{CONDITION}',' where '|| LTRIM(LTRIM(UPPER(condition)),'WHERE'));
else
pager_sql:=replace(pager_sql,'{CONDITION}',condition);
end if;
pager_sql:=replace(pager_sql,'{ORDERS}',orders);
pager_sql:=replace(pager_sql,'{TOP_COUNT}',pageIndex*pageSize);
pager_sql:=replace(pager_sql,'{PASS_COUNT}',(pageIndex-1)*pageSize);
dbms_output.put_line(pager_sql);
--执行分页语句
open lresult for pager_sql;
/* for hou in lresult loop
dbms_output.put_line(hou.name);
end loop;*/
end pager;
测试代码:
package one;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
public class pager{
public static void main(String[] args) {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "house", "house");
CallableStatement cas=con.prepareCall("{call pager('house','','',1,5,?,?,?)}");
cas.registerOutParameter(1, oracle.jdbc.OracleTypes.INTEGER);
cas.registerOutParameter(2, oracle.jdbc.OracleTypes.INTEGER);
cas.registerOutParameter(3, oracle.jdbc.OracleTypes.CURSOR);
cas.execute();
ResultSet rs=(ResultSet)cas.getObject(3);
while (rs.next()) {
System.out.println(rs.getString("title"));
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
可以查jdk文档上面 CallableStatement