Oracle SQL语句学习（4）--转换函数和条件表达式

原创张均云和恩墨 2022-09-26

632

一. 概述

上一篇文章里，了解了oracle中单行函数的使用，能让我们更加高效方便地管理数据库。这篇文章，主要介绍了数据类型转换函数、其他通用函数以及条件表达式【IF…THEN…】的使用.

二. 转换函数

转换函数指的时【数据类型】转换函数，可以分为隐式转换和显示转换。

2.1 隐式转换

隐式转换可以理解为oracle服务器会对一些数据类型进行自动的判断并转换，支持的转换有：

FROM	TO
VARCHAR2 OR CHAR	NUMBER
VARCHAR2 OR CHAR	DATE
NUMBER	VARCHAR2 OR CHAR
DATE	VARCAHR2 OR CHAR

例如，我们在查询含有数字类型的列时，如果我们将数字当作字符串来查询（加上’ '），oracle服务器会自动将其转换为number类型进行查询

# EMPNO列为number类型
SCOTT@oradb> DESC EMP
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 EMPNO                                                             NOT NULL NUMBER(4)
# 当作字符串进行查询
SCOTT@oradb> SELECT EMPNO FROM EMP WHERE EMPNO='7369';

     EMPNO
----------
      7369
复制

隐式转换对插入同样有效，我们对DEPARTMENT30表进行插入操作

# 四个列的数据类型
SCOTT@oradb> DESC DEPARTMENT30;
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 ID                                                                         NUMBER(4)
 ENAME                                                                      VARCHAR2(10)
 ANASAL                                                                     NUMBER
 HIREDATE                                                                   DATE
# 对第一列插入字符串，第二列插入数字
SCOTT@oradb> INSERT INTO DEPARTMENT30 VALUES('1111',1111,1111,'03-DEC-22');

1 row created.

SCOTT@oradb> SELECT * FROM DEPARTMENT30;

        ID ENAME          ANASAL HIREDATE
---------- ---------- ---------- ---------
      7499 ALLEN           19200 20-FEB-81
      7521 WARD            15000 22-FEB-81
      7654 MARTIN          15000 28-SEP-81
      7698 BLAKE           34200 01-MAY-81
      7844 TURNER          18000 08-SEP-81
      7900 JAMES           11400 03-DEC-81
      1111 1111             1111 03-DEC-22
# 查看结果，隐式转换成功
复制

2.2 显示转换

顾名思义，显示转换就需要利用一些数据类型转换函数，去改变数据类型。

2.2.1 TO_CHAR()

TO_CHAR对日期的转换
格式：TO_CHAR(DATE,‘FORMAT_MODEL’)

-日期格式必须包含在单引号中
-大小写敏感
-可以包含任意有效的日期格式
-可以使用fm去掉多余的空格或前导0
-与日期用逗号隔开

示例1：

SCOTT@oradb> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss am') from dual;

TO_CHAR(SYSDATE,'YYYY-
----------------------
2022-09-11 15:13:16 pm
复制

示例2：日期格式中添加字符串

SCOTT@oradb> select to_char(sysdate,'Day,"the" ddth "of" Month,yyyy') from dual;

TO_CHAR(SYSDATE,'DAY,"THE"DDTH"OF"MONTH,YYYY
--------------------------------------------
Sunday   ,the 11th of September,2022
复制

示例3：FM的使用让结果显示紧凑

SCOTT@oradb> select to_char(sysdate,'FMDay,"the" ddth "of" Month,yyyy') from dual;

TO_CHAR(SYSDATE,'FMDAY,"THE"DDTH"OF"MONTH,YY
--------------------------------------------
Sunday,the 11th of September,2022
复制

日期格式掩码 —— 日期格式中元素的含义

TO_CHAR对数字的转换
格式： TO_CHAR(NUMBER,‘FORMAT_MODEL’)

其中format_model中的元素常用的有：

示例：

SCOTT@oradb> select sal,to_char(sal,'$99,999.00') from emp;

       SAL TO_CHAR(SAL
---------- -----------
       800     $800.00
      1600   $1,600.00
      1250   $1,250.00
      2975   $2,975.00
      1250   $1,250.00
      2850   $2,850.00
      2450   $2,450.00
      3000   $3,000.00
      5000   $5,000.00
      1500   $1,500.00
      1100   $1,100.00
       950     $950.00
      3000   $3,000.00
      1300   $1,300.00
# 注意如果格式中的位数不够，会显示乱码###。下面的格式为显示小数点前三位，那么原工资为四位数的就无法正常显示
SCOTT@oradb> select sal,to_char(sal,'$99,9.00') from emp;

       SAL TO_CHAR(S
---------- ---------
       800  $80,0.00
      1600 #########
      1250 #########
      2975 #########
      1250 #########
      2850 #########
      2450 #########
      3000 #########
      5000 #########
      1500 #########
      1100 #########
       950  $95,0.00
      3000 #########
      1300 #########
复制

扩展：D可以代替.
G可以代替，
D和G必须成对使用：同一个格式中使用了D代表.那么,必须由G代替

示例：不成对使用D和G

SCOTT@oradb> select sal,to_char(sal,'$99,999.00'),to_char(sal,'$99G999.99') fromemp;
select sal,to_char(sal,'$99,999.00'),to_char(sal,'$99G999.99') fromemp
                                                                     *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
复制

2.2.2 TO_NUMBER()

格式： TO_NUMBER(CHAR[,FORMAT_MODEL])

[]是可选的，使用其是为了表述字符串char的精准格式，当字符串中包含特殊字符的时候，必须使用FORMAT_MODEL来精准匹配字符串的格式，否则会报错

示例1：基础使用

SCOTT@oradb> select to_number('1234500') from dual;

TO_NUMBER('1234500')
--------------------
             1234500
复制

示例2：当字符串中含有特殊字符时

# 不启用format_model会报错，无法转换
SCOTT@oradb> select to_number('$12,345.00') from dual;
select to_number('$12,345.00') from dual
                 *
ERROR at line 1:
ORA-01722: invalid number
# 启用format_model并精准匹配字符串的格式
SCOTT@oradb> select to_number('$12,345.00','$99999.99') from dual;

TO_NUMBER('$12,345.00','$99999.99')
-----------------------------------
                              12345
复制

2.2.3 TO_DATE()

格式：TO_DATE(CHAR[,FORMAT_MODEL])

[]是可选的，使用其是为了表述字符串char的精准格式，当字符串中日期格式与表中的日期格式不匹配的时候，必须使用FORMAT_MODEL来精准匹配字符串的格式，否则会报错。可以理解为将字符串char转换为系统标准时间，format_MODEL必须要和char精准匹配。当格式本身与系统格式匹配，则不需要format_model的值。

SCOTT@oradb> select * from emp where hiredate>='01-MAY-87';

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

SCOTT@oradb> select * from emp where hiredate>='1987-05-01';
select * from emp where hiredate>='1987-05-01'
                                  *
ERROR at line 1:
ORA-01861: literal does not match format string


SCOTT@oradb> select * from emp where hiredate>=to_date('1987-05-01','yyyy-mm-dd');

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
复制

三. 通用函数

3.1 NVL()

NVL()函数的作用是将空值转换成一个已知的值

-可以使用的数据类型有数字、日期、字符。
-数据类型必须匹配：
– NVL(COMM,0) 数字型
– NVL(HIREDATE,;01-JAN-97’) 日期型
– NVL(TO_CHAR(MGR),‘NO MANAGER’) 使用字符型需要将不是字符型的那一列转换成字符型

示例：

SCOTT@oradb> select ename,sal*12+nvl(comm,0) from emp;

ENAME      SAL*12+NVL(COMM,0)
---------- ------------------
SMITH                    9600
ALLEN                   19500
WARD                    15500
JONES                   35700
MARTIN                  16400
BLAKE                   34200
CLARK                   29400
SCOTT                   36000
KING                    60000
TURNER                  18000
ADAMS                   13200
JAMES                   11400
FORD                    36000
MILLER                  15600

SCOTT@oradb> select ename,mgr,nvl(to_char(mgr),'No Manager') from emp;

ENAME             MGR NVL(TO_CHAR(MGR),'NOMANAGER')
---------- ---------- ----------------------------------------
SMITH            7902 7902
ALLEN            7698 7698
WARD             7698 7698
JONES            7839 7839
MARTIN           7698 7698
BLAKE            7839 7839
CLARK            7839 7839
SCOTT            7566 7566
KING                  No Manager
TURNER           7698 7698
ADAMS            7788 7788
JAMES            7698 7698
FORD             7566 7566
MILLER           7782 7782

SCOTT@oradb> SELECT HIREDATE,NVL(HIREDATE,'17-DEC-80') FROM EMP;

HIREDATE  NVL(HIRED
--------- ---------
17-DEC-80 17-DEC-80
20-FEB-81 20-FEB-81
22-FEB-81 22-FEB-81
02-APR-81 02-APR-81
28-SEP-81 28-SEP-81
01-MAY-81 01-MAY-81
09-JUN-81 09-JUN-81
19-APR-87 19-APR-87
17-NOV-81 17-NOV-81
08-SEP-81 08-SEP-81
23-MAY-87 23-MAY-87
03-DEC-81 03-DEC-81
03-DEC-81 03-DEC-81
23-JAN-82 23-JAN-82
复制

3.2 NVL2()

语法：NVL(EXPR1,EXPR2,EXPR3)

如果参数1非空not null，则返回参数二的值，否则返回参数3的值。

SCOTT@oradb> select ename,sal,comm,sal+nvl(comm,0),nvl2(comm,sal+comm,sal) from emp;

ENAME             SAL       COMM SAL+NVL(COMM,0) NVL2(COMM,SAL+COMM,SAL)
---------- ---------- ---------- --------------- -----------------------
SMITH             800                        800                     800
ALLEN            1600        300            1900                    1900
WARD             1250        500            1750                    1750
JONES            2975                       2975                    2975
MARTIN           1250       1400            2650                    2650
BLAKE            2850                       2850                    2850
CLARK            2450                       2450                    2450
SCOTT            3000                       3000                    3000
KING             5000                       5000                    5000
TURNER           1500          0            1500                    1500
ADAMS            1100                       1100                    1100
JAMES             950                        950                     950
FORD             3000                       3000                    3000
MILLER           1300                       1300                    1300
复制

3.3 NULLIF()

语法： NULLIF(EXPR1,EXPR2)

比较两个表达式是否相同，如果相同返回null，如果不同返回expr1

SCOTT@oradb> select ename,job,length(ename) n1,length(job) n2,nullif(length(ename),length(job)) from emp;

ENAME      JOB               N1         N2 NULLIF(LENGTH(ENAME),LENGTH(JOB))
---------- --------- ---------- ---------- ---------------------------------
SMITH      CLERK              5          5
ALLEN      SALESMAN           5          8                                 5
WARD       SALESMAN           4          8                                 4
JONES      MANAGER            5          7                                 5
MARTIN     SALESMAN           6          8                                 6
BLAKE      MANAGER            5          7                                 5
CLARK      MANAGER            5          7                                 5
SCOTT      ANALYST            5          7                                 5
KING       PRESIDENT          4          9                                 4
TURNER     SALESMAN           6          8                                 6
ADAMS      CLERK              5          5
JAMES      CLERK              5          5
FORD       ANALYST            4          7                                 4
MILLER     CLERK              6          5                                 6
复制

四. 条件表达式

条件表达式的作用可以简单理解为当达到某种条件的时候，执行某一操作，不满足某条件的时候，执行另外一个操作。

在SQL语句中使用【IF-THEN-ELSE】逻辑
可以使用两两种方法实现：
-CASE表达式
-DECODE函数

4.1 DECODE()

语法： DECODE(COL|EXPRESSION,SEARCH1.RESULT1[,SEARCH2,RESULT2,…][,DEFAULT])

以上语法可以理解为：判断col/expression的值，如果其等于search1，返回result1的结果，如果其等于search2，返回result2，如果其等于…,其他情况为default值

示例：需求：工作是 ANALYST 的，工资涨 10%；
——————工作是 CLERK 的，工资涨 15%；
——————工作是 MANAGER 的，工资涨 20%；
——————其他的涨 25%。

SCOTT@oradb>select empno,ename,job,sal,
2 decode(job,'ANALYST', sal*1.1,
3            'CLERK', sal*1.15,
4            'MANAGER', sal*1.20,
5             sal*1.25)
6 new_sal
7 from emp order by job;

     EMPNO ENAME      JOB              SAL    NEW_SAL
---------- ---------- --------- ---------- ----------
      7788 SCOTT      ANALYST         3000       3300
      7902 FORD       ANALYST         3000       3300
      7934 MILLER     CLERK           1300       1495
      7900 JAMES      CLERK            950     1092.5
      7369 SMITH      CLERK            800        920
      7876 ADAMS      CLERK           1100       1265
      7698 BLAKE      MANAGER         2850       3420
      7566 JONES      MANAGER         2975       3570
      7782 CLARK      MANAGER         2450       2940
      7839 KING       PRESIDENT       5000       6250
      7844 TURNER     SALESMAN        1500       1875
      7654 MARTIN     SALESMAN        1250     1562.5
      7521 WARD       SALESMAN        1250     1562.5
      7499 ALLEN      SALESMAN        1600       2000
复制

DECODE可以处理空值以实现NVL函数的作用

SCOTT@oradb> select decode(comm,null,0,comm),nvl(comm,0) from emp;

DECODE(COMM,NULL,0,COMM) NVL(COMM,0)
------------------------ -----------
                       0           0
                     300         300
                     500         500
                       0           0
                    1400        1400
                       0           0
                       0           0
                       0           0
                       0           0
                       0           0
                       0           0
                       0           0
                       0           0
                       0           0
复制

4.2 CASE表达式

语法：

CASE EXPR WHEN COMPARISON_EXPR1 THEN RETURN_EXPR1
         [WHEN COMPARISON_EXPR2 THEN RETURN_EXPR2
 	 [WHEN COMPARISON_EXPR3 THEN RETURN_EXPR3
          ......
         ELSE ELSE_EXPR]
END
复制

SCOTT@oradb>select empno,ename,sal,job,
2 case job when 'ANALYST' then sal*1.1
3 when 'CLERK' then sal*1.15
4 when 'MANAGER' then sal*1.2
5 else sal*1.25
6 end new_sal
7 from emp order by job;

     EMPNO ENAME             SAL JOB          NEW_SAL
---------- ---------- ---------- --------- ----------
      7788 SCOTT            3000 ANALYST         3300
      7902 FORD             3000 ANALYST         3300
      7934 MILLER           1300 CLERK           1495
      7900 JAMES             950 CLERK         1092.5
      7369 SMITH             800 CLERK            920
      7876 ADAMS            1100 CLERK           1265
      7698 BLAKE            2850 MANAGER         3420
      7566 JONES            2975 MANAGER         3570
      7782 CLARK            2450 MANAGER         2940
      7839 KING             5000 PRESIDENT       6250
      7844 TURNER           1500 SALESMAN        1875
      7654 MARTIN           1250 SALESMAN      1562.5
      7521 WARD             1250 SALESMAN      1562.5
      7499 ALLEN            1600 SALESMAN        2000
复制

CASE的第二种形式：
就是将[case expr when]换成了[case when expr=]

SCOTT@oradb>select empno,ename,sal,job,
2 case when job='ANALYST' then sal*1.1
3 when job='CLERK' then sal*1.15
4 when job='MANAGER' then sal*1.2
5 else sal*1.25
6 end new_sal
7 from emp order by job;

     EMPNO ENAME             SAL JOB          NEW_SAL
---------- ---------- ---------- --------- ----------
      7788 SCOTT            3000 ANALYST         3300
      7902 FORD             3000 ANALYST         3300
      7934 MILLER           1300 CLERK           1495
      7900 JAMES             950 CLERK         1092.5
      7369 SMITH             800 CLERK            920
      7876 ADAMS            1100 CLERK           1265
      7698 BLAKE            2850 MANAGER         3420
      7566 JONES            2975 MANAGER         3570
      7782 CLARK            2450 MANAGER         2940
      7839 KING             5000 PRESIDENT       6250
      7844 TURNER           1500 SALESMAN        1875
      7654 MARTIN           1250 SALESMAN      1562.5
      7521 WARD             1250 SALESMAN      1562.5
      7499 ALLEN            1600 SALESMAN        2000
复制

CASE也可以处理空值
注意，对于空值的处理，case只能使用第二种形式

SCOTT@oradb> select comm,case when comm is null then 0 else comm end COMM from emp;

      COMM       COMM
---------- ----------
                    0
       300        300
       500        500
                    0
      1400       1400
                    0
                    0
                    0
                    0
         0          0
                    0
                    0
                    0
                    0
复制

五. 总结

六. 练习

6.1 写一个查询，选择出当前的日期。

SCOTT@oradb> select sysdate from dual;

SYSDATE
---------
11-SEP-22
复制

6.2 查询出雇员表（emp)表中的员工编号、姓名、工资、新的薪水（是原先薪水的1.25倍），并将该列标记为 New Salary。将该脚本保存到 les03_02.sql 中。

SCOTT@oradb> select empno,ename,sal*1.25 "New Salary" from emp;

     EMPNO ENAME      New Salary
---------- ---------- ----------
      7369 SMITH            1000
      7499 ALLEN            2000
      7521 WARD           1562.5
      7566 JONES         3718.75
      7654 MARTIN         1562.5
      7698 BLAKE          3562.5
      7782 CLARK          3062.5
      7788 SCOTT            3750
      7839 KING             6250
      7844 TURNER           1875
      7876 ADAMS            1375
      7900 JAMES          1187.5
      7902 FORD             3750
      7934 MILLER           1625

14 rows selected.

SCOTT@oradb> save les03_02.sql
Created file les03_02.sql
SCOTT@oradb> !cat les03_02.sql
select empno,ename,sal*1.25 "New Salary" from emp
/
复制

6.3 运行文件 les03_02.sql 中的查询。

SCOTT@oradb> @les03_02.sql

     EMPNO ENAME      New Salary
---------- ---------- ----------
      7369 SMITH            1000
      7499 ALLEN            2000
      7521 WARD           1562.5
      7566 JONES         3718.75
      7654 MARTIN         1562.5
      7698 BLAKE          3562.5
      7782 CLARK          3062.5
      7788 SCOTT            3750
      7839 KING             6250
      7844 TURNER           1875
      7876 ADAMS            1375
      7900 JAMES          1187.5
      7902 FORD             3750
      7934 MILLER           1625
复制

6.4 查询出雇员表中所有员工的姓名、入职时间、和工资调整日期（工作6 个月之后的第一个星期一）。其格式如下：“1981-09-23”。

SCOTT@oradb> SELECT ENAME,HIREDATE,TO_CHAR(NEXT_DAY(ADD_MONTHS(HIREDATE,6),'MONDAY'),'yyyy-mm-dd') from emp;

ENAME      HIREDATE  TO_CHAR(NE
---------- --------- ----------
SMITH      17-DEC-80 1981-06-22
ALLEN      20-FEB-81 1981-08-24
WARD       22-FEB-81 1981-08-24
JONES      02-APR-81 1981-10-05
MARTIN     28-SEP-81 1982-03-29
BLAKE      01-MAY-81 1981-11-02
CLARK      09-JUN-81 1981-12-14
SCOTT      19-APR-87 1987-10-26
KING       17-NOV-81 1982-05-24
TURNER     08-SEP-81 1982-03-15
ADAMS      23-MAY-87 1987-11-30
JAMES      03-DEC-81 1982-06-07
FORD       03-DEC-81 1982-06-07
MILLER     23-JAN-82 1982-07-26
复制

6.5 查出每个员工的名字（ename）、雇佣日期到现在的日期间隔的月数（要求四舍五入为整数），该列命名为 MONTHS_WORKED，并以该列的降序排序

SCOTT@oradb> SELECT ENAME,HIREDATE,ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATE),0) MONTHS_WORKED FROM EMP ORDER BY MONTHS_WORKED DESC;

ENAME      HIREDATE  MONTHS_WORKED
---------- --------- -------------
SMITH      17-DEC-80           501
ALLEN      20-FEB-81           499
WARD       22-FEB-81           499
JONES      02-APR-81           497
BLAKE      01-MAY-81           496
CLARK      09-JUN-81           495
TURNER     08-SEP-81           492
MARTIN     28-SEP-81           491
KING       17-NOV-81           490
JAMES      03-DEC-81           489
FORD       03-DEC-81           489
MILLER     23-JAN-82           488
SCOTT      19-APR-87           425
ADAMS      23-MAY-87           424
复制

6.6 查询员工的姓名，以及姓名的长度。要求其姓名首字母大写。

SCOTT@oradb> SELECT INITCAP(ENAME),LENGTH(ENAME) FROM EMP;

INITCAP(EN LENGTH(ENAME)
---------- -------------
Smith                  5
Allen                  5
Ward                   4
Jones                  5
Martin                 6
Blake                  5
Clark                  5
Scott                  5
King                   4
Turner                 6
Adams                  5
James                  5
Ford                   4
Miller                 6
复制

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

Oracle SQL学习笔记（共9篇）

oracle萌新的sql学习笔记