Oracle Tutorial: CASE statement and CASE expression

Case statements and case expressions are an important part of plsql Process Control. Although they are easy to use, they are easy to confuse. This article describes the case statement and case expression, and provides a demonstration to better understand the similarities and differences between the two and the precautions for use.

I. Simple case statements

1. --> Syntax
3. CASESELECTOR
4. WHENEXPRESSION 1THENSTATEMENT 1;
5. WHENEXPRESSION 2THENSTATEMENT 2;
6. ...
7. WHENEXPRESSION NTHENStatement n;
8. ELSEStatement n + 1;
9. END CASE;
11. -- A simple case statement indicates that the value or result obtained in SELECTOR matches the value or result in EXPRESSION n. Once a match is found, the corresponding statement is executed. Until it is found.
12. -- If EXPRESSION n does not match, then the ELSE runs the corresponding statement.
13. -- ELSE is optional. If any matching item is omitted and no matching item is found in the when condition, the case_not_found exception is received.
15. --> Demonstrate simple case statements
16. Sys @ ORCL>DECLARE
17. 2 v_num NUMBER: = & in_num;
18. 3 v_flag NUMBER;
19. 4BEGIN
20. 5 v_flag: = MOD (v_num, 2 );
21. 6
22. 7CASEV_flag
23. 8WHEN0
24. 9THEN
25. 10 DBMS_OUTPUT.put_line (v_num |'Is even number');
26. 11WHEN1
27. 12THEN
28. 13 DBMS_OUTPUT.put_line (v_num |Is odd number');
29. 14ELSE
30. 15NULL;
31. 16END CASE;
32. 17END;
33. 18/
34. Enter valueForIn_num: 5
35. 5IsOdd number
37. PL/SQLProcedureSuccessfully completed.

Ii. Search-type case statement

1. --> Syntax
3. CASE
4. WHENSearch condition 1THENSTATEMENT 1;
5. WHENSearch condition 2THENSTATEMENT 2;
6. ...
7. WHENSEARCH CONDITION NTHENStatement n;
8. ELSEStatement n + 1;
9. END CASE;
11. -- The search-type case statement looks different from the simple case statement. First, there is no selector after case, and then the result of search condition n after when is boolean,
12. -- The result of the first true statement is returned. If the search condition after all when is not TRUE, the STATEMENT after else
13. -- Is returned. If the else clause is omitted at this time, it is equivalent to a simple case statement and a case_not_found exception is also received. Who told them to be a compatriot?
15. --> The following example shows a search-type case.
16. Scott @ ORCL>DECLARE
17. 2 v_num NUMBER: = & in_num;
18. 3BEGIN
19. 4CASE
20. 5WHENV_num> 0
21. 6THEN
22. 7 DBMS_OUTPUT.put_line (v_num |'Is a positive number');
23. 8WHENV_num <0
24. 9THEN
25. 10 DBMS_OUTPUT.put_line (v_num |'Is a negative number');
26. 11ELSE
27. 12 DBMS_OUTPUT.put_line (v_num |Is zero');
28. 13END CASE;
29. 14END;
30. 15/
31. Enter valueForIn_num:-3
32. -3IsA negative number
34. --> As shown in the following example, if none of the when values is true and the else clause is omitted, CASE not found is welcome.
35. Scott @ ORCL>DECLARE
36. 2 v_num NUMBER: = & in_num;
37. 3BEGIN
38. 4CASE
39. 5WHENV_num> 0
40. 6THEN
41. 7 DBMS_OUTPUT.put_line (v_num |'Is a positive number');
42. 8WHENV_num <0
43. 9THEN
44. 10 DBMS_OUTPUT.put_line (v_num |'Is a negative number');
45. 11END CASE;
46. 12END;
47. 13/
48. Enter valueForIn_num: 0
49. DECLARE
50. *
51. ERRORAtLine 1:
52. ORA-06592:CASE NotFound while executingCASEStatement
53. ORA-06512:AtLine 4

1 2 Next Page