IT Notes‎ > ‎Database‎ > ‎Oracle‎ > ‎

Oracle 常用函数

Contents

  1. 1 字符函数
    1. 1.1 lower(string), upper(string)
    2. 1.2 length(string)
    3. 1.3 substr(string, m, n)
    4. 1.4 replace(string, string_search, string_replace)
    5. 1.5 instr(char1, char2[,m[,n]])
    6. 1.6 trim, ltrim, rtrim
    7. 1.7 to_char
  2. 2 逻辑函数
    1. 2.1 decode
    2. 2.2 nvl, nvl2
  3. 3 数学函数
    1. 3.1 三角函数
    2. 3.2 round
    3. 3.3 mod(m, n)
    4. 3.4 取整
    5. 3.5 abs
  4. 4 统计函数
  5. 5 日期函数
    1. 5.1 sysdate
    2. 5.2 add_months(d, n)
    3. 5.3 to_char, to_date
  6. 6 系统函数
Oralce 内置了非常多的函数，这里记录一下自己遇到的。不求系统，仅为己用。

字符函数

lower(string), upper(string)

大小写转换。
select lower('Hello, world!') from dual
得到 hello, world!
select upper('Hello, world!') from dual
得到 HELLO, WORLD!

length(string)

求字串长度。小本上的数据库字符集是 UTF8，对中文字符，一个字符的长度是1， 不知道别的语言集安装，是否会导致中文字符作两个字符(TODO)
select length('你好！') from dual; -- 结果为3

substr(string, m, n)

string 的 m 到 n 位字符串（从1开始）。例如
select substr('Hello, world!', 2, 5) from dual;
结果为 ello,

对于中文，可能会依赖于不同的配置环境。小本上设置的字符集是 UTF8，一个汉字作为一个字符，在虚拟机上的字符集是SIMPLIFIED CHINESE_CHINA.ZHS16GBK，对中文的支持也没问题，一个汉字算一个字符。

附：查找 Oracle 使用的编码语句：select userenv('language') from dual;（来源：http://bbs.csdn.net/topics/260020463）

replace(string, string_search, string_replace)

替换字串。
select replace('Hello, world!', 'o', '哦');
结果为 Hell哦, w哦rld!

instr(char1, char2[,m[,n]])

找子字串的位置。
select instr('hello', 'e') from dual;
结果为2，即字母 e 在 hello 字串的第二个位置。更详细的说明如下：
语法：instr(sourceString,destString,start,appearPosition). 
sourceString，源字符串；
destString，目标字串，想从源字符串中查找的子串；该参数也是可选的，默认为1； 
start，查找的开始位置，该参数可选的，默认为1； 如果start的值为负数，那么代表从右往左进行查找。 
appearPosition，想从源字符中查找出第几次出现的
返回值：查找到的字符串的位置。

trim, ltrim, rtrim

消除字符串两端的空格，trim 两端的空格都消除，ltrim 只消除左端的，rtrim 只消除右端的。
例：
select length('  hello ') from dual; -- 8
select length(trim('  hello ')) from dual; -- 5
select length(ltrim('  hello ')) from dual; -- 6
select length(rtrim('  hello ')) from dual; -- 7

to_char

字符转化函数，可以格式化一些数据。
格式化日期和在工资前加上货币符号的例子：
select to_char(hiredate, 'yyyy-mm-dd hh24:mi:ss') "入职时间",
       to_char(sal, 'L00000.00') "薪水" --L表示显示本地的货币符号
  from emp4;
结果为
TO_CHAR(HIREDATE,'YYYY-MM-DDHH TO_CHAR(SAL,'L0000.00')
------------------------------ -----------------------
1981-02-26 00:02:00                    RMB1520.00
1981-04-01 13:04:15                    RMB1700.00
1981-05-01 00:05:00                    RMB2850.00
1981-06-09 00:06:00                    RMB2950.00
1987-04-19 00:04:00                    RMB3333.00
2010-05-01 00:05:00                    RMB7000.00
2010-05-01 00:05:00                    RMB4567.00
2010-05-01 00:05:00                    RMB4233.56

'L9999.99'  
'L0000.00' 空位用0补全 

另，对 to_char 下面的日期函数部分中也有介绍。

逻辑函数

decode

decode 函数非常有用，相当于程序语言中的 if/else 结构。
语法：DECODE(value, if1, then1, if2, then2, if3, then3,  . . ., else)

nvl, nvl2

Oracle 中有个怪癖，任何量和 null 运算的结果都是 null，nvl 函数应运而生，它对不同条件下的 null 按需求转化，简单说它的功能可以表述为：“如果该值为 null，那么给这个值赋一个确定值（The function NVL returns the value of its second argument if its first argument is null.）”。在字符串连接时常有这样的用法：如果字符串为 null，那么设置为 '' （空串）。
select ename, nvl(comm, 8888) from emp; -- 如果奖金 comm 为空，则设置为 8888

How to use NVL and NVL2 function:
  1. NVL: http://www.techonthenet.com/oracle/functions/nvl.php
  2. NVL2: http://www.techonthenet.com/oracle/functions/nvl2.php

数学函数

Oracle 内置了不少常用的数学函数。

三角函数

cos, cosh, exp, ln, log, sin, sinh, sqrt, tan, tanh, acos, asin, atan,

round

round(n[, m]) 四舍五入，m是小数位，如果m是负数，表示四舍五入到小数点前m位。
trunc(n[, m])截取，m的规则同 round 函数

mod(m, n)

求余函数，如下两例：
select mod(3, 5) from dual; -- result: 3
select mod(5, 3) from dual; -- result: 2

取整

floor(x)，向上取整
ceil(x)，向下取整

abs

求绝对值

统计函数

count, avg, sum 等

日期函数

日期的默认格式：dd-mon-yy

sysdate

当前日期，如：
select sysdate from dual;
输出：2013-5-26 23:26:23

add_months(d, n)

在 d 时间之后的 n月，如：
select add_months(sysdate, 1) from dual;
输出：2013-6-26 23:29:46
注意：并没有类似的 add_days 这种函数
 
例2，查询入职8月以上的员工 
select ename from emp4 where add_months(hiredate, 8) < sysdate;
 
例3，显示满10年的员工信息
select * from emp where add_months(hiredate, 120)<=sysdate;
 
例4，显示每个员工在公司的天数
select ename, ceil(sysdate-hiredate) "入职天数" from emp4;

例5，找出各月倒数第三天受雇佣的员工
select * from emp4 where hiredate + 2 = last_day(hiredate);

to_char, to_date

例1，显示1981年所有入职的员工
select * from emp4 where to_char(emp4.hiredate, 'yyyy')='1981'

例2，显示12月份入职的员工
select * from emp4 where to_char(emp4.hiredate, 'mm')='05'
  
Oracle 默认的日期格式是 DD-MON-YY，可用如下改变日期的默认格式： 

例3，
select to_char(hiredate, 'yyyy-mm-dd hh24-mi-ss') from emp4; 

例4，
update emp4 
   set hiredate = to_date('1981-04-01 13:14:15', 'yyyy-mm-dd hh24:mi:ss') 
 where empno = 7566; 

另，对 to_char 上面的字符函数部分中也有介绍。

系统函数

sys_context，查询系统信息，它可以查如下内容

  1. terminal 当前用户的终端标识符 
  2. language 语言 
  3. db_name 当前数据库名称 
  4. nls_date_format 当前会话用的日期格式 
  5. session_user 当前会话客户所对应的数据库用户名 
  6. current_schema 当前会话客户对应的默认方案名 
  7. host 数据库所在主机名称 
例1，查系统语言
select sys_context('userenv', 'language') from dual; 
输出：SIMPLIFIED CHINESE_CHINA.AL32UTF8

例2，查数据库名称
select sys_context('userenv', 'db_name') from dual; 
输出：orcl

Comments