当前位置: 代码迷 >> Oracle技术 >> 麻烦高手看下,Oracle分页过程使用java测试如何出错了
  详细解决方案

麻烦高手看下,Oracle分页过程使用java测试如何出错了

热度:66   发布时间:2016-04-24 08:30:25.0
麻烦高手看下,Oracle分页过程使用java测试怎么出错了?
---此处是包的创建
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

------解决方案--------------------
目测你这一句少了空格
SQL code
v_sql:='select count(*) from'||tableName;改成v_sql:='select count(*) from '||tableName;
  相关解决方案