Oracle:从记录数据类型中选择 [英] Oracle: Select From Record Datatype

问题描述

我有一个返回记录数据类型的函数(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:从记录数据类型中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！