oracle8i中的case when似乎应该出现在select 语句中。
select case when v_test1=1 then 'one'
when v_test1>1 then '>1!'
when v_test1<1 then 'no match!'
end
from dual
select case when v_test1=1 then 'one'
when v_test1>1 then '>1!'
when v_test1<1 then 'no match!'
end
from dual
CASE selector
WHEN expression1 THEN result1
WHEN expression2 THEN result2
...
WHEN expressionN THEN resultN
[ELSE resultN+1]
END;
例子:
DECLARE
grade CHAR(1);
appraisal VARCHAR2(20);
BEGIN
...
appraisal :=
CASE grade
WHEN ’A’ THEN ’Excellent’
WHEN ’B’ THEN ’Very Good’
WHEN ’C’ THEN ’Good’
WHEN ’D’ THEN ’Fair’
WHEN ’F’ THEN ’Poor’
ELSE ’No such grade’
END;
...
END;
CASE
WHEN search_condition1 THEN result1
WHEN search_condition2 THEN result2
...
WHEN search_conditionN THEN resultN
[ELSE resultN+1]
END;例子2:
DECLARE
grade CHAR(1);
appraisal VARCHAR2(20);
BEGIN
...
appraisal :=
CASE
WHEN grade = ’A’ THEN ’Excellent’
WHEN grade = ’B’ THEN ’Very Good’
WHEN grade = ’C’ THEN ’Good’
WHEN grade = ’D’ THEN ’Fair’
WHEN grade = ’F’ THEN ’Poor’
ELSE ’No such grade’
END;
...
END;
v_Test1 NUMBER := 2;
BEGIN
CASE
WHEN v_Test1 = 1 THEN
DBMS_OUTPUT.PUT_LINE('One!')
WHEN v_Test1 > 1 THEN
DBMS_OUTPUT.PUT_LINE('> 1!')
ELSE
DBMS_OUTPUT.PUT_LINE('No match')
END;
END;
/
v_Test1 NUMBER := 2;
BEGIN
if v_Test1 = 1 THEN
DBMS_OUTPUT.PUT_LINE('One!');
elsif v_Test1 > 1 THEN
DBMS_OUTPUT.PUT_LINE('> 1!');
ELSE
DBMS_OUTPUT.PUT_LINE('No match');
END if;
END;
/
这种问题我碰到过,当时也是问谁谁不知道,郁闷了好久