Функция возвращает вызов sys_refcursor из sql с определенными столбцами

Вопрос:Это может показаться немного глупым, но я хотел бы знать, возможно ли это. У меня есть функция, возвращающая sys_refcursor CREATE OR REPLACE FUNCTION get_employee_details(p_emp_no IN EMP.EMPNO%TYPE) RETURN SYS_REFCURSOR AS o_cursor SYS_REFCURSOR; BEGIN OPEN o_cursor FOR SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM emp WHERE EMPNO = p_emp_no; RETURN o_cursor; -- exception part

Вопрос:

Это может показаться немного глупым, но я хотел бы знать, возможно ли это.

У меня есть функция, возвращающая sys_refcursor

CREATE OR REPLACE FUNCTION get_employee_details(p_emp_no IN EMP.EMPNO%TYPE) RETURN SYS_REFCURSOR AS o_cursor SYS_REFCURSOR; BEGIN OPEN o_cursor FOR SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM emp WHERE EMPNO = p_emp_no; RETURN o_cursor; — exception part END; /

и я мог бы получить результаты, используя

select get_employee_details(‘7369’) from dual;

Можно ли получить результат из указанной выше функции, указав имя столбца?
Например. Если я захочу получить ename или зарплату, как я могу указать в инструкции sql без использования блока plsql?
Что-то вроде

select get_employee_details(‘7369’) <specific column> from dual; Лучший ответ:

Нет, а не с ref cursor вообще, и в противном случае, не создавая типы SQL для возврата, как этот пример: http://dbaspot.com/oracle-server/9308-select-ref-cursor.html:

create or replace type myType as object ( a int, b varchar2(10) ) / create or replace type myTable as table of myType; / create or replace function f1 return myTable as l_data myTable := myTable(); begin for i in 1 .. 5 loop l_data.extend; l_data(i) := myType(i, ‘Row #’||i ); end loop; return l_data; end; / select * from TABLE ( cast( f1() as myTable ) ); ———- ———- 1 Row #1 2 Row #2 3 Row #3 4 Row #4 5 Row #5

Из последнего сообщения в этом потоке:

то, о чем вы уже знали, является единственным, кто может использовать REF CURSOR в выражении select.

Ответ №1

Для этой цели вы можете взглянуть на функции PIPELINED. Однако вам придется объявлять явный тип на уровне PL/SQL. Эта часть установит имя столбца вывода:

CREATE OR REPLACE TYPE my_rec AS OBJECT ( c CHAR, n NUMBER(1) ); CREATE OR REPLACE TYPE my_tbl AS TABLE OF my_rec;

Теперь большим преимуществом является то, что вы можете не только “переименовать” ваши столбцы, но и модифицировать записи с вашего курсора на лету. Для примера:

CREATE OR REPLACE FUNCTION my_fct RETURN my_tbl PIPELINED AS — dummy data — use your own cursor here CURSOR data IS SELECT ‘a’ as A, 1 AS B FROM DUAL UNION SELECT ‘b’, 2 FROM DUAL UNION SELECT ‘c’, 3 FROM DUAL UNION SELECT ‘d’, 4 FROM DUAL; BEGIN FOR the_row IN data LOOP PIPE ROW(my_rec(the_row.a, the_row.b*2)); — ^^ — Change data on the fly END LOOP; END

Использование:

SELECT * FROM TABLE(my_fct()) — ^^^^^^^^^^^^^^^ — Use this «virtual» table like any — other table. Supporting `WHERE` clause — or any other SELECT clause you want

Производство:

C N a 2 b 4 c 6 d 8

Оцените статью
Добавить комментарий