Shulou(Shulou.com)06/01 Report--

Editor to share with you what the conditional functions in Oracle are, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Conditional function NVL

The NVL function evaluates whether a column or expression of any data type is null. Returns an alternative non-null value if the original item is null; otherwise, the original item is returned.

The NVL function has two mandatory arguments. Syntax: NVL (original,ifnull). Where original represents the item to be tested, and returns ifnull if the original item evaluates to be empty. The data types of original and ifnull parameters must be the same. They must be of the same type, or it is possible to implicitly convert ifnull to the type of original parameter. The data type of the return value of the NVL function is the same as that of the original parameter.

Select nvl (1234) from dual;-- error

Select nvl (null,1234) from dual;-1234

Select nvl (substr ('abc',4),' No substring exists') from dual

NVL2

The NVL2 function is an enhancement to the NVL function, but the function is very similar. The NVL2 function evaluates whether a column or expression of any data type is null. If the first item is not null, the second parameter is returned, otherwise the third parameter is returned.

The NVL2 function has three mandatory arguments. Syntax: NVL2 (original,ifnotnull,ifnull), where original represents the item being tested. If original is not null, return ifnotnull;. If original is null, return ifnull. The data types of the ifnotnull and ifnull parameters must be the same or the ifnull parameter can be converted to the data type of the ifnotnull parameter, which cannot be the LONG data type. They can be of the same type, or you can convert ifnull to the type of ifnotnull parameter. The data type returned by the NVL2 function is the same as that of the ifnotnull parameter.

Select nvl2 (1234 from dual; 1 million string') from dual;-error

Select nvl2 (null,1234,5678) from dual;-5678

Select nvl2 (substr ('abc',2),' Not bc','No substring') from dual

NULLIF

The NULLIF function tests the equality of the two items. If they are equal, the function returns a null value, otherwise the first item of the two test items is returned.

The NULLIF function has two mandatory parameters that can be of any data type, the two parameter types must be the same, and the first parameter cannot be empty. Syntax: NULLIF (ifunequal,comparison_term), where the parameters ifunequal and comparison_term are compared. If they are the same, return NULL. If they are different, the ifunequal parameter is returned.

Select nullif (1234 people 1234) from dual

Select nullif ('24murJULMULMI 2009') from dual

Select nullif (1 minute null) from dual;-- 1

Select nullif (null,null) from dual;---- returns ORA-00932: inconsistent datatypes: expected-got CHAR

COALESCE

The COALESCE function returns the first non-null value from the argument list. If all parameters are empty, a null value is returned.

The COALESCE function has two mandatory arguments and any number of optional arguments. Syntax: COALESCE (expr1,expr2,...,exprn), return expr1 if it is not null, otherwise, return expr2 if it is not null, and so on. The COALESCE function is a general form of NVL function:

COALESCE (expr1,expr2) = NVL (expr1,expr2)

COALESCE (expr1,expr2) = NVL (expr1,NVL (expr2,expr3))

If a non-null value is found, COALESCE returns the same data type as the first non-null parameter. To avoid a "ORA-00931:inconsistent date types" error, the data type of all non-null parameters must be the same as the data type of the first non-null parameter.

Select coalesce (null,null,null,'a string') from dual

Select coalesce (null,null,null) from dual

Select coalesce (substr ('abc',4),' not bc','no substring') from dual

Select coalesce (substr ('abc',4),' not bc',123) from dual;-- error

DECODE

The DECODE function implements if-then-else conditional logic by testing the equality of the first two terms, returning the third argument if they are equal, and possibly another item if they are not equal.

The DECODE function uses at least three mandatory parameters, but can use more parameters. Syntax: DECODE (expr1,comp1,iftrue1 [, comp2,iftrue2... [, compN,iftrueN]] [, iffalse]). These parameters are calculated as the following pseudo-code example:

If expr1 = comp1 then return iftrue1

Else if expr1 = comp2 then return iftrue 2

...

...

Else if expr1 = compN then return iftrueN

Else return null | iffalse

All arguments to the DECODE function can be expressions. The returned data type is the same as the first data type that matches the comparison option. The expression expr1 is implicitly converted to the data type of the first comparison parameter comp1. When calculating the other comparison parameter comp2...compn, they are also implicitly converted to the same data type as comp1. DECODE assumes that the two null values are equal, so if expr1 is null and comp3 is the first null comparison parameter that occurs, the corresponding result parameter iftrue3 is returned.

Select decode (1234 is a match') from dual

Select decode (1234 is a match','No match') from dual

Select decode ('search','comp1','true1','comp2','true2','search','true3',substr (' 2searchcraftsmagery 2, 6), 'true4','false') from dual

Select decode (null,'comp1','true1','comp2','true2',null,'true3','false') from dual

Select decode (1234 and 123 match') from dual;--'No match'ORA-01722: invalid number

CASE expression

CASE expressions can be implemented in all third-and fourth-generation programming languages. Like the DECODE function, CASE expressions use if-then-else conditional logic. There are two variants of CASE expressions. A simple CASE expression lists conditional search terms once, and each comparison expression tests the equality of the search term. The searched CASE expression lists the individual criteria for each comparison expression.

CASE expressions use at least three mandatory parameters, but you can use more parameters. Its syntax depends on whether you use a simple CASE expression or a search CASE expression.

Syntax for simple CASE expressions:

CASE search_expr

WHEN comparison_expr1 THEN iftrue1

[WHEN comparison_expr2 THEN iftrue2

...

WHEN comparison_exprN THEN iftureN]

[ELSE iffalse]

END

A simple CASE expression is contained in a CASE...END code block and consists of at least one WHEN...THEN statement. In the simplest case-- there is only one WHEN...THEN statement, and search_expr is compared to comparison_expr1. If they are equal, grandma returns the result iftrue1. If it is not equal, a null value is returned, unless the ELSE component is defined, in which case the default iffure value is returned. When there are multiple WHEN...THEN statements in an CASE expression, the matching comparison expression is searched until a match is found.

The search, comparison, and result parameters can be column values, expressions, or all literals, but they must all be of the same data type.

Select

Case substr (1234, 1 and 3)

When '134' then '1234 is a match'

When '1235' then '1235 is a match'

When concat ('1pm is 23') then concat (' 1m record 23') | |'is a match''

Else'no match'

End

From dual

Syntax of the searched CASE expression:

CASE

WHEN condition1 THEN iftrue1

[WHEN condition2 THEN iftrue2

...

WHEN conditionN THEN iftrueN]

[ELSE iffalse]

END

The CASE expression for the search is contained in the CASE...END code block and consists of at least one WHEN...THEN statement. In the simplest case-- there is only one WHEN...THEN statement, calculate condition1;. If it is true, then the result iftrue1 is returned. If not, a null value is returned unless the ELSE component is defined, in which case the default iffase value is returned. When there are multiple WHEN...THEN statements in an CASE expression, the matching comparison expression is searched until a match is found.

Select

Case

When length (substr (1234 length of substring is 1)) = 1 then'1'

When length (substr (1234 and 1)) = 2 then 'length of substring is 2'

When length (substr (1234 length of substring is 1)) = 3 then 'length of substring is 3'

Else'no match'

End

From dual

These are all the contents of the article "what are the conditional functions in Oracle?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.