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

清疚 2022-05-11 07:26 335阅读 0赞

## 单行函数： ##

输入一行，输出一行

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