PL/SQL 函数返回 ORA-06503：PL/SQL：函数返回无值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16505223/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PL/SQL Function returns ORA-06503: PL/SQL: Function returned without value
提问by Dilshad Abduwali
I have to write a pl/sql code(actually a function) which returns a postcode when by taking the suburb as its parameter the code is like this:
我必须编写一个 pl/sql 代码（实际上是一个函数），当将郊区作为其参数时，该代码返回一个邮政编码，代码如下所示：
create or replace
FUNCTION get_postCode(p_suburb IN varchar2)
RETURN varchar2
IS
--
v_postcode varchar2;
--
CURSOR c1 IS
SELECT locality, postcode FROM table_postcode;
--
BEGIN
--
FOR r1 IN c1
loop
IF upper(r1.locality) = upper(p_suburb)
THEN
v_postcode := r1.postcode;
return v_postcode;
END IF;
exit WHEN c1%notfound;
END loop;
-- return v_postcode;
--
exception WHEN others then
v_postcode := null;
END;
the table_postcode was obtained from Post Office an it contains the suburbs(locality as column in the talble) and postcode and other fields that are not relevant to this case.
table_postcode 是从邮局获得的，它包含郊区（位置作为表格中的列）和邮政编码以及与此案例无关的其他字段。
when I use the function it is return correct value and when I use this function as on of the columns of the SELECT clause it return ONLY if I don't add any further clauses after FROM clause. It is quit strange for me.
当我使用该函数时，它返回正确的值，当我将此函数用作 SELECT 子句的列时，它仅在我不在 FROM 子句之后添加任何其他子句时才返回。这对我来说很奇怪。
the situation is :
情况是：
select street, suburb, get_postcode(suburb) from my_table;
the line above gives me the result but
上面的行给了我结果但是
select street, subur, get_postcode(suburb) from my_table order by suburb;
fails and gives me the following error message:
失败并给我以下错误消息：
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "U11254683.GET_POSTCODE", line 25
06503. 00000 - "PL/SQL: Function returned without value"
*Cause: A call to PL/SQL function completed, but no RETURN statement was
executed.
*Action: Rewrite PL/SQL function, making sure that it always returns
a value of a proper type.
if I call the function in a block like:
如果我在块中调用该函数，例如：
Declare
v_post varchar2(10);
Begin
v_post := get_postcode('Sydney');
DBMS_OUTPUT.PUT_LINE('The post code is '||v_post);
End;
result is correct and gives me 2000.
结果是正确的，给了我 2000。
回答by hol
You should return something when nothing is found. Before exceptioncomment in the statement
当什么都没有找到时，你应该返回一些东西。在exception声明中发表评论之前
return v_postcode;
The way your routine is written it may does find nothing and then hits the end of the function without returning anything, hence the error "function returned without value". It is literally exactly this.
您的例程的编写方式可能没有发现任何内容，然后在不返回任何内容的情况下到达函数的末尾，因此出现错误“函数返回无值”。字面意思就是这样。
However, you can do this much easier by directly selecting on the suburb. But what would you do if you get more than one postcode to suburb (if this is possible)?
但是，您可以通过直接选择郊区来轻松完成此操作。但是，如果您在郊区收到多个邮政编码（如果可能的话），您会怎么做？
create or replace FUNCTION get_postCode(i_suburb IN varchar2)
RETURN varchar2
IS
l_postcode varchar2;
BEGIN
select postcode
into l_postcode
where upper(i_suburb) = upper(locality);
--
return l_postcode;
exception
WHEN others then
return null;
END;
In this version you would get null if one suburb exists in more than one postcode. It is now up to your design decision how to handle that situation. You could then handle it in the exception
在此版本中，如果一个郊区存在于多个邮政编码中，您将得到 null。现在由您的设计决定如何处理这种情况。然后你可以在异常中处理它
exception
when TOO_MANY_ROWS then
return '**Error** Several Values'; -- do something meaningful here
when NO_DATA_FOUND then
return null; -- if you want to return null on nothing found
回答by Rachcha
Try this:
尝试这个：
CREATE OR REPLACE FUNCTION get_postCode
(p_suburb IN varchar2)
RETURN varchar2
IS
v_postcode varchar2;
CURSOR c1 IS
SELECT locality, postcode FROM table_postcode;
BEGIN
FOR r1 IN c1 LOOP
EXIT WHEN c1%notfound;
IF upper(r1.locality) = upper(p_suburb) THEN
v_postcode := r1.postcode;
EXIT;
END IF;
END LOOP;
return v_postcode;
exception WHEN others then
return null;
END;
/
By exiting the FORloop on finding out the first post code you are ensuring that there is not a TOO_MANY_ROWSexception raised. Also, by putting the returnoutside the IFstatement you are ensuring that atleast a NULLis returned in case nothing is found.
通过FOR在找出第一个邮政编码时退出循环，您可以确保没有TOO_MANY_ROWS引发异常。此外，通过将语句放在return外面，IF您可以确保NULL在未找到任何内容的情况下至少返回a 。
