row_to_col_func 为一个函数 函数地址
返回一个 sys_refcursor 游标。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: 无法从非嵌套表项访问行。
如下: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')
);
望高手解答。
返回一个 sys_refcursor 游标。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: 无法从非嵌套表项访问行。
如下: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')
);
望高手解答。
execute tzh.employees.getemprecords(:c)
print c
2 /
Type created
SQL> create or replace type type_t_emp as table of type_obj_emp;
2 /
Type createdSQL> 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>