《oracle常用函数》由会员分享，可在线阅读，更多相关《oracle常用函数（11页珍藏版）》请在金锄头文库上搜索。
1、非著名函数篇即虽然很少用到，但某些情况下却很实用 注：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, ASI
2、N, ATAN, and ATAN2 支持 30 位小数点。 1、MOD(n1,n2) 返回 n1 除 n2 的余数，如果 n2=0 则返回 n1 的值。 例如：SELECT MOD(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,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB 类型 例如：SELECT LOWER(WhaT is tHis) FROM DUAL; 2、UPPE
5、R(c) 将指定字符串内字符变为大写，支持 CHAR,VARCHAR2,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(Wha
6、T is tHis,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 中毫秒四舍五入 后的返回，虽然没有找到文档支持，但是想来应该八九不离十。同时，仅是某些情况下会 有一秒的误差，一般情况下并不会对你的操作造成影响，所以了解即可。 例如：SELECT
8、 SYSDATE,CURRENT_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
9、(SYSDATE,星期四) FROM 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(SYSD
10、ATE, add_months(sysdate, 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
12、-dd hh24:mi:ss) FROM 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
13、 的正整数。 例如： SELECT TO_DATE(2454336, J) FROM DUAL; SELECT TO_DATE(2007-8-23 23:25:00, yyyy-mm-dd hh24:mi:ss) FROM DUAL; 为什么公元制的话，c 的值必须不大于 5373484 呢？因为 Oracle 的 DATE 类型的取值范围 是公元前 4712 年 1 月 1 日至公元 9999 年 12 月 31 日。看看下面这个语句： SELECT TO_CHAR(TO_DATE(9999-12-31,yyyy-mm-dd),j) FROM DUAL; 3、TO_NUMBER(c,fmt,
14、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,def) 可以把它理解成一个增强型的 if else，只不过它并不 通过多行语句，而是在一个函数内实现 if else 的功能。 exp 做为初始参数。s 做为对比值，相同则返回 r，如果 s 有多个，则持续遍历所有
15、s，直到 某个条件为真为止，否则返回默认值 def(如果指定了的话)，如果没有默认值，并且前面的 对比也都没有为真，则返回空。 毫无疑问，decode 是个非常重要的函数，在实现行转列等功能时都会用到，需要牢记和熟 练使用。 例如：select decode(a2,a1,true1,a2,true2,default) from dual; 2、GREATEST(n1,n2,.n) 返回序列中的最大值 例如：SELECT GREATEST(15,5,75,8) “Greatest“ FROM DUAL; 3、LEAST(n1,n2.n) 返回序列中的最小值 例如：SELECT LEAST(15,
16、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 ELSE c1 END。c1,c2 可以是任何类型。如 果两者类型不同，则 oracle 会自动将 c2 转换为 c1 的类型。 例如：SELECT NVL(null, 12) FROM DUAL; 6、NVL2(c1,c2,c3) 大家可能都用到 nvl
