Oracle function usage tips (2)-NULLIF

Tips for using functions in Oracle (2)-The NULLIF function provides the abbreviated CASE expression through a comparison expression. Syntax NULLIF (expression-1, expression-2) the expression to be compared with expression-1. Expression-2: The expression to be compared. Use NULLIF to compare the values of two expressions. If the value of the first expression is equal to that 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 the second expression is NULL, NULLIF returns the first expression. The NULLIF function provides a simple way to compile some CASE expressions. Standard and compatibility SQL/92 basic features. SQL/99 core features. The Return Value of the following statement is a: select nullif ('A', 'B'). The return value is NULL. Select nullif ('A', 'A') When CASE expression provides a conditional SQL expression. A Case expression can be used wherever an expression can be used. The syntax of the CASE expression is as follows:

CASE expressionWHEN expressionTHEN expression, ...[ ELSE expression ]END

 

If the expression after the CASE statement is equal to the expression after the WHEN statement, the expression after the THEN statement is returned. Otherwise, return the expression after the ELSE Statement (if it exists ). For example, the following code uses the 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' then 'Hat'      ELSE 'Unknown'   END ) as TypeFROM "DBA".Product

 

The syntax that can be replaced is as follows:

CASEWHEN search-conditionTHEN expression, ...[ ELSE expression ]END

 

If the search condition after the WHEN statement is met, the expression after the THEN statement is returned. Otherwise, return the expression after the ELSE Statement (if it exists ). 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'      WHEN quantity >= 50  then 'Big Sale'      ELSE 'Regular price'FROM "DBA".Product

 

The NULLIF function used for the abbreviated CASE expression NULLIF function provides a method to write some CASE statements in 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 that of the second expression, NULLIF returns NULL. If they are not equal, NULLIF returns the first expression. The CASE statement is different from the CASE expression. Do not confuse the syntax of the CASE expression with the syntax of the CASE statement. When CASE statement indicates that this statement is used to select the execution path based on multiple situations.

Syntax 1 CASE value-expressionWHEN [constant | NULL] THEN statement-list... [WHEN [constant | NULL] THEN statement-list]... [ELSE statement-list] end case syntax 2 CASEWHEN [search-condition | NULL] THEN statement-list... [WHEN [search-condition | NULL] THEN statement-list]... [ELSE statement-list] END CASE

 

Syntax 1 a case statement is a control statement that allows you to select a list of SQL statements to be executed based on the expression value. Value-expression is an expression with a single value. Its value can be a string, number, date, or other SQL data type. If the value-expression has a matched WHEN clause, execute statement-list in the WHEN clause. If an ELSE clause does not have an appropriate WHEN clause, execute statement-list in the ELSE clause. Run the first statement after end case to start again. If value-expression can be NULL, use the ISNULL function to replace NULL value-expression with different expressions. Syntax 2 in this format, execute the first statement in the CASE statement that meets the search-condition. If search-conditions is not met, the ELSE clause is executed. If the expression can be NULL, use the following syntax for the first search-condition: WHEN search-condition is null then statement-list. No side effects. Standard and compatibility SQL/92 persistent storage module features. SQL/99 persistent storage module features. The following example uses the CASE statement to classify 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(10))BEGIN   DECLARE prod_name CHAR(20);   SELECT name INTO prod_name FROM "DBA"."product"   WHERE id = product_id;   CASE prod_name   WHEN 'Tee Shirt' THEN      SET type = 'Shirt'   WHEN 'Sweatshirt' THEN      SET type = 'Shirt'   WHEN 'Baseball Cap' THEN      SET type = 'Hat'   WHEN 'Visor' THEN      SET type = 'Hat'   WHEN 'Shorts' THEN      SET 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 < 30 THEN      MESSAGE 'Order Stock' TO CLIENT;   WHEN qty > 100 THEN      MESSAGE 'Overstocked' TO CLIENT;   ELSE      MESSAGE 'Sufficient stock on hand' TO CLIENT;   END CASE;END