我想写个块 来查看我写的包里得过程的返回结果集,但是怎么写都不对,请高手帮忙。
我写的块:
SET serveroutput ON
DECLARE
REF CURSOR mycur ;
BEGIN
pkg_test.p_test(mycur);
FOR cur IN mycur
LOOP
dbms_output.put_line(cur.taskname);
END LOOP;
END;
网上看的pl/sql里好像不能直接定义CURSOR 变量----------------------------------------------
包如下:
CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE t_cursor IS REF CURSOR;
PROCEDURE P_test
( cur_name OUT t_cursor);
END pkg_test;CREATE OR REPLACE PACKAGE BODY pkg_test
AS
PROCEDURE P_test
( cur_name OUT t_cursor)
IS
BEGIN
OPEN cur_name FOR
SELECT * FROM t_task;
END P_test;
END pkg_test;
我写的块:
SET serveroutput ON
DECLARE
REF CURSOR mycur ;
BEGIN
pkg_test.p_test(mycur);
FOR cur IN mycur
LOOP
dbms_output.put_line(cur.taskname);
END LOOP;
END;
网上看的pl/sql里好像不能直接定义CURSOR 变量----------------------------------------------
包如下:
CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE t_cursor IS REF CURSOR;
PROCEDURE P_test
( cur_name OUT t_cursor);
END pkg_test;CREATE OR REPLACE PACKAGE BODY pkg_test
AS
PROCEDURE P_test
( cur_name OUT t_cursor)
IS
BEGIN
OPEN cur_name FOR
SELECT * FROM t_task;
END P_test;
END pkg_test;
--创建包头
create or replace package types
as procedure query(V_SQL in varchar2,RECORDS in out SYS_REFCURSOR );
end;
--创建包体
create or replace package body types is
procedure query(v_sql IN varchar2,records in out SYS_REFCURSOR)
as
begin
open records for V_sql;
end;
END types;
--调用
declare
allr number;
rs sys_refcursor;
cid number;
cname NUMBER;
begin
types.query('select SNO,ENO from tbgo',rs);
loop
fetch rs into cid,cname;
exit when rs%notfound;
dbms_output.put_line(cid);
end loop;
close rs;
end;
declare
cur sys_refcursor;
...