单行函数
单行函数:只对一行进行变换,产生一个结果。函数可以没有参数,但必须要有返回值。
1,操作数据对象
2,接受参数返回一个结果
3,只对一行进行变换
4,每行返回一个结果
5,可以转换数据类型
6,可以嵌套
7,参数可以是一列或一个值
字符函数
1,大小写控制函数:
lower 转小写
upper 转大写
initcap 首字母大写
SQL> select lower('Hello wOrld') 一,upper('Hello wOrld') 二,initcap('Hello wOrld') 三
from dual; //以上三个函数对字符串进行处理,
2,字符控制函数
concat 连接字符串,支持2个参数
SQL> select concat('hello', 'world') from dual;
SQL>select 'hellp'|| 'world'||'hahah'||'4444' from dual; //使用||连接多个字符串
substr(str,pos,len) 截取字符串,从pos位置开始,截取len长度,如果len未填写,代表直接到末尾
SQL> select substr('hello',2) from dual; //result is ello
SQL> select substr('hello',2,3) from dual; //result is ell
length:字符数, lengthb:字节数:
SQL> select length('中国')from dual; //result is 2
SQL> select lengthb('中国') from dual; //result is 4
SQL> select lengthb('hello world') from dual; //一个英文字母 一字节
instr:在母串中查找子串, 找到返回下标,计数从1开始。没有返回0
SQL> select instr('hello world', 'llo') from dual; //result is 3
lpad:左填充,参1:待填充的字符串,参2:填充后字符串的总长度(字节), 参3:填充什么
rpad:右填充。同上
SQL> select lpad('hello',10,'*') "LEFT", rpad('hello', 10,'#') "RIGHT" from dual;
trim:去掉前后指定的字符
SQL> select trim('H' from 'Hello worldH') from dual; //result is ello world
//注意语法,参数中间含有from关键字
SQL> select trim(' hello world ') from dual; //当没指定去除的内容时,默认是去除空格
replace:替换 和 删除字符串
SQL> select replace('hello world','l','*') from dual; //result is he**o wor*d
SQL> select replace('hello world','l') from dual; //result is heo word
数值函数
round四舍五入
SQL> select round(45.926,2) "one" , //45.93
round(45.926,1) "two", //45.9
round(45.926,0) "three", //46
round(45.926) "for", //46
round(45.926, -1) "five", //50
round(45.926, -2) "six" //0
from dual;
trunc截断
SQL> select trunc(45.926,2) "one", //45.92
trunc(45.926,1) "two", //45.9
trunc(45.926,0) "three", //45
trunc(45.926) "for", //45
trunc(45.926,-1) "five", //40
trunc(45.926, -2) "six" //0
from dual;
mod取模
SQL> select
mod(1600, 600) from dual; //400
转换函数
在不同的数据类型之间完成转换。将“123” 转换为 123。有隐式转换和显示转换之分。
隐式转换:由Oracle数据库来做。前提条件是:被转换的对象是可以转换的。
SQL>select *
from emp
where hiredate = '17-11月-81'; //给定一个字符串 匹配 hirdate 列中的日期
显示转换:通过转换函数来完成
SQL> select
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;
格式 说明 举例
YYYY Full year in numbers 2011
YEAR Year spelled out(年的英文全称) twenty eleven
MM Two-digit value of month 月份(两位数字) 04
MONTH Full name of the month(月的全称) 4月
DY Three-letter abbreviation of the day of the week(星期几) 星期一
DAY Full name of the day of the week 星期一
DD Numeric day of the month 02
日期转字符串
SQL> select to_char(sysdate, 'YYYY-MM-DD hh24:mi:ss "今天是:" DAY') "TAB"
from dual;
字符串转日期
SQL> select to_date('2015-05-11 15:17:06 今天是 星期一', 'YYYY-MM-DD hh24:mi:ss "今天是" DY') "TAB" from dual;
数字转换成字符串
SQL>select
to_char(1999.4274, 'L9,999.99')
from dual;
//即查询 emp 表 sal列 中的数据按 本地货币符号,千位符号,2位小数显示
//¥1,999.43
字符串转换成数字
SQL> select
to_number('¥1,999.43', 'L9,999.99')
from dual; //1999.43
时间函数
在Oracle中日期型的数据,既有日期部分,也有时间部分。
SQL> select
sysdate from dual; //系统默认只显示日期
SQL> select
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') //显示日期和时间
from dual ;
SQL> select
to_char(sysdate, 'day') from dual ; //DAY 和 DY 都是 “星期几”
日期加、减数字得到的结果仍为日期。 它的运算单位为天
SQL> select
sysdate -1 "昨天", sysdate"今天", sysdate + 1"明天"
from dual;
SQL> select
to_char(sysdate-1, 'yyyy-mm-dd') 昨天,
to_char(sysdate, 'yyyy-mm-dd') 今天,
to_char(sysdate+1,'yyyy-mm-dd') 明天
from dual;
日期和日期可以相减,但是不允许相加
SQL> select ename, hiredate,
(sysdate - hiredate) 天,
(sysdate - hiredate)/7 星期,
(sysdate - hiredate)/30 月,
(sysdate - hiredate)/365 年 //该语句计算的结果得到的年份和月份并不精确
from emp //两个日期相减,得到的就是相差的天数。
日期函数
months_between:两个日期值相差的月数(精确值)
SQL> select ename,hiredate,
months_between(sysdate, hiredate) 月份 //该函数得出精确值
from emp ;
SQL> select months_between(sysdate,
to_date('2015-05-11 15:17:06 今天是 星期一', 'YYYY-MM-DD hh24:mi:ss "今天是" DY'))
from dual;
add_months:在某个日期值上,加上多少的月,正数向后计算,负数向前计算
SQL> select
add_months(sysdate,95) //计算95个月后是那一天
from dual;
last_day:日期所在月的最后一天
SQL> select
last_day(sysdate) from dual ;
next_day:指定日期的下一个日期
SQL> select
next_day(sysdate, '星期一') from dual;
日期的四舍五入 和 截断
SQL> select
round(sysdate, 'month'), //对月份进行取整, 天数舍入 其结果 xx月1日
round(sysdate, 'year') //对年份进行取整, 月数舍入 其结果 xxx年1月1日
from dual
SQL> select
trunc (sysdate, 'month'), //对月份进行取整,天数截断 其结果 xx月1日
round(sysdate, 'year') //对年份进行取整,月数截断 其结果 xxx年1月1日
from dual
通用函数
这些函数适用于任何数据类型,同时也适用于空值
NVL (expr1, expr2) 当expr1= null 返回 expr2, 否则返回expr1.
NVL2(expr1, expr2, expr3) 是nvl函数的增强版。
当expr1= null 返回 expr3, 否则返回expr2.
SQL> select empno, ename, sal, sal*12,
sal * 12 + nvl2(comm, comm, 0) 年薪
from emp;
NULLIF(expr1, expr2)
当 expr1= expr2 时返回null, 不相等的时候返回 expr1 值
SQL> select
nullif('L9,999.99', 'L9,999.99')
from dual;
COALESCE(expr1, expr2, …, exprn)
从左向右找参数中第一个不为空的值
SQL> select comm, sal,
coalesce(comm, sal) 结果值
from emp
条件表达式
case: 是一个表达式, 是sql 语法
其语法为:
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
SQL> select ename, job,sal 涨前薪水,{
case job when 'PRESIDENT' then sal + 1000
when 'MANAGER' then sal+800
else sal + 400
end} 涨后薪水,
enumber
from emp
order by enumber; //按照enumber 列来排序
decode:是一个函数, 是orcale 公司提供的语法
其语法为:
DECODE(col|expression, search1, result1
[,search2, result2,…,]
[,default])
SQL> select ename, job,sal 涨前薪水,{
decode(job, 'PRESIDENT' ,sal + 1000 ,
'MANAGER' , sal+800,
sal + 400) as 涨后薪水,
enumber
from emp
order by enumber; //按照enumber 列来排序