ORACLE常用数值函数、转换函数、字符串函数

水深无声 2022-08-24 04:14 303阅读 0赞

**ORACLE常用数值函数、转换函数、字符串函数**

感于总有些网友提出一些非常基础的问题，比如有没有实现某某功能的函数啊，某某函数是做什么用的啊，格式是什么等等，同时也感受到自己对oracle函数认识的不足，于是集中月余时间专注于oracle函数，小有心得不敢私藏，发之与诸公共享。   
    本文并不准备介绍全部的oracle函数，当前情势下，俺也还没这个时间，需要学习的东西太多了，要把多数时间花在学习经常能用上的技术方面:)，所以如果是准备深入了解所有oracle函数的朋友，还是去关注：Oracle SQL Reference官方文档更靠谱一些。  
　　本文更多将会介绍三思在日常中经常会用到的，或者虽然很少用到，但是感觉挺有意思的一些函数。分二类介绍，分别是：  
　　著名函数篇　－经常用到的函数  
　　非著名函数篇－即虽然很少用到，但某些情况下却很实用   
     注：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, 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取整后做处理，但是我文档中其它提到必须为整的地方需要特别注意，如果不为整执行时会报错的)。  
例如：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)**  
　　该类函数返回与输入类型相同的类型。  
 返回的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、UPPER(c) 将指定字符串内字符变为大写，支持CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB类型  
    例如：SELECT UPPER('WhaT is tHis') FROM DUAL;  
3、LPAD(c1,n\[,c2\]) 返回指定长度=n的字符串，需要注意的有几点：  
 如果n<c1.length则从右到左截取指定长度返回；  
 如果n>c1.length and c2 is null，以空格从左向右补充字符长度至n并返回；  
 如果n>c1.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;  
最后大家再猜一猜，如果n<0，结果会怎么样  
4、RPAD(c1,n\[,c2\]) 返回指定长度=n的字符串，基本与上同，不过补充字符是从右向左方向正好与上相反；  
    例如：SELECT RPAD('WhaT is tHis',5),RPAD('WhaT is tHis',25),RPAD('WhaT is tHis',25,'-') FROM DUAL;  
5、TRIM(\[\[LEADING||TRAILING||BOTH\] c2 FROM\] c1) 哈哈，被俺无敌的形容方式搞晕头了吧，这个地方还是看图更明了一些。 看起来很复杂，理解起来很简单：  
 如果没有指定任何参数则oracle去除c1头尾空格  
例如：SELECT TRIM(' WhaT is tHis ') FROM DUAL;  
 如果指定了c2参数，则oracle去掉c1头尾c2(这个建议细致测试，有多种不同情形的哟)  
例如：SELECT TRIM('W' FROM 'WhaT is tHis w W') FROM DUAL;  
 如果指定了leading参数则会去掉c1头部c2  
例如：SELECT TRIM(leading 'W' FROM 'WhaT is tHis w W') FROM DUAL;  
 如果指定了trailing参数则会去掉c1尾部c2  
例如：SELECT TRIM(trailing 'W' FROM 'WhaT is tHis w W') FROM DUAL;  
 如果指定了both参数则会去掉c1头尾c2(跟不指定有区别吗？没区别！)  
例如：SELECT TRIM(both 'W' FROM 'WhaT is tHis w W') FROM DUAL;  
注意：c2长度=1  
6、LTRIM(c1\[,c2\]) 千万表以为与上面那个长的像，功能也与上面的类似，本函数是从字符串c1左侧截取掉与指定字符串c2相同的字符并返回。如果c2为空则默认截取空格。  
例如：SELECT LTRIM('WWhhhhhaT is tHis w W','Wh') FROM DUAL;  
7、RTRIM(c1,c2)与上同，不过方向相反  
    例如：SELECT RTRIM('WWhhhhhaT is tHis w W','W w') FROM DUAL;  
8、REPLACE(c1,c2\[,c3\]) 将c1字符串中的c2替换为c3，如果c3为空，则从c1中删除所有c2。  
    例如：SELECT REPLACE('WWhhhhhaT is tHis w W','W','-') FROM DUAL;  
9、SOUNDEX(c) 神奇的函数啊，该函数返回字符串参数的语音表示形式，对于比较一些读音相同，但是拼写不同的单词非常有用。计算语音的算法如下：  
 保留字符串首字母，但删除a、e、h、i、o、w、y。  
 将下表中的数字赋给相对应的字母：  
1：b、f、p、v  
2：c、g、k、q、s、x、z  
3：d、t  
4：l  
5：m、n  
6：R  
 如果字符串中存在拥有相同数字的2个以上（包含2个）的字母在一起（例如b和f），或者只有h或w，则删除其他的，只保留1个；  
 只返回前4个字节，不够用0填充  
例如：SELECT SOUNDEX('dog'),soundex('boy') FROM DUAL;  
  
10、SUBSTR(c1,n1\[,n2\]) 截取指定长度的字符串。稍不注意就可能充满了陷阱的函数。  
n1=开始长度；  
n2=截取的字符串长度，如果为空，默认截取到字符串结尾；  
 如果n1=0 then n1=1  
 如果n1>0，则oracle从左向右确认起始位置截取  
例如：SELECT SUBSTR('What is this',5,3) FROM DUAL;  
 如果n1<0，则oracle从右向左数确认起始位置  
例如：SELECT SUBSTR('What is this',-5,3) FROM DUAL;  
 如果n1>c1.length则返回空  
例如：SELECT SUBSTR('What is this',50,3) FROM DUAL;  
然后再请你猜猜，如果n2<1，会如何返回值呢  
11、TRANSLATE(c1,c2,c3) 就功能而言，此函数与replace有些相似。但需要注意的一点是，translate是绝对匹配替换，这点与replace函数具有非常大区别。什么是绝对匹配替换呢？简单的说，是将字符串c1中按一定的格式c2替换为c3。如果文字形容仍然无法理解，我们通过几具实例来说明：  
例如：  
SELECT TRANSLATE('What is this','','-') FROM DUAL;  
SELECT TRANSLATE('What is this','-','') FROM DUAL;  
结果都是空。来试试这个：  
SELECT TRANSLATE('What is this',' ',' ') FROM DUAL;  
再来看这个：  
SELECT TRANSLATE('What is this','ait','-\*') FROM DUAL;  
是否明白了点呢？Replace函数理解比较简单，它是将字符串中指定字符替换成其它字符，它的字符必须是连续的。而translate中，则是指定字符串c1中出现的c2，将c2中各个字符替换成c3中位置顺序与其相同的c3中的字符。明白了？Replace是替换，而translate则像是过滤

**(三).字符型函数返回数字值(Character Functions Returning Number Values)**  
本类函数支持所有的数据类型  
1、INSTR(c1,c2\[,n1\[,n2\]\]) 返回c2在c1中位置  
 c1:原字符串  
 c2:要寻找的字符串  
 n1:查询起始位置，正值表示从左到右，负值表示从右到左 (大小表示位置,比如3表示左面第3处开始,-3表示右面第3处开始)。黑黑，如果为0的话，则返回的也是0  
 n2:第几个匹配项。大于0  
例如：SELECT INSTR('abcdefg','e',-3) FROM DUAL;  
2、LENGTH(c) 返回指定字符串的长度。如果  
例如：SELECT LENGTH('A123中') FROM DUAL;  
猜猜SELECT LENGTH('') FROM DUAL;的返回值是什么  
  
**(四).日期函数(Datetime Functions)**  
本类函数中，除months\_between返回数值外，其它都将返回日期。  
1、ADD\_MONTHS() 返回指定日期月份+n之后的值，n可以为任何整数。  
例如：SELECT ADD\_MONTHS(sysdate,12),ADD\_MONTHS(sysdate,-12) FROM DUAL;  
2、CURRENT\_DATE 返回当前session所在时区的默认时间  
例如：  
SQL> 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的工作机制写的很好，深入浅出)的短暂交流，我们认为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如果为字符的话，它的星期形式需要与当前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的值大小，结果可正可负，当然也有可能为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\]) 与前面介绍的数值型TRUNC原理相同，不过此处也是操作的日期型。  
例如：SELECT TRUNC(SYSDATE,'HH24') FROM DUAL;  
  
**(五).转换函数(Conversion Functions)**  
转换函数将指定字符从一种类型转换为另一种，通常这类函数遵循如下惯例：函数名称后面跟着待转换类型以及输出类型。  
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') 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的正整数。  
例如：  
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\[,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有多个，则持续遍历所有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,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，但你用过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 varchar2(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');  
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的各项信息，其拥有下列参数：  
 CURRENT\_SCHEMA:当前模式名；  
 CURRENT\_USER:当前用户；  
 IP\_ADDRESS:当前客户端IP地址；  
 OS\_USER:当前客户端[操作系统][Link 1]用户；  
等等数十项，更详细的参数列还请大家直接参考Oracle Online Documents  
例如：SELECT SYS\_CONTEXT('USERENV', 'SESSION\_USER') FROM DUAL;  
注：N表示数字型，C表示字符型，D表示日期型，\[\]表示内中参数可被忽略，fmt表示格式。  
单值函数在查询中返回单个值，可被应用到select，where子句，start with以及connect by 子句和having子句。

[Link 1]: http://product.it168.com/list/b/0501_1.shtml