返回一个 sys_refcursor 游标。
1
2
3
4
5
6
7
8
9
10
11
12
13
|
select
row_to_col_func(
'rowtocol_test'
,
'year,month'
,
'dept'
,
'expenditure'
,Aggregate_func =>
'sum'
,colorder =>
'dept'
,roworder =>
'1,2'
,when_value_null =>
'0'
)
from
dual;
ROW_TO_COL_FUNC('ROWTOCOL_TEST
<
Cursor
>
YEAR
MONTH
部门1 部门2 部门3
2009 1 50000 0 0
2009 2 50000 40000 25000
2009 3 0 25000 0
2010 1 35000 0 0
2010 2 0 0 60000
|
游标ROWTOCOL_TEST的值在 sqlplus可以看到。
我想能否直接返回一个table
用table函数提示,ORA-22905: 无法从非嵌套表项访问行。
如下:
1
2
|
select
*
from
table
( row_to_col_func(
'rowtocol_test'
,
'year,month'
,
'dept'
,
'expenditure'
,Aggregate_func =>
'sum'
,colorder =>
'dept'
,roworder =>
'1,2'
,when_value_null =>
'0'
)
);
|
望高手解答。
动态游标是给存储过程或者匿名块调用的
要用table函数,需要写表函数。
正解:
=============================================================================================================
SQL> create or replace type type_obj_emp as object(empno number(20),ename varchar2(10));
2 /
Type created
SQL> create or replace type type_t_emp as table of type_obj_emp;
2 /
Type created
SQL> CREATE OR REPLACE FUNCTION func_return_cursor RETURN type_t_emp AS
2 v_t_emp type_t_emp;
3 BEGIN
4 SELECT type_obj_emp(empno, ename) BULK COLLECT INTO v_t_emp FROM emp;
5 RETURN v_t_emp;
6 END;
7 /
Function created
SQL> select * from table(func_return_cursor);
EMPNO ENAME
--------------------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
14 rows selected
SQL>