EXECUTE IMMEDIATE 执行返回的结果是多行数据,我想把数据做为函数返回值返回。
网上看到的思路是 建立一个表类型,把EXECUTE IMMEDIATE 结果给该表类型变量,返回其变量。
按其方法还是没有弄出来,忘大虾指教。
--创建表类型
create or replace type tp_obj as object
(
ip varchar2(64),
term_type varchar2(64),
month_online varchar2(64),
avg_time varchar2(64),
lost_rate varchar2(64),
month_unclose varchar2(64),
unclose_time varchar2(64),
hall_name varchar2(64),
hall_type varchar2(64),
city_name varchar2(64),
dept_name varchar2(64),
ip_user varchar2(64),
con_phone varchar2(64),
equipment_ip varchar2(64),
equipment_name varchar2(64)
)
;create or replace type tb_report as table of tp_obj;
---创建函数
create or replace function func_bo_ping_dayreport
(
tablename in varchar2,
monthdate in varchar2,
deptid in varchar2
)
return tb_report;
as
v_tb tb_report := tb_report();
sql_str varchar2(8000);
begin
sql_str :=.....很长。。,sql字符串没问题。
EXECUTE IMMEDIATE sql_str returning bulk collect into v_tb;
return v_tb;
end func_bo_ping_dayreport;
网上看到的思路是 建立一个表类型,把EXECUTE IMMEDIATE 结果给该表类型变量,返回其变量。
按其方法还是没有弄出来,忘大虾指教。
--创建表类型
create or replace type tp_obj as object
(
ip varchar2(64),
term_type varchar2(64),
month_online varchar2(64),
avg_time varchar2(64),
lost_rate varchar2(64),
month_unclose varchar2(64),
unclose_time varchar2(64),
hall_name varchar2(64),
hall_type varchar2(64),
city_name varchar2(64),
dept_name varchar2(64),
ip_user varchar2(64),
con_phone varchar2(64),
equipment_ip varchar2(64),
equipment_name varchar2(64)
)
;create or replace type tb_report as table of tp_obj;
---创建函数
create or replace function func_bo_ping_dayreport
(
tablename in varchar2,
monthdate in varchar2,
deptid in varchar2
)
return tb_report;
as
v_tb tb_report := tb_report();
sql_str varchar2(8000);
begin
sql_str :=.....很长。。,sql字符串没问题。
EXECUTE IMMEDIATE sql_str returning bulk collect into v_tb;
return v_tb;
end func_bo_ping_dayreport;
SQL> CREATE OR REPLACE FUNCTION f_cur RETURN SYS_REFCURSOR IS
2 o SYS_REFCURSOR;
3 BEGIN
4 OPEN o FOR 'select * from emp';
5 RETURN o;
6 END;
7 /函数已创建。SQL> select f_cur from dual;F_CUR
--------------------
CURSOR STATEMENT : 1CURSOR STATEMENT : 1 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 2850 30
7782 CLARK MANAGER 7839 1981-06-09 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 3000 20
7839 KING PRESIDENT 1981-11-17 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 1100 20
7900 JAMES CLERK 7698 1981-12-03 950 30
7902 FORD ANALYST 7566 1981-12-03 3000 20
7934 MILLER CLERK 7782 1982-01-23 1300 10已选择14行。
SQL>
SQL> CREATE OR REPLACE PROCEDURE p_cur(o OUT SYS_REFCURSOR) IS
2 BEGIN
3 OPEN o FOR 'select * from emp';
4 END;
5 /过程已创建。SQL> var cur refcursor;
SQL> exec p_cur(:cur);PL/SQL 过程已成功完成。SQL> print cur; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 2850 30
7782 CLARK MANAGER 7839 1981-06-09 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 3000 20
7839 KING PRESIDENT 1981-11-17 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 1100 20
7900 JAMES CLERK 7698 1981-12-03 950 30
7902 FORD ANALYST 7566 1981-12-03 3000 20
7934 MILLER CLERK 7782 1982-01-23 1300 10已选择14行。SQL>
CREATE OR REPLACE FUNCTION func_bo_ping_dayreport(tablename IN VARCHAR2,
monthdate IN VARCHAR2,
deptid IN VARCHAR2)
RETURN tb_report AS
sql_str VARCHAR2(8000);BEGIN
sql_str := 'select tp_obj(
ip ,
term_type ,
month_online ,
avg_time ,
lost_rate ,
month_unclose ,
unclose_time ,
hall_name ,
hall_type ,
city_name ,
dept_name ,
ip_user ,
con_phone ,
equipment_ip ,
equipment_name
) from your_table'; EXECUTE IMMEDIATE sql_str
RETURNING BULK COLLECT
INTO v_tb; RETURN v_tb;
END func_bo_ping_dayreport;
上面的3个方法我都试了,
只有返回游标那个在sql window中执行成功了(command window中 value error),
但查询出来的只有一行<Cursor>,由于函数要在sql语句中使用,我要的效果是把游标结果查出来,或者函数返回的就是一个多行数据的。
http://blog.csdn.net/minitoy/archive/2011/01/23/6159575.aspx
Oracle Pipelined Table Functions简介