Question

怎么把存储过程返回值结果集放置在临时表中

Answer 1

我刚做参考：
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;
/