类型转换函数

NVL2

语法：

NVL2(expr1,expr2,expr3)

功能：如果expr1值不为NULL，则返回expr2的值；如果expr1是NULL，则返回expr3的值。

示例：

返回员工编号和工资列表，工资如果为NULL则返回0，不为NULL则返回1。

--删除同名表staffS_xian。
DROP TABLE IF EXISTS staffS_xian;
--创建表staffS_xian。
CREATE TABLE  staffS_xian
(
  staff_ID       NUMBER(6) not null,
  NAME           VARCHAR2(20),
  EMAIL          VARCHAR2(25),
  PHONE_NUMBER   VARCHAR2(20),
  HIRE_DATE      DATE,
  employment_ID  VARCHAR2(10),
  SALARY         NUMBER(8,2),
  MANAGER_ID     NUMBER(6),
  section_ID     NUMBER(4)
);
--向表staffS_xian中加入记录1。
INSERT INTO staffS_xian (staff_ID, NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, employment_ID, SALARY, MANAGER_ID, section_ID)
values (198, '王莹', 'wangying@126.com', '18095605632', to_date('21-06-1999', 'dd-mm-yyyy'), 'SH_CLERK', NULL, 124, 50);

--向表staffS_xian中加入记录2。
INSERT INTO  staffS_xian (staff_ID, NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, employment_ID, SALARY, MANAGER_ID, section_ID)
values (199, '何开平', 'hekaipng02@126.com', '18095605532', to_date('13-01-2000', 'dd-mm-yyyy'), 'SH_CLERK', 2600.00, 124, 50);

--向表staffS_xian中加入记录3。
INSERT INTO  staffS_xian (staff_ID, NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, employment_ID, SALARY, MANAGER_ID, section_ID)
values (200, '李瑞', 'lirui03@126.com', '18095565632', to_date('17-09-1987', 'dd-mm-yyyy'), 'AD_ASST', 4400.00, 101, 10);
--返回员工编号和工资列表，工资如果为NULL则返回，不为NULL则返回1。
SELECT staff_ID, NVL2(SALARY,1,) "SALARY" FROM staffS_xian WHERE staff_ID IS NOT NULL ORDER BY staff_ID;

STAFF_ID                                 SALARY
---------------------------------------- ------------
198                                      
199                                      1
200                                      1

3 rows fetched.

TO_CHAR

语法：

TO_CHAR(expr[,fmt])

功能：将expr转换成字符串。

入参是可转成string的表达式；返回值是STRING。

fmt为日期类型的格式控制符，例如，yyyy/mm/dd/hh/hh12/hh24/mi/ss/ff3等，（详情请参见日期类型说明）。如果没有指定format，则默认使用当前SESSION的时间格式。

当前SESSION的时间格式可以通过如下命令设置：

--通过NLS_DATE_FORMATDATE设置DATE类型的时间格式。
ALTER SESSION SET NLS_DATE_FORMAT='format';

--通过NLS_TIMESTAMP_FORMAT设置TIMESTAMP类型的时间格式。
ALTER SESSION SET NLS_TIMESTAMP_FORMAT='format';

说明：

该函数当前仅支持对日期类型(date, timestamp)指定格式控制符。

示例：

指定格式控制符，返回当前系统时间。

SELECT to_char(sysdate, 'MON-YY-DD HH:MM:SS AM') FROM dual;

TO_CHAR(SYSDATE, 'MON-YY-DD HH
-------------------------------
JAN-18-07 05:01:15 AM

1 rows fetched.

设置当前SESSION的时间格式，并返回当前连接的系统时间。

--通过NLS_DATE_FORMATDATE设置时间格式。
ALTER SESSION SET NLS_DATE_FORMAT='YYYYMMDD HH24:MI:SS';
--查询当前系统时间。
SELECT TO_CHAR(sysdate) FROM DUAL;

TO_CHAR(SYSDATE)
------------------------------------------------
20181207 15:12:24

1 rows fetched.

将“01110”转换成字符串。

SELECT TO_CHAR('01110') FROM DUAL;

TO_CHAR('01110')
----------------
01110

1 rows fetched.

TO_CLOB

语法：

TO_CLOB(str)

功能：将入参转换为CLOB类型，str为待转换的类型参数。

示例：

将字段comment的数据转换为CLOB类型。

--删除表employee。 
DROP TABLE IF EXISTS employee_branch_nanjing;
--创建表employee。 
CREATE TABLE employee_branch_nanjing(employee_id INT NOT NULL,first_name VARCHAR(10),last_name VARCHAR(10), hire_date DATETIME,comment VARCHAR(1012));
--加入记录1。
INSERT INTO employee_branch_nanjing(employee_id,first_name,last_name,hire_date,comment) VALUES(1001,'Alice','BROWN','2017-06-20 12:00:00','excellent staff of 2018,bonus 5000¥');
--加入记录2。
INSERT INTO employee_branch_nanjing(employee_id,first_name,last_name,hire_date,comment) VALUES(1002,'BOB','Smith','2017-10-20 12:00:00','excellent manager of 2018,bonus 2000¥');
--加入记录3。
INSERT INTO employee_branch_nanjing(employee_id,first_name,last_name,hire_date,comment) VALUES(1003,'ALAN','Jones','2017-05-10 12:00:00','');
--提交事务。
COMMIT;
--将字段comment的数据转换为CLOB类型。
UPDATE employee_branch_nanjing SET comment=TO_CLOB(comment);

3 rows affected.

TO_DATE

语法：

TO_DATE(expr[,fmt])

功能：将expr转换成日期类型。

fmt为转换格式，支持如下：yyyy/mm/dd/hh/hh12/hh24/mi/ss/ff3等。

入参是可转成DATE类型的字符串表达式或者数值类型（隐式转换）；返回值是DATE。

示例：

将字符串转换为日期类型。

SELECT TO_DATE('2018-06-28 13:14:15', 'YYYY-MM-DD HH24:MI:SS:FF')  FROM DUAL;
 
TO_DATE('2018-06-28 13:14:15', 'YYYY-MM-DD HH24:MI:SS:FF')
----------------------------------------------------------
2018-06-28 13:14:15

1 rows fetched.

TO_NUMBER

语法：

TO_NUMBER(n[, fmt])

功能：将n转换为number类型。

入参n是可转成数值型的表达式或字符串。fmt参数可选，表示n的转换格式，不设置时，默认转换为十进制。当前fmt支持两种固定格式，分别为：

X：对n按16进制串进行转换，在该格式中n只能包含16进制字符，0~9，A~F， a~f。

以XX这种格式转换时，X的个数必须大于等于前面的字符个数。
以0X这种格式转换时，n只能为0或正整数。

000.00：对n按10进制格式转换，小数点前面的数字的个数表示整数的个数，若n中整数个数超过格式中的整数个数将会报错；fmt中小数点后面的数字个数表示转换后保留的小数位数。

返回值是number。

示例1：

将123E500转换为number类型。

SELECT TO_NUMBER('123E500', 'XXXXXXX') from DUAL;

TO_NUMBER('123E500', 'XXXXXXX')
----------------------------------------
19129600

1 rows fetched.

示例2：

将123.500转换为number类型。

SELECT TO_NUMBER('123.500', '000.0000') from DUAL;

TO_NUMBER('123.500', '000.0000')
----------------------------------------
123.5

1 rows fetched.

示例3：

将00FFFFFF转换为number类型。

SELECT TO_NUMBER('00FFFFFF', '0000000X') from DUAL;
----------------------------------------
16777215

1 rows fetched.

TO_TIMESTAMP

语法：

TO_TIMESTAMP(expr[,fmt])

功能：将expr转换成时间类型。

fmt为转换格式，支持如下：yyyy/mm/dd/hh/hh12/hh24/mi/ss/ff3等。

入参是可转成TIMESTAMP类型的字符串表达式；返回值是TIMESTAMP。

示例：

将字符串转换为时间类型。

SELECT TO_TIMESTAMP('2018-06-28 13:14:15', 'YYYY-MM-DD HH24:MI:SS:FF') FROM DUAL;
 
TO_TIMESTAMP('2018-06-28 13:14:15', 'YYYY-MM-DD HH24:MI:SS:FF')
---------------------------------------------------------------
2018-06-28 13:14:15.000000

1 rows fetched.

UNHEX

语法：

UNHEX(expr1)

功能：expr1为十六进制字符串，将十六进制字符串转化为byte字节表示的字符。

示例：

将十六进制字符串（746869732069732061207465737420737472）转化为byte字节表示的字符。

SELECT UNHEX('746869732069732061207465737420737472') FROM DUAL;

UNHEX('746869732069732061207465737420737472')
----------------------------------------------------------------
this is a test str

1 rows fetched.

ITPub博客

] Gauss 100 数据库函数介绍之类型转换函数Part2

类型转换函数

NVL2

TO_CHAR

TO_CLOB

TO_DATE

TO_NUMBER

TO_TIMESTAMP

UNHEX

最新文章