Conditional statements in Oracle

in Oracle, case and then and decode usage one. Case... then syntax: – Writing one: CaseWhen value 1 then return value 1 when value 2 then return value 2ElseDefault Value – two: CaseWhen condition 1 then return value 1 when condition 2 then return value 2Elsedefault value end; Case 1:--If the department number is 10, the display is dept10--If the department number is 20, the display is dept20--If the department number is 30, the display is Dept30--otherwise displayed as other--The result of this column query, the column name is shown as department use one: select Ename, Sal, CaseDeptno when10 Then' Dept10 ' when20 Then' Dept20 ' when30 Then' Dept30 'Else' Other 'End Department from EMP notation two: Select ename, Sal, CaseWhen Deptno= 10 Then' Dept10 'When Deptno= 20 Then' Dept20 'When Deptno= 30 Then' Dept30 'Else' Other 'End Department from EMP in this example the conditions are equal and the result is the same. If it is not equivalent or there are more than one expression, you can only use the second kind, for example: select Ename,sal, CaseWhen deptno= or Deptno = or Deptno = "Dept" | |Deptno End Deptfrom emp;select ename,sal, CaseWhen deptno<=20 then ' dept ' | |Deptno End Deptfrom emp;select ename, Sal, CaseWhen Sal> 0 and Sal <= 1500 Then' Level1 'When Sal> Sal <= 2500 Then' Level2 'When Sal> 2500 and Sal <= 4500 Then' Level3 'Else' Level4 'end Sal_level from emp order by Sal Desc; ii. decode function: decode (condition, value 1, return value 1, value 2, return value 2, ..., default) use Decode function to implement case 1:select ename, Sal, Decode (Deptno,Ten, ' dept10 ', ' dept20 ', ' dept30 ', ' other 'Department from EMP The result is the same as in case, but it seems a lot more concise. The extended usage of the Decode () function:1. Compare the size with the sign function:--the small value syntax for get arg1 and arg2: Select Decode (sign Arg1-ARG2),-1, ARG1,ARG2) from dual; Example: Select Decode (sign (3-5), 1,3,5) from dual--5Note: The sign () function returns 0, depending on whether a value is 0, positive, or negative, respectively .1,-1an UPDATE statement that changes the value of a field in a table to a value of "1 "is changed to" 8 "," 0 "to" 9 ""UPDATE tablename set field name= Decode (field name, 1,8,0,9) where field name in (1, 0); iii. comparison of DECODE and case1.      DECODE only Oracle, other databases are not supported; 2.      When the case is used, Oracle, SQL Server, and MySQL are supported; 3.DECODE can only be used for equal judgment, but may match the sign function to be greater than, less than, equal to the judgment, case when can use for =,>=,<,<=,<>,isNULL, is notNULLand other judgments; 4.      DECODE use it to be relatively concise, case although complex but more flexible; 5In addition, in decode, null and null are equal, but in case, the example can only be judged with IS null: There is a column of comm in the EMP table, or 0 if it is null, otherwise, it is displayed as the original value:--decode can display the results we requested SQL> select Ename,decode (Comm,NULL, 0, comm) comma from EMP;--NULL does not turn 0, is still null, not the result we requested SQL> select Ename, ( CaseComm WhenNULLThen 0ElseComm End) Comm from EMP;--This will successfully display NULL as 0SQL> select Ename, ( CaseWhen Comm isNULLThen 0ElseComm end) Comm from EMP;

Conditional statements in Oracle