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 the case expression, and provides an example
This helps you better understand the similarities and differences between the two and the precautions for use.

I. Simple case statements
--> Syntax </P> <p> case selector <br/> when expression 1 then Statement 1; <br/> when expression 2 then Statement 2; <br/>... <br/> when expression n then statement N; <br/> else statement n + 1; <br/> end case; </P> <p> -- 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, then the corresponding statement is executed. Until it is found. <Br/> -- if the corresponding expression n does not match, then the else goes into the array and runs the corresponding statement. <Br/> -- 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. </P> <p> --> demonstrate simple case statements <br/> sys @ orcl> declare <br/> 2 v_num number: = & in_num; <br/> 3 v_flag number; <br/> 4 Begin <br/> 5 v_flag: = Mod (v_num, 2 ); <br/> 6 <br/> 7 case v_flag <br/> 8 When 0 <br/> 9 then <br/> 10 dbms_output.put_line (v_num | 'is even number' ); <br/> 11 when 1 <br/> 12 then <br/> 13 dbms_output.put_line (v_num | 'is odd number '); <br/> 14 else <br/> 15 NULL; <br/> 16 end case; <br/> 17 end; <br/> 18/<br/> enter value for in_num: 5 <br/> 5 is odd number </P> <p> PL/SQL procedure successfully completed.Ii. Search-type case statement
--> Syntax </P> <p> case <br/> when Search Condition 1 then Statement 1; <br/> when Search Condition 2 then Statement 2; <br/>... <br/> when search condition n then statement N; <br/> else statement n + 1; <br/> end case; </P> <p> -- the search 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, <br/> -- the result of the first true statement is returned. If the search condition after all when is not true, the result of statement <br/> -- after else 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? </P> <p> --> the following example shows the search-type case <br/> Scott @ orcl> declare <br/> 2 v_num number: = & in_num; <br/> 3 begin <br/> 4 case <br/> 5 when v_num> 0 <br/> 6 then <br/> 7 dbms_output.put_line (v_num | 'is positive number '); <br/> 8 When v_num <0 <br/> 9 then <br/> 10 dbms_output.put_line (v_num | 'is a negative number '); <br/> 11 else <br/> 12 dbms_output.put_line (v_num | 'is 0'); <br/> 13 end case; <br/> 14 end; <br/> 15/<br/> enter value for in_num: -3 <br/>-3 is a negative number </P> <p> --> In the following example, none of the when values are true, and the else clause is omitted, then we welcome you to the case not found <br/> Scott @ orcl> declare <br/> 2 v_num number: = & in_num; <br/> 3 begin <br/> 4 case <br/> 5 when v_num> 0 <br/> 6 then <br/> 7 dbms_output.put_line (v_num | 'is positive number '); <br/> 8 When v_num <0 <br/> 9 then <br/> 10 dbms_output.put_line (v_num | 'is a negative number '); <br/> 11 end case; <br/> 12 end; <br/> 13/<br/> enter value for in_num: 0 <br/> declare <br/> * <br/> error at line 1: <br/> ORA-06592: Case not found while executing case statement <br/> ORA-06512: at Line 4 </P> <p> /***************************** * *******************/<br/>/* Author: robinson Cheng */<br/>/* blog: http://blog.csdn.net/robinson_0612 */<br/>/* MSN: robinson_0612@hotmail.com */<br/>/* QQ: 645746311 */<br/> /******************************** ******************/Iii. Similarities and Differences Between Simple case statements and search case statements
-- Similarities: both of them are used to execute corresponding statements or complete specific tasks based on different conditions, and can be replaced in some cases. <Br/> -- difference: <br/> -- a simple case statement provides a selector, and the expression data type must match the selector data type. Otherwise, an error is returned. <Br/> -- The Case search statement does not have the selector. the result obtained after the when clause must be a Boolean value (null, trur, false) </P> <p> -- The following example is an example of interchange between a simple case and a search case. In addition, this method also implements the transpose of rows to columns. <Br/> Scott @ orcl> select sum (case when deptno = 20 Then SAL end) as sal_sum_20, --> simple case method <br/> 2 sum (case when deptno = 30 then Sal end) as sal_sum_30 <br/> 3 from EMP where comm> 300; </P> <p> sal_sum_20 sal_sum_30 <br/> ---------- <br/> 13075 8300 </P> <p> Scott @ orcl> select sum (Case deptno when 20 then sal end) as sal_sum_20, --> Search Case Method <br/> 2 sum (Case deptno when 30 then Sal end) as sal_sum_30 <br/> 3 from EMP where comm> 300; </P> <p> sal_sum_20 sal_sum_30 <br/> ---------- <br/> 13075 8300 </P> <p> -- use the search case method, when the data type of the selector is not Boolean, an error message indicating Type mismatch is returned. For example: <br/> Scott @ orcl> declare <br/> 2 v_num Number: = & sv_num; <br/> 3 v_flag number; <br/> 4 Begin <br/> then <br/> 5 case v_flag <br/> 6 when Mod (v_num, 2) = 0 <br/> 7 then <br/> 8 dbms_output.put_line (v_num | 'is even number '); <br/> 9 else <br/> 10 dbms_output.put_line (v_num | 'is odd number'); <br/> 11 end case; <br/> 12 end; <br/> 13/<br/> enter value for sv_num: 7 <br/> case v_flag <br/> * <br/> error at line 5: <br/> ORA-06550: line 5, column 9: <br/> PLS-00615: Type Mismatch found at 'v _ flag' between case operand and when operands <br/> ORA-06550: line 5, column 4: <br/> PL/SQL: Statement ignoredIv. Case expression
-- How to understand the case expression and case statement? We may wish to look at the context to facilitate memory. The analysis is as follows: <br/> -- Case expression, then when it is followed, it must be an expression or a specific value. <Br/> -- case statement, a certain statement, a function, or a computing expression followed by when. A statement must contain a semicolon. <Br/> -- the last difference is that case ends with end, while case statements end with case end. </P> <p> Scott @ orcl> declare <br/> 2 v_num number: = & in_num; <br/> 3 v_flag number; <br/> 4 v_result varchar2 (20); <br/> 5 begin <br/> 6 v_flag: = Mod (v_num, 2 ); <br/> 7 <br/> 8 v_result: = <br/> 9 case v_flag <br/> 10 when 0 then to_char (v_num) | 'is even number' <br/> 11 when 1 then to_char (v_num) | 'is odd number' <br/> 12 end; <br/> 13 dbms_output.put_line (v_result); <br/> 14 end; <br/> 15/<br/> enter value for in_num: 3 <br/> 3 is odd number </P> <p> PL/SQL procedure successfully completed. </P> <p> --> The following statements are also commonly used. <br/> Scott @ orcl> select ename, <br/> 2 case deptno <br/> 3 when 20 then 'developement '<br/> 4 when 30 then 'sales' <br/> 5 else 'cler' <br /> 6 end <br/> 7 as deptname <br/> from Scott. EMP; <br/> 8 <br/> ename deptname <br/> ---------- ------------ <br/> JOHN clerk <br/> Henry developement <br/> Allen sales <br/> ward sales <br/> ..........V. Case nesting
-- Nested case statements and expressions are nested case statements and expressions in case statements and expressions. It is not difficult to nest case statements. Note the matching problem between case and case end/end <br/> -- The following is an example of case nesting demonstrated using case expressions. <Br/> -- Update the comm column in the EMP table. The first case is to judge based on the Department. A case is nested under it, and the new comm value is determined based on the comm value. <Br/> Update Scott. EMP <br/> set comm = <br/> case deptno <br/> when 20 then case <br/> when comm is null then 500 <br/> when comm <200 then 300 <br/> else 100 <br/> end <br/> when 30 then case <br/> when comm is null then 700 <br/> when comm <200 then 500 <br/> else 200 <br/> end <br/> else 1000 <br/> end;6. More references

Enable User Process Tracking

Parent cursor, child cursor, and shared cursor

Bind variables and their advantages and disadvantages

Use of the display_cursor function of dbms_xplan

Use of the display function of dbms_xplan

Description of each field module in the execution plan

Use explain plan to obtain the SQL statement execution plan

Enable autotrace

The function invalidates the index column.

Oracle variable binding