是这样的我自己写个存储过程带了一个OUT游标参数,也就是想返回一个游标。
在网上搜了过程能返回结果集好像函数也可以。。我有点糊涂。望牛人跟我讲讲。谢谢!!!create or replace procedure retCursor(ret_cursor out sys_refcursor)
is
ret_cursor_value sys_refcursor;
begin
open ret_cursor_value for select * from student; --打开游标,返回一张表的数据
ret_cursor:=ret_cursor_value; --游标赋值
end retCursor;问题一:我在PL/SQL工具中 怎么调用这个游标???问题二:我用JAVA代码调用的时候报以下错误 Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection
("jdbc:oracle:thin:@XX.XX.XX.XXX:XXXX:oracle9i","XXX_temp","XXX_temp");
DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
CallableStatement cs = conn.prepareCall("{? = call retCursor()}");
cs.registerOutParameter(1,OracleTypes.CURSOR);
cs.execute();
ResultSet rs = ((OracleCallableStatement)cs).getCursor(1);
System.out.println(rs);
while(rs.next())
{
}java.sql.SQLException: ORA-06550: 第 1 行, 第 13 列:
PLS-00306: 调用 'RETCURSOR' 时参数个数或类型错误
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:582)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1983)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1141)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2149)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2032)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2894)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:608)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:684)
at com.yl.hibernater.ProcedureText.main(ProcedureText.java:23)
在网上搜了过程能返回结果集好像函数也可以。。我有点糊涂。望牛人跟我讲讲。谢谢!!!create or replace procedure retCursor(ret_cursor out sys_refcursor)
is
ret_cursor_value sys_refcursor;
begin
open ret_cursor_value for select * from student; --打开游标,返回一张表的数据
ret_cursor:=ret_cursor_value; --游标赋值
end retCursor;问题一:我在PL/SQL工具中 怎么调用这个游标???问题二:我用JAVA代码调用的时候报以下错误 Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection
("jdbc:oracle:thin:@XX.XX.XX.XXX:XXXX:oracle9i","XXX_temp","XXX_temp");
DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
CallableStatement cs = conn.prepareCall("{? = call retCursor()}");
cs.registerOutParameter(1,OracleTypes.CURSOR);
cs.execute();
ResultSet rs = ((OracleCallableStatement)cs).getCursor(1);
System.out.println(rs);
while(rs.next())
{
}java.sql.SQLException: ORA-06550: 第 1 行, 第 13 列:
PLS-00306: 调用 'RETCURSOR' 时参数个数或类型错误
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:582)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1983)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1141)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2149)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2032)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2894)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:608)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:684)
at com.yl.hibernater.ProcedureText.main(ProcedureText.java:23)
解决方案 »
- oracle sql语句参数最多多少个?
- execute immediate的问题
- 紧急:oracle我的一个包里面的过程出现死循环了,怎么停止
- 某表的一个字段是1,2,3这种形式,这是代码值,想转换成中文
- Oracle中自定义数据类型如何导入到不同的用户
- 大哥你们好,我刚学习oracle9i,想问一下,怎么才能备份oracle9i的数据库
- 在一个DUP文件里有多个用户的数据,现在用IMP导入,只想导入其中一个用户的数据,应该怎么做?
- 关于如何扩展BLOB字段的系统索引
- 高分请教 Proc*C/C++ 编程环境问题
- 请教:ORA00439,对象模式未启动,咋办(ORA.8.1.7)
- 请教一个查询问题!急!
- 下面的查询怎样修改来提高执行效率?
定义一个游标,然后把它作为procedure的参数传入retCursor,然后可以用1楼的来fetch了问题二:我用JAVA代码调用的时候报以下错误
参数和procedure的不匹配
定义的是refCursorz作为参数 而你却是"{? = call retCursor()}"这时不对的应该是call retCursor(?)procedure是没有返回值的,这时和function的一个区别,procedure通过out parameter收集需要返回的信息。
下面贴代码/*
PL/SQL Code (包中带过程) 过程带游标的OUT参数,返回游标(ref cursor)
create or replace package my_pack as
type my_ref_cursor is ref cursor;
procedure getMyCursor(val out my_ref_cursor);
end my_pack;
create or replace package body my_pack as
procedure getMyCursor(val out my_ref_cursor)
is
begin
open val for select * from student;
end;
end my_pack;
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@10.36.65.232:1521:oracle9i","omc_temp","omc_temp");
DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
CallableStatement cs = conn.prepareCall("{ call my_pack.getMyCursor(?) }");
cs.registerOutParameter(1,OracleTypes.CURSOR);
cs.execute();
ResultSet rs = ((OracleCallableStatement)cs).getCursor(1);
while(rs.next())
{
System.out.println(rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3)+" "+ rs.getDate(4)+" "+rs.getString(5));
}*/
/*
PL/SQL Code(存储过程) 带游标的OUT参数,返回游标(ref cursor)
create or replace procedure retCursor(ret_cursor out sys_refcursor)is
ret_cursor_value sys_refcursor;
begin
open ret_cursor_value for select * from student;
ret_cursor:=ret_cursor_value;
end retCursor; Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@10.36.65.232:1521:oracle9i","omc_temp","omc_temp");
DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
CallableStatement cs = conn.prepareCall("{ call retCursor(?) }");
cs.registerOutParameter(1,OracleTypes.CURSOR);
cs.execute();
ResultSet rs = ((OracleCallableStatement)cs).getCursor(1);
while(rs.next())
{
System.out.println(rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3)+" "+ rs.getDate(4)+" "+rs.getString(5));
}*/
存储过程 用系统默认的 sys_refcursor 游标类型 定义变量就OK了