函数从具有特定列的 sql 返回 sys_refcursor 调用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13690110/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Function return sys_refcursor call from sql with specific columns
提问by J?cob
This may find little silly, but I would like to know whether this is possible.
这可能有点傻，但我想知道这是否可能。
I have a function which return sys_refcursor
我有一个返回 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;
/
and I could get the results using
我可以使用得到结果
select get_employee_details('7369') from dual;
Is it possible to get the result from the above function by specifying column name? E.g. If I would want to get ename or salary, how could I specify in the sql statement without using a plsql block? Something like
是否可以通过指定列名从上述函数中获取结果？例如，如果我想获得 ename 或工资，我如何在不使用 plsql 块的情况下在 sql 语句中指定？就像是
select get_employee_details('7369') <specific column> from dual;
采纳答案by J?cob
No, not with a ref cursorat all, and otherwise not without creating SQL types to cast the return into, like this example: http://dbaspot.com/oracle-server/9308-select-ref-cursor.html:
不，根本没有 a 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
From the last post on that thread:
从该线程的最后一篇文章：
the way you already knew about is the only one to use the REF CURSOR in a select statement.
您已经知道的唯一一种在 select 语句中使用 REF CURSOR 的方法。
回答by Sylvain Leroux
For that purpose, you might want to take a look at PIPELINEDfunctions. You will have to declare explicit type at PL/SQL level though. That part will set the output column name:
为此，您可能需要查看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;
Now, the great advantage is you can not only "rename" your columns, but modify the records from your cursor on the flytoo. For ex:
现在，最大的优点是，你不仅可以“重命名”你的专栏，但修改的记录，从你的光标在飞行了。例如：
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
Usage:
用法：
SELECT * FROM TABLE(my_fct())
-- ^^^^^^^^^^^^^^^
-- Use this "virtual" table like any
-- other table. Supporting `WHERE` clause
-- or any other SELECT clause you want
Producing:
生产：
C N
a 2
b 4
c 6
d 8
