Oracle Series: (11) General function and conditional judgment function

Using NVL (A, B) General function, statistical employee annual income, NVL () acting on any type, ie (number/varchar2/date)

General function: The parameter type can be number or varchar2 or date type

Select ENAME,SAL*12+NVL (comm,0) from EMP;

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M02/86/F7/wKioL1fP3ufzfvGzAAAaGsv5Dyc989.png "title=" 006. PNG "alt=" Wkiol1fp3ufzfvgzaaaagsv5dyc989.png "/>

Use NVL2 (A,b,c) General function, if a is not NULL, take B value, otherwise take C value, statistic employee annual income

Select ENAME,SAL*12+NVL2 (comm,comm,0) from EMP;

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/86/F7/wKioL1fP3yuj2FfpAAAbo5eX_UY447.png "title=" 007. PNG "alt=" Wkiol1fp3yuj2ffpaaabo5ex_uy447.png "/>

Using the Nullif (A, B) General function, if the type is the same, if A and B are the same, return null, otherwise a, compare 10 and 10.0 is the same

Select Nullif (Ten, ' Ten ') from dual;

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M02/86/F8/wKiom1fP34-j4ZSXAAAXU7xPSbY609.png "title=" 008. PNG "alt=" Wkiom1fp34-j4zsxaaaxu7xpsby609.png "/>

Use the case expression in the common syntax of the SQL99 standard, the position is the analyst, the salary is +1000, the position is the manager, the salary is +800, the position is other, the salary is +400.

Case Field

When condition 1 then expression 1

When condition 2 then expression 2

else Expression n

End

After class please refer to <mysql5.x's Manual >-12.2 this chapter

Select Ename "Name", Job "job", Sal "pre-pay", case job when ' ANALYST ' and sal+1000 when ' MANAGER ' th En sal+800 else sal+400 end "wage rise" from EMP;

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M01/86/F7/wKioL1fP4ELQ6tBFAABCzVwIMnI052.png "title=" 009. PNG "alt=" Wkiol1fp4elq6tbfaabczvwimni052.png "/>

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/86/F7/wKioL1fP4o_xgRUEAAA_xJfO0fg654.png "title=" 011. PNG "alt=" Wkiol1fp4o_xgrueaaa_xjfo0fg654.png "/>

Using the Decode () function in Oracle-specific syntax, the position is analyst, salary +1000, position is manager, salary +800, position is other, salary +400

Decode (field, condition 1, expression 1, condition 2, expression 2,... Expression N)

Select Ename "Name", Job "job", Sal "pre-Pay", decode (Job, ' ANALYST ', sal+1000, ' MANAGER ', sal+800,sal+400) "Up salary" from EMP;

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M02/86/F8/wKiom1fP4IaDQ38_AAA2q44Tw4g840.png "title=" 010. PNG "alt=" Wkiom1fp4iadq38_aaa2q44tw4g840.png "/>

The single quotation marks appear in the following places: 1) string, for example: ' Hello ' 2) Date type, for example: ' 1 July-December-80 ' 3) to_char/to_date (date, ' Yyyy-mm-dd HH24:MI:SS ')

The double quotation marks appear in the following places: 1) column aliases, for example: select Ename "Name" from EMP 2) to_char/to_date (date, "YYYY" year "MM" month "DD" Day "HH24:MI:SS") "The English characters are not case sensitive

Oracle Series: (11) General function and conditional judgment function