语法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;