OCP-单行函数

函数

• 单行函数

什么是单行函数？

针对每一行返回一个结果。于是我们称这个函数为单行函数.

• 多行函数

什么是多行函数？

针对每个perset(集合)只返回一个结果

嵌套函数不是函数，而是函数的一种使用方法。

单行函数可以无限制的嵌套

多行函数最多只能嵌套2层

单行函数

字符处理函数

• lower：小写字母处理函数

  select lower(ename) as name from emp;
  

• lpper：大写字母处理函数

  select upper(lower(ename)) as name from emp;
  

• initcap：首字母大写处理函数，首字母大写其他小写

  select initcap(upper(lower(ename))) as name from emp;
  

• concat：拼接字符串函数，单次最多拼接两个字符串

  select concat(concat('a','b'),'c') from dual;
  

• substr：截取字符串函数 substr(exp1,exp2,exp2) exp1字段 exp2从第几位开始 exp3截取几位

  select ename,substr(hiredate,1,10) from emp;
  

• length：获取字符串长度

  select length(hiredate) from emp;
  

• instr：返回某个字符在指定字符串中的绝对位置，返回的是一个数字

  select instr('abcde','c') from dual;	
  select instr('abcde','c',2) from dual; 	#查到的前提下，返回的是绝对位置。c的位置>3的时候会返回0
  select instr('abcdeabcde','c',1,1) from dual;#从第一位开始查查第一次出现的
  select instr('abcdeabcde','c',4,1) from dual;
  select instr('abcdeabcde','c',4,2) from dual;
  

• lpad：left左填充

  select lpad(ename,'*',10) from emp;		#在左边以*填充填充到10个字符
  

• rpad：right右填充，同上

• trim：去除空格（ltrim rtrim），能不能去除中间的空格？

  select ltrim('   ab c   ') from dual;		#前面3个空格，后面3个空格
  select trim('   ab c'   '') from dual;		#证明trim不可以去除中间的空格
  

• replace：替换字符

  select replace('abc','c','b')from dual;		#将c替换成b
  

数字函数

• round：四舍五入函数

select round(21.345) from dual;	/21		#未指定保留，缺省保留到个位
select round(21.545) from dual;	/22	
select round(21.345,2) from dual;		#exp2表示保留2位小数
select round(21.345,-1) from dual;/20	#负数表示小数点向前移1位，判断1<4还是>5
select round(26.345,-1) from dual;/10

• trunc：截断数字

select trunc(21.567) from dual;/21		#不采用四舍五入，直接截断
select trunc(21.567,2) from dual;/21.56
select trunc(21,-1) from dual;/20
select trunc(25,-1) from dual;/20
OCP考题
select trunc(round(156.00,-1),-1) from dual;/160

• mod：取余函数 10/3=3余1

  select mod(10,3) from dual;/1
  select mod(100,12) from dual;/4
  

日期函数

Oracle数据库默认的日期格式：DD-MON-RR/天-月-年

show parameter nls_										#查看数据库的默认参数
nls_date_format 		     string	 yyyy-mm-dd HH24:MI:SS
alter session set nls_date_format='DD-MON-RR';			#设置默认日期格式
#修改参数两个级别，一个是session会话级别，一个是system级别
#session	退出会话失效
#system	永久

• months_between：核算指定的两个日期相隔月份

  select months_between(sysdate,'2020-07-01') from dual;
  

• add_months：添加月份到指定的日期

  select add_months(sysdate,1) from dual;
  

• next_day：指定到下一周的某个日期

  select next_day(sysdate,'sunday') from dual;		#计算下一个周日的时间
  NEXT_DAY(SYSDATE,'S
  -------------------
  2021-08-08 14:41:38
  

• last_day：当月最后一天的日期

  select last_day(sysdate) from dual;
  select trunc(sysdate, 'mm') from dual;			#查看本月的第一天
  

日期函数与数字函数的搭配

模型规则：round trunc mod

• 如果格式模型为月份，日期是1-15号，结果返回当前月的第一天。
• 如果格式模型为月份，日期是16-31号，结果返回下个月的第一天。
• 如果格式模型为年份，月份是1-6月，结果返回当前年的第一个月。
• 如果格式模型为年份，月份是7-12月，结果返回下一年的第一个月。

注意：round完全遵循上面规则，但是trunc依然遵循截断规则。

例子：

select round(sysdate,'month') from dual;/20210801
select round(sysdate,'year') from dual;/20220101
select trunc(sysdate,'month') from dual;/20210801
select trunc(sysdate,'year') from dual;/20210101

转换函数

• 隐式转换

处理hiredate日期型，使用了一个字符处理函数length

select length(sysdate) from dual;		#由Oracle服务器隐式转换成字符型 日期 -> 字符
select * from emp where deptno='20';	#字符->数值
select 2||3 from dual;					#将数值隐式转换成字符 数值->字符
select '66.6'+22 from dual;				#字符->数字
select 10+('13.5'||11) from dual;

• 显示转换

• to_number

  select to_number('$123.45','$999.999.999.99') from dual;
  

• to_char

  select to_char(hiredate,'yyyymmdd') from emp;
  select ename,to_char(sal,'$999,999,99') from emp;
  

• to_date

  select to_date('20210805','yyyy-mm-dd') from dual;
  

通用函数

• NVL：处理控制null nvl（exp1，exp2）如果exp1为null，则返回exp2，否则返回exp1

  select nvl(exp1,exp2) from dual;
  select nvl(null,1) from dual;
  select ename,sal,nvl(comm,0)+1000 from emp;
  

• NVL2：nvl2(exp1,exp2,exp3)如果exp1为空null，则返回exp3，否则返回exp2

  • 不管是nvl还是nvl2，只要第一个表达式为空，都返回最后一个表达式

  select nvl2(null,4,7) from dual;/7
  select nvl2(1908,4,7) from dual;/4
  

• NULLIF：nullif(exp1,exp2)判断两个表达式是否相等，如果相等，则返回exp1

  select nullif(5,6) from dual;
  select nullif(2,6) from dual;
  

• COALESCE：coalesc(exp1,exp2,exp3,exp4...expn)返回第一个不为空的表达式

  select coalesce(null,2,null,3,4,null,5,6) from dual;/2
  

条件表达式

如果怎样怎样，那么就怎样怎样

Oracle里面有一个专有的函数(条件表达式)

Decode

• 需求1：将所有员工工资每人加上1500元

  select ename,sal,nvl(sal,0)+1500 as newsal from emp;
  

• 需求2：如果是scott，加1500，如果是king，扣800；其他人保持不变

  select ename,sal,decode(ename,'SCOTT',sal+1500,'KING',sal-800,sal) from emp;
  

case表达式

case语法是基于ANSI的SQL委员会制定的标准。

语法：case when then else end

• 第1种用法：

  select ename,sal,case ename when 'SCOTT' then sal+1500 
  							when 'KING'  then sal-800
  							else sal end as newsal
  							from emp;
  

• 第2种用法：

  需求：如果scott，且工资小于2000，那么加1500；如果king，且工资大于3000，那么扣800，其他人保持不变。

  select ename,sal,case when ename='SCOTT' and sal<2000 then sal+1500
  					  when ename='KING'  and sal>3000 then sal-800
  					  else sal end as newsal
  					  from emp;