本文目录
- oracle 中多表联合取某一个字段值的问题
- 函数VALUE和COALESCE到底有没有区别
- SQL中 nvl（）、coalesce（）、decode（）这三个函数，如果只是判断非空的话，哪一个效率相比较高
- db2中是否有类似于oracle中nvl的函数
- DB2到Oracle的SQL转换
- 请教oracle下判断为null的函数
- oracle中nvl和coalesce函数的效率哪个好
- 如何使用Oracle的COALESCE函数和nvl函数
oracle 中多表联合取某一个字段值的问题
Coalesce函数Coalese函数的作用是的NVL的函数有点相似，其优势是有更多的选项。格式如下：Coalesce(expr1, expr2, expr3….. exprn)Coalesce是这样来处理这些参数的。假如第一个参数为空，则看第二个参数是否是空，否则则显示第一个参数，假如第二个参数是空再看第三个参数是否为空，否则显示第二个参数，依次类推。
函数VALUE和COALESCE到底有没有区别
Purpose NVL lets you replace null (returned as a blank) with a string in the results of a query. If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVLreturns expr1. The arguments expr1 and expr2 can have any data type. If their data types are different, then Oracle Database implicitly converts one to the other. If they cannot be converted implicitly, then the database returns an error. The implicit conversion is implemented as follows: • If expr1 is character data, then Oracle Database converts expr2 to the data type of expr1 before comparing them and returns VARCHAR2 in the character set of expr1. • If expr1 is numeric, then Oracle Database determines which argument has the highest numeric precedence, implicitly converts the other argument to that data type, and returns that data type. Examples SQL》 select * from scott.products; LIST_PRICE MIN_PRICE ---------- ---------- 10000 8000 20000 30000 30000 SQL》 select min_price,nvl(min_price,0) from scott.products; MIN_PRICE NVL(MIN_PRICE,0) ---------- ---------------- 8000 8000 0 30000 30000 --------------------------------------------------------------------------------------------------------------------------------------- nvl2用法为nvl2(expr1,expr2,expr3)，其作用是判断expr1是否为null，若不为null，返回expr2，为空返回expr3。 nvl(expr1,expr2)等同于nvl2(expr1,expr1,expr2)。 官方文档用法解释如下： NVL2 Syntax De.ion of nvl2.gif follows Purpose NVL2 lets you determine the value returned by a query based on whether a specified expression is null or not null. If expr1 is not null, then NVL2 returnsexpr2. If expr1 is null, then NVL2 returns expr3. The argument expr1 can have any data type. The arguments expr2 and expr3 can have any data types except LONG. If the data types of expr2 and expr3 are different, then Oracle Database implicitly converts one to the other. If they cannot be converted implicitly, then the database returns an error. If expr2 is character or numeric data, then the implicit conversion is implemented as follows: • If expr2 is character data, then Oracle Database converts expr3 to the data type of expr2 before returning a value unless expr3 is a null constant. In that case, a data type conversion is not necessary, and the database returns VARCHAR2 in the character set of expr2. • If expr2 is numeric data, then Oracle Database determines which argument has the highest numeric precedence, implicitly converts the other argument to that data type, and returns that data type. Examples SQL》 select list_price,www.hbbz08.com min_price,nvl2(min_price,min_price,list_price) nv2_m from scott.products; LIST_PRICE MIN_PRICE NV2_M ---------- ---------- ---------- 10000 8000 8000 20000 20000 30000 30000 30000 --------------------------------------------------------------------------------------------------------------------------------------- nullif用法为nullif(expr1,expr2)，其作用是判断expr1与expr2是否相等，若相等则返回null，否则返回expr1。 官方文档用法解释如下： NULLIF Syntax De.ion of nullif.gif follows Purpose NULLIF compares expr1 and expr2. If they are equal, then the function returns null. If they are not equal, then the function returns expr1. You cannot specify the literal NULL for expr1. If both arguments are numeric data types, then Oracle Database determines the argument with the higher numeric precedence, implicitly converts the other argument to that data type, and returns that data type. If the arguments are not numeric, then they must be of the same data type, or Oracle returns an error. The NULLIF function is logically equivalent to the following CASE expression: CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 ENDExamples SQL》 select list_price,min_price,nullif(list_price,min_price) from scott.products; LIST_PRICE MIN_PRICE NULLIF(LIST_PRICE,MIN_PRICE) ---------- ---------- ---------------------------- 10000 8000 10000 20000 20000 30000 30000 --------------------------------------------------------------------------------------------------------------------------------------- coalesce用法为coalesce(expr1,expr2……exprn)，其作用是在expr1,expr2……exprn这列表达式中查找第一个不为null的值且返回该值。如果都为null，则返回null。 官方文档用法解释如下： COALESCE Syntax De.ion of coalesce.gif follows Purpose COALESCE returns the first non-null expr in the expression list. You must specify at least two expressions. If all occurrences of expr evaluate to null, then the function returns null. Oracle Database uses short-circuit evaluation. The database evaluates each expr value and determines whether it is NULL, rather than evaluating all of theexpr values before determining whether any of them is NULL. If all occurrences of expr are numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type, then Oracle Database determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that data type, and returns that data type. This function is a generalization of the NVL function. You can also use COALESCE as a variety of the CASE expression. For example, COALESCE(expr1, expr2)is equivalent to: CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 ENDSimilarly, COALESCE(expr1, expr2, ..., exprn)where n 》= 3, is equivalent to: CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE COALESCE (expr2, ..., exprn) ENDExamples SQL》 select list_price,min_price,coalesce(list_price,min_price) from scott.products; LIST_PRICE MIN_PRICE COALESCE(LIST_PRICE,MIN_PRICE) ---------- ---------- ------------------------------ 10000 8000 10000 20000 20000 30000 30000 30000 到此，nvl、nvl2、nullif、coalesce四个函数用法我们都了解了，看似很简单，但实际结合应用起来，就容易犯混，如下例子： examples: Examine the data in the LIST_PRICE and MIN_PRICE columns of the PRODUCTS table: LIST_PRICE MIN_PRICE 10000 8000 20000 30000 30000 Which two expressions give the same output? (Choose two.) A. NVL(NULLIF(list_price, min_price), 0) B. NVL(COALESCE(list_price, min_price), 0) C. NVL2(COALESCE(list_price, min_price), min_price, 0) D. COALESCE(NVL2(list_price, list_price, min_price), 0) Answer: BD 乍看你呢很快给出答案么，下面给出答案解析。 A. NVL(NULLIF(list_price, min_price), 0) 查询结果如下： SQL》 select NVL(NULLIF(list_price, min_price), 0) from scott.products; NVL(NULLIF(LIST_PRICE,MIN_PRICE),0) ----------------------------------- 10000 20000 0 B. NVL(COALESCE(list_price, min_price), 0) 查询结果如下： SQL》 select NVL(COALESCE(list_price, min_price), 0) from scott.products; NVL(COALESCE(LIST_PRICE,MIN_PRICE),0) ------------------------------------- 10000 20000 30000 C. NVL2(COALESCE(list_price, min_price), min_price, 0)查询结果如下： SQL》 select NVL2(COALESCE(list_price, min_price), min_price, 0) from scott.products; NVL2(COALESCE(LIST_PRICE,MIN_PRICE),MIN_PRICE,0) ------------------------------------------------ 8000 30000 D. COALESCE(NVL2(list_price, list_price, min_price), 0) 查询结果如下： SQL》 select COALESCE(NVL2(list_price, list_price, min_price), 0) from scott.products; COALESCE(NVL2(LIST_PRICE,LIST_PRICE,MIN_PRICE),0) ------------------------------------------------- 10000 20000 30000 由此可见，选BD
SQL中 nvl（）、coalesce（）、decode（）这三个函数，如果只是判断非空的话，哪一个效率相比较高
nvl只支持2个参数，这是oracle特有的coalesce支持不定参数，coalesce(bonus,0,1)应该写成coalesce(bonus,0)，最后1个不用写，这是ansi标准函数，在多次外连接时尤其有用，譬如 t1 full join t2 on t1.id=t2.id full join t3 on coalesce(t1.id,t2.id)=t3.id full join t4 on coalesce(t1.id,t2.id,t3.id)=t4.iddecode判断不了非空
db2中是否有类似于oracle中nvl的函数
有，coalesce函数，还有一个是value函数，不过后面的并不是通用的，所以最好用前面的。
DB2到Oracle的SQL转换
本篇记录项目中DB2的SQL转为Oracle时的不同之处： 1、Oracle没有DROP TABLE IF EXISTS 2、db2函数用returns 3、db2用DECLARE来申明变量，Oracle申明变量在as/is后面 4、db2用SET来赋值变量，Oracle用:= 5、db2用ELSEIF，oracle用ELSIF 6、Oracle中的coalesce函数（用来获取第一个不为空的值）里面的数据类型，必须全部都跟第一列的 数据类型一致 7、Oracle函数最后要加个END 8、DB2为sysibm.dual，Oracle为dual 9、Oracle对象名不能超过30个字符
请教oracle下判断为null的函数
COALESCE()这个函数是ASNI标准的SQL函数，MS SQL 和 Oracle都可以用NVL()是Oracle独有的函数，功能与COALESCE()相同而在 MS SQL中的ISNULL()函数功能与COALESCE()相同1 SELECT NVL(FIELD,’*’) FROM TABLE2 SELECT COALESCE(FIELD,’*’) FROM TABL以上两个语句把如果FIELD字段为NULL的替换为 ’*’
oracle中nvl和coalesce函数的效率哪个好
如果只有2个表达式的话肯定NVL好一点但是超过2个以上,用NVL要嵌套,效率肯定没有集成的coalesce好
如何使用Oracle的COALESCE函数和nvl函数
NVL是Oracle PL/SQL中的一个函数。它的格式是NVL( string1, replace_with)。它的功能是如果string1为NULL，则NVL函数返回replace_with的值，否则返回string1的值，如果两个参数都为NULL ，则返回NULL。注意事项：string1和replace_with必须为同一数据类型，除非显式的使用TO_CHAR函数进行类型转换。例：NVL(TO_CHAR(numeric_column), ’some string’) 其中numeric_column代指某个数字类型的值。例：nvl(yanlei777,0) 》 0NVL(yanlei777, 0) 的意思是 如果 yanlei777 是NULL， 则取 0值
