oracle PL/SQL 函数返回没有值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15334301/
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 returned without value
提问by Bishan
I wrote below FUNCTIONto check given reference numberis exist.
我写在下面FUNCTION检查给出的reference number是否存在。
FUNCTION find_reference_no(
p_ref_no IN VARCHAR2) RETURN VARCHAR2
AS
v_ref_no varchar2(50);
BEGIN
select REF_NO into v_ref_no from cash where REF_NO = p_ref_no;
EXCEPTION
WHEN no_data_found THEN
v_ref_no := '#';
RETURN v_ref_no;
END;
I have called this function in a AFTER INSERT TRIGGER. when I'm inserting data, Ii'm getting error as
我在AFTER INSERT TRIGGER. 当我插入数据时，我收到错误
ORA-06503: PL/SQL: Function returned without value
ORA-06503: PL/SQL: Function returned without value
How can I solve this ?
我该如何解决这个问题？
PS:I'm not inserting data into cashtable. I'm inserting data into another table (assume it is table B) and called this function in it's (table B) AFTER INSERT TRIGGER.
PS：我没有将数据插入cash表中。我将数据插入另一个表（假设它是表 B）并在它的 (table B) 中调用这个函数AFTER INSERT TRIGGER。
回答by Nick Krasnov
All functions must execute a RETURN statement. Your function has the RETURN in its exception block, so that statement won't get executed in normal circumstances.
所有函数都必须执行 RETURN 语句。您的函数在其异常块中有 RETURN，因此在正常情况下不会执行该语句。
Enclosing selectstatement by additional begin endblock with its own exceptionsection will solve your problem. So your function might look like this:
select附加begin end块的封闭语句exception将解决您的问题。所以你的函数可能是这样的：
create or replace function find_reference_no(
p_ref_no IN VARCHAR2) return varchar2
AS
v_ref_no varchar2(50);
begin
begin
select REF_NO
into v_ref_no
from cash
where REF_NO = p_ref_no;
exception
WHEN no_data_found THEN
v_ref_no := '#';
end;
return v_ref_no;
end;
回答by David Aldridge
Since the function just returns the same value as the parameter if it exists in the table, you could avoid the awkward use of exception handling and rewrite this as:
由于该函数仅返回与表中存在的参数相同的值，因此您可以避免异常处理的笨拙使用并将其重写为：
function find_reference_no(
ref_no in varchar2)
return varchar2
as
row_count integer
begin
select count(*)
into row_count
from cash
where cash.ref_no = find_reference_no.ref_no and
rownum = 1
if row_count = 0
return '#'
else
return ref_no
end if;
end find_reference_no;
I'd return a 1 or 0 (ie. the value of row_count) to indicate that the record does or does not exist though.
我会返回一个 1 或 0（即 row_count 的值）来指示该记录是否存在。
function find_reference_no(
ref_no in varchar2)
return varchar2
as
row_count integer
begin
select count(*)
into row_count
from cash
where cash.ref_no = find_reference_no.ref_no and
rownum = 1
return row_count
end find_reference_no;
