COALESCE (), IsNull (), Nullif () functions __ functions

http://blog.csdn.net/baoshan/article/details/2907602 COALESCE ()
Usage Select COALESCE (null,null, ' 1 ', ' 2 ') returns 1
Action returns the first NON-EMPTY expression in its argument.

IsNull ()
Usage Select isnull (null,0) returns 0
Action encountered null return specified value

Nullif ()
Usage nullif (' AA ', ' AA ') returns NULL, NULLIF (1,2) returns 1
Action two values equal returns null http://blog.csdn.net/topsjava/article/details/1746152 nullif function function

Provides an abbreviated case expression by comparing an expression. Grammar

Nullif ( expression-1, expression-2 ) Parameters

expression-1 the expression to compare.

expression-2 the expression to compare. usage

Nullif compares the values of two expressions.

If the value of the first expression is equal to the value of the second expression, NULLIF returns NULL.

If the value of the first expression is not equal to the value of the second expression, or if the second expression is NULL, Nullif returns the first expression.

The NULLIF function provides an easy way to write some case expressions. Standards and compatibility

sql/92 Primary characteristics.

sql/99 Core features.
Sample

The following statement returns the value a:

SELECT nullif (' A ', ' B ')

The following statement returns NULL.

SELECT nullif (' A ', ' a ')
------------------------------------------------------------------------------------ ---------------

Case Expressions

Case expressions provide conditional SQL expressions. Case expressions can be used anywhere you can use an expression.

The syntax for the case expression is as follows:

CASE Expression
When expression
THEN expression, ...
[ ELSE expression]
End

If the expression after the case statement equals the expression after the When statement, the expression after the THEN statement is returned. Otherwise, the expression after the Else statement (if it exists) is returned.

For example, the following code uses a case expression as the second clause of the SELECT statement.

SELECT ID,
(case name when
' Tee shirt ' then ' shirt ' when ' sweatshirt ' then ' shirt ' when
' baseball Cap ' th En ' Hat '
ELSE ' Unknown ' end
] as Type from
"DBA". Product

The syntax that can be replaced is as follows:

Case
when Search-condition
THEN expression, ...
[ ELSE expression]
End

If the search condition after the When statement is met, the expression after the THEN statement is returned. Otherwise, the expression after the Else statement (if it exists) is returned.

For example, the following statement uses a case expression as the third clause of a SELECT statement to associate a string with a search condition.

SELECT ID, name,
(case when
name= ' Tee shirt ' then ' Sale ') quantity >= ' big
Then '
ELSE ' Reg Ular price ' from
' DBA '. Product

nullif function for shorthand case expressions

The NULLIF function provides a way to write some case statements in a short format. The NULLIF syntax is as follows:

Nullif (Expression-1, Expression-2)

Nullif compares the values of two expressions. If the value of the first expression is equal to the value of the second expression, NULLIF returns NULL. If they are not equal, NULLIF returns the first expression.

The case statement differs from the case expression by not confusing the syntax of the case expression with the syntax of the case statement.

------------------------------------------------------------------------------------

Case Statement Description

Use this statement to select an execution path based on a variety of circumstances. Syntax 1

Case Value-expression
when [Constant | NULL ] THEN statement-list ...
[ When [constant | NULL ] THEN statement-list] ...
[ ELSE Statement-list]
End Case Syntax 2

Case
When [search-condition | NULL] THEN statement-list ...
[ when [search-condition | NULL] THEN statement-list] ...
[ ELSE Statement-list]
End Case usage

Syntax 1 A case statement is a control statement that allows you to select a list of SQL statements to execute based on the value of an expression. Value-expression is an expression with a single value that can be a string, a number, a date, or another SQL data type. If the value-expression value has a matching when clause, the statement-list in the When clause is executed. If there is no appropriate when clause and there is an else clause, the statement-list in the ELSE clause is executed. Executes the first statement after the end case to start again.

If value-expression can be empty, use the ISNULL function to replace the NULL value-expression with a different expression.

Syntax 2 in this format, executes the first statement in the case statement that satisfies the search-condition. If Search-conditions is not satisfied, the ELSE clause is executed.

If an expression can be null, use the following syntax for the first search-condition:

When Search-condition is NULL THEN statement-list

Permissions

No. Side Effects

No. Standards and compatibility sql/92 Persistent enclosure features.
sql/99 Persistent enclosure features.
Sample

The following procedure uses the case statement to divide the products listed in the Product table of the sample database into shirts, hats, shorts, and unknown categories.

CREATE PROCEDURE ProductType (in product_id INT, out Type CHAR ())
BEGIN
DECLARE prod_name CHAR ();
Select name into Prod_name from "DBA". Product "
WHERE id = product_id;
Case Prod_name as
' Tee shirt ' THEN
set type = ' Shirt ' when
' sweatshirt ' THEN
SET type = ' Shirt
' When ' baseball Cap ' THEN
set type = ' hat ' while
' Visor ' THEN
set type = ' hat ' when
' shorts ' THEN
S ET type   ' shorts '
ELSE
SET type = ' UNKNOWN ' end case
;
End

The following example uses Syntax 2 to generate a message about the number of products in the sample database.

 CREATE PROCEDURE stocklevel (in product_id int) BEGIN DECLARE qty int;
SELECT Quantity into Qty from product WHERE id = product_id;
Case When Qty < THEN the "order of the stock" to CLIENT;
When qty > THEN message ' overstocked ' to CLIENT;
ELSE message ' sufficient-hand ' to CLIENT;
End case; End