oracle过程返回一张表,ORACLE 存储过程怎么返回临时表结果集
我刚做的，参考下：
SQL> CREATE GLOBAL TEMPORARY TABLE REPROTTEST(
2 ID NUMBER,
3 ANAME VARCHAR2(20)
4 ) ON COMMIT DELETE ROWS;(也可以用PRESERVER ROWS，看实际需求)
Table created
SQL> create or replace procedure report_month_responsibility(
2 o_cur out sys_refcursor)
3 as
4 begin
5 insert into reprottest(id,aname) values(1,'1');
6 open o_cur for select * from reprottest;
7 end report_month_responsibility;
8 /
Procedure created
SQL> set serverout on
SQL> declare
2 v_id number;
3 v_aname varchar2(20);
4 o_cur sys_refcursor;
5 begin
6 report_month_responsibility(o_cur);
7 fetch o_cur into v_id,v_aname;
8 while o_cur%found loop
9 dbms_output.put_line('输出结果：'||v_id||','||v_aname);
10 fetch o_cur into v_id,v_aname;
11 end loop;
12 commit;
13 end;
14 /
输出结果：1,1
PL/SQL procedure successfully completed
你的那个ORA-01031: insufficient privileges，是权限不足的问题。
SQL代码：
CREATE GLOBAL TEMPORARY TABLE REPROTTEST(
ID NUMBER,
ANAME VARCHAR2(20)
) ON COMMIT DELETE ROWS;
create or replace procedure report_month_responsibility(
o_cur out sys_refcursor)
as
begin
insert into reprottest(id,aname) values(1,'1');
open o_cur for select * from reprottest;
end report_month_responsibility;
declare
v_id number;
v_aname varchar2(20);
o_cur sys_refcursor;
begin
report_month_responsibility(o_cur);
fetch o_cur into v_id,v_aname;
while o_cur%found loop
dbms_output.put_line('输出结果：'||v_id||','||v_aname);
fetch o_cur into v_id,v_aname;
end loop;
commit;
end;
/
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！