Oracle 11g 学习三：单行函数

    数据库开发必须学习单行函数。单行函数分为：字符函数、数字函数、日期函数、转换函数和通用函数，其中最后两个函数最为重要。

一、字符函数：主要进行字符串的操作

    a. UPPER(字符串|列)：将输入的字符串变为大写

       例：SELECT UPPER('hello') FROM dual ;

           SELECT * FROM emp WHERE ename=UPPER('&str') ;  -->将输入的字符串转大写

    b. LOWER(字符串|列)：将输入的字符串转小写

       例：SELECT LOWER(ename) FROM emp ;

    c. INITCAP(字符串|列)：输入字符串首字母大写

       例：SELECT INITCAP(ename) FROM emp ;

    d. LENGTH(字符串|列)：求出字符串的长度

       例：SELECT ename，LENGTH(ename) FROM emp WHERE LENGTH(ename)=4;

    e. REPLACE(字符串|列)：字符替换

       例：SELECT ename,REPLACE(ename,'A','_') FROM emp ;  -->ename中的字母‘A’由‘_’替换

    f. SUBSTR(字符串|列，开始点[，结束点])：字符串的截取

       注意：SUBSTR(字符串|列，开始点)：表示从起始点截取至结尾

             SUBSTR(字符串|列，开始点，结束点)：表示从起始点截取至结束点，其中起始点为0或者1都一样都表示从字符串开头开始，起始点也可以为负值-x(如-3)表示从字符串的结尾倒数x处开始截取至末尾。

       例：SELECT ename,SUBSTR(ename,0,3) FROM emp ;

           SELECT ename,SUBSTR(ename,1,3) FROM emp ;

           SELECT ename,SUBSTR(ename,-3) FROM emp ;

二、数字函数

    a. ROUND(数字|列[,保留小数的位数])：四舍五入；保留小数位也可为-1，表示从十位开始保留四舍五入

       例：SELECT ROUND(903.53567),ROUND(-903.53567),ROUND(903.53567,2),ROUND(903.53567,-1) FROM dual;

    b. TRUNC(数字|列[,保留小数位])：舍弃指定位置的内容

       例：SELECT TRUNC(903.53567),TRUNC(-903.53567),TRUNC(903.53567,2),TRUNC(903.53567,-1) FROM dual;

    c. MOD(数字1,数字2)：取模或者取余数

       例：SELECT MOD(37,5) FROM dual ; --> 37%5=2

三、日期函数

    SYSDATE：表示取得当前日期时间

    日期+数字=日期：表示若干天之后

       例：SELECT SYSDATE +30,SYSDATE+100 FROM dual ;

    日期-数字=日期：表示若干天之前

                     例：SELECT SYSDATE +30,SYSDATE+100 FROM dual ;

    日期-日期=数字：表示两个日期间的天数，一定是大日期减小日期

       例：SELECT ename,hiredate,SYSDATE-hiredate FROM emp ;

    a. LAST_DAY(日期)：求出指定日期的最后一天

       例：SELECT LAST_DAY(SYSDATE) FROM dual ;

    b. NEXT_DAY(日期，星期x)：求出指定下一个星期x的日期，其中‘星期x’也可以使用1-7的数字表示，1表示星期日，2表示星期一，3表示星期二，依次类推。

       例：SELECT NEXT_DAY(SYSDATE,'星期二') FROM emp ;

           SELECT NEXT_DAY(SYSDATE,3) FROM emp ;

    c. ADD_MONTHS(日期，数字)：求出若干月之后的日期

       例：SELECT ADD_MONTHS(SYSDATE,4) FROM dual ;

    d. MONTHS_BETWEEN(日期1,日期2)：求出两个日期间的月份，日期1最好大于日期2

       例：求出雇员雇佣的月份

           SELECT ename,hiredate,MONTHS_BETWEEN(SYSDATE,hiredate) FROM emp ;

    注意：在Oracle中使用日期操作，建议使用日期函数，因为这可以有效避免闰年问题。

四、转换函数：完成数据格式间的转换

    a. TO_CHAR(字符串|列，格式字符串)：将日期，数字转换为字符显示

       例1：按固定格式输出年月日需要一些格式字符串：年：yyyy，月：mm，日：dd；前导fm表示去掉日期显示中的‘0’

           SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd') FROM emp ;

           SELECT TO_CHAR(SYSDATE,'fmyyyy-mm-dd') FROM emp ;

       例2：输出年月日时分秒：SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss') FROM emp ;

       例3：TO_CHAR()也可用于数字的格式化，其中字母‘L’表示LOCAL含义，即当期所在语言环境下的货币符号

           SELECT TO_CHAR(97895463764,'L999,999,999,999') FROM emp ;

    b. TO_DATE()：将字符串转换为日期DATE型数据

       例：SELECT TO_DATE('2014-08-02','yyyy-mm-dd') FROM dual ;

    c. TO_NUMBER():将字符串转换为数字

       例：SELECT TO_NUMBER('1')+TO_NUMBER('3') FROM dual;

       但是在Oracle中更习惯与使用如下简单方式：SELECT'1'+'3' FROM dual;

五、通用函数

    通用函数主要有两个：NVL()、DECODE()，这两个函数是Oracle独有的函数

    a. NVL()函数用于处理NULL项，可以将NULL转化为一个数字：NVL(NULL,数字x)

       例：SELECT ename,sal,comm,(sal+comm)*12 income FROM emp ; --> 结果income中存在为NULL项，是由于comm中存在NULL项造成的。

       使用NVL()函数：SELECT ename,sal,comm,(sal+NVL(comm,0))*12 income FROM emp ;

    b. DECODE()函数：多数值判断，语法格式如下：

       DECODE(数值|列，判断1，显示1，判断2，显示2，判断3，显示3，...)

       例：现在要求显示全部雇员的职位，但是显示使用中文，CLERK：办事员，SALESMAN：销售，MANAGER：经理，ANALYST：分析员，PRESIDENT：总裁

       SELECT ename,empno,job,DECODE(job,'CLERK','办事员','SALESMAN','销售','MANAGER','经理','ANALYST','分析员','PRESIDENT','总裁') FROM emp ;

*************by jixiangrurui  转载请注明出处*************