Oracle PLSQL返回一行类型

本文介绍了Oracle PLSQL返回一行类型的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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;