以输入表名、每页显示记录数、当前
页。返回总记录数,总页数,和返回的结果集create package testpackage as
type test_cursor is ref cursor;
end testpackage;create or replace procedure hx_paging(
tableName in varchar2, -- 输入的表名
pageSize in number, -- 每页记录数
currentPage in number, -- 当前页
rowCount out number, -- 总行数
pageCount out number, -- 总页数
resultCursor out testpackage.test_cursor -- 结果集) is
v_sql varchar2(1000); -- 定义一个存放SQL语句的变量
v_begin number:=pageSize*(currentPage-1); -- 算出起始行
v_end number:=pageSize*currentPage; -- 算出结束行
begin
v_sql:='select * from (select t.*,rownum rn from '||tableName||' t where rownum<='||v_end||') where rn>'||v_begin; -- select 语句
open resultCursor for v_sql; --打开游标
v_sql:='select count(*) from '||tableName; -- 查询总记录数的SQL语句
execute immediate v_sql into rowCount; -- 把总记录数赋给 rowCount
pageCount:=ceil(rowCount/pageSize); -- 算出总页数
close resultCursor; --关闭游标
end;
/-------------------无注释版------------------create or replace procedure hx_paging(
tableName in varchar2,
pageSize in number,
currentPage in number,
rowCount out number,
pageCount out number,
resultCursor out testpackage.test_cursor) is
v_sql varchar2(1000);
v_begin number:=pageSize*(currentPage-1);
v_end number:=pageSize*currentPage;
begin
v_sql:='select * from (select t.*,rownum rn from '||tableName||' t where rownum<='||v_end||') where rn>'||v_begin;
open resultCursor for v_sql;
v_sql:='select count(*) from '||tableName;
execute immediate v_sql into rowCount;
pageCount:=ceil(rowCount/pageSize);
close resultCursor;
end;
/我用JDBC调用 他总报 java.sql.SQLException: ORA-00911: invalid character 不知道哪错了...还有还有 如果不用JDBC的话怎么调试啊?? 不会....
页。返回总记录数,总页数,和返回的结果集create package testpackage as
type test_cursor is ref cursor;
end testpackage;create or replace procedure hx_paging(
tableName in varchar2, -- 输入的表名
pageSize in number, -- 每页记录数
currentPage in number, -- 当前页
rowCount out number, -- 总行数
pageCount out number, -- 总页数
resultCursor out testpackage.test_cursor -- 结果集) is
v_sql varchar2(1000); -- 定义一个存放SQL语句的变量
v_begin number:=pageSize*(currentPage-1); -- 算出起始行
v_end number:=pageSize*currentPage; -- 算出结束行
begin
v_sql:='select * from (select t.*,rownum rn from '||tableName||' t where rownum<='||v_end||') where rn>'||v_begin; -- select 语句
open resultCursor for v_sql; --打开游标
v_sql:='select count(*) from '||tableName; -- 查询总记录数的SQL语句
execute immediate v_sql into rowCount; -- 把总记录数赋给 rowCount
pageCount:=ceil(rowCount/pageSize); -- 算出总页数
close resultCursor; --关闭游标
end;
/-------------------无注释版------------------create or replace procedure hx_paging(
tableName in varchar2,
pageSize in number,
currentPage in number,
rowCount out number,
pageCount out number,
resultCursor out testpackage.test_cursor) is
v_sql varchar2(1000);
v_begin number:=pageSize*(currentPage-1);
v_end number:=pageSize*currentPage;
begin
v_sql:='select * from (select t.*,rownum rn from '||tableName||' t where rownum<='||v_end||') where rn>'||v_begin;
open resultCursor for v_sql;
v_sql:='select count(*) from '||tableName;
execute immediate v_sql into rowCount;
pageCount:=ceil(rowCount/pageSize);
close resultCursor;
end;
/我用JDBC调用 他总报 java.sql.SQLException: ORA-00911: invalid character 不知道哪错了...还有还有 如果不用JDBC的话怎么调试啊?? 不会....
解决方案 »
- 只有一个 dmp 文件 如何得知原 表空间名称 ? 用UltraEdit如何查看 ?
- 高手们请看看这个问题!如何用jdbc将jpg图像写入数据库而不用oracle的包?
- 如何获得Oracle数据库中,number类型字段的长度?
- ORACLE日志文件的问题
- Date类型相减,差值为分钟,如何去处理
- Oracle 10G 透明网关设置求助
- 用Oracle9i卸载升过级Oracle9i
- 为何在只装了oracle client 的机了上,没法运行 rman;
- 100分求解一表的关联问题
- 在ORACLE官方网上下在ORCALE9I为什么不能安装?
- 触发器UPDATE 的时候 有简洁的方法没
- 更新一列数据的问题!
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;public class CopyOfOracleJDBC { public static void main(String[] args) { Connection conn = null;
CallableStatement cs = null;
ResultSet rs = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(
"jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "tiger");
cs = conn.prepareCall("call hx_paging(?,?,?,?,?,?);"); // in
cs.setString(1, "emp");
cs.setInt(2, 5);
cs.setInt(3, 2); // out
cs.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER);
cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);
cs.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR); cs.execute(); int rowCount = cs.getInt(4);
int pageCount = cs.getInt(5);
rs = (ResultSet) cs.getObject(6); System.out.println("总记录数=" + rowCount);
System.out.println("总页数=" + pageCount);
while (rs.next()) {
System.out.println("编号:" + rs.getInt(1) + " 名字:" + rs.getString(2)
+ " 工资:" + rs.getFloat(6));
} } catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
rs=null;
}
if(cs!=null){
try {
cs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
cs=null;
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
conn=null;
}
}
}
}
cursor都关掉了,你还取得到数据么?
create or replace procedure hx_paging(
tableName in varchar2,
pageSize in number,
currentPage in number,
rowCount out number,
pageCount out number,
resultCursor out sys_refcursor) is
v_sql varchar2(1000);
v_begin number:=pageSize*(currentPage-1);
v_end number:=pageSize*currentPage;
begin
v_sql:='select * from (select t.*,rownum rn from '||tableName||' t where rownum<='||v_end||') where rn>'||v_begin;
open resultCursor for v_sql;
v_sql:='select count(*) from '||tableName;
execute immediate v_sql into rowCount;
pageCount:=ceil(rowCount/pageSize);
end;
SQL> var pn number
SQL> var o_cur refcursor
SQL> exec hx_paging('emp',1,2,:rn,:pn,:o_cur);PL/SQL procedure successfully completed.SQL> print :o_cur EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RN
---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- ---------- 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 2
SQL>
遇到同样问题的童鞋可以参考了
谢谢TOY大大.. 完结给分..
//还有上面那个JDBC代码会出现 java.sql.SQLException: ORA-00911: invalid character
//这个是因为
cs = conn.prepareCall("call hx_paging(?,?,?,?,?,?);");
// 这个语句里面的SQL语句最后不能有分号