我可以在具有if if条件的函数内编写一个循环吗? [英] can i write a loop inside a function that has if then else if conditions?
问题描述
content:我正在编写一个函数来基于3个条件返回值，但是我需要在其中编写一个循环，以便它可以检查传递的每个id的条件.我可以写吗?
content : i am writing a function to return a value based on 3 conditions, but i need to write a loop inside it so it can check the conditions for each id passed. can i write that?
代码:
return varchar2
is
a_hldd_code varchar2();
o_result varchar2();
cursor c_get_hold_codes is
Select sprhold_hldd_code,sprhold_pidm,stvhldd_desc
from sprhold
join stvhldd on stvlhdd_pidm=sprhold_pidm
and sprhold_hldd_code like'T%';
Begin
open c_get_hold_codes;
fetch c_get_hold_codes into a_hldd_code;
close c_get_hold_codes;
if a_hldd_code in ('TL','TY'..) then
o_result := 'Level 1';
else if a_hldd_code not in () then
0_result := 'Level2';
elseif a_hldd_code is null then
o_result :='Level 3';
End if;
return o_result;
end;
如果函数在读取所有记录后处于第一个条件，则应该返回级别1.例如.一个id有5条记录，例如(TL，T8，T6，T5，T4)，它应该仅返回1级，而不返回2级...但是我的函数返回2级.我缺少什么?
The function should return level 1 if it falls in the first condition after it reads all the records. eg. an id has 5 records like (TL,T8,T6,T5,T4) it should return only level 1 and not level 2 ... but my function return level 2.. what am i missing?
create or replace FUNCTION fwt_get_holds(
i_id id.table_im%TYPE
) RETURN VARCHAR2 IS
o_level VARCHAR2(4000);
BEGIN
o_level := null;
FOR c IN ( SELECT DISTINCT sprhold_hldd_code
FROM
sprhold,
stvhldd
WHERE
stvhldd_code = sprhold_hldd_code
AND sprhold_hldd_code LIKE 'T%'
AND sprhold_to_date >= to_date(sysdate)
AND sprhold_pidm = i_id)
LOOP
IF c.sprhold_hldd_code in ('TF','TB','TY','TL','TS')
then
o_level:='Level 1';
ELSE IF c.sprhold_hldd_code not in ('TF','TB','TY','TL','TS')
then
o_level:='Level 2';
ELSE
o_level := 'Level 3';
END IF;
RETURN o_level;
END LOOP;
END fwt_get_holds;
推荐答案
从我的角度来看，您想要的一切都不会发生.坏消息，是吗?
The way I see it, nothing of what you want will happen. Bad news, eh?
您编写的代码是错误的-不是因为明显的错误，而是-游标的SELECT语句包含3列，您正在将它们提取到1个varchar2变量中. 3个不适合1个；不是那样的.
Code you wrote is wrong - not because of obvious mistakes, but - cursor's SELECT statement contains 3 columns which you're fetching into a 1 varchar2 variable. 3 can't fit into 1; not that way, that is.
此外，您会在函数内 中执行循环操作吗?当然，可以做到这一点(例如，为简单起见，切换到光标FOR循环)，但是-根据放置RETURN的位置，您将返回第一个O_RESULT值或最后一个O_RESULT值(请参见注释)在代码内):
Moreover, what would you do with a loop within the function? It can be done, of course, for example (switching to cursor FOR loop for simplicity), but - depending on where you put RETURN, you'll either return the first O_RESULT value or the last (see comments within the code):
for cur_r in (select sprhold_hldd_code, ...
from sprhold ...
where --> ID condition missing here; ID you're passing, allegedly
)
loop
if cur_r.sprhold_hldd_code in ('TL', 'TY', ...) then ...
-- in a number of IFs, you find what O_RESULT variable is
end if;
-- if you put RETURN here, only one loop iteration will execute
end loop;
-- if you put RETURN here, only the last O_RESULT value will be returned
这意味着您实际上要在函数外放置一个循环，即为要传递给该函数的所有ID循环调用该函数.像这样:
It means that you'd actually want to put a loop OUTSIDE of the function, i.e. call the function in a loop for all those IDs you're about to pass to the function. Something like this:
function f_result (par_id in number) return varchar2 is
o_result varchar2(20);
begin
select sprhold_hldd_code
into l_sprhold_hldd_code
from sprhold ...
where some_id = par_id;
if l_sprhold_hldd_code in ...
-- find O_RESULT in a number of IFs
end if;
return o_result;
end;
现在循环调用
begin
for cur_r in (select id from some_table where some_condition) loop
dbms_output.put_line('For ID = ' || cur_r.id || ', function returned ' || f_result(cur_r.id));
end loop;
end;
如果以上方法均无济于事，请尝试改写该问题.
If none of above helps, try to rephrase the question.
这篇关于我可以在具有if if条件的函数内编写一个循环吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
