Oracle常用函数以及行列转换.docx

常用的函数数值函数 ABS(N) 返回指定值的绝对值 CEIL(N) 返回大于或等于给出数字的最小整数 FLOOR(N) 对给定的数字取整数 MOD(M,N) 返回一个M除以N的余数 ROUND( M ,N ) 按照指定的小数位元数进行四舍五入运算的结果参数：如果N不为整数则截取N整数部分，如果N0则四舍五入为N位小数，如果N小于0则四舍五入到小数点向左第N位。 TRUNC(M,N) 返回M按精度N截取后的值参数：如果N不为整数则截取N整数部分，如果N0则截取到N位小数，如果N小于0则截取到小数点向左第N位，小数前其它数据用0表示。转换函数 TO_CHAR (NUMBER | date,format) TO_NUMBER (char , format) TO_DATE (char, format)分组函数 MAX AVG MIN COUNT SUM字段为空的判断NULL的概述NULL在Oracle中是很特殊的值，任何类型的值都可以是NULL。如果在某行中有一列没有值，那么就说慈航中这个列的值是NULL。NULL是未知的东西，常称它为“UNKNOWN”或空值。正因为它是未知的，才会有很多值得注意的规则。NULL可以是任何数据类型的值，也可以不依赖于数据类型单独存在(字面量NULL)，任何没有NOT NULL约束或主键约束的列都有可能出现NULL值。NULL和其他任何值都不相等或相等，包括自身(当然是用IS NULL判断是可以的)。但是在某些时候，Oracle却把他们当成相等的(指Oracle的一些内部规则)，如SQL集合语句、Group by分组、Decode函数等。NULL的判断和比较运算规则Oracle中判断一个列值或变量值是否是NULL，必须是用IS NULL或IS NOT NULL，而不能使用=NULL或NULL。NULL的算数或者逻辑运算规则NULL做一些算数运算，比如+、-、*、/等，结果还是NULL，但是对于连接操作符|，NULL忽略。select null+10 from dual;select null|ygczm from dual;select concat(null,ygczm) from dual;NULL与函数 Oracle对NULL处理有专门的函数，所以这里只讲述NVL、NVL2、NULLIF、COALESCE、DECODE函数，以及两种CASE表达式对NULL的处理。NVL函数 语法：NVL(expr1,expr2) 描述：常用语expr1可能是NULL，而我们又不想在查询结果中出现空值的转换需求。如果expr1是NULL，那么用expr2作为返回值，不是NULL则返回expr1。expr1和expr2一般是类型相同的，如果类型不同会采用自动类型转换，如果转换失败则出错。采用的自动类型转换规则如下： 如果expr1是字符类型，那么在比较前expr2转换为expr1类型，返回值类型是expr1所在的字符集的VARCHAR2类型。 如果expr1是数值型，Oracle会检查expr1和expr2的数值类型优先级，然后将优先级低的参数类型转换为优先级高的参数类型，最后返回的类型是优先级最高的那个类型。数值类型优先级BINARY_DOUBLEBINARY_FLOATNUMBER。 如果expr1是字面量NULL，expr2是非NULL。则转为expr2类型，返回值同expr2。NVL2函数 语法：NVL(expr1，expr2，expr3) 描述：常用于对expr1的值不管是否是NULL都要转换的需求。比NVL要先进一点，加了额外的判断，expr1如果是NULL则返回expr3，否则返回expr2。这里的类型转换就和expr1没有关系了，一般要求expr2和expr3类型要一致，如果不一致则采用自动类型转换。 如果expr2是字符类型，在比较前将expr3转换为expr2类型，如果expr3是字面量NULL，则没有必要转换。 如果expr2是数值类型，Oracle会检查expr2和expr3的数值类型优先级，然后将低优先级的参数类型转换为高优先级的参数类型，最后返回值类型就是高优先级的类型。 如果expr2是字面值NULL，expr3不是NULL，则返回值同expr3类型。下面的6个例子中哪些是错的呢？select NVL2(NULL,1,2) from dual;select NVL2(NULL,1,2) from dual;select NVL2(NULL,NULL,2) from dual;select NVL2(NULL,1,2) from dual;select NVL2(NULL,1,a) from dual;select NVL2(NULL,a,1) from dual;NULLIF函数 语法：NULLIF(expr1，expr2) 描述：判断expr1和expr2是否相等，若相等则返回NULL，否则则返回expr1。注意和NVL、NVL2函数不同的是，expr2要与expr1类型相同，必须类型相同。另外还有一点expr1不能是字面量NULL。COALESCE函数 语法：COALESCE(expr1，expr2，exprn) 描述：此函数可以接受多个表达式或字面量值，从左到右返回第一个非NULL值，如所有的列元素都为NULL，则返回NULL。它有短路计算功能，比如expr1为NULL，expr2非NULL，则返回expr2的值，不再计算expr3的值，其他依次类推。它实现的功能可以用NVL、CASE WHEN或DECODE代替，当然对于找第一个非NULL值，用这个函数是最简单的。注意：此函数要求所有的变量都是同一类型，否则会报错。CASE表达式和DECODE函数处理NULL 语法：DECODE(value, if1, then1, if2,then2, if3,then3, . . . else ) Simple Oracle case表达式,使用表达式确定返回值case search_expression WHEN expression1 THEN result1 . WHEN expressionN THEN resultN ELSE default_result END Searched case表达式，使用条件确定返回值case WHEN condition1 THEN result1 . WHEN condistionN THEN resultN ELSE default_resultEND Simple case无法精确匹配NULL，而DECODE可以匹配NULL，如果不需要匹配NJULL，那么和DECODE的功能是一样的。 Searched case使用范围更广泛，可以对非等值条件以及NULL进行判断，而DECODE和SIMPLE CASE 要实现非等值判断比较麻烦，可以与SIGN函数配合使用。SIGN(N)描述：取数字N的符号,大于0返回1,小于0返回-1,等于0返回0 THEN和ELSE后面的数据类型必须一致。行转列1、decode行转列这就是最常见的行转列，主要原理是利