Oracle 19C学习 - 05. 转换函数与条件表达式 - LeoZhangJing

Oracle 19C学习 - 05. 转换函数与条件表达式

（本博文采用的数据库是Oracle自带的hr用户范本数据库)

课程目标

隐式数据转换与显式数据转换

学习TO_CHAR, TO_NUMBER, TO_DATE转换函数的使用方法。

NULL通用函数

条件表达式

数据转换的类型

隐式数据转换： Oracle数据库自己进行的数据类型转换。

显示数据转换：用户操作来进行数据类型的转换。

隐式数据转换：

VARCHAR2 OR CHAR 与 DATE之间双向自动转换。

VARCHAR2 OR CHAR 与 NUMBER之间双向自动转换。

-- 字符串自动转为日期  01-10月-23 00:00:00
SELECT ADD_MONTHS('01-10月-22', 12) FROM dual;

-- 日期自动转为字符串 今天日期: 03-10月-22 21:55:55
SELECT '今天日期: ' || sysdate FROM dual;

-- 字符串自动转为数字 
SELECT CEIL('25.99') FROM dual; --26

-- 数字自动转为字符串  25 / 3 的余数：1
SELECT '25 / 3 的余数：' || MOD(25,3) FROM dual;

显示数据转换:

CHAR与DATE： TO_DATE / TO_CHAR

CHAR与NUMBER：TO_NUMBER / TO_CHAR

使用TO_CHAR函数转换日期为字符串

TO_CHAR(Date, ‘Format_Model’)

Format_Model参数内容：

-- SQLDEVELOPER执行结果
--fm让数字显示的日和月都不带前导0， MONTH显示完整的月份信息
-没有fm看开头所以日期带前导0， MON显示缩写的月份信息。
SELECT TO_CHAR(TO_DATE('2010-9-3','YYYY-MM-DD'), 'fmDD-MONTH-YYYY') as with_FM,     
TO_CHAR(TO_DATE('2010-9-3','YYYY-MM-DD'), 'DD-MON-YYYY') as Without_FM FROM dual; 

/* OUTPUT
WITH_FM   WITHOUT_FM 
--------- -----------
3-9月-2010 03-9月 -2010
*/


-- SQLPLUS的执行结果
SQL> SELECT TO_CHAR(SYSDATE, 'YEAR-MON-DD HH:MI:SS AM') FROM dual;

TO_CHAR(SYSDATE,'YEAR-MON-DDHH:MI:SSAM')
----------------------------------------------------------------------
TWENTY TWENTY-TWO-OCT-04 10:13:11 AM

使用TO_CHAR函数将数字转换为字符串

TO_CHAR(number, 'Format_Model')

Format_Model参数内容：

-- 格式字符串长度不够，会造成数据显示不开，变为####.
-- 小数位长度不够会四舍五入进位。
SELECT TO_CHAR(99911199.99, 'L999,999,999.00') FROM dual;
-- ￥99,911,199.99

SELECT USERENV('LANGUAGE') FROM dual; --显示语言信息，货币也是在这里决定的。SQLDeveloper和SQLPLUS的显示会有不同。

-- SQLDeveloper: SIMPLIFIED CHINESE_CHINA.AL32UTF8
SELECT USERENV('LANGUAGE') FROM dual; 

-- SQLPLUS: AMERICAN_AMERICA.AL32UTF8
SQL> SELECT USERENV('LANGUAGE') FROM dual;

使用TO_NUMBER函数将字符串转换为数字

TO_NUMBER(STR, [Format_Model])：将字符串转换为数字。

-- 没有格式的数字，直接转换，不需要格式字符串参数。
SELECT TO_NUMBER('20010') + 90 FROM dual; - 20100

-- 转换的时候，字符串要和格式字符串相匹配
SELECT TO_NUMBER('￥1,999.50' , 'L9,9999.99') FROM dual; --1999.5

使用TO_DATE函数将字符串转换为日期

TO_DATE(STR, 'FORMAT_MODEL')：将字符串转换为日期类型。

SELECT TO_DATE('2020-FEB-28','YYYY-MON-DD') FROM dual;   --根据数据的类型编写适应的Format Model。

TO_DATE('2020-FEB-2
-------------------
2020-02-28 00:00:00

通用函数(与NULL有关系的函数)

NVL函数可以将空值转换为实际值，转换的实际值可以是日期、字符和数字。

NVL(检测值，替换值)：当检测值为NULL的时候，使用替换值替换，替换值的类型要与字段的类型匹配。

SELECT last_name, NVL(commission_pct, 99) FROM employees;
/*  OUTPUT
Davies    99
Vargas    99
Russell    0.4
Partners    0.3
*/

NVL2将空值与非空值都进行替换。

NVL2(检测值，非空转换值，空值转换值)：当检测值为空的时候，转换为空值转换值。不为空的，转换为非空转换值.

SELECT last_name, NVL2(commission_pct, 0, 1) FROM employees;
/* OUTPUT
Matos    1
Vargas    1
Russell    0
Partners    0
Errazuriz    0
*/

NULLIF 比较两个数据，如果不相等，输出第一个数据，如果相等，输出NULL。

NULLIF(值1，值2）如果值1和值2不同，结果为值1；如果相同，则为NULL。

SELECT last_name, LENGTH(last_name), first_name, LENGTH(first_name), 
        NULLIF(LENGTH(last_name), LENGTH(first_name)) FROM employees;
/*
Cambrault    9    Gerald     6    9
Cambrault    9    Nanette    7    9
Chen         4    John       4    (null)
Chung        5    Kelly      5    (null)
*/

COALESCE的第一个表达式不为空，返回第一个表达式的值，否则返回第二个表达式。

COALESCE(表达式1，表达式2）：当表达式1中有NULL值，返回表达式2，否则结果为表达式1的计算结果。

SELECT last_name, commission_pct, COALESCE(salary * (1+commission_pct) , salary + 2000) 
FROM employees;
/* OUTPUT
Vargas     (null)      4500
Russell     0.4   　　 19600
Partners    0.3    　　17550
*/

条件表达式 - CASE表达式

CASE [判断值] WHEN 比较值表达式 THEN 表达式

　　　 WHEN 比较值表达式 THEN 表达式

　　　　　　. . .

　　　　　　ELSE 表达式

END as 别名

CASE后给定字段，WHEN后面直接写字段的值， THEN写返回值。

CASE后不给定字段，WHEN后面写逻辑表达式， THEN写返回值。

-- CASE后有判断值
SELECT first_name || '.' || last_name, job_id, salary,
       CASE job_id WHEN 'IT_PROG'  THEN salary * 1.1
                   WHEN 'ST_CLERK' THEN SALARY * 1.2
                   ELSE salary
       END as 工资调整
FROM employees WHERE JOB_ID IN ('IT_PROG', 'ST_CLERK');
/*
Valli.Pataballa        IT_PROG        4800    5280
Diana.Lorentz          IT_PROG        4200    4620
Julia.Nayer            ST_CLERK    　　3200    3840
Irene.Mikkilineni      ST_CLERK    　　2700    3240
*/

-- CASE后无判断值
SELECT first_name || '.' || last_name, job_id, salary,
       CASE  WHEN job_id = 'IT_PROG' and salary <= 5000 THEN '工资过低'
             WHEN job_id = 'ST_CLERK' and salary > 3000 THEN '工资过高'
             ELSE '工资正常'
       END as 工资检查
FROM employees WHERE JOB_ID IN ('IT_PROG', 'ST_CLERK');
/* OUTPUT
Bruce.Ernst         IT_PROG     6000    工资正常
David.Austin    　 　IT_PROG     4800    工资过低
Valli.Pataballa     IT_PROG     4800    工资过低
Diana.Lorentz    　　IT_PROG     4200    工资过低
Julia.Nayer         ST_CLERK    3200    工资过高
*/

条件表达式 - DECODE函数

DECODE(判断值, 判断表达式1, 表达式1,

判断表达式1, 表达式1,

默认执行表达式) as 别名

SELECT last_name, job_id, salary, 
DECODE(job_id, 'IT_PROG',  8000,
               'ST_CLERK', 4000,
               salary * 1.1) as revised_salary
FROM employees WHERE JOB_ID IN ('IT_PROG', 'ST_CLERK', 'PU_CLERK');
/*
Pataballa      IT_PROG        4800    8000
Khoo           PU_CLERK       3100    3410
Mikkilineni    ST_CLERK       2700    4000
*/

posted on 2021-05-02 23:49 LeoZhangJing 阅读(267) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

Oracle 19C学习 - 05. 转换函数与条件表达式

导航