Oracle：从记录数据类型中选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1020348/
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
Oracle: Select From Record Datatype
提问by Rob van Wijk
I have a function that returns a record datatype (2 fields: ID and Name). How can I get at the data from a select statement?
我有一个返回记录数据类型的函数（2 个字段：ID 和名称）。如何从 select 语句中获取数据？
Specifically, I am trying using an OracleCommand object attempting to get the object into my C# code. I initially tried ...
具体来说，我正在尝试使用 OracleCommand 对象尝试将该对象放入我的 C# 代码中。我最初尝试...
CALL FUNCTION_NAME() INTO :loRetVal
... but I get a data type error for whatever type I use. I have also tried ...
...但无论我使用什么类型，我都会收到数据类型错误。我也试过...
SELECT * FROM FUNCTION_NAME()
... and ...
... 和 ...
SELECT * FROM TABLE ( FUNCTION_NAME() )
... to no avail. I guess I am looking for ...
……无济于事。我想我正在寻找...
SELECT * FROM RECORD ( FUNCTION_NAME() )
... which, of course, doesn't exist.
……这当然不存在。
The only solution I have been able to come up with is to wrap this function call in another function call in which the outer function returns a TABLE of records containing this sole record. This, however, seems cumbersome and I am looking for a simpler method. Any help would be appreciated.
我能想出的唯一解决方案是将此函数调用包装在另一个函数调用中，其中外部函数返回一个包含该唯一记录的记录表。然而，这似乎很麻烦，我正在寻找一种更简单的方法。任何帮助，将不胜感激。
EDIT: Sorry, I have also tried SELECT FUNCTION_NAME() FROM DUAL.
编辑：对不起，我也试过SELECT FUNCTION_NAME() FROM DUAL。
回答by Rob van Wijk
A record datatype is a PL/SQL datatype. SQL doesn't know about it. That's probably why you are getting an error. See this example:
记录数据类型是一种 PL/SQL 数据类型。SQL 对此一无所知。这可能就是您收到错误的原因。看这个例子：
SQL> create package mypkg
2 as
3 type myrec is record
4 ( id int
5 , name varchar2(10)
6 );
7 function f return myrec;
8 end mypkg;
9 /
Package created.
SQL> create package body mypkg
2 as
3 function f return myrec
4 is
5 r myrec;
6 begin
7 r.id := 1;
8 r.name := 'test';
9 return r;
10 end f;
11 end mypkg;
12 /
Package body created.
SQL> desc mypkg
FUNCTION F RETURNS RECORD
ID NUMBER(38) OUT
NAME VARCHAR2(10) OUT
SQL> select mypkg.f from dual
2 /
select mypkg.f from dual
*
ERROR at line 1:
ORA-00902: invalid datatype
The error in SQL I was referring to. You can call it from PL/SQL though:
我所指的 SQL 中的错误。你可以从 PL/SQL 调用它：
SQL> declare
2 r mypkg.myrec;
3 begin
4 r := mypkg.f;
5 dbms_output.put_line(r.id);
6 dbms_output.put_line(r.name);
7 end;
8 /
1
test
PL/SQL procedure successfully completed.
If you want to use the function in SQL, then you can create a SQL objecttype. Note that calling your function directly from C# looks way more preferable than insisting on using SQL to do this. But just for the record:
如果您想在 SQL 中使用该函数，那么您可以创建一个 SQL 对象类型。请注意，直接从 C# 调用您的函数看起来比坚持使用 SQL 来执行此操作更可取。但只是为了记录：
SQL> drop package mypkg
2 /
Package dropped.
SQL> create type myobj is object
2 ( id int
3 , name varchar2(10)
4 );
5 /
Type created.
SQL> create package mypkg
2 as
3 function f return myobj;
4 end mypkg;
5 /
Package created.
SQL> create package body mypkg
2 as
3 function f return myobj
4 is
5 begin
6 return myobj(1,'test');
7 end f;
8 end mypkg;
9 /
Package body created.
SQL> select mypkg.f from dual
2 /
F(ID, NAME)
--------------------------------------------------------------
MYOBJ(1, 'test')
1 row selected.
Regards, Rob.
问候，罗布。
回答by Slowbie
Can you
你是否可以
CREATE TYPE <object name> AS TABLE OF <record type>
and use that directly in a SQL statement? I ask because I have a stored proc that I can not edit. The stored proc has an output variable that is record type that I have to reference in a SQL statement. I have already created a function to call the proc, but if I don't have to convert the record to type object that would be nice.
并直接在 SQL 语句中使用它？我问是因为我有一个无法编辑的存储过程。存储过程有一个输出变量，它是我必须在 SQL 语句中引用的记录类型。我已经创建了一个函数来调用 proc，但是如果我不必将记录转换为类型对象，那就太好了。
I would later call it like:
我以后会这样称呼它：
SELECT *
FROM TABLE( CAST( <function name>() as <object name>));
回答by ProdDBA
I think this what you are looking for; to get the values out in a select statement:
我认为这就是你正在寻找的；在 select 语句中获取值：
select result.id as id, result.name
from ( select function() as result from dual);
Because your function returns a record an not a native type you can't use the standard methods. The if you want to get the actual record as an object into C# then you have do some reading on user defined types in the ODP .net documentation.
因为您的函数返回的记录不是本机类型，所以您不能使用标准方法。如果您想将实际记录作为对象获取到 C# 中，那么您必须阅读 ODP .net 文档中的用户定义类型。
You could also wrap the function in another function that returns a ref cursor and that is used in C# in a more standard fashion.
您还可以将该函数包装在另一个函数中，该函数返回一个引用游标，并以更标准的方式在 C# 中使用。
回答by Horus
I think you are looking for PIPELINED functionality:
我认为您正在寻找 PIPELINED 功能：
CREATE TABLE test_table(tt_id INTEGER,tt_text VARCHAR2(40));
CREATE PACKAGE test_pkg IS
TYPE tp_rec IS RECORD(tt_id INTEGER,tt_text VARCHAR2(40));
TYPE tp_recs IS TABLE OF tp_rec;
FUNCTION test_func RETURN tp_recs PIPELINED;
FUNCTION test_func1 RETURN tp_recs PIPELINED;
FUNCTION test_func2(ivar INTEGER) RETURN tp_recs PIPELINED;
END;
/
CREATE OR REPLACE PACKAGE BODY test_pkg IS
FUNCTION test_func RETURN tp_recs PIPELINED
AS
currec tp_rec;
BEGIN
currec.tt_id := 1;
currec.tt_text := 'test1';
PIPE ROW(currec);
END;
FUNCTION test_func1 RETURN tp_recs PIPELINED
AS
currec tp_rec;
CURSOR t_cursor IS
SELECT * FROM test_table;
BEGIN
OPEN t_cursor;
LOOP
FETCH t_cursor INTO currec;
EXIT WHEN t_cursor%NOTFOUND;
PIPE ROW(currec);
END LOOP;
CLOSE t_cursor;
END;
FUNCTION test_func2(ivar INTEGER) RETURN tp_recs PIPELINED
AS
currec tp_rec;
BEGIN
SELECT * INTO currec FROM test_table WHERE tt_id = ivar;
PIPE ROW(currec);
END;
END;
/
BEGIN
INSERT INTO test_table VALUES(1,'test1');
INSERT INTO test_table VALUES(2,'test2');
INSERT INTO test_table VALUES(3,'test3');
COMMIT;
END;
/
SELECT * FROM TABLE(test_pkg.test_func());
SELECT * FROM TABLE(test_pkg.test_func1());
SELECT * FROM TABLE(test_pkg.test_func2(2));
The above code is tested, and should give you a good start on this. Just look up the PIPELINED keyword in Oracle for more information (assuming you are working with Oracle...)
上面的代码已经过测试，应该会给你一个良好的开端。只需在 Oracle 中查找 PIPELINED 关键字以获取更多信息（假设您正在使用 Oracle...）
回答by David
The formatting of my comment for Rob van Wijk is bad. To continue his thought.
我对 Rob van Wijk 的评论格式很糟糕。继续他的想法。
-- create a collection type
CREATE TYPE myobj_tab AS TABLE OF myobj;
-- have the function return a collection type
CREATE OR REPLACE function f return myobj_tab
IS
objtab myobj_tab;
BEGIN
objtab := myobj_tab(myobj(1,'test'));
return objtab;
end f;
-- CAST it as a table and straight up select from it.
SELECT id, name FROM TABLE(CAST(f() AS myobj_tab));
回答by Jeffrey Kemp
Why do you need to use SQL at all? You might be able to just use a System.Data.CommandType.StoredProcedure to call the function.
为什么完全需要使用 SQL？您也许可以只使用 System.Data.CommandType.StoredProcedure 来调用该函数。
call-an-oracle-function-from-c#
call-an-oracle-function-from-c#
