oracle 常用函数 集合运算

一. 单行函数

1.1 字符函数

	函 数 			说 明
	ASCII 		返回对应字符的十进制值
	CHR 		给出十进制返回字符
	CONCAT 		拼接两个字符串，与 || 相同
	INITCAT 	将字符串的第一个字母变为大写
	INSTR 		找出某个字符串的位置
	INSTRB 		找出某个字符串的位置和字节数
	LENGTH 		以字符给出字符串的长度
	LENGTHB 	以字节给出字符串的长度
	LOWER 		将字符串转换成小写
	LPAD 		使用指定的字符在字符的左边填充
	LTRIM 		在左边裁剪掉指定的字符
	RPAD 		使用指定的字符在字符的右边填充
	RTRIM 		在右边裁剪掉指定的字符
	REPLACE		执行字符串搜索和替换
	SUBSTR 		取字符串的子串
	SUBSTRB 	取字符串的子串（以字节）
	SOUNDEX 	返回一个同音字符串
	TRANSLATE 	执行字符串搜索和替换
	TRIM 		裁剪掉前面或后面的字符串
	UPPER 		将字符串变为大写
	
	a)求字符串长度 LENGTH
		select length('ABCD') from dual; 
		
		伪表dual,没有真实的意义,为了(单行)测试函数而特别设计的	
	b)求字符串的子串 SUBSTR
		select substr('ABCD',2,2) from dual;    //结果为BC
	
		字符串的截取(源字符串,从第几位开始(起始为1),截取字符数)	
	c)字符串拼接 CONCAT
		select concat('ABC','D') from dual;		//结果为ABCD
		也可以用|| 对字符串进行拼接 
		select 'ABC'||'D' from dual;
	
	+ :号只能在数字中或日期中
	||:字符串拼接函数

1.2 数值函数

	函数 							说明
	ABS(value)					绝对值
	CEIL(value) 				大于或等于 value 的最小整数
	COS(value) 					余弦
	COSH(value) 				反余弦
	EXP(value) 					e 的value 次幂
	FLOOR(value) 				小于或等于 value 的最大整数
	LN(value) 					value 的自然对数
	LOG(value) 					value 的以 10 为底的对数
	MOD(value,divisor) 			求模
	POWER(value,exponent) 		value 的 exponent 次幂
	ROUND(value,precision) 		按 precision 精度 4舍5入
	SIGN(value)					value 为正返回 1;为负返回-1;为0返回 0.
	SIN(value) 					余弦
	SINH(value) 				反余弦
	SQRT(value) 				value 的平方根
	TAN(value) 					正切
	TANH(value)					反正切
	TRUNC(value,precision) 		按照 precision 截取 value
	VSIZE(value)				返回 value 在 ORACLE的存储空间大小

	a)四舍五入函数ROUND
		select round(100.567) from dual    //结果101
		select round(100.567,2) from dual  //结果100.57
	b)截取函数TRUNC
		select trunc(100.567) from dual    //结果100
		select trunc(100.567,2) from dual  //结果100.56
	c)取模 MOD(相当于java中的%)
		select mod(10,3) from dual         //结果1

1.3 日期函数

	函 数 								描 述
	ADD_MONTHS 						在日期 date 上增加 count个月
	GREATEST(date1,date2,. . .) 	从日期列表中选出最晚的日期
	LAST_DAY( date ) 				返回日期 date 所在月的最后一天
	LEAST( date1, date2, . . .)		从日期列表中选出最早的日期
	MONTHS_BETWEEN(date2,date1)		给出 Date2 - date1 的月数(可以是小数)
	NEXT_DAY( date,’day’) 			给出日期 date 之后下一天的日期，这里的day 为星期，如： MONDAY,Tuesday 等。
	NEW_TIME(date,’this’,’other’)   给出在 this 时区=Other时区的日期和时间
	ROUND(date,’format’) 			未指定 format 时，如果日期中的时间在中午之前，则
									将日期中的时间截断为 12 A.M.(午夜，一天的开始),否
									则进到第二天。时间截断为12 A.M.(午夜，一天的开始),
									否则进到第二天。
	TRUNC(date,’format’) 			未指定 format 时，将日期截为 12A.M.( 午夜，一天的开始).

	*我们用sysdate这个系统变量来获取当前日期和时间
	select sysdate from dual ;  //    2018/10/11 星期二 下午 10:43:46
	
	a)加月函数 ADD_MONTHS ：
		在当前日期基础上加指定的月
		select add_months(sysdate,2) from dual 
	b)求所在月最后一天 LAST_DAY
		select last_day(sysdate) from dual
	c)）日期截取TRUNC
		select TRUNC(sysdate) from dual 
		select TRUNC(sysdate,'yyyy') from dual    //2016/1/1 星期五

		select TRUNC(sysdate,'mm') from dual 		//按月截取(把日截掉)	,当月第一天

		select * last_day(sysdate-4) from dual;		//把日减到上个月的日子,就可以取到上个月的最后一天
				
	    select * trunc(sysdate,'mi') from dual; 		//按分钟截取,秒是0
		
	    select * next_day(sysdate,'星期一') from dual;	//求下个星期一

1.4 转换函数

	函 数 				描 述
	CHARTOROWID 	将 字符转换到 rowid类型
	CONVERT 		转换一个字符节到另外一个字符节
	HEXTORAW 		转换十六进制到 raw 类型
	RAWTOHEX 		转换 raw 到十六进制
	ROWIDTOCHAR 	转换 ROWID 到字符
	TO_CHAR 		转换日期格式到字符串
	TO_DATE 		按照指定的格式将字符串转换到日期型
	TO_MULTIBYTE 	把单字节字符转换到多字节
	TO_NUMBER 		将数字字串转换到数字
	TO_SINGLE_BYTE 	转换多字节到单字节

	**字符串在oracle数据库中位置靠左,数字位置靠右
	
	a)数字转字符串 TO_CHAR
		select TO_CHAR(1024) from dual    //1024

	b)日期转字符串 TO_CHAR
		select TO_CHAR(sysdate,'yyyy-mm-dd') from dual    //2016-10-11
 
		select TO_CHAR(sysdate,'yyyy-mm-dd hh:mi:ss') from dual   //2018-5-27 15:20:55
		
		select to_char(sysdate,'yyyy')||'年'||to_char(sysdate,'mm')||'月' from dual;	
		
		select '100'+0 from dual;		//字符串转数字
	
		select to_number('100')+10 from dual;	
		
	c)字符串转日期 TO_DATE
		select TO_DATE('2017-01-01','yyyy-mm-dd') from dual 查询结果如下：
		2017/01/01 星期天
	d)字符串转数字TO_NUMBER
		select to_number('100') from dual

1.5 其他函数

	a)空值处理函数 NVL
		NVL（检测的值，如果为null的值）；
			select NVL(NULL,0) from dual    //0
		
		显示价格表中业主类型ID为1的价格记录，如果MAXNUM为NULL,则显示9999999
			select PRICE,MINNUM,NVL(MAXNUM,9999999) from T_PRICETABLE where OWNERTYPEID=1
	
	b)空值处理函数 NVL2
		NVL2（检测的值，如果不为null的值，如果为null的值）；
		
		需求：显示价格表中业主类型ID 为1的价格记录，如果上限值为NULL,显示“不限”.(NVL2处理中文)
			select PRICE,MINNUM,NVL2(MAXNUM,to_char(MAXNUM) , '不限') from T_PRICETABLE where OWNERTYPEID=1
	
	c)条件取值 decode
		decode(条件,值 1,翻译值 1,值 2,翻译值 2,...值 n,翻译值 n,缺省值) 【功能】根据条件返回相应值
		
		需求：显示下列信息（不要关联查询业主类型表，直接判断 1 2 3 的值）
		eg1: select name,decode( ownertypeid,1,'居民',2,'行政事业单位 ',3,'商业') as 类型 from T_OWNERS
		
		eg2: select name ,(case
					 ownertypeid when 1 then '居民' 
					 when 2 then '行政事业单位' 
					 when 3 then '商业' else '其它'
					 end )from T_OWNERS
					 
		还有另外一种写法： //这种情况更灵活,when后面的条件可以继续附加,做更加复杂的查询
		eg3:select name,(case 
					when ownertypeid= 1 then '居民' 
					when ownertypeid= 2 then '行政事业' 
					when ownertypeid= 3 then '商业' 
					end )from T_OWNERS

二. 行列转换

	按季度统计2012年各个地区的水费
		select (select name from T_AREA where id= areaid ) 区域, 
				sum( case when month>='01' and month<='03' then money else 0 end) 第一季度, 
				sum( case when month>='04' and month<='06' then money else 0 end) 第二季度, 
				sum( case when month>='07' and month<='09' then money else 0 end) 第三季度, 
				sum( case when month>='10' and month<='12' then money else 0 end) 第四季度 
				from T_ACCOUNT where year='2012' group by areaid

三. 分析函数

	三个分析函数可以用于排名使用。
	
	1. RANK 相同的值排名相同，排名跳跃
		需求：对T_ACCOUNT表的usenum字段进行排序，相同的值排名相同，排名跳跃
		语句： select rank() over(order by usenum desc ),usenum from T_ACCOUNT 
	2. DENSE_RANK 相同的值排名相同，排名连续
		需求：对T_ACCOUNT表的usenum字段进行排序，相同的值排名相同，排名连续
		语句： select dense_rank() over(order by usenum desc ),usenum from T_ACCOUNT 

	3. ROW_NUMBER 返回连续的排名，无论值是否相等
		需求：对T_ACCOUNT表的usenum字段进行排序，返回连续的排名，无论值是否相等
		语句： select row_number() over(order by usenum desc ),usenum from T_ACCOUNT

		用row_number()实现的分页查询
		select * from (select row_number() over(order by usenum desc ) rownumber,usenum 
		from T_ACCOUNT) where rownumber>10 and rownumber<=20 

四. 集合运算

	**集合运算时:列数和类型要相对应,不然会报错
	
		集合运算，集合运算就是将两个或者多个结果集组合成为一个结果集。集合运算
	包括：
	··UNIONALL(并集)，	返回各个查询的所有记录，包括重复记录。
	··UNION(并集)，		返回各个查询的所有记录，不包括重复记录。
	··INTERSECT(交集)， 返回两个查询共有的记录。
	··MINUS(差集)，		返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录。
		
	a)并集运算
		UNIONALL 不去掉重复记录 
			select * from t_owners where id<=7 union all select * from t_owners where id>=5
		
		UNION 去掉重复记录
			select * from t_owners where id<=7 union select * from t_owners where id>=5
	b)交集运算
		select * from t_owners where id<=7 intersect select * from t_owners where id>=5
	
	c)差集运算
		select * from t_owners where id<=7 minus select * from t_owners where id>=5 

	eg:用minus运算符来实现分页，
		select rownum,t.* from T_ACCOUNT t where rownum<=20 
		minus 
		select rownum,t.* from T_ACCOUNT t where rownum<=10

五. 案例

	eg:统计某年收费情况，按月份分组汇总，
		select to_char(feedate,'mm') 月份,sum(money) 金额,sum(usenum) 用水量 from t_account
		where year='2012' group by to_char(feedate,'mm') order by to_char(feedate,'mm');