oracle function

函数

• 单行函数：一个输入一个输出
• 多行函数：多个输入一个输出
• lower(a):a转小写
• upper(a):a转大写
• substr(a,b):在a中，取从b位开始到结尾的子串
• initcap():首字母大写
• length()：字符长度
• lengthb():字节长度
• instr(a,b):在a中查找b，找到返回从1开始的下标，找不到返回0
• lpad('abcd', 10, ''): 在abcd的左填充 ,一共10位，即****abcd,rpad()为右填充
• trim():去掉前后指定的字符，select trim('H' from 'Hello WorldH') from dual; -- ello World
• replace('Hello', '', 'l'):用替换‘Hello’中的‘l’
• round(45.926, 2): 45.93,四舍五入
• round(45.926, 0): 46
• round(45.926, -1): 50
• round(45.926, -2): 0
• trunc(45.926, 2): 45.92, 舍去
• mod(1600, 300): 100,取余

日期

• 系统时间：oracle: sysdate, mysql: now()函数
• select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;
• select ename, hiredate, (sysdate - hiredate) 天, (sysdate-hiredate)/7 星期, (sysdate-hiredate)/30 月 from daul; -- 计算员工工龄
• 日期 +/- 一个数字 => +/- 天数，结果仍为日期
• 两个日期相减返回两者之间相差的天数，日期之间相加无意义
• 可用数字除以24，来向日期 +/- 小时数
• months_between(sysdate, hiredate): 相差月数
• add_months(): 向指定日期加上若干个月数
• next_day(): 指定日期的下一个日期
• last_day(sysdate): 本月的最后一天
• round(sysdate, 'month'):
• round(sysdate, 'year'):
• trunc(sysdate, 'year')
• select next_day(sysdate, '星期二') from dual; -- 下个星期二的日期

显式类型转换

		 to_number()  <<<                >>>  to_date()  
number <=================>  character  <=================> date
		   to_char()  >>>				 <<<  to_char()

• select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss "今天是" day ') from dual;
• select to_char(sal, 'L9,999.99') from dual; -- ￥4.800.00， L本地货币单位 ‘，’表千位分隔符， 9表一位数字， ‘.’表小数点

通用函数

• nvl(a, b): a为null,返回b,否则返回a
• nvl2(a, b, c): a为null，返回c,否则返回b
• select sal*12 + nvl2(comm,comm,0) from emp;
• nullif(a,b): 当a=b,返回null,否则返回a
• coalesce(expr1, ... ): 从左到右找到第一个不为null的值
• select comm,sal, coalesce(comm, sal) from emp;

涨工资

	* select ename,job,sal 涨前,
		case job when 'PRESIDENT' then sal + 1000
				 when 'MANAGER' then sal + 800
				 else sal + 400
		end 涨后
	  from emp;
	* select ename, job, sal 涨前，
		decode(job, 'PRESIDENT', sal + 1000, 
					'MANAGER', sal + 800, 
								sal + 400) 涨后
	  from emp;

多行函数/组函数

• avg(),count(),max(),min(),sum()
• 组函数自动滤空，可以嵌套滤空函数来屏蔽自动滤空
• group by子句：在select列表中所有未包含在组函数中的列都应该出现在group by子句中
• group by deptno, job: 先按第一列分组，第一列相同在按第二列分组
• select deptno, count(ename) from emp; 错误的，count为组函数，deptno不在group by子句中，即缺少group by子句
• 不能在where子句中使用组函数，可以在having子句中使用组函数，其他情况可通用，但where比having效率高
• group by rollup(a,b) <=> group by a,b + group by a + group by null.
• break on deptno skip 2 --相同部门号只打印一次，且不同之间跳过2行
• break on null --取消以上格式
• select deptno,job,sum(sal) from emp group rollup(deptno,job) -- 做报表
• wm_concat(varchar2) -- 行转列组函数
• select deptno,wm_concat(ename) namelist from emp group by deptno;