《oracle系统函数举例》由会员分享，可在线阅读，更多相关《oracle系统函数举例（19页珍藏版）》请在金锄头文库上搜索。
1、ORACLE 函数介绍第一篇著名函数之单值函数注： N 表示数字型， C 表示字符型， D 表示日期型，表示内中参数可被忽略，fmt 表示格式。单值函数在查询中返回单个值，可被应用到select，where 子句， start with 以及 connect by 子句和having子句。(一).数值型函数 (Number Functions) 数值型函数输入数字型参数并返回数值型的值。多数该类函数的返回值支持38 位小数点， 诸如： COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH 支持 36 位小数点。 ACOS, ASIN,
2、ATAN, and ATAN2 支持 30 位小数点。1、MOD(n1,n2) 返回 n1 除 n2 的余数，如果n2=0 则返回 n1 的值。例如： SELECTMOD ( 24 , 5) FROM DUAL; 2、ROUND(n1,n2) 返回四舍五入小数点右边n2 位后 n1 的值， n2 缺省值为0， 如果 n2 为负数就舍入到小 数点左边相应的位上(虽然 oracle documents 上提到 n2的值必须为整数， 事实上执行时此处的判断并不严谨，即使 n2 为非整数， 它也会自动将n2 取整后做处理， 但是我文档中其它提到必须为整的地方需要特别注意，如果不为整执行时会报错的)。例
3、如： SELECT ROUND( 23 . 56 ),ROUND( 23.56, 1),ROUND(23.56,- 1) FROM DUAL; 3、TRUNC(n1,n2 返回截尾到n2 位小数的n1 的值，n2 缺省设置为0， 当 n2 为缺省设置时会将n1 截尾为整数，如果n2 为负值，就截尾在小数点左边相应的位上。例如： SELECT TRUNC( 23.56),TRUNC(23.56, 1),TRUNC(23.56,- 1) FROM DUAL; (二).字符型函数返回字符值(Character Functions Returning Character Values) 该类函数返回与
4、输入类型相同的类型。返回的 CHAR 类型值长度不超过2000 字节；返回的 VCHAR2 类型值长度不超过4000 字节；如果上述应返回的字符长度超出，oracle 并不会报错而是直接截断至最大可支持长度返回。返回的 CLOB 类型值长度不超过4G；对于 CLOB 类型的函数，如果返回值长度超出，oracle 不会返回任何错误而是直接抛出错误。1、LOWER(c) 将指定字符串内字符变为小写，支持CHAR,V ARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB类型例如： SELECT LOWER( WhaT is tHis) FROM DUAL; 2、UPPER(c) 将指
5、定字符串内字符变为大写，支持CHAR,V ARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB类型例如： SELECT UPPER( WhaT is tHis) FROM DUAL; 3、LPAD(c1,n,c2) 返回指定长度 =n 的字符串，需要注意的有几点：如果 nc1.length and c2 is null ，以空格从左向右补充字符长度至n 并返回；如果 nc1.length and c2 is not null ，以指定字符c2 从左向右补充c1 长度至 n 并返回；例如： SELECT LPAD(WhaT is tHis, 5),LPAD(WhaT is tHi
6、s, 25 ),LPAD(WhaT is tHis, 25 , -) FROM DUAL; 最后大家再猜一猜，如果n0，则 oracle 从左向右确认起始位置截取例如： SELECT SUBSTR( What is this, 5,3 ) FROM DUAL;如果 n1c1.length 则返回空例如： SELECT SUBSTR( What is this, 50,3 ) FROM DUAL; 然后再请你猜猜，如果n2 alter session set nls_date_format = mm-dd-yyyy ; SQL select current_date from dual; 3、S
7、YSDATE 功能与上相同，返回当前session 所在时区的默认时间。但是需要注意的一点是，如果同时使用sysdate 与 current_date 获得的时间不一定相同，某些情况下current_date 会比sysdate 快一秒。经过与xyf_tck( 兄台的大作ORACLE的工作机制写的很好，深入浅出)的短暂交流，我们认为current_date 是将current_timestamp 中毫秒四舍五入后的返回，虽然没有找到文档支持，但是想来应该八九不离十。同时，仅是某些情况下会有一秒的误差，一般情况下并不会对你的操作造成影响，所以了解即可。例如： SELECTSYSDATE,CURR
8、ENT_DATE FROM DUAL;4、LAST_DAY(d) 返回指定时间所在月的最后一天例如： SELECT last_day(SYSDATE) FROM DUAL; 5、NEXT_DAY(d,n) 返回指定日期后第一个n 的日期， n 为一周中的某一天。但是，需要注意的是n 如果为字符的话，它的星期形式需要与当前session默认时区中的星期形式相同。例如：三思用的中文nt， nls_language 值为 SIMPLIFIED CHINESE SELECT NEXT_DAY(SYSDATE, 5) FROM DUAL;SELECT NEXT_DAY(SYSDATE, 星期四 ) FR
9、OM DUAL;两种方式都可以取到正确的返回，但是：SELECT NEXT_DAY(SYSDATE, Thursday) FROM DUAL;则会执行出错，提供你说周中的日无效，就是这个原因了。6、MONTHS_BETWEEN(d1,d2) 返回 d1 与 d2 间的月份差，视d1,d2 的值大小，结果可正可负，当然也有可能为 0 例如：SELECT months_between(SYSDATE, sysdate), months_between(SYSDATE, add_months(sysdate, -1), months_between(SYSDATE, add_months(sysda
10、te, 1) FROM DUAL; 7、ROUND(d,fmt) 前面讲数值型函数的时候介绍过ROUND ，此处与上功能基本相似，不过此处操作的是日期。如果不指定fmt 参数，则默认返回距离指定日期最近的日期。例如： SELECT ROUND( SYSDATE, HH24) FROM DUAL;8、TRUNC(d,fmt) 与前面介绍的数值型TRUNC 原理相同，不过此处也是操作的日期型。例如： SELECT TRUNC( SYSDATE, HH24) FROM DUAL;(五).转换函数 (Conversion Functions) 转换函数将指定字符从一种类型转换为另一种，通常这类函数遵循
11、如下惯例：函数名称后面跟着待转换类型以及输出类型。1、TO_CHAR() 本函数又可以分三小类，分别是转换字符 -字符 TO_CHAR(c) ：将 nchar,nvarchar2,clob,nclob 类型转换为char 类型；例如： SELECT TO_CHAR( AABBCC) FROM DUAL; 转换时间 - 字符 TO_CHAR(d,fmt)：将指定的时间(data,timestamp,timestamp with time zone)按照指定格式转换为varchar2类型；例如： SELECT TO_CHAR( sysdate, yyyy-mm-dd hh24:mi:ss) FRO
12、M DUAL; 转换数值 - 字符 TO_CHAR(n,fmt)：将指定数值n 按照指定格式fmt转换为varchar2类型并返回；例如： SELECT TO_CHAR(-100 , L99G999D99MI) FROM DUAL;2、TO_DATE(c,fmt,nls) 将 char,nchar,varchar2,nvarchar2 转换为日期类型，如果fmt 参数不为空，则按照fmt 中指定格式进行转换。注意这里的fmt 参数。 如果 ftm 为J则表示按照公元制(Julian day) 转换， c 则必须为大于 0 并小于 5373484 的正整数。例如：SELECT TO_DATE(2
13、454336, J) FROM DUAL; SELECT TO_DATE(2007-8-23 23:25:00, yyyy-mm-dd hh24:mi:ss) FROM DUAL; 3、TO_NUMBER(c,fmt,nls) 将 char,nchar,varchar2,nvarchar2 型字串按照fmt 中指定格式转换为数值类型并返回。例如： SELECT TO_NUMBER( -100.00, 9G999D99) FROM DUAL;(六).其它辅助函数(Miscellaneous Single-Row Functions) 1、DECODE(exp,s1,r1,s2,r2.s,r,de
14、f) 可以把它理解成一个增强型的if else，只不过它并不通过多行语句，而是在一个函数内实现if else 的功能。exp 做为初始参数。s做为对比值，相同则返回r，如果 s 有多个，则持续遍历所有s，直到某个条件为真为止，否则返回默认值def(如果指定了的话)，如果没有默认值，并且前面的对比也都没有为真，则返回空。毫无疑问， decode 是个非常重要的函数，在实现行转列等功能时都会用到，需要牢记和熟练使用。例如： select decode(a2, a1, true1, a2, true2, default) from dual;2、GREATEST(n1,n2,.n) 返回序列中的最大
15、值例如： SELECT GREATEST( 15 , 5, 75, 8) “Greatest“ FROM DUAL;3、LEAST(n1,n2.n) 返回序列中的最小值例如： SELECT LEAST(15 , 5, 75 , 8) LEAST FROM DUAL;4、NULLIF(c1,c2) Nullif 也是个很有意思的函数。逻辑等价于：CASE WHEN c1 = c2 THEN NULL ELSE c1 END 例如： SELECT NULLIF(a, b),NULLIF(a, a) FROM DUAL; 5、NVL(c1,c2) 逻辑等价于IF c1 is null THEN c2
16、 ELSE c1 END。 c1,c2 可以是任何类型。 如果两者类型不同，则 oracle 会自动将c2 转换为 c1 的类型。例如： SELECT NVL( null, 12) FROM DUAL;6、NVL2(c1,c2,c3) 大家可能都用到nvl，但你用过nvl2 吗？如果c1 非空则返回c2,如果 c1 为空则返回c3 例如： select nvl2(a, b, c) isNull,nvl2(null, b, c) isNotNull fromdual;7、SYS_CONNECT_BY_PA TH(col,c) 该函数只能应用于树状查询。返回通过 c1连接的从根到节点的路径。 该函数必须与connect by 子句共同使用。例如：createtable tm
