单行函数

单行函数

单行函数:只对一行进行变换，产生一个结果。函数可以没有参数，但必须要有返回值。

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 列来排序