首页 > 代码库 > Oracle常用函数
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用内部用下列数字格式来存储世纪、年、月、日、小时、分、秒
| CENTURY | YEAR | MONTH | DAY | HOUR | MINUTE | SECOND |
| 21 | 14 | 10 | 09 | 15 | 37 | 21 |
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
Oracle常用函数