---此处是包的创建
create or replace package testpackage as type test_cursor is ref cursor;
end testpackage;
---开始编写分页过程
create or replace procedure pro_fenye
(tableName in varchar2,
pagesSize in number,---一页显示记录数
pageNow in number,
myRows out number,---总记录数
myPageCount out number,---总页数
p_cursor out testpackage.test_cursor---返回的记录集
) is
v_sql varchar2(1000);
v_begin number:=(pageNow-1)*pagesSize+1;
v_end number:=pageNow*pagesSize;
begin
v_sql:='select * from (select t1.* ,rownum rn from (select * from '|| tableName
||') t1 where rownum<='|| v_end ||') where rn>='|| v_begin;
---把游标和sql关联
open p_cursor for v_sql;
---计算myRows和myPageCount
v_sql:='select count(*) from'||tableName;
---执行sql,并把返回的值赋给myRows
execute immediate v_sql into myRows;
---计算myPageCount
if mod(myRows,pagesSize)=0 then
myPageCount:=myRows/pagesSize;
else
myPageCount:=myRows/pagesSize+1;
end if;
close p_cursor;
end;以下是java代码:
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL", "scott", "110139");
CallableStatement cs = ct.prepareCall("{call pro_fenye(?,?,?,?,?,?)}");
cs.setString(1, "emp");
cs.setInt(2, 5);
cs.setInt(3, 1);
cs.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER);
cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);
cs.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);
cs.execute();
int rowNum = cs.getInt(4);
int pageCount = cs.getInt(5);
ResultSet rs = (ResultSet)cs.getObject(6);
System.out.println("总记录数:" + rowNum);
System.out.println("总页数:" + pageCount);出错提示:
java.sql.SQLSyntaxErrorException: ORA-00923: 未找到要求的 FROM 关键字
ORA-06512: 在 "SCOTT.ZS_PRO_FENYE1", line 19
create or replace package testpackage as type test_cursor is ref cursor;
end testpackage;
---开始编写分页过程
create or replace procedure pro_fenye
(tableName in varchar2,
pagesSize in number,---一页显示记录数
pageNow in number,
myRows out number,---总记录数
myPageCount out number,---总页数
p_cursor out testpackage.test_cursor---返回的记录集
) is
v_sql varchar2(1000);
v_begin number:=(pageNow-1)*pagesSize+1;
v_end number:=pageNow*pagesSize;
begin
v_sql:='select * from (select t1.* ,rownum rn from (select * from '|| tableName
||') t1 where rownum<='|| v_end ||') where rn>='|| v_begin;
---把游标和sql关联
open p_cursor for v_sql;
---计算myRows和myPageCount
v_sql:='select count(*) from'||tableName;
---执行sql,并把返回的值赋给myRows
execute immediate v_sql into myRows;
---计算myPageCount
if mod(myRows,pagesSize)=0 then
myPageCount:=myRows/pagesSize;
else
myPageCount:=myRows/pagesSize+1;
end if;
close p_cursor;
end;以下是java代码:
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL", "scott", "110139");
CallableStatement cs = ct.prepareCall("{call pro_fenye(?,?,?,?,?,?)}");
cs.setString(1, "emp");
cs.setInt(2, 5);
cs.setInt(3, 1);
cs.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER);
cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);
cs.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);
cs.execute();
int rowNum = cs.getInt(4);
int pageCount = cs.getInt(5);
ResultSet rs = (ResultSet)cs.getObject(6);
System.out.println("总记录数:" + rowNum);
System.out.println("总页数:" + pageCount);出错提示:
java.sql.SQLSyntaxErrorException: ORA-00923: 未找到要求的 FROM 关键字
ORA-06512: 在 "SCOTT.ZS_PRO_FENYE1", line 19
解决方案 »
- Oracle写存储过程是==错误:PLS-00103: 出现符号 "%"在需要下列之一时: 。。。。。
- ora 存储过程
- 求助
- oracle异机恢复
- 在线等待高手相助: 启动时提示出错:ORA-00064: object is too large to allocate on this O/S(1,4800000)
- 紧急求助!!各高手请帮忙!!
- 斑竹帮帮忙,急!!!Oracle9i 的相关问题!!
- 为什么OracleOraHome81ManagerServer不能启动
- 眼泪求救,oracle 11g 导出的dmp无法导入9i
- 事物的redo条目是什么时候产生并放入缓冲的?
- 11G中的sql/plus,pl/sql的一点问题?
- 硕士学历的计算机白领(进者有分)
这个是什么
SQL> conn scott/110139
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
8888 wangwu MANAGER 7566 1988/12/12 2478.00 100.00 10
7988 张三 MANAGEER 7839 1989/12/22 5000.00 1500.00 10
2345 赵六 CLERK 7988 1987/2/11 星 1235.23 23.66 20
8801 test用户 MANAGER 7782 2012/4/17 星 23.00 23.00 10
7369 SMITH CLERK 7902 1980/12/17 10000.00 20
7499 ALLEN SALESMAN 7698 1981/2/20 星 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981/2/22 星 1250.00 500.00 30
7566 JONES MANAGER 7839 1981/4/2 星期 2975.00 20
7654 MARTIN SALESMAN 7698 1981/9/28 星 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981/5/1 星期 2850.00 200.00 30
7782 CLARK MANAGER 7839 1981/6/9 星期 2450.00 10
7788 SCOTT CLERK 7566 1987/4/19 星 1320.61 20
7839 KING PRESIDENT 1981/11/17 6000.00 10
7844 TURNER SALESMAN 7698 1981/9/8 星期 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987/5/23 星 1100.00 20
7900 JAMES CLERK 7698 1981/12/3 星 950.00 30
7902 FORD ANALYST 7566 1981/12/3 星 3000.00 20
7934 MILLER CLERK 7782 1982/1/23 星 1300.00 10