Oracle中函数使用技巧(2) - NULLIF
Oracle中函数使用技巧(2) - NULLIF
NULLIF 函数
功能
通过比较表达式提供缩写的 CASE 表达式。
语法
NULLIF ( expression-1, expression-2 )
参数
expression-1 要比较的表达式。
expression-2 要比较的表达式。
用法
NULLIF 比较两个表达式的值。
如果第一个表达式的值与第二个表达式的值相等,NULLIF 返回 NULL。
如果第一个表达式的值不等于第二个表达式的值,或者第二个表达式为 NULL,则 NULLIF 返回第一个表达式。
NULLIF 函数提供了编写某些 CASE 表达式的简便方法。
标准和兼容性
SQL/92 初级特性。
SQL/99 核心特性。
示例
下面的语句返回值 a:
SELECT NULLIF( 'a', 'b' )
下面的语句返回 NULL。
SELECT NULLIF( 'a', 'a' )
---------------------------------------------------------------------------------------------------
CASE 表达式
CASE 表达式提供条件 SQL 表达式。Case 表达式可以在能够使用表达式的任何地方使用。
CASE 表达式的语法如下:
CASE expression WHEN expression THEN expression, ... [ ELSE expression ] END
如果 CASE 语句后的表达式等于 WHEN 语句后的表达式,则返回 THEN 语句后的表达式。否则,返回 ELSE 语句后的表达式(如果它存在)。
例如,以下代码用 CASE 表达式作为 SELECT 语句的第二子句。
SELECT id, ( CASE name WHEN 'Tee Shirt' then 'Shirt' WHEN 'Sweatshirt' then 'Shirt' WHEN 'Baseball Cap' then 'Hat' ELSE 'Unknown' END ) as Type FROM "DBA".Product
可以替换的语法如下:
CASE WHEN search-condition THEN expression, ... [ ELSE expression ] END
如果满足 WHEN 语句后的搜索条件,则返回 THEN 语句后的表达式。否则,返回 ELSE 语句后的表达式(如果它存在)。
例如,以下语句用 CASE 表达式作为 SELECT 语句的第三子句,以将字符串与搜索条件相关联。
SELECT id, name, ( CASE WHEN name='Tee Shirt' then 'Sale' WHEN quantity >= 50 then 'Big Sale' ELSE 'Regular price' FROM "DBA".Product
用于简写 CASE 表达式的 NULLIF 函数
NULLIF 函数提供了一种以短格式写出某些 CASE 语句的方法。NULLIF 语法如下:
NULLIF ( expression-1, expression-2 )
NULLIF 比较两个表达式的值。如果第一个表达式的值与第二个表达式的值相等,NULLIF 返回 NULL。如果它们不相等,NULLIF 则返回第一个表达式。
CASE 语句不同于 CASE 表达式
不要将 CASE 表达式的语法和 CASE 语句的语法混淆。
------------------------------------------------------------------------------------
CASE 语句
说明
此语句用于根据多种情况选择执行路径。
语法 1 CASE value-expression WHEN [ constant | NULL ] THEN statement-list ... [ WHEN [ constant | NULL ] THEN statement-list ] ... [ ELSE statement-list ] END CASE 语法 2 CASE WHEN [ search-condition | NULL] THEN statement-list ... [ WHEN [ search-condition | NULL] THEN statement-list ] ... [ ELSE statement-list ] END CASE
用法
语法 1 CASE 语句是控制语句,它允许您根据表达式的值选择要执行的 SQL 语句列表。 value-expression 是具有单值的表达式,它的值可以是字符串、数字、日期或其它 SQL 数据类型。如果 value-expression 的值存在匹配的 WHEN 子句,则执行 WHEN 子句中的 statement-list。如果没有合适的 WHEN 子句而有 ELSE 子句,则执行 ELSE 子句中的 statement-list。执行在 END CASE 后的第一个语句重新开始。
如果 value-expression 可以为空,使用 ISNULL 函数用不同的表达式替换 NULL value-expression。
语法 2 在这种格式下,执行 CASE 语句中第一个满足 search-condition 的语句。如果不满足 search-conditions,则执行 ELSE 子句。
如果表达式可以为 NULL,对第一个 search-condition 使用以下语法:
WHEN search-condition IS NULL THEN statement-list
权限
无。
副作用
无。
标准和兼容性
SQL/92 持久存储模块特性。
SQL/99 持久存储模块特性。
示例
下面的过程使用 CASE 语句,将示例数据库的产品表中列出的产品分为衬衣、帽子、短裤、未知几大类。
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
下面的示例使用语法 2 生成关于示例数据库中产品数量的消息。
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