13518219792
我刚做的，参考下：
成都创新互联成立于2013年，我们提供高端重庆网站建设公司、成都网站制作公司、成都网站设计公司、网站定制、成都营销网站建设、微信小程序开发、微信公众号开发、成都网站营销服务，提供专业营销思路、内容策划、视觉设计、程序开发来完成项目落地，为橡塑保温企业提供源源不断的流量和订单咨询。
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;
/
Oracle 需要通过 返回一个游标来处理。
-- 测试表数据。
select * from test_main;
ID VALUE
---------- --------------------
1 ONE
3 THREE
2 TWO
-- 返回结果集的函数.
CREATE OR REPLACE FUNCTION Get_Test_Main_All
RETURN SYS_REFCURSOR
IS
return_cursor SYS_REFCURSOR;
BEGIN
OPEN return_cursor FOR 'SELECT * FROM test_main';
RETURN return_cursor;
END;
/
-- 普通的查询，来查看结果.
SELECT Get_Test_Main_All() FROM dual;
GET_TEST_MAIN_ALL()
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
ID VALUE
---------- --------------------
1 ONE
3 THREE
2 TWO
-- 存储过程调用， 来获取结果.
DECLARE
-- 调用函数的返回值.
testCursor SYS_REFCURSOR;
-- 存储单行数据.
testRec test_main%ROWTYPE;
BEGIN
-- 调用返回结果集的函数.
testCursor := Get_Test_Main_All();
-- 循环，遍历结果.
LOOP
-- 游标向前.
FETCH testCursor INTO testRec;
-- 无数据的情况下，退出循环.
EXIT WHEN testCursor%NOTFOUND;
-- 输出调试信息.
dbms_output.put_line( TO_CHAR(testRec.id) || ' ' || testRec.value);
END LOOP;
END;
/
1 ONE
3 THREE
2 TWO
PL/SQL 过程已成功完成。
--------------------------------------------------------------------------------
上面的是使用动态SQL处理的。
下面是正常SQL处理的。
CREATE OR REPLACE FUNCTION Get_Test_Main
RETURN SYS_REFCURSOR
IS
return_cursor SYS_REFCURSOR;
BEGIN
OPEN return_cursor FOR SELECT * FROM test_main;
RETURN return_cursor;
END;
/
SQL SELECT Get_Test_Main() FROM dual;
GET_TEST_MAIN()
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
ID VALUE
---------- --------------------
2 TWO
--------------------------------------------------------------------------------
上面的是没有参数的
下面是有参数的
CREATE OR REPLACE FUNCTION Get_Test_Main (
p_id INT
)
RETURN SYS_REFCURSOR
IS
return_cursor SYS_REFCURSOR;
BEGIN
OPEN return_cursor FOR SELECT * FROM test_main WHERE id = p_id;
RETURN return_cursor;
END;
/
SQL SELECT Get_Test_Main(2) FROM dual;
GET_TEST_MAIN(2)
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
ID VALUE
---------- --------------------
2 TWO
返回游标的函数，不是 “表值函数”
SQL SELECT * FROM Get_Test_Main(2);
SELECT * FROM Get_Test_Main(2)
*
ERROR 位于第 1 行:
ORA-00933: SQL 命令未正确结束
返回cursor的话，那么必须要cursor的手段来处理，不能作为查询语句的目的表。
如果需要在函数返回一个可以供查询语句使用的结果集，那么该函数的返回类型应该定义为一个索引表类型(一个table类型)，然后在查询语句中使用table函数将函数返回的索引表转换成查询可以使用的目的表。示例如下:
1. 创建返回索引表所需的类型
create or replace type type_rec is object (idx integer, user_name varchar2(50));
create or replace type type_tb is table of type_rec;
2. 创建函数
create or replace function fn_return_tb
return type_tb
is
o_tb type_tb := type_tb();
i number := 0;
begin
for v_rec in (select 1 as idx, 'Andy' as user_name from dual
union select 2, 'Jack' from dual
union select 3, 'Paul' from dual) loop
o_tb.extend;
i := i + 1;
o_tb(i) := type_rec (v_rec.idx, v_rec.user_name);
end loop;
return o_tb;
end fn_return_tb;
3. 调用函数
select s.*
from table(fn_return_tb()) s;
执行结果如下图，
oracle的function是不能执行ddl的 就是类似创建表这种不用commit的操作
返回表名 比如你传入一个表名 就返回它 可以建一个function 但只是学习 没什么意义
create or replace function testFunction(table_name in varchar2)
return varchar2 is
begin
return table_name;
end;
-- 定义类型
CREATE OR REPLACE TYPE MyTable AS OBJECT(A int, B int, C int);
/
CREATE OR REPLACE TYPE MyTableResult IS TABLE OF MyTable;
/
CREATE OR REPLACE FUNCTION getTestTable return MyTableResult
IS
-- 预期返回结果.
return_Result MyTableResult := MyTableResult();
BEGIN
-- 结果追加一行.
return_Result.EXTEND;
-- 设置结果内容.
return_Result(return_Result.COUNT) := MyTable(A = 1, B=2, C=3);
-- 结果追加一行.
return_Result.EXTEND;
-- 设置结果内容.
return_Result(return_Result.COUNT) := MyTable(A = 4, B=5, C=6);
-- 结果追加一行.
return_Result.EXTEND;
-- 设置结果内容.
return_Result(return_Result.COUNT) := MyTable(A = 7, B=8, C=9);
return return_Result;
END getTestTable;
/
SQL SELECT
2 A, B, C
3 FROM
4 table( getTestTable() );
A B C
---------- ---------- ----------
1 2 3
4 5 6
7 8 9
SQL
作为结果返回，这正是引用游标SYS_REFCURSOR或者类型TABLE TYPE来实现的功能。
