本文介绍了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;更多推荐
Oracle PLSQL返回一行类型
发布评论