--程序包-规范(通用存储过程)
create or replace package pagePack
as
type pageCursorType is ref cursor;--游标类型
procedure procPage(
tableName varchar2,--查询的表名
showField varchar2,--查询的字段,T.*表示所有
whereText varchar2,--查询的条件,不要where
orderText varchar2,--排序的字段,不要order by
pageIndex integer,--显示的页码
pageSize integer,--每页显示的记录数
pageCursor out pageCursorType,--返回的游标
counter out integer--返回的记录总数
);
end;
--程序包-主体
create or replace package body pagePack
as
procedure procPage(
tableName varchar2,--查询的表名
showField varchar2,--查询的字段,T.*表示所有
whereText varchar2,--查询的条件,不要where
orderText varchar2,--排序的字段,不要order by
pageIndex integer,--显示的页码
pageSize integer,--每页显示的记录数
pageCursor out pageCursorType,--返回的游标
counter out integer--返回的记录总数
)
as
sqlstr varchar2(400);
wText varchar2(100);
oText varchar2(100);
begin
if whereText is not null then
wText:=' where '||whereText;
end if;
if orderText is not null then
oText:=' order by '||orderText;
end if;
sqlstr:='select * from
(
select rownum rn, '||showField||' from
(select * from '||tableName||wText||oText ||' ) T
where rownum<=:1
)
where rn>:2';
dbms_output.put_line(sqlstr);
open pageCursor for sqlstr using pageSize*pageIndex,pageSize*(pageIndex-1);
sqlstr:='select count(*) from '||tableName||wText||oText;
dbms_output.put_line(sqlstr);
execute immediate sqlstr into counter;
end;
end;
java中调用
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:orcl", "scott", "ronaldo");
String callsql = "{call pagePack.procPage(?,?,?,?,?,?,?,?)}";
OracleCallableStatement ocs = (OracleCallableStatement) conn
.prepareCall(callsql);
int i = 1;
ocs.setString(i++, "product");
ocs.setString(i++, "pname");
ocs.setString(i++, null);
ocs.setString(i++, null);
ocs.setString(i++, "1");
ocs.setString(i++, "2");
ocs.registerOutParameter(7, oracle.jdbc.OracleTypes.CURSOR);
ocs.registerOutParameter(8, Types.INTEGER);
ocs.executeUpdate();
ResultSet rs = ocs.getCursor(7);
while (rs.next()) {
System.out.println(rs.getString(2));
}
int sum = ocs.getInt(8);
System.out.println("一共个数是" + sum);