Oracle常用函数

一、函数的作用（了解）

       1、进行数据计算

       2、修改单个数据项

       3、处理成组行的输出

       4、指定日期和数字的显示格式

       5、转换列数据类型

二、函数类型

       1、单行函数

       2、多行函数

三、单行函数

       1、定义：单行函数只处理单个行，并且每行返回一个结果；

       2、常见类型：字符函数、数字函数、日期函数、转换函数、通用函数；

       3、用途：用于维护数据操作、获取参数并返回一个特定功能输出的值、函数是在每行上操作、每行有一行返回                              值、可以修改数据类型、能嵌套、可以接受多个参数；

       4、特征：作用于查询返回的每个行、为每行返回一个结果、可能会返回与引用数据类型不同的数据值、可能需要                          一个或多个参数、可以用在SELECT、WHERE、ORDER BY子句中，也可以嵌套；

四、字符函数举例

        1、大写转换：

            SQL> select upper(‘computer‘) from dual;

            UPPER(‘COMPUTER‘)

            -----------------

            COMPUTER

    2、小写转换：

            SQL> select lower(‘COMPUTER‘) from dual;

            LOWER(‘COMPUTER‘)

            -----------------

            computer

    3、首字母大写

            SQL> select initcap(‘computer home‘) from dual;

            INITCAP(‘COMPUTERHOME‘)

            -----------------------

            Computer Home

        4、字符串连接

            SQL> select concat(‘computer‘,‘home‘) from dual;

            CONCAT(‘COMPUTER‘,‘HOME‘)

            -------------------------

            computerhome

       说明：concat函数效果等同于||，为了书写方便，日常习惯中我们经常使用||；

    5、字符串截取

            说明：字符串角标从1开始；

                  第二个参数1表示从第几位开始截取，第三个参数3表示截取的位数；

                  第三个参数可以为空，表示从截取位开始截取剩余所有字符内容；

                  substr函数也可支持从末位开始截取，方法是第二个参数是负数；

            SQL> select substr(‘computer‘,1,3) from dual;

            SUBSTR(‘COMPUTER‘,1,3)

            ----------------------

            com

            SQL> select substr(‘computer‘,1) from dual;

            SUBSTR(‘COMPUTER‘,1)

            --------------------

            computer

            SQL> select substr(‘computer‘,-5) from dual;

            SUBSTR(‘COMPUTER‘,-5)

            ---------------------

            puter

    6、字符串长度

            SQL> select length(‘computer‘) from dual;

            LENGTH(‘COMPUTER‘)

            ------------------

                             8

    7、字符位置

            SQL> select instr(‘computer‘,‘m‘) from dual;

            INSTR(‘COMPUTER‘,‘M‘)

            ---------------------

                                3

       说明：若查询的字符在该字符串中存在，则返回角标数值，如果不存在返回0，若查询的字符在该字符串中存在多个，则返回第一次出现的角标。

    8、填充函数（lapd、rpad）

            SQL> select lpad(‘hello‘,13,‘*‘) from dual;

            LPAD(‘HELLO‘,13,‘*‘)

            --------------------

            ********hello

        说明：上面例子是lpad的举例，rpad效果类似lpad，含义是从从字符串左边开始填充*，*和字符串的总长度是13

    9、删除字符串两端的空格

            SQL> select trim(‘ computer ‘) from dual;

            TRIM(‘COMPUTER‘)

            ----------------

            computer

四、数字函数     

    1、round函数

            SQL> select round(32.658,2) from dual;

            ROUND(32.658,2)

            ---------------

                      32.66

            说明：保留数字的小数位数，按照四舍五入原则进行取舍。

    2、trunc函数

            SQL> select trunc(32.658,2) from dual;

            TRUNC(32.658,2)

            ---------------

                      32.65

            说明：truc函数将精确小数位数后数字全部切除，不进行四舍五入。

    3、mod函数

            SQL> select mod(11,3) from dual;

             MOD(11,3)

            ----------

                     2

            说明：mod是取余数的函数。

五、日期函数     

    1、存储格式：

            ORACLE用内部用下列数字格式来存储世纪、年、月、日、小时、分、秒

   2、默认显示格式：

        ORACLE默认按照美国人习惯的格式进行显示（sqlplus中），格式是日-月-年，如09-10月-14；

   3、oracle中如何查询当前日期

        select sysdate from dual；

        说明：查询出的结果是根据oracle所安装的计算机时间而定，并非客户端时间。

   4、如何修改成中国人习惯的“年-月-日”格式

        alter session set nls_date_format=‘YYYY-MM-DD‘;

        说明：这个更改只对当前会话有效，注销sqlplus重新登录后，恢复至默认显示状态；

   5、oracle中日期最小的计算单位是天

      比如我们需要计算当前日期距离2012年7月14日相隔多少天。

        SQL> select sysdate-to_date(‘20120714‘,‘yyyymmdd‘) from dual;

        SYSDATE-TO_DATE(‘20120714‘,‘YY

        ------------------------------

                      817.660671296296

   6、months_between:计算两个日期相隔的月份数

        SQL> select months_between(sysdate,to_date(‘20120714‘,‘yyyymmdd‘)) from dual;

        MONTHS_BETWEEN(SYSDATE,TO_DATE

        ------------------------------

                      26.8601515830346

   7、add_months：月份添加函数

        SQL> select add_months(sysdate,1) from dual;

        ADD_MONTHS(SYSDATE,1)

        ---------------------

        2014/11/9 15:59:08

        说明：参数支持负数，即倒减运算；

   8、numtoyminterval：年、月计算函数

        SQL> select to_date(‘20120714‘,‘yyyymmdd‘)+numtoyminterval(2,‘year‘) from dual;

        TO_DATE(‘20120714‘,‘YYYYMMDD‘)

        ------------------------------

        2014/7/14

        说明：numtoyminterval不能单独使用，必须与一个具体的时间点联合起来做算术运算

   9、numtodsinterval：日、时、分、秒计算函数

        SQL> select to_date(‘20120714‘,‘yyyymmdd‘)+numtodsinterval(818,‘day‘) from dual;

        TO_DATE(‘20120714‘,‘YYYYMMDD‘)

        ------------------------------

        2014/10/10

        说明：函数中的参数只能是单数形式，比如day、hour、second等；

   10、ORACLE中周日是每周的第一天，根据软件版本的不同，可能用0或1来表示，如果用0，则用0-6表示周日到周六，用1表示，则用1-7表示周日至周六；

       next_day：默认表示指定日期一周内计算周几的具体日期，如果想想计算下周六是几号，如下：

        SQL> select next_day(sysdate+7,7) from dual;

        NEXT_DAY(SYSDATE+7,7)

        ---------------------

        2014/10/18 16:28:36

    11、last_day：计算指定日期所在月份的最后一天

        SQL> select last_day(sysdate) from dual;

        LAST_DAY(SYSDATE)

        -----------------

        2014/10/31 16:33:

    12、使用上午或下午区分当前日期时间

        SQL> select to_char(sysdate,‘yyyy-mm-dd AMhh:mi:ss‘) from dual;

        TO_CHAR(SYSDATE,‘YYYY-MM-DDAMH

        ------------------------------

        2014-10-09 下午04:41:56