CREATE OR REPLACE PROCEDURE ll_01(p_cursor OUT sys_refcursor) IS BEGIN OPEN p_cursor FOR SELECT * from table; END ll_01;--这样就返回一个游标了
冰说得方法不错,不过直接在procedure中用返回游标好像不行,因为游标必须先定义再使用啊.写在包里可解决. create or replace package financeanalyse is TYPE t_cursor IS REF CURSOR; procedure compose_Finance(re_cursor out T_CURSOR); end financeanalyse ; / create or replace package body financeanalyse is procedure compose_Finance(re_cursor out T_CURSOR);IS BEGIN OPEN re_cursor FOR SELECT * from table; end financeanalyse ; /
--存储过程 CREATE OR REPLACE PROCEDURE ll_02 (p_cursor OUT sys_refcursor) IS BEGIN OPEN p_cursor FOR SELECT * from b_areas; END ll_02;--测试(toad) declare p_cursor sys_refcursor; row_area b_areas%rowtype; begin ll_02(p_cursor); loop fetch p_cursor into row_area; exit when p_cursor%notfound; dbms_output.put_line (row_area.area_id); end loop; end;--结果 N H X O E S M W --可以不用包阿
BEGIN
OPEN p_cursor FOR
SELECT * from table;
END ll_01;--这样就返回一个游标了
create or replace package financeanalyse is
TYPE t_cursor IS REF CURSOR;
procedure compose_Finance(re_cursor out T_CURSOR);
end financeanalyse ;
/
create or replace package body financeanalyse is
procedure compose_Finance(re_cursor out T_CURSOR);IS
BEGIN
OPEN re_cursor FOR
SELECT * from table;
end financeanalyse ;
/
CREATE OR REPLACE PROCEDURE ll_02 (p_cursor OUT sys_refcursor) IS
BEGIN
OPEN p_cursor FOR
SELECT * from b_areas;
END ll_02;--测试(toad)
declare
p_cursor sys_refcursor;
row_area b_areas%rowtype;
begin
ll_02(p_cursor);
loop
fetch p_cursor into row_area;
exit when p_cursor%notfound;
dbms_output.put_line (row_area.area_id);
end loop;
end;--结果
N
H
X
O
E
S
M
W
--可以不用包阿