这位老兄,又发个帖子啊,前面那个cursor的答案就是阿create or replace package TaskByTime is type mycursor is ref cursor; procedure ProTaskByTime(ret_cursor out mycursor); end TaskByTime;create or replace package body TaskByTime is procedure ProTaskByTime(ret_cursor out mycursor) is begin open ret_cursor for select * from emp; end ProTaskByTime; end TaskByTime;declare rec emp%rowtype; cur TaskByTime.mycursor; begin TaskByTime.ProTaskByTime(cur); loop fetch cur into rec; exit when cur%notfound; dbms_output.put_line(rec.ename); end loop; end;
你刚才写的返回游标的就是返回结果集的例子,还有这种,但这种是用在程序中调用的create or replace procedure p_c as begin execute immediate 'select * from emp'; end; /
CREATE OR REPLACE PACKAGE BODY mypack IS PROCEDURE myproc( outcursor IN OUT mycursor ) IS BEGIN OPEN outcursor FOR SELECT * FROM Student WHERE ROWNUM<10; RETURN; END myproc; END;public void callProcForResult(){ try { cs = conn.prepareCall("{call mypack.myproc(?)}"); cs.registerOutParameter(1, OracleTypes.CURSOR); cs.execute(); ResultSet rs = (ResultSet)cs.getObject(1); while(rs!=null && rs.next()){ System.out.println(new StringBuilder("ID:").append(rs.getInt(1)).append("\t Name:").append(rs.getString(2)) .append("\t Phone:").append(rs.getString(6)).append("\t Address:").append(rs.getString(7)).toString()); } } catch (SQLException e) { e.printStackTrace(); } }
例如从永久表中返回结果集: USE pubs GO CREATE PROCEDURE ap_CreateResultFromPermtable AS SELECT au_iname FROM authors GO 例如从局部变量中创建结果集: USE pubs GO CREATE PROCEDURE ap_CreateResultFromVariable AS DECLARE @au_iname char(20) SELECT @au_iname = au_iname FROM authors WHERE au_id = ‘172-32-1176’ SELECT @au_id GO
type mycursor is ref cursor;
procedure ProTaskByTime(ret_cursor out mycursor);
end TaskByTime;create or replace package body TaskByTime is
procedure ProTaskByTime(ret_cursor out mycursor) is
begin
open ret_cursor for select * from emp;
end ProTaskByTime;
end TaskByTime;declare
rec emp%rowtype;
cur TaskByTime.mycursor;
begin
TaskByTime.ProTaskByTime(cur);
loop
fetch cur into rec;
exit when cur%notfound;
dbms_output.put_line(rec.ename);
end loop;
end;
as
begin
execute immediate 'select * from emp';
end;
/
PROCEDURE myproc(
outcursor IN OUT mycursor
)
IS
BEGIN
OPEN outcursor FOR
SELECT * FROM Student WHERE ROWNUM<10;
RETURN;
END myproc;
END;public void callProcForResult(){
try {
cs = conn.prepareCall("{call mypack.myproc(?)}");
cs.registerOutParameter(1, OracleTypes.CURSOR);
cs.execute();
ResultSet rs = (ResultSet)cs.getObject(1);
while(rs!=null && rs.next()){
System.out.println(new StringBuilder("ID:").append(rs.getInt(1)).append("\t Name:").append(rs.getString(2))
.append("\t Phone:").append(rs.getString(6)).append("\t Address:").append(rs.getString(7)).toString());
}
} catch (SQLException e) {
e.printStackTrace();
}
}