Oracle学习笔记之四sp1，Oracle 11g的常用函数

从Oracle学习笔记之四，SQL语言入门中摘出来的，独立成一章节

3.1 字符类函数

1. ASCII(c)和CHR(i)    分别用于返回一个字符的ASCII码和返回给定ASCII值所对应的字符。
2. CONCAT(s1,s2)    将字符串s2连接到字符串s1的后面。任一字符串为空值则忽略此空值；两值都为空值则返回null。同样“||"也有此功能，且更方便些。
3. INITCAP(s)    将字符串s的每个单词的第一个字母大写，其它字母小写。
4. INSTR(s1,s2[,i][,j])    该函数用于返回字符串s2在s1中第j次出现时的位置，从i位开始搜索。如果i为负数，则搜索从右到左进行。
5. LENGTH(s)    返回字符串s的长度。注意：一个汉字的长度是1。
6. LOWER(s)和UPPER(s)    返回字符串s的小写与大写形式。
7. LTRIM(s1,s2)、RTRIM(s1,s2)   分别移去字符串开始、结尾部分指定字符。默认空格。
8. TRIM( [ leading | trailing | both  [ trim_character ]  ]   string1 )     该函数移去字符串开始与结尾部分指定字符。也可以只移去开始或者是结尾部分指定字符。
9. REPLACE(s1,s2[,s3])    该函数使用s3字符串替换出现在s1字符串中的所有s2字符串，返回替换后的新字符串。s3默认值为空字符串。
10. SUBSTR(s,i,[j])   从字符串s1的第i个位置截取长度为j的子字符串。省略j则取到尾部。

select ASCII('A'),CHR(65) from dual;        
--65    A
select CONCAT('Hello ','World'),'Hello '||'World' from dual;
--Hello World    Hello World
select INITCAP('I am a good boy!') from dual;
--I Am A Good Boy!
select INSTR('Hello World...','World',1,1),INSTR('Hello World...','World',-1,1) from dual;
--7    7
select LENGTH('Hello') from dual;
--5
select LOWER('Hello'),UPPER('Hello') from dual;
--hello    HELLO
select LTRIM('###Hello##','#'),RTRIM('###Hello##','#') from dual;
--Hello##    ###Hello
select TRIM('   tech   '),TRIM(' '  from  '   tech   '),TRIM(leading '0' from '000123'),TRIM(trailing '1' from 'Tech1'),TRIM(both '1' from '123Tech111') from dual;
--tech    tech    123    Tech    23Tech
select REPLACE('Hello World','World','Beijing'),REPLACE('Hello World','World') from dual;
--Hello Beijing    Hello 
select SUBSTR('Hello World',1,5),SUBSTR('Hello World',6) from dual;
--Hello     World

3.2 数字类函数

1. CEIL(n)         返回大于或等于数值n的最小整数。
2. ROUND(n1,n2)  返回舍入小数点右边n2位的n1的值，n2默认值为0。如果n2为负数，就舍入到小数点左边的相应的位上。
3. POWER(n1,n2)  返回n1的n2次方。
4. MOD(n1,n2)      返回n1除以n2的余数。
5. ABS(n)             返回n的绝对值。
6. FLOOR(n)         返回小于或等于n的最小整数。

select CEIL(7.3) from dual;
--8
select ROUND(123.4567,2),ROUND(123.4567),ROUND(123.4567,-2) from dual;
--123.46    123    100
select POWER(2,3) from dual;
--8
select MOD(10,3) from dual;
--1
select ABS(-10) from dual;
--10
select FLOOR(7.3) from dual;
--7

3.3 日期和时间类函数

1. SYSDATE()                返回系统当前日期。
2. MONTHS_BETWEEN(d1,d2)    返回d1和d2之间的数目，若d1与d2的日期（DAY）都相同，或者都是该月的最后一天，则返回一个整数，否则返回的结果将包含一个小数。
3. LAST_DAY(d)             返回包含日期d月份的最后一天。
4. NEXT_DAY(d,day)      返回d后第一周指定day的日期。
5. ADD_MONTHS(d,i)     返回日期d加上i个月之后的结果，i为任意整数。

select SYSDATE from dual;
--2013/7/27 星期六 20:59:34
select MONTHS_BETWEEN(TO_DATE('2013-7-27','yyyy-mm-dd'),TO_DATE('2013-12-7','yyyy-mm-dd')) from dual;
---4.35483870967741935483870967741935483871
select LAST_DAY(TO_DATE('2013-7-27','yyyy-mm-dd')) from dual;
--2013/7/31 星期三
select NEXT_DAY(TO_DATE('2013-7-27','yyyy-mm-dd'),'星期二') from dual;
--2013/7/30 星期二
select ADD_MONTHS(TO_DATE('2013-7-27','yyyy-mm-dd'),2) from dual;
--2013/9/27 星期五

3.4 转换类函数

1. TO_CHAR(x[,format])     该函数实现将表达式转换为字符串。

   参考：http://www.java2s.com/Tutorial/Oracle/0300__Conversion-Functions/0140__TO_CHAR.htm

2. TO_DATE(s[,format])     该函数实现将字符串s转换为日期类型。
3. TO_NUMBER(s[,format])     该函数返回字符串s代表的数字。

　　　　参考：http://www.java2s.com/Tutorial/Oracle/0300__Conversion-Functions/0160__TO_NUMBER.htm

select TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss'),TO_CHAR(12345.67, '$99,999.99') from dual;
--2013-07-27 21:00:12     $12,345.67
select TO_DATE('2013-7-27 12:43:32','yyyy-mm-dd hh24:mi:ss') from dual;
--2013/7/27 星期六 12:43:32
select TO_NUMBER('98.32'),TO_NUMBER('$98.32','$999.99'),TO_NUMBER('1E+6','9.99EEEE') from dual;
--98.32    98.32    1000000

3.5  聚合类函数

1. AVG(x[DISTINCT|ALL])          计算选择列表项的平均值。
2. COUNT(x[DISTINCT|ALL])      返回查询结果中的记录数。
3. MAX(x[DISTINCT|ALL])          返回选择列表项目中的最大数。
4. MIN(x[DISTINCT|ALL])          返回选择列表项目中的最小数。
5. SUM(x[DISTINCT|ALL])          返回选择列表项目的数值总和。

select AVG(BROWSE_COUNT) from BBSDETAIL;
--2378.136894183198040251693164897449445218
select MAX(BROWSE_COUNT) from BBSDETAIL;
--10072
select MIN(BROWSE_COUNT) from BBSDETAIL;
--0
select SUM(BROWSE_COUNT) from BBSDETAIL;
--99804855

3.6 其它辅助函数

1. DECODE(exp,s1,r1,s2,r2..s,r[,default])     在一个函数内实现if else的功能。exp为输入值，s做为对比值，相同则返回r，如果s有多个，则持续遍历所有s，直到某个条件为真为止；匹配不成功时返回默认值default，如果没有默认值，则返回空。
2. GREATEST(n1,n2,...n)     返回序列中的最大值。
3. LEAST(n1,n2....n)           返回序列中的最小值。
4. NULLIF(c1,c2)                逻辑等价于：case when c1 = c2 then null else c1 end。
5. NVL(c1,c2)                     逻辑等价于：if c1 is null then c2 else c1 end。

select DECODE('CM','AM','RE1','BM','RE2','CM','RE3','UNKNOWN') from dual;
--RE3
select GREATEST(12,34,55,1,23,12) from dual;
--55
select LEAST(12,34,55,1,23,12) from dual;
--1
select NULLIF('Hello','World'),NULLIF('Hello','Hello') from dual;
--Hello
select NVL('Hello','World'),NVL(null,'World') from dual;
--Hello    World