最近又看了oracle的函数,总结一下,以备后用。。。
oracle中的函数分为:
一、单行函数:
有五大类:字符、数字、日期、转换、通用
1、字符函数:
①:大小写控制函数:lower、upper、initcap(作用首字母大写)
举例:select lower('CAT'),upper('pig'),initcap('study oracle') from dual;
②:字符控制函数:concat、substr(作用取子串)、length、instr
(作用某个字符在字符串中首次出现的位置)、
lpad | rpad、trim、replace
举例:select concat('hello','world'),substr('helloworld',2,4),
length('helloworld') from dual;
-------> helloworld ello 10
instr是从1开始,没有的话输出0;
例:select instr('helloworld','w') from dual;
lpad|rpad:
lpad(salary,10,'*') ----->左对齐,输出10位,不够*补齐,rpad相反
trim将字符串的首尾的字符去掉:
例:select trim('h' from 'hhhellohworldhh') from daul;------》ellohworld
replace:所有都替换:
select replace('abcdab','b','m') from dual;将所有的b用m来替换
2、数字函数
①:round(四舍五入,默认取整)
例:select round(255.25,2), round(255.25), round(255.25,-2) from dual;
----------> 255.25 255 300
②:trunc(截断,默认截小数)
例:select trunc(255.25,1), trunc(255.25), trunc(255.25,-1) from dual;
----------> 255.2 255 250
③:mod(求余)
例:select mod(32,3) from dual; ------->2
3、日期函数
①:months_between(两个日期相差的月数)
例:select months_between(sysdate,hire_day) from employees ;
②:add_months(向指定日期中加上若干月数)
③:next_day(指定日期的下一星期几对应的日期)
例:select add_months(sysdate,2), add_months(sysdate,-3),
next_day(sysdate,'星期日') from dual;
④:last_day(本月的最后一天)
⑤:round(日期四舍五入)
⑥:trunc(日期截断)
4、转换函数
数据类型的转换
①:隐形(oracle自动完成转换)
源数据类型 | 目标数据类型 |
varchar2 or char | number |
varchar2 or char | date |
number | varchar2 |
date | varchar2 |
例:select '12'+2 from dual; -------->14
select sysdate + '2' from dual;
②:显性
to_char to_number
------------------------------> --------------------------->
date character number
<--------------------------- <---------------------------
to_date to_char
例:select employee_id,hire_date from employees
where to_char(hire_date,'yyyy-mm-dd')='1994-06-07';
select employee_id,hire_date from employees
where to_date('1994-06-07','yyyy-mm-dd')=hire_date;
如果条件中含有汉字则用双引号
例:where to_char(hire_date,'yyyy''年''mm"月"dd"日"')
=’1994年06月07日‘;
例:select to_char(1234567.89,'999,999,99') from dual;
显示美元$
例:select to_char(1234567.89,'$999,999,99') from dual;
显示当地的货币符号用L
例:select to_char(1234567.89,'L999,999,99') from dual;
例:select to_number('¥12,345,68','L999,999,999,99') from dual;
5、通用函数
这些函数适用于任何数据类型,同时也适用于空值
①:nvl(expr1,expr2)
将空值转换成为一个已知的值
nvl(salary,0) ------>当薪水为空的时候用0来代替,不为空就为salary的值
注意:当由数值转换为字符的时候注意类型的转换
nvl(to_char(department_id,'999999'),'没有部门')
②:nvl2(expr1,expr2,expr3)
当expr1不为空的时候返回expr2,为空的时候返回expr3
③:nullif(expr1,expr2)
expr1和expr2相等的话就返回null,不相等的话就返回expr1
④:coalesce(expr1,expr2,expr3...exprn)
expr1为空,返回expr2,expr2为空,返回expr3。。。
条件表达式
在SQL语句中使用IF-THEN-ELSE逻辑
使用两种方法
case表达式
语法:
case expr when expr1 then return expr1
when expr2 then return expr2
when exprn then return exprn
else expr
end
例:select employees_id, last_name,department_id,
case department_id when 10 then salary*1.1
when 20 then salary*1.2
else salary *1.3 new_salary
end
from employees where departmen_id in(10,20,30);
decode函数
例:
select employee_id, last_name, department_id, decode
(department_id,10,salary*1.1,
20,salary*1.2,
salary*1.3) new_salary
from employees where departmen_id in(10,20,30);
二、多行函数: