oracle----函数收集

日期函数

1.ADD_MONTHS(date, n):用于从一个日期值增加或减少一些月份
例子： add_months(sysdate,12) 增加一年 ，如果现在是2010年5月9日，运算后结果是2011年5月9日
 
2. MONTHS_BETWEEN(date1, date2):判断两个日期之间相差的月份
例子： months_between('22-10月-09', sysdate)
输出：-4.0934573  早四个多月

3. LAST_DAY(date):函数返回包含日期的月份的最后一天
例子 ： last_day('10-2月 -10') 返回 28-2月 -10  

4. ROUND(date[,'fmt']):将日期d按照fmt指定的格式舍入，fmt为字符串
今天是10年2月24日
例子： round(sysdate,'MONTH') 返回 01-3月 -10 就是根据日来四舍五入月
round(sysdate,'YEAR') 返回 01-1月 -10  根据月来四舍五入年

5. TRUNC(date[, 'fmt']):返回由fmt指定的单位的日期
今天是10年2月24日
例子：
trunc(sysdate,'MONTH')  返回 01-2月 -10  
trunc(sysdate,'YEAR')   返回 01-1月 -10   

6. NEXT_DAY(date, 'char'):找到下一个星期几
今天是10年2月24日

例子：
next_day(sysdate,'星期五')  返回 26-2月 -10 

7. extract(date_field from [datetime_value]):找出日期或间隔值的字段值---提取函数
今天是10年2月24日

例子： extract(month from sysdate)  返回 2
extract(year from sysdate)  返回 2010
extract(day from sysdate)  返回 24

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

字符函数

1. initcap('hello') 首字母变大写
返回 ： Hello 

2. lower('HOW ARE YOU') 返回  how are you   

3. upper('hello world')  返回  HELLO WORLD  

4. ltrim('xyzadams','xyz') 返回 adams 
把'xyzadams' 字符串中左边的'xyz'字串去掉

5. rtrim('xyzadams','ams') 返回 xyzad

6.TRANSLATE(string,from_str,to_str):返回将（所有出现的）from_str中的每个字符替换为to_str中的相应字符以后的string 
例子：
translate('JACK and JUE', 'JU','BL')
返回 'BACK and BLE'
意思是把字符串中的字母J变成B ,把U变成L
translate('JACK and JUE', 'J','BL') 这个等效于 
translate('JACK and JUE', 'J','B') 因为L没有对应的东西


7. replace('JACK and JUE','J','BL')
返回 BLACK and BLUE  

8.instr('abcdeadab','a',2,2) 返回 8 意思是在'abcdeadab'找'a',从第二个位置开始找，找第二次出现的a

instr-----indexString

instr('abcdeadab','a') 返回 1 等效于 instr('abcdeadab','a',1,1)

9. substr('abcdefgh',2,4) 返回 bcde 
从'abcdefgh'的第二个位置开始找，字串长度为4

10.concat('hello',' world') 返回 hello world 

11.  chr(65) 返回 A   
 相当于java中的 new Charater(65); 就是把65当做ascii转换成字符

12. ascii('a') 返回  97
返回a的ascii

13.  lpad('abcde',10,'wt') 返回 wtwtwabcde 
leftpadding 把'abcde'变成10个字符，左边用'wt'填充

14. rpad('abcde',10,'w') 返回 abcdewwwww

15.trim([leading/trailing/both][匹配字符串或数值][from][需要被处理的字符串或数值])
例子： 
trim(0 from 7600)  返回 76
去掉两边的0
trim('中' from '中秋八月中') 返回 '秋八月'
trim(leading '半' from '半夜二更半') 返回 '夜二更半'

16. length('how are you') 返回 11

17. DECODE 条件语句 
select empno,ename,job,sal,
  2  decode(job,'CLERK', sal*1.5,
  3             'SALESMAN', sal*2.0,
  4             'MANAGER',sal*3,
  5                       sal ) as "new salary"
  6  from emp;

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

数字函数
1. abs(-12) 返回 12 求绝对值

2. ceil(44.778) 返回 45 ； 进位法保留整数，也就是整数总要+1

3. COS(x)函数，返回x的余弦值。x是以弧度表示的角度
例子： 
cos(180) 返回  -.59846007 

4. COSH(x)函数，返回X的双曲余弦 
cosh(0) 返回 1

5.  FLOOR(x)函数，用来返回小于或等于X的最大整数，不能对小数操作
floor(100.2) 返回100  返回整数，舍去小数

6.POWER(x,y)函数，返回X的Y次幂。底X和指数Y都不必是正整数，但如果X是负数的话，Y必须是整数     
power(4,2) 返回 16 

7.MOD(被除数，除数)求余函数，如果除数为0，则返回被除数
例子：
mod(10,3)  返回 1

8. ROUND(x[,y])函数，返回舍入到小数点右边Y位的X值。Y缺省为0，这将X舍入为最接近的整数。如果Y是负数， 那么舍入到小数点左边相应的位上，Y必须为整数 
例子:
round(100.347,2) 返回 100.35  四舍五入，保留两个小数

9.TRUNC(x[,y])截取值函数，Y缺省为0，这样X被截取成一个整数。如果Y为负数，那么截取到小数点左边相应位置 

 trunc(100.347,2) 返回100.34
trunc(100.347,-2)  各位的位置是0，所以小数点后一位的位置是1，所以-2代表保留三个整数 所以返回 100

10. sqrt(4) 返回2  开根号

11.SIGN(x)函数，此函数用来返回一个数的正负值，若为一个正数则返回1，若为一个负数则返回-1，若为0则仍返回0，有点像把模拟量数字化的意思 
返回符号位
sign(-10) 返回-1
sign(5)  返回1
sign(0) 返回0 

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

转换函数

1.
 to_char(sysdate, ' YYYY "年" fmMM "月" fmDD "日" HH24:MI:SS ')
日期转字符串， 其中fm表示去掉前导0
返回
2010 年 2 月 24 日 22:46:56   

把数字转换成字符： to_char(800,'C99999') 返回 'CNY800' 这个在钱币格式化时候很有用

2. to_number('100') 返回100 

3. to_date 

------------------------------------------------------------------------
条件函数

1.NVL(exp1，exp2) :  如果exp1为null，则NVL返回exp2；反之返回exp1 
 
NVL(comm,0) 如果comm为null用0代替


2.NVL2(exp1,exp2,exp3) :如果exp1不是null，则返回exp2，如果是，则返回exp3 

NVL2(comm,sal+comm,sal) ---就不说了


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

分析函数 


1. select ename,job,deptno,sal, row_number() over (order by sal desc) as sal_rank from emp;

ROW_NUMBER 返回连续的排位，不论值是否相等

2. select deptno,ename,sal,comm, rank() over(partition by deptno order by sal desc,comm) rank  from emp;

RANK 具有相等值的行排位相同，序数随后跳跃

3. 
SQL> select d.dname,e.ename,e.sal,dense_rank() over(partition by e.deptno order by e.sal desc)
  as denrank
  from emp e, dept d
  where e.deptno=d.deptno;

DENSE_RANK 具有相等值的行排位相同，序号是连续的