Oracle中代码
Create or replace procedure fenye(
V_table in varchar2,
v_pagesize in number,--一页显示多少记录
v_pagenow in number,
V_rowscount out number,--总记录数
v_totalpage out number,--总页数
p_cursor out toolpack.tool_cursor
) is
--定义一个sql语句,字符串
V_sql varchar(1000);
--定义两个整数
v_begin number:=(v_pagenow-1)*v_pagesize+1;
v_end number:=v_pagesize*v_pagenow;
Begin
v_sql:='select * from (select t1.*,rownum rn from (select * from '||V_table||')t1 where rownum<
='||v_end||')where rn >='||v_begin;
--把sql语句和油标关联
open p_cursor for v_sql;
--计算V_rowscount 和v_totalpage
v_sql:='select count(*) from'||V_table;
--执行sql,并把返回的值赋给V_rowscount
execute immediate v_sql into v_rowscount;
--计算v_totalpage
If mod(v_rowscount,v_pagesize)=0 then
v_totalpage:=v_rowscount/v_pagesize;
Else v_totalpage:=v_rowscount/v_pagesize+1;
End if;
--关闭油标
close p_cursor;
end;
/
commit;
java中的
Connection con =null;
CallableStatement cs=null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
con=DriverManager.getConnection("jdbc:oracle:thin:@10.23.92.47:1521:myoral","scott","tiger");
cs=con.prepareCall("{call fenye(?,?,?,?,?,?)}");
cs.setString(1, "emp");//表名
cs.setInt(2, 1);//一页显示几条记录
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);
System.out.println(rownum);
ResultSet rs=(ResultSet) cs.getObject(6);
while(rs.next()){
System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3));
}
rs.close();
cs.close();
con.close();
} catch (Exception e) {
e.printStackTrace();
// TODO: handle exception
}
在执行到cs.excute()时出现了以下异常,求帮忙解决
java.sql.SQLException: ORA-04044: 此处不允许过程, 函数, 程序包或类型
ORA-06512: 在 "SCOTT.FENYE", line 18
ORA-06512: 在 line 1
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:215)
at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:965)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1170)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3445)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4394)
at TestOracle.FenYe.main(FenYe.java:32)