oracle单行函数详解

oracle单行函数详解
一  介绍
1．函数分为单行函数和多行函数
2．单行函数分为：字符、数值、日期、转换、通用的函数
3．单行函数:
	操作数据对象
	接受参数返回一个结果
	只对一行进行变换
	每行返回一个结果
	可以转换数据类型
	可以嵌套
	参数可以是一列或一个值
二  详解：
1.	字符函数分为 
（1）	大小写控制函数
小写转换 : SQL> select lower('LAOWANG') from dual;
SQL> select * from emp where lower(ename)='smith';
大写转换: SQL> select upper('laowng') from dual;
只转换第一个字符为大写：
SQL> select initcap('laoWANG') from dual;
（2）	字符控制函数
连接字符串：SQL> select concat('hi','laowang') from dual;
截取字符串：SQL> select substr('laowang',0,1) from dual; 结果 l
SQL> select substr('laowang',0,2) from dual; 结果 la
SQL> select substr('laowang',1,2) from dual;结果la
可以看出截取字符串的时候是从索引为1开始的
		字符串的长度：SQL> select length('laowang') from dual;  结果为6
		获取指定字符的索引：SQL> select instr('laowang','a') from dual; 
							结果为2
		从左边截取10个字符：SQL> select lpad('laowang',10) from dual;
								结果 laowang
			  没有的用*代替：SQL> select lpad('laoang',20,'*') from dual;
								结果：**************laowang
		从右边截取：SQL> select rpad('laoang',20,'*') from dual;
		去空格：SQL> select trim('laowang   ') from dual;
					注：取出的是’’中字符前后的字符，去不掉两个字符之间的的空格
		查询某字符中包含某字符：
				SQL> select * from emp where instr(ename,'R')!=0;或
				SQL> select * from emp where instr(ename,'R') <>0;
				SQL> select * from emp where instr(ename,'R')=4;
R在索引为4的ename
2．数值函数
截取.后两位四舍五入：SQL> select round(234324.354,2) from dual;  
结果为234324.35
		  截取.后两位，不截取：SQL> select mod(1700,200) from dual;
								结果为：100
								SQL> select mod(1701.5,200) from dual;
								结果为：101.5
3．日期函数
日期的差值：
SQL> select to_date('08-8月-08')-to_date('13-3月-12') from dual;  结果为：-1313天数
SQL> select (to_date('08-8月-08')-to_date('13-3月-12'))/365 from dual;   结果：-3.5972603
两个日期相差的月数：
SQL> select months_between(sysdate,hiredate) from emp;     
			向指定日期中加入若干月数：
				SQL> select add_months(sysdate,3) from dual;
			指定日期的下一个日期：
SQL> select next_day(sysdate,'星期一') from dual;
本月的最后一天：SQL> select last_day(sysdate) from dual;
			日期四舍五入：SQL> select round(sysdate,'yyyy') from dual;
							SQL> select round 
(to_date('1998-10-08','yyyy-mm-dd'),'yyyy') from dual;
			日期截断：SQL> select trunc(sysdate,'YEAR') from dual;
4．转换函数
数据类型转换分为隐形的和显性的，我们对显型转换来介绍
转换函数对日期的转换：
首先显示出当前日期：SQL> select  to_char(sysdate) from dual;
SQL> select  to_char(sysdate,'YYYY-MM-DD') from dual;
结果为：2012-03-13
			SQL> select  to_char(sysdate,'YYYY-MM-DD-day') from dual;
					结果为：2012-03-13-星期二  
			SQL> select  to_char(sysdate,'YYYY/MM/DD-day') from dual;
					结果为：2012/03/13-星期二  
SQL> select  to_char(sysdate,'YYYY"年"MM"月"DD"日"-day') from dual;			结果为：2012年03月13日-星期二

SQL> select  to_char(sysdate,'year"年"mon-DD-day') from dual;   结果为：twenty twelve年3月 -13-星期二     

SQL> select  to_char(sysdate,'yyyy-mm-dd HH24:MI:SS AM') 
from dual;   结果为：2012-03-13 10:30:39 上午 

SQL> select  to_char(sysdate,'DD "of" MONTH') from dual;
				结果：13 of 3月

转换函数对数字的转换:
			SQL> select to_char(sal,'$99,999.00') from emp;
						结果为： $800.00   美元符
			SQL> select to_char(sal,'L99,999.00') from emp;
						结果为：￥800.00    本地的

			字符串转换为数字：SQL> select to_number('999.00') from dual;
						结果为：999
6．通用函数
通用函数函数适用于任何数据类型，同时也适用于空值：
（1）将空值转换为一个已知的值：
				可以使用的数据类型有日期、字符、数字。
				为空的替换为0  SQL> select nvl(comm,0) from emp;
				为空的替换为指定日期：
SQL> select nvl(hiredate,’01-2月-01’) from emp;
			（2）．相当于是一个三目运算符
SQL> select nvl2(comm,1,0) from emp;
解释：当comm为空的用0替换，不为空的用1替换
SQL> select nvl2(comm,comm,0) from emp;
			解释：当comm为空的用0替换，不为空你替换为原有的comm
（3）．nullif(expr1, expr2) :  相等返回NULL，不等返回expr1 
		SQL>select length(ename) "expr1",
length(job) "expr2",
nullif(length(ename),length(job)) 
from emp;	
结果为：5          5                                                           
        5          8           5    
		   （4）．使用 coalesce 函数       
coalesce与 nvl 相比的优点在于 coalesce可以同时处理交替的多个值。
如果第一个表达式为空,则返回下一个表达式，对其他的参数进行coalesce。
SQL> select coalesce(commission_pct,salary,10)  from employees;
解释：在执行此函数的时候如果commission_pct没有值，显示的是salary，一旦commission_pct有值了，显示的commission_pct的值。


2.	条件表达式
在 SQL 语句中使用if-then-else逻辑
例1．SQL> select empno,ename,hiredate,
  2  case
  3  job when 'CLERK' then 1.2*sal
  4  when 'MANAGER' then 2*sal
  5  else sal
  6  end
  7  "涨工资了"
  8  from emp;
	结果为：
	
EMPNO ENAME      HIREDATE         涨工资了                                      
----- ---------- -------------- ----------                                      
 7369 SMITH      17-12月-80            960                                      
 7499 ALLEN      20-2月 -81           1600


例2：
SQL> select empno,ename,hiredate,
  2  decode
  3  (job,'CLERK',1.2*sal,
  4  'MANAGER',2*sal,
  5  sal) "工资又涨了"
  6  from emp;

结果为：
EMPNO ENAME      HIREDATE       工资又涨了                                      
----- ---------- -------------- ----------                                      
 7369 SMITH      17-12月-80            960                                      
 7499 ALLEN      20-2月 -81           1600                                      
 7521 WARD       22-2月 -81           1250