ORACLE常用数值函数转换函数字符串函数.docx

1、ORACLE常用数值函数转换函数字符串函数ORACLE常用数值函数、转换函数、字符串函数本文并不准备介绍全部的oracle函数，当前情势下，俺也还没这个时间，需要学习的东西太多了，要把多数时间花在学习经常能用上的技术方面:)，所以如果是准备深入了解所有oracle函数的朋友，还是去关注：Oracle SQL Reference官方文档更靠谱一些。-本文更多将会介绍三思在日常中经常会用到的，或者虽然很少用到，但是感觉挺有意思的一些函数。分二类介绍，分别是： 著名函数篇经常用到的函数 非著名函数篇即虽然很少用到，但某些情况下却很实用 注：N表示数字型，C表示字符型，D表示日期型，表示内中参数可被忽

2、略，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, ATAN, and ATAN2支持30位小数点。 1、MOD(n1,n2) 返回n1除n2的余数，如果n2=0则返回n1的值。 例如：SELE

3、CT MOD(24,5) FROM DUAL; 2、ROUND(n1,n2) 返回四舍五入小数点右边n2位后n1的值，n2缺省值为0，如果n2为负数就舍入到小数点左边相应的位上(虽然oracle documents上提到n2的值必须为整数，事实上执行时此处的判断并不严谨，即使n2为非整数，它也会自动将n2取整后做处理，但是我文档中其它提到必须为整的地方需要特别注意，如果不为整执行时会报错的)。 例如：SELECT ROUND(23.56),ROUND(23.56,1),ROUND(23.56,-1) FROM DUAL; 3、TRUNC(n1,n2 返回截尾到n2位小数的n1的值，n2缺省设置

4、为0，当n2为缺省设置时会将n1截尾为整数，如果n2为负值，就截尾在小数点左边相应的位上。 例如：SELECT TRUNC(23.56),TRUNC(23.56,1),TRUNC(23.56,-1) FROM DUAL; (二).字符型函数返回字符值(Character Functions Returning Character Values) 该类函数返回与输入类型相同的类型。 l 返回的CHAR类型值长度不超过2000字节； l 返回的VCHAR2类型值长度不超过4000字节； 如果上述应返回的字符长度超出，oracle并不会报错而是直接截断至最大可支持长度返回。 l 返回的CLOB类型值

5、长度不超过4G； 对于CLOB类型的函数，如果返回值长度超出，oracle不会返回任何错误而是直接抛出错误。 1、LOWER(c) 将指定字符串内字符变为小写，支持CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB类型 例如：SELECT LOWER(WhaT is tHis) FROM DUAL; 2、UPPER(c) 将指定字符串内字符变为大写，支持CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB类型 例如：SELECT UPPER(WhaT is tHis) FROM DUAL; 3、LPAD(c1,n,c2) 返回指定长度=

6、n的字符串，需要注意的有几点： l 如果nc1.length and c2 is null，以空格从左向右补充字符长度至n并返回； l 如果nc1.length and c2 is not null，以指定字符c2从左向右补充c1长度至n并返回； 例如：SELECT LPAD(WhaT is tHis,5),LPAD(WhaT is tHis,25),LPAD(WhaT is tHis,25,-) FROM DUAL; 最后大家再猜一猜，如果n0，则oracle从左向右确认起始位置截取 例如：SELECT SUBSTR(What is this,5,3) FROM DUAL; l 如果n1c1

7、.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、SYSDATE 功能与上相同，返回当前session所在时区的默认时间。但是需要注意的一点是，如果同时使用sysdate与current_date获得的时间不一定相同，某些情况下current_date会比sysdate快一秒。经过与xyf_tck(兄台的大作ORACLE的工作机制写

8、的很好，深入浅出)的短暂交流，我们认为current_date是将current_timestamp中毫秒四舍五入后的返回，虽然没有找到文档支持，但是想来应该八九不离十。同时，仅是某些情况下会有一秒的误差，一般情况下并不会对你的操作造成影响，所以了解即可。 例如：SELECT SYSDATE,CURRENT_DATE FROM DUAL; 4、LAST_DAY(d) 返回指定时间所在月的最后一天 例如：SELECT last_day(SYSDATE) FROM DUAL; 5、NEXT_DAY(d,n) 返回指定日期后第一个n的日期，n为一周中的某一天。但是，需要注意的是n如果为字符的话，它的

9、星期形式需要与当前session默认时区中的星期形式相同。 例如：三思用的中文nt，nls_language值为SIMPLIFIED CHINESE SELECT NEXT_DAY(SYSDATE,5) FROM DUAL; SELECT NEXT_DAY(SYSDATE,星期四) FROM DUAL; 两种方式都可以取到正确的返回，但是： SELECT NEXT_DAY(SYSDATE,Thursday) FROM DUAL; 则会执行出错，提供你说周中的日无效，就是这个原因了。 6、MONTHS_BETWEEN(d1,d2) 返回d1与d2间的月份差，视d1,d2的值大小，结果可正可负，当

10、然也有可能为0 例如： SELECT months_between(SYSDATE, sysdate), months_between(SYSDATE, add_months(sysdate, -1), months_between(SYSDATE, add_months(sysdate, 1) FROM DUAL; 7、ROUND(d,fmt) 前面讲数值型函数的时候介绍过ROUND，此处与上功能基本相似，不过此处操作的是日期。如果不指定fmt参数，则默认返回距离指定日期最近的日期。 例如：SELECT ROUND(SYSDATE,HH24) FROM DUAL; 8、TRUNC(d,fmt

11、) 与前面介绍的数值型TRUNC原理相同，不过此处也是操作的日期型。 例如：SELECT TRUNC(SYSDATE,HH24) FROM DUAL; (五).转换函数(Conversion Functions) 转换函数将指定字符从一种类型转换为另一种，通常这类函数遵循如下惯例：函数名称后面跟着待转换类型以及输出类型。 1、TO_CHAR() 本函数又可以分三小类，分别是 l 转换字符-字符TO_CHAR(c)：将nchar,nvarchar2,clob,nclob类型转换为char类型； 例如：SELECT TO_CHAR(AABBCC) FROM DUAL; l 转换时间-字符TO_CH

12、AR(d,fmt)：将指定的时间(data,timestamp,timestamp with time zone)按照指定格式转换为varchar2类型； 例如：SELECT TO_CHAR(sysdate,yyyy-mm-dd hh24:mi:ss) FROM DUAL; l 转换数值-字符TO_CHAR(n,fmt)：将指定数值n按照指定格式fmt转换为varchar2类型并返回； 例如：SELECT TO_CHAR(-100, L99G999D99MI) FROM DUAL; 2、TO_DATE(c,fmt,nls) 将char,nchar,varchar2,nvarchar2转换为日期

13、类型，如果fmt参数不为空，则按照fmt中指定格式进行转换。注意这里的fmt参数。如果ftm为J则表示按照公元制(Julian day)转换，c则必须为大于0并小于5373484的正整数。 例如： 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_

14、CHAR(TO_DATE(9999-12-31,yyyy-mm-dd),j) 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,def) 可以把它理解成一个增强型的if else，只不过它并不通过多行语句，而是在一个函数

15、内实现if else的功能。 exp做为初始参数。s做为对比值，相同则返回r，如果s有多个，则持续遍历所有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;

16、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 ELSE c1 END。c1,c2可以是任何类型。如果两者类型不同，则oracle会自动将c2转换为c1的类型。 例如：SELEC

17、T 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 from dual; 7、SYS_CONNECT_BY_PATH(col,c) 该函数只能应用于树状查询。返回通过c1连接的从根到节点的路径。该函数必须与connect by 子句共同使用。 例如： create table tmp3( rootcol varchar2(10), nodecol varc

18、har2(10) ); insert into tmp3 values (,a001); insert into tmp3 values (,b001); insert into tmp3 values (a001,a002); insert into tmp3 values (a002,a004); insert into tmp3 values (a001,a003); insert into tmp3 values (a003,a005); insert into tmp3 values (a005,a008); insert into tmp3 values (b001,b003);

19、insert into tmp3 values (b003,b005); select lpad( , level*10,=) | sys_connect_by_path(nodecol,/) from tmp3 start with rootcol = a001 connect by prior nodecol =rootcol; 8、SYS_CONTEXT(c1,c2,n) 将指定命名空间c1的指定参数c2的值按照指定长度n截取后返回。 Oracle9i提供内置了一个命名空间USERENV，描述了当前session的各项信息，其拥有下列参数： l CURRENT_SCHEMA:当前模式名；

20、 l CURRENT_USER:当前用户； l IP_ADDRESS:当前客户端IP地址； l OS_USER:当前客户端操作系统用户； 等等数十项，更详细的参数列还请大家直接参考Oracle Online Documents 例如：SELECT SYS_CONTEXT(USERENV, SESSION_USER) FROM DUAL; 注：N表示数字型，C表示字符型，D表示日期型，表示内中参数可被忽略，fmt表示格式。 单值函数在查询中返回单个值，可被应用到select，where子句，start with以及connect by 子句和having子句。 (一).数值型函数(Number

21、Functions) 数值型函数输入数字型参数并返回数值型的值。多数该类函数的返回值支持38位小数点，诸如：COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH 支持36位小数点。ACOS, ASIN, ATAN, and ATAN2支持30位小数点。 1、ABS(n) 返回数字的绝对值 例如：SELECT ABS(-1000000.01) FROM DUAL; 2、COS(n) 返回n的余弦值 例如：SELECT COS(-2) FROM DUAL; 3、ACOS(n) 反余弦函数，n between -1 and 1，返回值betw

22、een 0 and pi。 例如：SELECT ACOS(0.9) FROM DUAL; 4、BITAND(n1,n2) 位与运算，这个太有意思了，虽然没想到可能用到哪里，详细说明一下： 假设3,9做位与运算，3的二进制形式为：0011，9的二进制形式为：1001，则结果是0001，转换成10进制数为1。 例如：SELECT BITAND(3,9) FROM DUAL; 5、CEIL(n) 返回大于或等于n的最小的整数值 例如：SELECT ceil(18.2) FROM DUAL; 考你一下，猜猜ceil(-18.2)的值会是什么呢 6、FLOOR(n) 返回小于等于n的最大整数值 例如：SELECT FLOOR(2.2) FROM DUAL; 再猜猜floor(-2.2)的值会是什么呢 7、BIN_TO_NU