Shulou(Shulou.com)05/31 Report--

This article will explain in detail what are the general functions and conditional expressions in the database. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

1. General function description NVL syntax: NVL (expr1,expr2) description: if expr1 is NULL, the function displays the value of expr2; example:

SELECT SALARY, NVL (TO_CHAR (COMMISSION_PCT), 0) FROM EMPLOYEES

NVL2 syntax: NVL2 (expr1,expr2,expr3) description: if the value of expr1 is NULL, the function displays the value of expr3; if not NULL, displays the value of expr2; example:

SELECT LAST_NAME

SALARY

COMMISSION_PCT

NVL2 (COMMISSION_PCT, 'SAL+COMM',' SAL') INCOME

FROM EMPLOYEES

WHERE DEPARTMENT_ID IN (50,80)

NULLIF syntax: NULLIF (expr1,expr2) description: if expr1=expr2, return NULL; if not equal, return the value of the first expression; example:

SELECT FIRST_NAME

LENGTH (FIRST_NAME) "expr1"

LAST_NAME

LENGTH (LAST_NAME) "expr2"

NULLIF (LENGTH (FIRST_NAME), LENGTH (LAST_NAME)) RESULT

FROM EMPLOYEES

COLESCE syntax: COALSECE (expr1,expr2,expr3) description: if all are NULL, the function value is NULL;. If one item is not NULL, it will show which exprN;. If all three items are not empty, the first expr; example will be displayed:

SELECT COALESCE (NULL, 1,2,3,4) FROM DUAL

SELECT COALESCE (NULL, NULL, 2, 3, 4) FROM DUAL

Second, the conditional expression function explains the CASE syntax:

CASE expr

WHEN comparison_expr1THEN return_expr1

[WHENcomparison_expr2 THENreturn_expr2

WHENcomparison_exprn THENreturn_exprn

ELSE else_expr]

END description:

1. Customize the grouping and data analysis of the known data in the database according to your own logic.

2. Use this condition control statement to realize custom condition grouping.

3. the nesting function in the conditional control statement achieves the ideal calculation effect.

SELECT last_name,salary

(CASE WHEN salary

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.