oracle 11g(十)：SQL 函数（oracle的内置函数）单行函数

单行函数：

输入一行，输出一行

1.字符函数：

• upper(char),改大写
• lower(char),
• initcap(char):字符串中每个单词首字母大写

练习：

select * from emp where job=upper('salesman');

select empno,initcap(ename) from emp;

select empno ,initcap(null) from emp;

----->三个函数的参数为空时，查询返回值无论原来是什么，最终返回值也为null

2.字符控制函数

• concat(str1，str2)：连接
• substr(char,m[,n]),截取字符串，m:起始位置（>=0），n：截取长度
• length(char):长度，后缀有空格也算
• instr(char2,char1[,n[,m]]]):char1在char2中起始位置n（默认1）后第m次出现的位置。
• lpad(char1,n,char2):用char2字符串在char1左端填充，使填充后的字符串长n。
• RPAD(char1,n,char2):用char2字符串在char1右端填充，使填充后的字符串长n。
• replace(char,search_string[,replace_str]):把char里的字符串search_string用 replace_str替换

--练习：
SELECT ename || ':' ||sal from emp;

select concat(concat(ename,':'),sal) from emp;

select * from emp where substr(job,1,4)='SALE';

SELECT *FROM emp where length(ename)=6;

--instr
--dual 虚拟表
select instr('hello oracle' ,'oracle') from dual;

--从第五个字符起，oracle第二次出现的位置
select instr('hello oracle hello oracle hello oracle' ,'oracle',5,2) from dual;

--Lpad:job字段不足9位的左填充*,直到为9位
select lpad(job,9,'*') from emp;
select rpad(job,9,'*') from emp;

select replace('hello oracle','oracle','world') from dual;
--hello world

3.数值函数：

• Round(n[,m]):对n进行四舍五入到小数点后m位，没有m，保留到整数位。M<0,，四舍五入到小数点前的m位。
• Trunc(n[,m]):对n截取值小数点后m位，没有m，截取到整数位。M<0,，截取到小数点前的m位。
• Mod(m,n),m%n（n=0,结果为m）

  --练习：
  select round(sal/30,2),trunc(sal/30,2) from emp;--日工资保留小数点后两位；
  
  select empno from emp where mod(empno,2)=0;

  4.日期函数

• sysdate:系统日期

• months _between(d1,d2):返回d1和d2之间相差的月数

• add_months(d,n):求出d后n个月的日期

• last_day(d):用于返回d对应月份的最后一天的日期

• round(d[,fmt]):返回日期四舍五入的结果，fmt，四舍五入的方式：’YEAR’（七月1日分界），’MONTH’（16日）

• TRUNC(d[,fmt]):截断日期值。fmt=’YEAR’，则为本年1月1日，fmt=’MONTH’，则为本月1日

• next_day(d,char)：d以后第一个工作日的日期，char用于指定工作日。如果日期语言是American则，char对应Monday

  如果日期语言简体中文则，char对应’星期一’。

  select sysdate-1 昨天, sysdate 今天, sysdate 明天 from dual;
  
  select round(MONTHS_BETWEEN(sysdate,hiredate)/12) from emp;
  
  select ename, add_months(hiredate,30*12) from emp;
  
  select sysdate,last_day(sysdate) from dual;
  
  select empno,ename,last_day(hiredate) from emp;--每位员工入职月的最后一天
  
  
  --查询每月最后一天入职的员工有哪些？
  
  select empno,ename,hiredate from emp where hiredate=last_day(hiredate);
  
  select hiredate,round(hiredate,'YEAR'),round(hiredate,'MONTH') FROM
  
  emp where empno=7654;
  
  --28-9月 -81 01-1月 -82 01-10月-81
  
  
  
  select hiredate,trunc(hiredate,'YEAR'),trunc(hiredate,'MONTH') FROM
  
  emp where empno=7654;
  
  --28-9月 -81 01-1月 -81 01-9月 -81
  
  
  select sysdate,next_day(sysdate,'星期一') from dual;
  
  

   

5.转换函数：一种数据类型到另一种数据类型

1）日期--->字符  

to_char(d[,fmt[,’nslparams’]]):d:要显示的日期；fmt：格式；nslparams用于指定日期显示语言（‘NSL_DATE_LANGUAGE=language’）

默认日期显示格式：DD_MON_RR

当使用自定义的格式显示日期时，必须用双引号引住字符值

练习：

select to_char(hiredate,'YYYY-MM-DD') from emp;
--1980-12--17

select to_char(hiredate,'YYYY"年"MM"月"DD"日"') from emp;
--1980年12月17日

select to_char(hiredate,'DD-MON-RR','NLS_DATE_LANGUAGE=AMERICAN') from emp;
--APR-87

to_char(sysdate,’day’)返回当前是星期几。将当前日期，转为day的格式输出为字符

2）字符--->日期

to_date(char,[,fmt[,’nlsparams’]])

Char：日期；fmt:char对应的格式；nlsparams:指定日期语言

select ename,hiredate from emp

where hiredate>to_date('1981-12-21','YYYY-MM-DD');

--SCOTT 19-4月 -87

 

3）字符-->数值

 to_number(n[,fmt])

n：包含数字的字符串，fmt：n的格式，把n，格式为fmt转化为数字

select ename ,sal from emp

where sal>to_number('￥2000','L99999');

--JONES 2975  

3）数值--->字符  

to_char(n[,fmt]):n数字，fmt格式,把n转成fmt形式的字符

格式模式，fmt值：

      9：显示数字忽略前导0

     0：显示数字，位数不足用0补齐

      .:指定位置显示小数点

  ，：指定位置显示逗号

       $:指定位置显示$

        L：数字前加本地货币符号

select sal,to_char(sal,'L0,000,000.00') from emp;

        -- ￥0,000,800.00
select sal,to_char(sal,'L9,999,999.99') from emp;

              -- ￥800.00  $800.00
select sal,to_char(sal,'$9,999,999.99') from emp;

6.通用函数

• 处理NULL：

NVL(expr1,expr2):expr1空返回expr2，否则，返回expr1;

select ename,sal,comm,sal+nvl(comm,0) from emp;

--SMITH 800 null     800

• NVL2(expr1,expr2,expr3):expr1不空返回expr2，否则，返回expr3;

select ename,sal,comm,sal+nvl2(comm,comm+sal,sal) from emp;

--SMITH 800 NULL       800

• NULLIF(expr1,expr2):expr1=expr2返回null,否则返回expr1

select empno,ename,hiredate,nullif(hiredate,trunc(sysdate,'MONTH')) FROM emp;

--CLARK 09-6月 -81 09-6月 -8

• COALESCE(EXPR1[,EXPR2][……])：返回参数列表的第一个not null的结果

select ename,sal,comm,COALESCE(sal+comm,sal,comm) from emp;

--SMITH 800  null   800

7.条件表达式:if then else

• case表达式：

select ename,case job

when 'CLERK' then '办事员'

when 'SALESMAN' then '销售'

when 'MANAGER' THEN '经理'

when 'ANALIST' THEN '分析员'

else '总裁'

end

from

emp;

--SMITH 办事员

 

• Decode函数：

Decode(col|expr, search1,result1[,search2,result2][,……][,default])

类似c的switch case

select ename,job,decode(job,'CLERK','办事员',

'SALESMAN','销售员','MANAGER','经历','ANALIST','分析员','总裁')

from emp;

--SMITH CLERK 办事员

区间判断case比decode好用

select empno,ename,sal,

case when sal<2000 then 'low'

when sal<5000 then '中'

else '高'

end

from emp;

--SMITH 800 low

8.嵌套函数（综合运用）：由内到外

--聘用日期三个月后的下一个星期一的日期，显示格式2009-08-23

select ename,hiredate,

to_char(next_day(add_months(hiredate,3),'星期一'),'YYYY-MM-DD')

from emp;

--SMITH 17-12月-80 1981-03-23

 

--雇员日薪四舍五入到小数点后两位，格式’￥1，182.19‘

select ename,sal,
to_char(round(sal/30,2),'L9,999.99')
from  emp;

--SMITH 800             ￥26.67