环境: oracle 11g客户端 PL/SQL-- 包
CREATE OR REPLACE PACKAGE RETN_VAL
IS
TYPE RETN_VAL_CUR IS REF CURSOR;
END RETN_VAL;-- 存储过程
CREATE OR REPLACE PROCEDURE USP_RETN_VAL
( RETN_VAL_CUR OUT RETN_VAL.RETN_VAL_CUR
)
IS
BEGIN
OPEN RETN_VAL_CUR FOR
SELECT * FROM TBL1;
END;请问这样可以返回数据吗?该如何调用存储过程来返回结果集?
谢谢!
CREATE OR REPLACE PACKAGE RETN_VAL
IS
TYPE RETN_VAL_CUR IS REF CURSOR;
END RETN_VAL;-- 存储过程
CREATE OR REPLACE PROCEDURE USP_RETN_VAL
( RETN_VAL_CUR OUT RETN_VAL.RETN_VAL_CUR
)
IS
BEGIN
OPEN RETN_VAL_CUR FOR
SELECT * FROM TBL1;
END;请问这样可以返回数据吗?该如何调用存储过程来返回结果集?
谢谢!
( RETN_VAL_CUR OUT RETN_VAL.RETN_VAL_CUR
)
IS
BEGIN
OPEN RETN_VAL_CUR FOR
SELECT * FROM TBL1;
END;
这里不用打开游标,可以返回个游标
参考
http://www.cnblogs.com/yangfan/archive/2010/12/09/1901386.html
CREATE OR REPLACE PROCEDURE USP_RETN_VAL
( RETN_VAL_CUR OUT sys_refcursor)
IS
BEGIN
OPEN RETN_VAL_CUR FOR
SELECT * FROM TBL1;
END;调用
declare
cur sys_refcursor;
begin
USP_RETN_VAL(cur);
open cur;
-------操作游标cur就行了
end;
type youtype is table of xxx;
youy youtype
然后直接使用select * bulk collect into youy