加入一个 oracle 表值函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6764000/
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
Join to an oracle table valued function
提问by Ryan Fisch
Is is possible to join to an Oracle table valued function?
是否可以加入 Oracle 表值函数？
SELECT
*
FROM
SOME_TABLE a
INNER JOIN
TABLE(GET_TABLE_LIST()) b ON = a.COL_A = b.COL_A
回答by Justin Cave
You can, yes. Since I don't have your get_TrfrmEngMachineInfoTfunction, I'll create my own collection and join it to the EMPtable in the SCOTTschema
你可以，是的。由于我没有你的get_TrfrmEngMachineInfoT函数，我将创建自己的集合并将其加入架构中的EMP表SCOTT
SQL> create or replace type typ_person
2 as object (
3 person_id number,
4 person_name varchar2(30)
5 );
6 /
Type created.
SQL> create or replace type tbl_person
2 as table of typ_person;
3 /
Type created.
SQL> ed
Wrote file afiedt.buf
1 create or replace function get_person_list
2 return tbl_person
3 is
4 l_people tbl_person;
5 begin
6 select typ_person( empno, ename )
7 bulk collect into l_people
8 from emp;
9 return l_people;
10* end;
SQL> /
Function created.
SQL> select p.*
2 from emp e
3 join table( get_person_list() ) p on (p.person_id = e.empno);
PERSON_ID PERSON_NAME
---------- ------------------------------
7623 PAV
7369 smith
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 SM0
7902 FORD
7934 MILLER
1234 FOO
16 rows selected.
回答by Ozgehan
Maybe this can work for you...
也许这对你有用......
SELECT table_name.*, myFunc(table_name.OBJID) as new_field_name from table_name
That will select everything from the table_nametable and add the result of the function in to the new set.
这将从table_name表中选择所有内容并将函数的结果添加到新集合中。
myFuncuses table_name.OBJIDfield to match some records and do whatever...
myFunc使用table_name.OBJID字段来匹配一些记录并做任何事情......
