Oracle PLSQL返回一行类型 [英] Oracle PLSQL return one row type

问题描述

我想创建一个仅在一行中返回多个值的函数:

Hi i want create a function that return multiple values in only one row:

create or replace TYPE foo_type AS OBJECT (
col1 NUMBER             ,
col2 VARCHAR2(150 BYTE) 
);

CREATE FUNCTION foo_function( ) RETURN foo_type 
as
 row_type foo_type;

select
 b1,  -- NUMBER TYPE            
 b2   -- VARCHAR2(150 BYTE) TYPE
into 
 row_type 
from 
 table_xxx 
where 
 rownum=1; --Only one row!

return row_type;

END  foo_function;

如果我编译，我收到: ORA-00947值不足

If i compile i received: ORA-00947 not enough values

我尝试过:

select
 b1,  -- NUMBER TYPE            
 b2   -- VARCHAR2(150 BYTE) TYPE
into 
 row_type.col1,
 row_type.col2 
from 
 table_xxx 
where 
 rownum=1; --Only one row!

函数被编译，但是当id运行时:

And the function is compiled but when id run:

select foo_function() from dual;

Oracle返回: ORA-06530对未初始化的组合的引用

Oracle return: ORA-06530 reference to uninitialized composite

推荐答案

select b1, b2
into   row_type 
from   table_xxx 
where  rownum=1;

之所以抛出ORA-00947 not enough values是因为有两个值b1和b2，但是只有一个变量row_type可以将它们插入.

Is throwing ORA-00947 not enough values because there are two values b1 and b2 but only one variable row_type to insert them into.

您可以执行以下操作:

CREATE FUNCTION foo_function( ) RETURN foo_type 
as
  row_type foo_type;
BEGIN                        -- missing BEGIN
  select foo_type ( b1, b2 ) -- You need to put the values into a foo_type object
  into   row_type 
  from   table_xxx 
  where  rownum=1;

  return row_type;
END  foo_function;

或者:

CREATE FUNCTION foo_function( ) RETURN foo_type 
as
  row_type foo_type := foo_type( NULL, NULL ); -- Initialise the object.
BEGIN
  select b1,            b2
  into   row_type.col1, row_type.col2
  from   table_xxx 
  where  rownum=1;

  return row_type;
END  foo_function;

这篇关于Oracle PLSQL返回一行类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！