Oracle series: (11) General functions and conditional judgment functions

Use the general function NVL (aformab) to calculate the annual income of employees. NVL () acts on any type, that is, (number/varchar2/date).

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

Select ename,sal*12+NVL (comm,0) from emp

Using the general function NVL2, if an is not NULL, take b value, otherwise take c value to calculate the annual income of employees.

Select ename,sal*12+NVL2 (comm,comm,0) from emp

If the type is the same, return NULL if an is the same as b, otherwise return a, and compare whether 10 and 10 are the same.

Select NULLIF (10 ~ (10)) from dual

Using the case expression in the SQL99 standard general syntax, the position will be an analyst with a salary of + 1000, a position of a manager with a salary of + 800, and a position with a salary of + 400

Case field

When condition 1 then expression 1

When condition 2 then expression 2

Else expression n

End

Please refer to this chapter-12.2 after class.

Select ename "name", job "position", sal "salary before increase", case job when 'ANALYST' then sal+1000 when' MANAGER' then sal+800 else sal+400 end "salary after increase" from emp

Using the decode () function in the oracle-specific syntax, the position is for the analyst, the salary is + 1000; the position is the manager, the salary is + 800; the position is other, the salary is + 400

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

Select ename "name", job "position", sal "salary before increase", decode (job,'ANALYST',sal+1000,'MANAGER',sal+800,sal+400) "salary after increase" from emp

The single quotation marks appear as follows:

1) string, for example: 'hello'

2) date type, for example:'17-December-80'

3) to_char/to_date (date, 'YYYY-MM-DD HH24:MI:SS')

The double quotation marks appear as follows:

1) column aliases, for example: select ename "last name" from emp

2) the English characters in to_char/to_date (date, 'YYYY "year" MM "month" DD "day" HH24:MI:SS')' are not sensitive to case.