create or replace package pkg_test
as
type cur_test is ref cursor; -- 定義一個cursor的type
end pkg_test;
/
create or replace procedure p_test
(
v_cur out pkg_test.cur_test
)
as
v_sql varchar2(100); --
begin
v_sql := 'select a1,a2 from test';
OPEN v_cur FOR v_sql; --
exception
when others then
DBMS_OUTPUT.PUT_LINE('Error ---------------' || sqlcode || ' : ' || sqlerrm );
end p_test;
/
as
type cur_test is ref cursor; -- 定義一個cursor的type
end pkg_test;
/
create or replace procedure p_test
(
v_cur out pkg_test.cur_test
)
as
v_sql varchar2(100); --
begin
v_sql := 'select a1,a2 from test';
OPEN v_cur FOR v_sql; --
exception
when others then
DBMS_OUTPUT.PUT_LINE('Error ---------------' || sqlcode || ' : ' || sqlerrm );
end p_test;
/
在DECLARE时就出错了。DECLARE
P_CURSOR REF CURSOR;BEGIN
TYPEDEFINE.GET ( P_CURSOR );
END;
IS PROCEDURE get(p_cursor out cursortype)
IS BEGIN
open p_cursor for select * from GSG where GSG03='1G' ;
END get;
END TYPEDEFINE;
/
DECLARE
P_CURSOR TYPEDEFINE.cursortype;BEGIN
TYPEDEFINE.GET( P_CURSOR );
loop
fetch P_CURSOR into ...;
exit when P_CURSOR%notfound;
dbms_output.put_line(...);
end loop;
END;
/