SQL Fundamentals: Using Single-Row Functions to Customize Output使用单行函数自定义输出
SQL Fundamentals || Single-Row Functions || 字符函数 character functions
SQL Fundamentals || Single-Row Functions || 数字函数number functions
SQL Fundamentals || Single-Row Functions || 日期函数date functions
SQL Fundamentals || Single-Row Functions || 转换函数 Conversion function
SQL Fundamentals || Single-Row Functions || 通用函数 General function
SQL> SELECT SYSDATE,
TO_CHAR(SYSDATE,'YY-MM-DD HH24:MI:SS')
FROM DUAL;
SYSDATE TO_CHAR(SYSDATE,'
------------------ -----------------
24-JUL-17 17-07-24 17:15:53
SELECT SYSDATE
当前系统时间
,TO_CHAR(SYSDATE,'YYYY-MM-DD')
格式化日期
,
TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')
格式化日期时间
,
TO_CHAR(SYSDATE,'
FM
YYYY-MM-DD HH24:MI:SS')
去掉前导
0
的日期时间
FROM dual ;
使用
FM
可以取消前导
0
，但不建议去掉
.
使用其他方法格式化年、月、日
SELECT SYSDATE ,TO_CHAR(SYSDATE,'YEAR-MONTH-DY')
FROM dual ;
查询出所有在每年
2
月份雇佣的雇员信息
SELECT * FROM emp WHERE TO_CHAR(hiredate,'MM')='02' ;
SELECT * FROM emp WHERE TO_CHAR(hiredate,'MM')=2 ;
将每个雇员的雇佣日期进行格式化显示，要求所有的雇佣日期可以按照“年
-
月
-
日”的形式显示，也可以将雇佣的年、月、日拆开分别显示
SELECT empno,ename,job,hiredate,
TO_CHAR(hiredate,'YYYY-MM-DD')
格式化雇佣日期
, TO_CHAR(hiredate,'YYYY')
年
,
TO_CHAR(hiredate,'MM')
月
,TO_CHAR(hiredate,'DD')
日
FROM emp ;
使用英文的日期格式表示出每个雇员的雇佣日期
SELECT empno ,ename , hiredate ,TO_CHAR(hiredate,'YEAR-MONTH-DY') FROM emp ;
SELECT TO_CHAR(987654321.789,'999,999,999,999.99999')
格式化数字
,
TO_CHAR(987654321.789,'000,000,000,000.00000')
格式化数字
FROM dual ;
格式化货币显示
SELECT TO_CHAR(987654321.789,'L999,999,999,999.99999')
显示货币
,
TO_CHAR(987654321.789,'$999,999,999,999.99999')
显示美元
FROM dual ;
2
、
字符串
变为
数字
.
使用
TO_NUMBER()
函数将字符串变为数字
SELECT TO_NUMBER('09') + TO_NUMBER('19')
加法计算
,
TO_NUMBER('09') * TO_NUMBER('19')
乘法计算
FROM dual ;
不利用
TO_NUMBER()
函数字符串也可以自动变为数字
SELECT '09' + '19'
加法计算
, '09' * '19'
乘法计算
FROM dual ;
In some cases, the Oracle server receives data of one data type where it expects data of a different data type. When this happens, the Oracle server can automatically convert the data to the expected data type. This data type conversion can be done
implicitly
隐式
by the Oracle server or
explicitly
显式
by the user.
Implicit Data Type Conversion
A VARCHAR2 or CHAR value can be implicitly converted to NUMBER or DATE type value by Oracle. Similarly, a NUMBER or DATA type value can be automatically converted to character data by Oracle server. Note that the impicit interconversion happens only when the character represents the a valid number or date type value respectively.
Implicit data type conversions work according to the
rules explained
in the following sides.
In expressions, the Oracle server can automatically convert the following:
在表达式中，一个字符串值能被隐式转换为
数字
或
日期类型。
字符串到数字的转换只有当字符串表示一个合法的数字时才能成功
.
Explicit Data Type Conversion
SQL Conversion functions are single row functions which are capable of typecasting column value, literal or an expression . TO_CHAR, TO_NUMBER and TO_DATE are the three functions which perform cross modification of data types.
Explicit data type conversions are performed by
using the conversion functions
.
Conversion functions
convert a value from
one data
type to
another
.
Generally, the
form of the function names
follows the convention
data type TO data type
.
The
first data type
is the
input data
type and the
second data type
is the
output
.
显示数据类型转换通过转换函数执行。转换函数将一个值从一种类型转换成另一种类型
.
Converts a number or date value to a VARCHAR2 character string with the format model fmt.
fmt
是格式模型
,
指定转换后的字符串的格式
.
