语法1:
CASE search_expression
WHEN expression1 THEN result1
WHEN expression2 THEN result2
....
WHEN expressionN THEN resultN
ELSE default_result
END CASE;
语法2:
CASE
WHEN condition1 THEN result1
WHEN condistion2 THEN result
....
WHEN condistionN THEN resultN
ELSE default_result
END CASE;
没有else,可能会导致报错:
如果在case语句中没有找到符合条件的when语句,并且没有ELSE子句,那么oralce便会报错:ORA-06592: 执行 CASE 语句时未找到 CASE。
例子:
-- 1980、1981、1982、1987入职员工的个数
declare
cursor c is select to_char(t.hiredate,'yyyy') from emp t ;
theyear varchar2(20);
count1980 binary_integer := 0;
count1981 binary_integer := 0;
count1982 binary_integer := 0;
count1987 binary_integer := 0;
begin
open c;
loop
fetch c into theyear;
exit when c%notfound;
--case语法1
/*case theyear
when '1980' then count1980 := count1980+1;
when '1981' then count1980 := count1980+1;
when '1982' then count1981 := count1981+1;
else count1987 := count1987+1;--注意:此处为else
end case;*/
--case语法2
case
when theyear='1980' then count1980 := count1980+1;
when theyear='1981' then count1981 := count1981+1;
when theyear='1982' then count1982 := count1982+1;
-- else count1987 := count1987+1; -- 注意:如果此处没有else,当theyear不为 1980、1981、1982时候就会报错,‘执行CASE语句时,未找到CASE’
end case;
end loop;
close c;
dbms_output.put_line(count1980+count1981+count1982+count1987||'---'||count1980||'---'||count1981||'---'||count1982||'---'||count1987);
end;