Oracle之SQL函数使用整理归纳

Oracle提供很多函数可以用来辅助数据查询。若有不当之处，欢迎批评指正！

1、ceil（）函数

用法：ceil（n），取大于等于数值n的最小整数。

select  mgr,mgr/100,ceil(mgr/100) from scott.emp;

2、mod（）函数

用法：mod（m，n），取m整除n后的余数。

select  mgr,mod(mgr,1000) ,mod(mgr,100) ,mod(mgr,10) from scott.emp;

3、power（）函数

用法：power（m，n），取m的n次方。

select  mgr,power(mgr,2),power(mgr,3) from scott.emp;

4、round（）函数

用法：round（m，n），四舍五入，保留n位。

select  mgr,round(mgr/100,2),round(mgr/1000,2) from scott.emp;

5、sign（）函数

用法：sign（n），n>0，取1，；n=0，取0；n<0，取-1。

select  mgr,mgr-7800,sign(mgr-7800) from scott.emp;

6、avg（）函数

用法：avg（字段名），求平均值，要求字段为数值型。

select  avg(mgr) 平均值 from scott.emp;

7、count（）函数

用法：count（字段名）或count（*），统计总数。

select count(*)  记录总数 from scott.emp;

8、min（）函数

用法：min（字段名），计算数值型字段的最小数。

select min(sal) 最低薪水 from scott.emp;

9、max（）函数

用法：max（字段名），计算数值型字段的最大数。

select max(sal) 最高薪水 from scott.emp;

10、sum（）函数

用法：sum（字段名），计算数值型字段总和。

select sum(sal) 薪水总和 from scott.emp;

11、lower（）函数

用法：lower（c），将指定字符串内字符变为小写，支持CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB类型。

select  lower('WhaT iS tHIs') from dual;

12、upper（）函数

用法：upper（c），将指定字符串内字符变为大写，支持CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB类型。

select  upper('WhaT iS tHIs') from dual;

13、trim（'s'  from  'string'）

leading 剪掉前面的字符

trailing  剪掉后面的字符

如果不指定，默认为空格符

14、ltrim和rtrim

ltrim  删除左边出现的字符串

rtrim  删除右边出现的字符串

select ltrim(rtrim('  hello  ',' '),' ') from dual;     -----hello

15、substr（string,strat,count）

取子字符串，从start开始，取count个

select  substr('130888888888999',3,8)from dual;  --08888888

16、replace（'string','s1','s2'）

string  希望被替换的字符或变量

s1  被替换的字符串

s2  要替换的字符串

select replace('he love you','he','i') from dual; ---i love you

转换函数（Conversion  Functions）：

 1、TO_CHAR()本函数又可以分三小类，分别是

        转换字符---->字符TO_CHAR(c)：将char，nvarchar2，clob，nclob类型转换为char类型；

 例如：select TO_CHAR('AABBCC') from dual;

   转换时间---->字符TO_CHAR(d[.fmt])：将指定的时间（data,timestamp,timestamp with 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参数。如果fmt为‘J’则表示按照公元制（Julian  day）转换，c则必须为大于0并小于5373484的正整数(因为Oracle的DATE类型的取值范围是公元前4721年1月1日至公元9999年12月31日：select TO_CHAR(TO_DATE('9999-12-31','yyyy-mm-dd  hh24:mi:ss'),'j') from dual;)。

  例如：select TO_DATE(2454336,'J') from dual;

         select  TO_DATE('2007-8-23  23:23: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;

用来转换空值的函数:

  1、NVL（expr1，expr2）

       如果该函数的第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值

 2、NVL2（expr1，expr2，expr3）

      如果该函数的第一个参数为空那么显示第三个参数的值，如果第一个参数的值不为空，则显示第二个参数本来的值

 3、NULLIF（expr1，expr2）

      如果expr1和expr2相等则返回空（NULL），否则返回第一个值

分组函数：

 基于一组行来返回结果

 为每一组行返回一个值