问题描述
我有一个返回记录数据类型的函数(2个字段:ID和Name).如何从select语句中获取数据?
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?
具体来说，我正在尝试使用OracleCommand对象，尝试将该对象放入我的C#代码中.我最初尝试过...
Specifically, I am trying using an OracleCommand object attempting to get the object into my C# code. I initially tried ...
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()
...和...
SELECT * FROM TABLE ( FUNCTION_NAME() )
...无济于事.我想我正在寻找...
... to no avail. I guess I am looking for ...
SELECT * FROM RECORD ( FUNCTION_NAME() )
...当然不存在.
我唯一能想到的解决方案是将该函数调用包装在另一个函数调用中，其中外部函数返回一个包含该唯一记录的记录表.但是，这似乎很麻烦，我正在寻找一种更简单的方法.任何帮助将不胜感激.
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.
对不起，我也尝试过SELECT FUNCTION_NAME() FROM DUAL.
推荐答案
记录数据类型是PL/SQL数据类型. SQL对此一无所知.这可能就是为什么您遇到错误.参见以下示例:
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:
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
我指的是SQL错误.您可以通过PL/SQL调用它:
The error in SQL I was referring to.You can call it from PL/SQL though:
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.
如果要在SQL中使用该函数，则可以创建一个SQL对象类型.请注意，直接从C#调用函数看起来比坚持使用SQL来执行此操作更可取.但仅作记录:
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> 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.
关于，罗布.
这篇关于Oracle:从记录数据类型中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！