学习笔记：Oracle函数大全

     编者按

截至2020 0717，感觉我找的是最全的，没有之一！
这篇文章全是手敲，就连代码也不例外，复制后 去运行 难免出错，还请指正。 ^. ^

本文不建议 从头到尾阅读，需要的时候 Ctrl + F 搜索对应函数名即可。

     01. 字符函数

1.01 ～ 1.13 返回的是字符值
1.14 之后 返回的是数字

         1.01 CHR(n)

SELECT 
	CHR(54740) 别名1, 
	CHR(65) 别名2 
FROM DUAL;

---->
别名1   别名2
赵      A

功能： 给出整数X，返回对应的ASCII码字符。CHR和ASCII是一对反函数。
测试： 中文字符返回的是乱码… 跟编码也有关系吗？ 自己研究去吧。

         1.02 CONCAT(ch1, ch2)

SELECT 
	CONCAT('520', '1314') || '嘿嘿' AS "C1", 
	'520' || '1314' AS "C2", CONCAT(520, 1314) AS "C3" 
FROM DUAL;

---->
C1           C2        C3
5201314嘿嘿   5201314   5201314

功能： 连接两个字符串；与 “||” 的区别，concat只能连接2个字符串， “||” 可以连接多个；MySQL中的concat可以连接多个字符串，即concat(ch1, ch2, ch3 ,…).
测试： OK.

         1.03 INITCAP(ch)

SELECT 
	INITCAP('smith pEOVE') "R1", 
	INITCAP('smith!pEOVE') "R2", 
	INITCAP('smith7pEOVE') "R3" 
FROM DUAL;

---->
R1            R2            R3
Smith Peove   Smith!Peove   Smith7peove

功能： 返回字符单词首字母大写，其余小写，单词首字母大写判断 用空格 和 非字母非数字 字符分隔。
测试： OK.

         1.04 UPPER(ch)

SELECT UPPER('PeoVE 7') RESULT FROM DUAL;

---->
RESULT
PEOVE 7

功能： 所有字母大写.
不是字母的字符不变；
如果 ch 是 CHAR 数据类型的，那么结果也是CHAR类型的；
如果 ch 是 VARCHAR2 类型的，那么结果也是 VARCHAR2 类型的。
补充： SELECT UPPER(字段名) RESULT FROM DUAL;
测试： OK.

         1.04 LOWER(ch)

SELECT LOWER('PeoVE') RESULT FROM DUAL;

---->
RESULT
peove

功能： 所有字母小写.
补充： SELECT LOWER(字段名) RESULT FROM DUAL;
测试： OK.

         1.05 LPAD(expr1, n, expr2)、RPAD(expr1, n, expr2)

SELECT 
	LPAD('peove', 10, '-') R1,
	LPAD('peove', 10) R2,
	LPAD('Peove', 3, '#') R3,
	LPAD('Peove', 7, 'ABC') R4
FROM DUAL;

---->
R1           R2            R3    R4
-----peove   |     peove   Peo   ABPeove

功能：
在 expr1 字符左边或右边粘贴数个 expr2 字符，直到字符总字节数达到 n 字节；expr2默认为空格。 （ LPAD 在列的左边粘贴字符，RPAD 在列的右边粘贴字符 ）
如果 expr1 长度大于 n，则返回 expr1 左端 n 个字符。
如果 expr2 的长度要比 n 字符少，就复制 补充长度到 n。
如果 expr2 多于 n 字符，则仅 expr2 前面的 n 各字符被使用。
补充： RPAD 就不试了；当然也可以联合使用，如：LPAD(RPAD(‘Peove’, 7, ‘-’), 10, ‘#’).
测试： OK.

         1.06 NLS_INICAP(ch)、NLS_LOWER(ch)、NLS_UPPER(ch)

测试：
数据库函数提示只有第一个参数，如标题所写的那样，并没有这样的 NLS_INICAP(ch, nlsparams)；
只用一个参数：和INICAP、LOWER、UPPER用法一样。
两个参数都用：第2个参数可以写 ‘NLS_SORT= SCHINESE_RADICAL_M’，但效果跟没有一样。

这三个函数等 高手 来补充。

         1.07 REGEXP_REPLACE(source, pattern, replace_str, pos, occ, par)

SELECT 
	REGEXP_REPLACE('HELLO world', 'o', '*') R1, 
	REGEXP_REPLACE('HELLO 171', '\d', '*') R2, 
	REGEXP_REPLACE('HELLO 171y', '\d+', '*') R3, 
	REGEXP_REPLACE('HeLLO 171', '[a-z0-9]', '*') R4, 
	REGEXP_REPLACE('HeLLO 171', '[a-zA-Z]', '*', 2, 1) R5, 
FROM DUAL;

---->
R1            R2	      R3		 R4			 R5
HELLO w*rld   HELLO ***   HELLO *y   H*LLO ***   H*LLO 171

功能： 10g新增函数，扩展了REPLACE函数的功能，并且用于按照特定正则表达式的规则替换字符串。
其中参数 （后面3个参数也可以不写）
source 指定源字符表达式，
pattern 指定正则表达式，
replace_str 指定替换字符串，
pos 起始位置（默认从1开始），
occ 替换的次数（0是无限次），
par 不区分大小写。
补充： 正则表达式的使用

         1.08 REGEXP_SUBSTR(source, pattern, pos, occ, par)

SELECT 
	REGEXP_SUBSTR('http://www.oracle.com/products', 'http://([[:alnum:]\.?]+)') RESULT
FROM DUAL;

---->
RESULT
http://www.oracle.com

功能： 10g新增函数，扩展了SUBSTR函数的功能，并且用于按照特定表达式的规则返回字符串的子串。
其中参数 （后面3个参数也可以不写）
source 指定源字符表达式，
pattern 指定正则表达式，
pos 起始位置（默认从1开始），
occ 替换的次数（0是无限次），
par 不区分大小写。
补充： [[:alnum:]] 是所有的字母和数字。
测试： OK.

         1.09 REPLACE(source, search_str, replace_str)

SELECT 
	REPLACE('Fuck!!', 'Fuck', '*') RESULT
FROM DUAL;

---->
RESULT
*!!

功能： 把 source 中的所有的子字符串 search_str 用可选的 replace_str 替换，如果没有指定replace_str，所有的string中的子字符串search_str都将被删除。REPLACE是TRANSLATE所提供的功能的一个子集。
补充： 只能替换单个，想用高级的，用REGEXP_REPLACE
测试： OK.

         1.10 TRIM(trim_source)、LTRIM(ch)、RTRIM(ch)

SELECT 
	TRIM('  p e o v E  ') R1,
	TRIM('e' FROM 'eep e o v Ee') R2,
	LTRIM('eepeove', 'e') R3,
	RTRIM('eepeove', 'vee') R4
FROM DUAL;

---->
R1          R2			R3		R4
p e o v E   p e o v E   peove   eepeo

功能：
TRIM 修理左边和右边的，中间不处理；
LTRIM 只处理左边；
RTRIM 只处理右边。
补充：
TRIM、LTRIM、RTRIM 缺省值是 ‘’；
TRIM 只能写单字符，如 TRIM('e’e FROM ‘eep e o v Ee’) R2，会报错；
TRIM 想去掉两边 自定义的单字符时，需要用FROM，没有第2个参数用法；
LTRIM 和 RTRIM 有第2个参数，且可以写多字符。
测试： OK.

         1.11 SOUNDE(ch)

SELECT 
	SOUNDEX('weather') R1,
	SOUNDEX('wather') R2,
	SOUNDEX('WEther') R3,
	SOUNDEX('wqyrt') R4,
	SOUNDEX('SUNDAY') R5
FROM DUAL;

---->
R1	   R2	  R3  	 R4	    R5
W360   W360   W360   W263   S530

功能： 返回 ch 的声音表示形式；这对于比较两个拼写不同但是发音类似的单词而言很有帮助，如果字符发音相同，则返回的结果会一致。
补充： SELECT 字段名 RESULT FROM 表明 WHERE SOUNDEX(字段名) = SOUNDEX(‘wear’);
测试： OK.

         1.12 SUBSTR(ch, pos, length)

SELECT
	SUBSTR('我要稳稳的幸福 能抵挡末日的残酷 在不安的深夜 能有个归宿', 3, 10) R1,
	SUBSTR('17807211314', 3, 20) R2,
	SUBSTR('17807211314', 3) R3
FROM DUAL;

---->
R1				   R2		   R3
稳稳的幸福 能抵挡末   807211314   807211314  

功能： 截取字符串，从第 pos（包含）个开始取 length 个字符，这个务必要注意，必须是字符。 VARCHAR2最长4000个字节，GBK编码中一个中文字符占2个字节，韩文字符占4个字节，如果 ch 是 date 或者 number 的数据类型，会自动转化为 VARCAHR2。
补充： 如果 length 超出了长度 或者 不写，都会截取到最后。
测试： OK.

         1.13 TRANSLATE(expr, from_str, to_str)

SELECT
	TRANSLATE('2abc1314', '01234abcde', '99999XXXXX') R1,
	TRANSLATE('2abc1314f', '01234abcdef', '99999XXXXX') R2,  /** 第2个参数比第3个参数长, 多的是f, 没有在第3个的位置找到对应的替换, 就会被删除了. */
	TRANSLATE('2abc1314', '012ab', '*****-----') R3,  /** 第3个参数比第2个长 无所谓 */
	TRANSLATE('2abc1314', '012ab', '*-') R4,  /** 2在'012ab'里, 但 '012ab' 所在的位置 在 '*-' 是没有的, 所以显示 ''; c 在 第二个参数 没又, 保留下来 */
	REPLACE(TRANSLATE('A中国Yd1234', '0123456789', '0000000000'), '0') R5,
	LENGTH(TRANSLATE(',01234,567,,8,', 'a123456789', ' ')) R6  /** 'a123456789' 中 逗号出现的次数, 第3个参数不能写 '' 不然结果会null; 第2个参数 开头必须个 expr不存在的, 比如 a、！、@等 来抵消第3个参数第1个位置的空格, 不写的话结果就多1. */
FROM DUAL;

---->
R1         R2         R3         R4
9XXX9999   9XXX9999   ***c*3*4   c-3-4

功能： 将字符 expr 按照 from_str 与 to_str 的对应规则进行处理，返回将所出现的 from_str 中的每个字符替换为 to_str 中的相应字符以后的 expr；TRANSLATE 是 REPLACE 所提供的功能的一个超集。
如果 from_str 比 to_str 长，那么在 from_str 中而不在 to_str 中而外的字符将从 expr 中被删除，因为它们没有相应的替换字符； to_str 不能为空，Oracle把空字符串认为是NULL，并且如果 TRANSLATE 中的任何参数为NULL，那么结果也是NULL。
测试： OK.

         1.14 ASCII(ch)

SELECT 
	ASCII('A') A, 
	ASCII('a') a, 
	ASCII('0') ZERO, 
	ASCII(' ') SPACE, 
FROM DUAL;

---->
A    a	  ZERO   SPACE
65   97   48     32

功能： 返回 ch 字符串首字符的十进制表示 ASCII 码值。 CHR 和 ASCII 是互为相反的函数；CHR 得到给定字符编码的响应字符， ASCII得到给定字符的字符编码。
测试： OK.

         1.15 INSTR(str, substr)

SELECT 
	INSTR('rang oracle Traning', 'ra') R1,
	INSTR('rang oracle Traning', 'ra', 1, 2) R2, /** 第3个参数：从'ra'第1次出现的位置开始；第4个参数：'ra'第2次出现的位置 */
	INSTR('rang oracle Traning', 'ra', 2, 2) R3,
	INSTR('rang oracle Traning', 'ra', 3, 2) R4, /** 第3个参数 超出了 第2个参数 所在 第1个参数的次数时, 都会返回 第2个参数 在 第1个参数 最后一次出现的位置 */
	INSTR('rang oracle Traning', 'ra', 1, 1) R5,
	INSTR('rang oracle Traning', 'ra', 1, 4) R6, /** 第4个参数 超出了 次数时, 没有找到会返回 0 */
FROM DUAL;

---->
R1   R2   R3   R4   R5   R6
1    7    14   14   1    0

功能： 得到在 str 中包含 substr 的位置。在 str 字符串中搜索 substr 字符，返回发现指定的字符的位置。
a>0，str 是从左边开始检查的，开始的位置为a；
a<0，str 是从右边开始进行扫描的，开始的位置为a。
第b次出现的位置将被返回； a和b都缺省设置为1，这将会返回在 str 中第一次出现 substr 的位置；如果 substr 在 a 和 b 的规定下没有找到，那么返回0。位置的计算是相对于 str 的开始位置的，不管a和b的取值是多少。

str 被搜索的字符串
substr 希望搜索的字符串
a 搜索的开始位置，默认为1(如果为负数会从后向前搜索)
b 出现的位置，默认为1

补充： str 和 substr 是区分大小写的。
测试： OK.

         1.16 INSTRB

SELECT 
	INSTRB('rang oracle Traning', 'ra') R1,
	INSTRB('rang oracle Traning', 'ra', 2, 1)R2
FROM DUAL;

---->
R1   R2
1    7

功能： 和INSTR相同，只是操作的对参数字符使用的位置的是字节。
补充： Navicat 对这个函数 没有提示。
测试： OK.

         1.17 LENGTH(ch)

SELECT 
	LENGTH('酒醉的蝴蝶') R1,
	LENGTH(' 1314 ') R2,
	LENGTH('') R3
FROM DUAL;

---->
R1   R2   R3
5    6    null

功能： 返回字符串的长度；特别注意的，对于空的字段，返回为空，而不是0。
测试： OK.

         1.18 LENGTHB

SELECT 
	LENGTH('酒醉的蝴蝶1314') R1,
	LENGTH('酒醉的蝴蝶') R2,
	LENGTH(' 1314 ') R3,
	LENGTH('') R4
FROM DUAL;

---->
R1   R2   R3   R4
19   15   6    null

功能： 返回以字节为单位的 ch 的长度，对于单字节字符集 LENGTHB 和LENGTH 是一样的。
补充： Navicat 对这个函数 没有提示。1个汉子是3个 Byte，数字和字母是1个 Byte，应该是 UTF-8 吧？
测试： OK.

         1.19 NLSSORT(ch)

SELECT 
	SELECT XM FROM RYGL ORDER BY NLSSORT('XM', 'NLS_SORT=SCHINESE_STROKE_M');  /** 按照笔画排序, 数字在前 */
	SELECT XM FROM RYGL ORDER BY NLSSORT('XM', 'NLS_SORT=SCHINESE_RADICAL_M'); /** 按部首排序, 数字在前 */
	SELECT XM FROM RYGL ORDER BY NLSSORT('XM', 'NLS_SORT=SCHINESE_PINYIN_M');  /** 按照首字母拼音排序(也可以将 英文首字母排序 a b c d ...), 数字在前 */
	SELECT XM FROM RYGL ORDER BY NLSSORT('XM', 'NLS_SORT=BINARY_CI');          /** 顺序：空格、数字、排序 */
FROM DUAL;

功能： 表：RYGL，字段：XM；按照 笔画、部首、拼音 对 XM进行排序。
Session级别的设置，修改 ORACLE 字段的默认排序方式：

按拼音：alter session set nls_sort = SCHINESE_PINYIN_M;
按笔画：alter session set nls_sort = SCHINESE_STROKE_M;
按偏旁：alter session set nls_sort = SCHINESE_RADICAL_M;
忽略大小写：alter session set nls_sort = BINARY_CI;

注意： NLS_SORT 是性能杀手，设置 NLS_SORT 就不走索引了。
测试： OK.

     02. 数学函数

数学函数的输入和输出都是数字型，并且多数函数精确到38位；
cos / cosh / exp / ln / log / sin / sinh / sqrt / tan/ tanh 精确到36位；
acos / asin / atan / atan2 精确到30位。
数学函数可以在sql语句和plsql块中引用。

参数是 数字的，也可以用纯数字的字符；如果里面 有英文，则报错。 如

SELECT ABS('1314') FROM DUAL

         2.01 ABS(n)

SELECT 
	ABS(100) R1,
	ABS(-100) R2,
	ABS('-100') R3,
FROM DUAL;

---->
R1	  R2	R3
100   100   100

功能： 得到 n 的绝对值。
补充： 如果 ’ ’ 里有非数字，报错：ORA - 01722: invalid number.
测试： OK.

         2.02 ACOS(n)

SELECT 
	ACOS(1)   R1, /** cos0° = 1 */
	ACOS(-1)  R2,
	ACOS(0.5) R3, /** 写成 ACOS(1/2) 也可 */
	ACOS(0)   R4, /** cos90° = 0, 返回结果 涉及 角度和弧度的换算: 弧度 = 角度 * π / 180. */
FROM DUAL;

---->
R1   R2			R3		   R4
0    3.141592   1.047197   1.570796

功能： 返回 n 的反余弦值，n 应该从-1到1之间的数，结果在 0 到 π 之间，输出以弧度为单位。
补充： 实际返回 小数点 30位数字，这里取 6位。
测试： OK.

         2.02 COS(n)

SELECT 
	COS('3.14159')   R1, /** 参数是 弧度, cos180° = -1 */
	COS('-1')  R2,
	COS(0.5)   R3, 
	COS(0)     R4, /** cos0° = 1, 返回结果 涉及 角度和弧度的换算: 弧度 = 角度 * π / 180. */
FROM DUAL;

---->
R1   		R2		   R3		  R4
-0.999999   0.540302   0.877582   1 

功能： 返回 n 的余弦值，输出以弧度为单位。
补充： 实际返回 小数点 36位数字，这里取 6位。
测试： OK.

         2.02 COSH(n)

SELECT 
	COS('3.14159')   R1,
	COS('-1')  R2,
	COS(0.5)   R3, 
	COS(0)     R4,
FROM DUAL;

---->
R1   		R2		   R3		  R4
11.591922   1.543080   1.127625   1 

功能： 返回 n 的双曲余弦值，输出以弧度为单位。
补充： 实际返回 小数点 36位数字，这里取 6位。
测试： OK.

         2.03 ASIN(n)

SELECT 
 	ASIN(1)   R1, /** sin0° = 0 */
	ASIN(-1)  R2,
	ASIN(0.5) R3, /** 写成 ASIN(1/2) 也可 */
	ASIN(0)   R4, /** sin90° = 1, 返回结果 涉及 角度和弧度的换算: 弧度 = 角度 * π / 180. */
FROM DUAL;

---->
R1		   R2		   R3   	  R4
1.570796   -1.570796   0.523598   0

功能： 返回 n 的反正弦值，n的范围应该是 -1到 1之间，返回的结果在 -π/2 到 π/2 之间，输入、输出以弧度为单位。
测试： OK.

         2.03 SIN(n)

SELECT 
	SIN(3.14159/2) R1, /** sin90° = 1 */
	SIN(-1)  R2,
	SIN(0.5) R3,
	SIN(0)   R4
FROM DUAL;

---->
R1	 	   R2		   R3		  R4
0.999999   -0.841470   0.479425   0

功能： 计算 n 的正弦值； n 是一个以弧度表示的角度。
测试： OK.

         2.03 SINH(n)

SELECT 
	SIN(1)   R1, 
	SIN(-1)  R2,
	SIN(0.5) R3,
	SIN(0)   R4
FROM DUAL;

---->
R1	 	   R2		   R3		  R4
1.570796   -1.570796   0.523598   0

功能： 返回 n 的双曲正弦值。n 是一个以弧度表示的角度。
测试： OK.

         2.04 ATAN(n)

SELECT 
 	ATAN(1)   R1, /** tan45° = 1 */
	ATAN(-1)  R2,
	ATAN(0.5) R3, /** 写成 ATAN(1/2) 也可 */
	ATAN(0)   R4, /** tan0° = 0, 返回结果 涉及 角度和弧度的换算: 弧度 = 角度 * π / 180. */
FROM DUAL;

---->
R1		   R2 		   R3		  R4
0.785398   -0.785398   0.463647   0

功能： 计算 n 的反正切值，返回值在 -π/2 到 π/2 之间，输出单位是弧度。
测试： OK.

         2.04 TAN(n)

SELECT 
	TAN(0) R1, /** tan0° = 0 */
	TAN(3.14159/4) R2, /** tan45° = 1 */
	TAN(3.14159) R3
FROM DUAL;

---->
R1   R2			   R3
0    0.999998...   753695.995174...

功能： 计算 n 的正切值。n 是一个以弧度位单位的角度。
测试： OK.

         2.04 TANH(n)

SELECT 
	TANH(0) R1,
	TANH(3.14159/4) R2,
	TANH(3.14159/2) R3
FROM DUAL;

---->
R1   R2			   R3
0    0.655793...   0.917152

功能： 计算 n 的双曲正切值。
测试： OK.

         2.05 ATAN2(n1, n2)

SELECT 
 	ATAN2(1, 2)   R1, 
	ATAN2(-1, 2)  R2,
	ATAN2(0.5, 1) R3, 
	ATAN2(0, 1)   R4, 
FROM DUAL;

---->
R1		   R2		   R3 		  R4
0.463647   -0.463647   0.463647   0

功能： 返回 n1 除以 n2 的反正切值，结果在 -π/2 到 π/2 之间，输出单位是弧度。
补充： 第2个参数 不能写0，因为 分母为0 不成立，报错：ORA - 01426: numeric overflow.
测试： OK.

         2.06 CEIL(n)

SELECT 
	CEIL(3.14157)    R1,
	CEIL('-3.14157') R2
FROM DUAL;

---->
R1	 R2
4    -3

功能： 向上取整。
补充： 纯数字的 字符也可以。
测试： OK.

         2.07 FLOOR(n)

SELECT 
	FLOOR(3.14157)    R1,
	FLOOR('-3.14157') R2
FROM DUAL;

---->
R1	 R2
3    -4

功能： 向下取整。
补充： 纯数字的 字符也可以。
测试： OK.

         2.08 EXP(n)

SELECT 
	EXP(1)   R1,
	EXP('2') R2
FROM DUAL;

---->
R1	 	   R2
2.718281   7.389056

功能： 计算 e 的 n 次幂； e为自然对数，约等于2.718281。
补充： 纯数字的 字符也可以。
测试： OK.

         2.09 LN(n)

SELECT 
	LN(1) R1, /** e 的0次幂 = 1 */
	LN(2) R2,
	LN(EXP(1)) R3 /** EXP(1) = e, e 的1次幂 = e */
FROM DUAL;

---->
R1   R2	 	  	  R3
0    0.693147..   1

功能： 返回 n 的自然对数；x必须是正数，并且大于0。
测试： OK.

         2.10 LOG(n1, n2)

SELECT 
	LOG(2, 1) R1, /** 2 的 0 次幂 = 1 */
	LOG(2, 4) R2  /** 居然出现了精度丢失 ？？！ */
FROM DUAL;

---->
R1   R2	 	  
0    1.999999...

功能： 计算以 n1 为底的 n2 的对数。n1 > 0 ADN n1 != 1，n2 > 0。
补充： log₂1 = 0.
测试： OK； 但是出现了 精度丢失，结果如上.

         2.11 MOD(n1, n2)

SELECT 
	MOD(10, 3) R1,
	MOD(3, 3)  R2,
	MOD(3, 0)  R3,
	MOD(2, 3)  R4
FROM DUAL;

---->
R1	 R2	  R3   R4	  
1    0    3    2

功能： 取余函数。
返回 n1 除以 n2 的余数；
如果 n2 是 0，则返回 n1，
如果 n1 < n2，则返回n1。
测试： OK.

         2.12 POWER(n1, n2)

SELECT 
	POWER(2, 10) R1,
	POWER(3, 3)  R2
FROM DUAL;

---->
R1	   R2  
1024   27

功能： 计算 n1 的 n2 次幂。
补充： POWER(2, 10) --> 2¹⁰ = 1024
测试： OK.

         2.13 ROUND(date, fmt)

SELECT 
	ROUND(55.655) R1,
	ROUND(55.655, 2) R2,
	ROUND(45.654, -1) R3,
	ROUND(45.654, -2) R4,
	ROUND(50.654, -2) R5,
	ROUND(TO_DATE('2020-07-27', 'yyyy-mm-dd'), 'year')  R6, /** 1-6月 7-12月 四舍五入 到最近的 yyyy年1月 */
	ROUND(TO_DATE('2020-07-27', 'yyyy-mm-dd'), 'month') R7, /** 1-15日 16-31日 四舍五入到最近的 mm月1日 */
	ROUND(TO_DATE('2020-07-27', 'yyyy-mm-dd'), 'day')   R8  /** 星期1-星期3 星期4-星期7 四舍五入到最近的 星期天 */
FROM DUAL;

---->
R1	 R2	     R3	  R4   R5	 R6				  	   R7					 R8
56   55.66   50   0    100   2021-01-01 00:00:00   2020-08-01 00:00:00   2020-07-26 00:00:00

功能： 四舍五入函数。
fmt 缺省值为0，date 保留整数；
fmt > 0，date 保留小数点右边 fmt 位；
fmt <0，date 保留小数点左边 |fmt| 位；
可以对时间进行 round，效果是只保留年月日。
测试： OK.

         2.14 SIGN(n)

SELECT 
	SIGN(1314)  R1,
	SIGN(-1314) R2,
	SIGN(0)     R3
FROM DUAL;

---->
R1	  R2   R3	  
1    -1    0

功能： 检测 n 的正负。
如果 n < 0 返回 -1；
如果 n = 0 返回 0；
如果 n > 0 返回 1。
补充： 常与 decode 结合使用。
测试： OK.

         2.15 SQRT(n)

SELECT 
	SQRT(64) R1,
	SQRT(2) R2
FROM DUAL;

---->
R1   R2
8    1.414213

功能： 返回 n（n > 0） 的平方根。
测试： OK.

         2.16 TRUNC(date, fmt)

SELECT 
	TRUNC(77.1314) R1,
	TRUNC(77.1314, 2)  R2,
	TRUNC(77.1314, -2) R3,
	TRUNC(77.1314, -1) R4
FROM DUAL;

---->
R1   R2      R3   R4
77   77.13   0    70

功能： 截取数字函数，只舍不入函。
fmt 缺省值为0，date 保留整数；
fmt > 0，date 保留小数点右边 fmt 位；
fmt < 0，date 保留小数点左边 |fmt| 位。
测试： OK.

     03. 日期时间函数

日期时间函数用于处理date和timestamp类型的数据，除了函数 months_between 返回数字外，其余均返回date类型；
Oracle以7位数字格式来存放日期数据，包括世纪、年、月、日、小时、分钟、秒，并且默认日期显式格式为 “DD-MON-YY”。

         3.01 ADD_MONTHS(date, int)

SELECT 
	ADD_MONTHS(TO_DATE('202008', 'yyyymm'), 2)      R1,
	ADD_MONTHS(TO_DATE('202008', 'yyyymm'), -2)     R2,
	ADD_MONTHS(TO_DATE('20200831', 'yyyymmdd'), 1)  R3,
	ADD_MONTHS(TO_DATE('20200831', 'yyyymmdd'), -2) R4
FROM DUAL;

---->
R1					  R2					R3				      R4
2020-10-01 00:00:00   2020-06-01 00:00:00   2020-09-30 00:00:00   2020-06-30 00:00:00

功能： 返回日期 date 加上 int 个月后的月份。
int 可以是任意整数。如果结果日期中的月份所包含的天数比 date 日期中的“日”分量要少，（即相加后的结果日期中的日分量信息已经超过该月的最后一天，例如，8月31日加上一个月之后得到9月31日，而9月只能有30天）返回结果月份的最后一天。
测试： OK.

         3.02 CURRENT_DATE

SELECT 
	CURRENT_DATE R1,
	SYSDATE R2
FROM DUAL;

---->
R1					  R2
2020-08-12 19:52:00   2020-08-12 19:52:00

功能： 9i 新增函数，返回当前会话时区所对应的日期时间。
补充：
CURRENT_DATE 是会话的当前日期和时间；
SYSDATE 是服务器（主机）的当前日期和时间。
一般情况下，二者相同。但如果修改了当前会话的时区，则会不同。
测试： OK.

         3.03 CURRENT_TIMESTAMP(precision)

SELECT 
	CURRENT_TIMESTAMP R1,
	CURRENT_TIMESTAMP(0) R2,
	CURRENT_TIMESTAMP(6) R3,
	CURRENT_TIMESTAMP(4) R4
FROM DUAL;

---->
R1									R2						     R3									 R4
2020-08-12 19:57:23.241518 +08:00   2020-08-12 19:57:23. +08:0   2020-08-12 19:57:23.241518 +08:00   2020-08-12 19:57:23.2415 +08:00

功能： 9i新增函数，返回当前会话时区所对应的日期时间。
补充：
CURRENT_TIMESTAMP 与时区设置有关，返回的秒是系统的，返回的日期和时间是根据时区转换过的。
CURRENT_DATE 是对CURRENT_TIMESTAMP准确到秒的四舍五入。
测试： OK.

         3.04 DBTIMESONE

SELECT 
	DBTIMEZONE R1
FROM DUAL;

---->
R1
+00:00

功能： 9i新增函数，返回数据库所在时区。
补充： UTC/GMT+08:00 东八区: 112度30分～127度30分；东８时区 香港新加坡（GMT+ 8）.
测试： OK.

         3.05 EXTRACT(time_unit FROM expr)

SELECT 
	EXTRACT(YEAR FROM SYSDATE)  R1,
	EXTRACT(MONTH FROM SYSDATE) R2,
	EXTRACT(DAY FROM SYSDATE)   R3,
	EXTRACT(YEAR FROM TO_DATE('1997-07-17', 'yyyy-mm-dd'))  R4,
	EXTRACT(MONTH FROM TO_DATE('1997-07-17', 'yyyy-mm-dd')) R5,
	EXTRACT(DAY FROM TO_DATE('1997-07-17', 'yyyy-mm-dd'))   R6
FROM DUAL;

---->
R1	   R2   R3   R4     R5   R6
2020   8    12   1997   7    17

功能： 9i新增函数，从 expr 日期中获取年月日。
补充： ORACLE 没法获取 时分秒。
测试： OK.

         3.06 FROM_TZ(timestamp, timezone)

SELECT 
	FROM_TZ(timestamp '2020-08-12 20:36:00', '3:00') R1,
	FROM_TZ(timestamp '2020-08-12 20:36:00', '8:00') R2
FROM DUAL;

---->
R1									  R2
2020-08-12 20:36:00.000000000 +3:00   2020-08-12 20:36:00.000000000 +8:00

功能： 9i新增函数，将特定时区的TIMESTAMP值转换为TIMESTAMP WITH TIME ZONE值。
补充： 没看出这个有啥用来？？ 有的没的 都 试一遍的。。 哎 好苦逼。
测试： OK.

         3.07 LAST_DAY(date)

SELECT 
	LAST_DAY(SYSDATE) R1,
	LAST_DAY(TO_DATE(TO_CHAR(SYSDATE, 'yyyy-mm-dd'), 'yyyy-mm-dd')) R2,
	LAST_DAY(TO_DATE('1997-08-17', 'yyyy-mm-dd')) R3
FROM DUAL;

---->
R1					  R2					R3
2020-08-31 08:28:50   2020-08-31 00:00:00   1997-08-31 00:00:00

功能： 计算包含日期的 date 的月份最后一天的日期；
这个函数可以用来计算当月中剩余天数。
测试： OK.

         3.08 MONTHS_BETWEEN(date1, date2)

SELECT 
	MONTHS_BETWEEN(TO_DATE('2020-08-07', 'yyyy-MM-dd'), TO_DATE('2020-05-07', 'yyyy-MM-dd')) R1,
	MONTHS_BETWEEN(TO_DATE('2020-08-07', 'yyyy-MM-dd'), TO_DATE('2020-05-14', 'yyyy-MM-dd')) R2, /** 2个月24天(31-14+7=24), 24 / 31 ≈ 0.774193... */
	MONTHS_BETWEEN(TO_DATE('2020-08-31', 'yyyy-MM-dd'), TO_DATE('2020-04-30', 'yyyy-MM-dd')) R3
FROM DUAL;

---->
R1   R2			   R3
3    2.774193...   4

功能： 计算 date1 和 date2 之间相差的月数。
如果 date1 < date2，则返回负数；如果 date1，date2 这两个日期中日分量信息是相同的,或者这两个日期都分别是所在月的最后一天，那么返回的结果是一个整数；否则返回一个小数，小数为富余天数除以31，Oracle以每月31天为准计算结果。
测试： OK.

         3.09 NEW_TIME(date, tz1, tz2)

SELECT 
	SYSDATE CurrentTime,
	NEW_TIME(SYSDATE, 'PDT', 'GMT') R1,
	NEW_TIME(SYSDATE, 'CST', 'GMT') R2,
	NEW_TIME(SYSDATE, 'GMT', 'CST') R2
	
FROM DUAL;

---->
CurrentTime			  R1					R2				      R3
2020-08-14 16:37:03   2020-08-14 23:37:03   2020-08-14 23:37:03   2020-08-14 10:37:03

功能： 计算当时区 tz1 中的日期和时间是 date 时候，返回时区 tz2 中的日期和时间。
tz1 和 tz2 是字符串； 给出在 this时区=other时区的日期和时间。

补充：

时区1     时区2          说明

AST       ADT           大西洋标准时间

BST       BDT           白令海标准时间

CST       CDT           中部标准时间

EST       EDT           东部标准时间

GMT                     格林尼治标准时间

HST       HDT           阿拉斯加—夏威夷标准时间

MST       MDT           山区标准时间

NST                     纽芬兰标准时间

PST       PDT           太平洋标准时间

YST       YDT           YUKON标准时间

测试： OK.

         3.10 NEXT_DAY(date, ch)

SELECT 
	SYSDATE CurrentTime,
	TO_CHAR(SYSDATE, 'day') CurrentWeekLower,
	TO_CHAR(SYSDATE, 'DAY') CurrentWeekUpper,
	TO_NUMBER(TO_CHAR(SYSDATE - 1, 'd')) CurrentWeekNumber,
	NEXT_DAY(TO_DATE('2020-08-14', 'yyyy-MM-dd'), 'Saturday') R1, /** 下个星期六 */
	NEXT_DAY(TO_DATE('2020-08-14', 'yyyy-MM-dd'), 'Sunday')   R2, /** 下个星期天 */
	NEXT_DAY(TO_DATE('2020-08-14', 'yyyy-MM-dd'), 'Monday')   R3, /** 下个星期一 */
	NEXT_DAY(TO_DATE('2020-08-14', 'yyyy-MM-dd'), 'Tuesday')  R4, /** 下个星期二 */
	NEXT_DAY(TO_DATE('2020-08-14', 'yyyy-MM-dd'), 'Friday')   R5  /** 下个星期五 */
FROM DUAL;

---->
CurrentTime   	      CurrentWeekLower   CurrentWeekUpper   CurrentWeekNumber
2020-08-14 17:13:18   friday 			 Friday				5

R1					  R2					R3					  R4			        R5
2020-08-15 00:00:00   2020-08-16 00:00:00   2020-08-17 00:00:00   2020-08-18 00:00:00   2020-08-21 00:00:00

功能： 给出日期 date 和星期 ch 之后计算下一个星期 ch 的日期。
ch 是星期几；当前会话的语言指定了一周中的某一天；返回值的时间分量与 date 的时间分量是相同的；ch 的内容可以忽略大小写。

补充：

星期一 Monday
星期二 Tuesday
星期三 Wendnesday
星期四 Thursday
星期五 Friday
星期六 Saturday
星期天 Sunday

测试： OK.

         3.11 NUMTODSINTERVAL(n, time_unit)

SELECT 
	NUMTODSINTERVAL(100, 'MINUTE') R1,
	NUMTODSINTERVAL(100, 'SECOND') R2,
	NUMTODSINTERVAL(100, 'HOUR')   R3,
	NUMTODSINTERVAL(100, 'DAY')    R4
FROM DUAL;

---->
R1							R2									
+000000000 01:40:00.00...   +000000000 00:01:40.00...   

R3							R4
+000000000 04:00:00.00...   +000000100 00:00:00.00..

功能： 将数字 n 转换为 INTERNAL DAY TO SECOND格式。
time_unit 可以是 DAY \ HOUR \ MINUTE \ SECOND。
测试： OK.

         3.12 NUMTOYMINTERVAL(n, time_unit)

SELECT 
	NUMTOYMINTERVAL(100, 'MONTH') R1, /* 8年零4个月 */
	NUMTOYMINTERVAL(100, 'YEAR')  R2
FROM DUAL;

---->
R1			    R2
+000000008-04   +000000100-00

功能： 将数字 n 转换为INTERVAL YEAR TO MONTH格式。
time_unit 可以是 year / month。
测试： OK.

         3.13 ROUND(date, fmt)

SELECT 
	SYSDATE CurrentDay,
	ROUND(SYSDATE, 'YEAR')  R1,
	ROUND(SYSDATE, 'YY')    R2,
	ROUND(SYSDATE, 'MONTH') R3,
	ROUND(SYSDATE, 'DD')	R4,
	ROUND(SYSDATE, 'DAY')   R5, /* 天数 四舍五入, 最大值是30的. */
	ROUND(SYSDATE, 'HH')	R6
FROM DUAL;

---->
CurrentDay			  R1					R2
2020-08-31 23:03:23   2021-01-01 00:00:00   2021-01-01 00:00:00

R3					  R4					R5					  R6
2020-09-01 00:00:00   2020-09-01 00:00:00   2020-08-30 00:00:00   2020-08-31 23:00:00

功能： 将日期 date 按照由 fmt 指定的格式进行四舍五入处理处理；如果没有给 fmt 则使用缺省设置 DD （月份， 相当于 ‘MONTH’）.
测试： OK.

         3.15 SYS_EXTRACT_UTC(dateTimeTZ)

SELECT 
	SYSDATE CurrentTime,
	SYS_EXTRACT_UTC(SYSTIMESTAMP) R1
FROM DUAL;

---->
CurrentTime           R1
2020-09-25 15:19:32   2020-09-25 07:19:32.124133

功能： 返回特定时区时间所对应的格林威治时间。
补充： 格林威治时间，英国 皇家格林尼治天文台，比北京时间晚8个小时。
测试： OK.

         3.16 SYSDATE

SELECT 
	SYSDATE CurrentTime,
	TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24') R1
FROM DUAL;

---->
CurrentTime           R1
2020-09-25 15:26:23   2020-09-25 15

功能： 取得当前的日期和时间，类型是DATE。它没有参数。但在分布式SQL语句中使用时，SYSDATE 返回本地数据库的日期和时间。
测试： OK.

         3.17 SYSTIMESTAMP

SELECT 
	SYSDATE CurrentTime,
	SYSTIMESTAMP R1
FROM DUAL;

---->
CurrentTime           R1
2020-09-25 15:28:08   2020-09-25 15:28:08.354311 +08:00

功能： 9i新增函数，返回当前系统的日期时间及时区。
测试： OK.

         3.18 TO_DSINTERVAL(ch)

SELECT 
	SYSDATE CurrentTime,
	TO_DSINTERVAL('0 18:10:10') R1,
	SYSDATE + TO_DSINTERVAL('800 08:30:00') R2, /* 系统当前时间 加上 800天8小时30分钟 */
	SYSDATE + TO_DSINTERVAL('-800 08:30:00') R3 /* 前边加 符号, 天数减, 时间也是减的; 所以, 如果想加天数 减时间 需要在套一层了. */
	SYSDATE + TO_DSINTERVAL('1 00:00:00') R4, /* 加1天 */
	SYSDATE + TO_DSINTERVAL('30 00:00:00') R5 /* 加30天, 注意 这里可能不是加1个月的, 加1个月用 ADD_MONTHS */
FROM DUAL;

---->
CurrentTime    		  R1						      R2
2020-09-25 15:36:54   +000000000 18:10:10.000000000   2022-12-05 00:06:54

R3					  R4					R5
2018-07-18 07:06:54   2020-09-26 15:36:54   2020-10-25 15:36:54

功能： 9i新增函数，将符合特定日期和时间格式的字符串转变为INTERVAL DAY TO SECOND类型。
直接对日期 进行 准确的加减。
测试： OK.

         3.19 TO_TIMESTAMP(ch, fmt)

SELECT 
	SYSDATE CurrentTime,
	SYSTIMESTAMP,
	TO_CHAR(SYSTIMESTAMP, 'yyyy-mm-dd hh24:mi:ss.ff') R1,
	TO_TIMESTAMP('2018-10-31 12:52:42.1234567', 'yyyy-mm-dd hh24:mi:ss.ff') R2,
	CAST(TO_TIMESTAMP('2018-10-31 12:52:42.1234567', 'yyyy-mm-dd hh24:mi:ss.ff') AS DATE) R3, /*  TIMESTAMP 转　DATE */
	CAST(TO_DATE('2018-10-31 12:52:42', 'yyyy-mm-dd hh24:mi:ss') AS TIMESTAMP) R4　/* DATE 转 TIMESTAMP */
FROM DUAL;

---->
CurrentTime   	      SYSTIMESTAMP   					  R1
2020-09-25 17:12:50   2020-09-25 17:19:45.683359 +08:00   2020-09-25 17:19:45.683359

R2								R3				      R4
2018-10-31 12:52:42.123456700   2018-10-31 12:52:42   2018-10-31 12:52:42.000000

功能： 9i新增函数，将符合特定日期和时间格式的字符串转变为TIMESTAMP类型。
补充： TO_DATE 和 TO_TIMESTAMP 的区别： TO_TIMESTAMP 可以到 毫秒，TO_DATE 最多到分。
TIMESTAMP 是 DATE 的扩展类型，能支持到毫秒，毫秒的显示精度是6位，不过有效位是3位，即最大值达到999，满1000ms就进为1s。

而与 TO_DATE 对应的转换函数可以使用 TO_TIMESTAMP。两个 DATE 相减得到是两个时间的间隔，单位是天，两个 TIMESTAMP 相减的话，不能直接的得到天数，而是得到多少天，多少小时，多少秒，多少毫秒等。
测试： OK.

         3.20 TO_TIMESTAMP_TZ(ch, fmt)

SELECT 
	SYSTIMESATMP R1,
	SYSTIMESTAMP AT TIME ZONE SESSIONTIMEZONE R2,
	TO_TIMESTAMP('20181013', 'yyyymmdd') R3
FROM DUAL;

---->
R1									R2									R3
2020-09-26 09:07:24.175936 +08:00   2020-09-26 09:07:24.175936 +08:00   2018-10-13 00:00:00.000000000

功能： 9i新增函数，将符合特定日期和时间格式的字符串转变为TIMESTAMP WITH TIME ZONE类型。
测试： OK.

         3.21 TO_YMINTERVAL(ch)

SELECT 
	SYSDATE,
	TO_YMINTERVAL('02-08') R1, /* 参数格式要求是 'xx-xx' */
	SYSDATE + TO_YMINTERVAL('02-00') R2, /* 当前时间加 2年0个月 */
	SYSDATE + TO_YMINTERVAL('00-03') R3, /* 当前时间加 0年3个月 */
	SYSDATE - TO_YMINTERVAL('00-04') R4  /* 当前时间减 0年4个月 */
FROM DUAL;

---->
SYSDATE   			  R1			  R2
2020-09-26 09:17:45   +000000002-08   2022-09-26 09:17:45
R3					  R4
2020-12-26 09:17:45   2020-05-26 09:17:45

功能： 9i新增函数，将符合特定日期和时间格式的字符串转变为INTERVAL YEAR TO MONTH类型。
加减年份和月份的。
测试： OK.

         3.22 TRUNC

SELECT 
	SYSDATE,
	TRUNC(SYSDATE, 'hh') R1,
	TRUNC(SYSDATE, 'mi') R2,
	TRUNC(SYSDATE, 'dd') R3,
	TRUNC(SYSDATE, 'mm') R4,
	TRUNC(SYSDATE, 'yyyy') R5,
	TO_CHAR(TRUNC(SYSDATE, 'hh'), 'yyyy/mm/dd hh24:mi:ss') R6
FROM DUAL;

---->
SYSDATE   			  R1					R2
2020-09-26 09:28:31   2020-09-26 09:00:00   2020-09-26 09:28:00  

R3					  R4					R5					  R6
2020-09-26 00:00:00   2020-09-01 00:00:00   2020-01-01 00:00:00   2020/09/26 09:00:00

功能： 截断日期时间数据，计算截尾到由 fmt 指定单位的日期 date。缺省参数同ROUNG。如果 fmt=‘mi’ 表示保留分，则截断秒，如此类推。
测试： OK.

         3.23 TZ_OFFSET(timezone)

SELECT 
	TZ_OFFSET('UTC') R1,
	TZ_OFFSET('EST') R2 /* 时区缩写与UTC（GMT）时差对照表 中 EST = UTC-5 */
FROM DUAL;

---->
R1		 R2
+00:00   -05:00

功能： 9i新增函数，返回特定时区与 UTC 相比的时区偏移。
补充： 时区缩写与UTC (GMT) 时差对照表
测试： OK.

     04. 转换函数

说明：用于将数值从一种数据类型转换为另一种数据类型。

         4.01 ASCIISTR(ch)

SELECT 
	ASCIISTR('A') R1,
	ASCII('A') R2, /* 将首字母字符转成 ASCII码值 */
	ASCIISTR('A中国') R3,
	ASCII('A中国') R4
FROM DUAL;

---->
R1   R2	  R3		    R4
A    65   A\4E2D\56FD   65

功能： 9i新增函数，将任意字符集的字符串转变为数据库字符集的ASCII字符串。
测试： OK.

         4.02 BIN_TO_NUM(expr1, … exprn)

SELECT 
	BIN_TO_NUM(1) R1,
	BIN_TO_NUM(1, 0) R2,
	BIN_TO_NUM(1, 1) R3,
	BIN_TO_NUM(1, 0, 0) R4
FROM DUAL;

---->
R1   R2   R3   R4
1    2    3    4

功能： 9i新增函数，二进制转十进制。
测试： OK.

         4.03 CAST(expr AS type)

SELECT 
	CAST('能抵挡末日的残酷' AS VARCHAR2(10)) R1, /* 按照长度截取 */
	CAST('能抵挡末日的残酷' AS VARCHAR2(255)) R2,
	CAST(123 AS VARCHAR2(5)) R3, /* INT 转 VARCHAR2, 如果长度 <3 会报错的. */
	CAST('13' AS INT) R4, /* 字符串 转 INT */
	CAST('13.46' AS INT) R5, /* 结果是 四舍五入 */
	CAST('13.56' AS INT) R6
FROM DUAL;

---->
R1      R2			    R3    R4   R5   R6
能抵挡   能抵挡末日的残酷   123   13   13   14

功能： 用于将一个内置数据类型或集合类型转变为另一个内置数据类型或集合类型。

可以作用于长度为0的空字段视图建表格用。

Create table tb_dual nologging as Select cast(null as varchar2(1)) from dual;

高级用法查看

测试： OK.

         4.04 CHARTOROWID(ch)

SELECT * FROM TEST;

---->
ID   AGE   NAME
1    18    ces
2    11    11


SELECT ROWID, ROWIDTOCHAR(ROWID) R1, AGE, NAME FROM TEST;

---->
ROWID			     R1                   AGE   NAME
AABEKZAE1AAAAOzAAA   AABEKZAE1AAAAOzAAA   18    ces
AABEKZAE1AAAAO2AAA   AABEKZAE1AAAAO2AAA   11    11

功能： 将字符数据类型转换为 ROWID 类型,，把包含外部格式的 ROWID 的 CHAR 或 VARCHAR2 数值转换为内部的二进制格式。参数 ch 必须是包含外部格式的 ROWID 的18字符的字符串。

oracle7和oracle8中的外部格式是不同的，CHARTOROWID 是 ROWIDTOCHAR的反函数。
测试： OK.

         4.05 COMPOSE(ch)

功能： 9i新增函数，用于将输入字符串转变为 UNICODE 字符串值。 ch 输入啥 就 return 啥。
测试： OK.

         4.06 CONVERT(ch, dest_charset, source_charset)

SELECT 
	CONVERT('中国', 'US7ASCII', 'WE8ISO8859P1') R1
FROM DUAL;

---->
R1
a?-???

功能： 将字符串 ch 从source_charset 所表示的字符集转换为由 dest_charset 所表示的字符集。

如果 source_charset 没有被指定，它缺省的被设置为数据库的字符集。
测试： OK.

         4.07 DECOMPOSE(str)

功能： 9i新增函数，用于分解字符串并返回相应的UNICODE字符串。
测试： 没测出来是干啥的用。

         4.08 HEXTORAW(ch)

SELECT 
	RAWTOHEX(HEXTORAW('12')) R1,
	RAWTOHEX(HEXTORAW(16)))  R2,
	RAWTOHEX(HEXTORAW('A1')) R3
FROM DUAL;

---->
R1   R2   R3
12   16   A1

功能： 将 ch 一个十六进制构成的字符串转换为二进制RAW数值。ch 中的每两个字符表示了结果RAW中的一个字节。HEXTORAW 和 RAWTOHEX 为相反的两个函数。

补充： 16进制，参数只能输入 0-9 和 A-F.

测试： OK.

         4.09 INTERVAL

INTERVAL '123-2' YEAR(3) TO MONTH

表示: 123年2个月, "YEAR(3)" 表示年的精度为3, 可见"123"刚好为3为有效数值, 如果该处YEAR(n), n<3就会出错, 注意默认是2.
结果：123-02

 

INTERVAL '11:12:10.1234567' HOUR TO SECOND

表示：小时，秒
结果：+00 11:12:10.123457

 

INTERVAL '123' YEAR(3)

表示: 123年0个月
结果：+123-00
 


INTERVAL '300' MONTH(3)

表示: 300个月, 注意该处MONTH的精度是3啊.
结果：+025-00 (300个月就是25年)

 

INTERVAL '4' YEAR

表示: 4年, 同 INTERVAL '4-0' YEAR TO MONTH 是一样的,注意 YEAR默认是2
结果：+04-00

 

INTERVAL '50' MONTH

表示: 50个月, 同 INTERVAL '4-2' YEAR TO MONTH 是一样, 注意 MONTH默认是2
结果：+04-02  (50个月就是 4年零2个月)

 

INTERVAL '123' YEAR

表示: 该处表示有错误, 123精度是3了, 但系统默认是2, 所以该处应该写成 INTERVAL '123' YEAR(3) 或"3"改成大于3小于等于9的数值都可以的

 

INTERVAL '5-3' YEAR TO MONTH + INTERVAL '20' MONTH =
INTERVAL '6-11' YEAR TO MONTH

表示: 5年3个月 + 20个月 = 6年11个月

功能： 该数据类型常用来表示一段时间差，注意时间差只精确到年和月。 precision为年或月的精确域， 有效范围是0到9，默认值为2。
测试： OK.

         4.10 RAWTONHEX(raw)

SELECT 
	RWATONHEX('7D') R1,
	HEXTORAW('3744') R2,
	RAWTOHEX('7D') R3,
	RAWTOHEX('1') R4,
	RAWTOHEX('2') R5,
	RAWTOHEX('3') R6,
	RAWTOHEX('4') R7
FROM DUAL;

---->
R1     R2   R3     R4   R5   R6   R7
3744   7D   3744   31   32   33   34

功能： 9i新增函数，将 RAW 类数值 raw 转换为一个相应的十六进制表示的字符串。 raw 中的每个字节都被转换为一个双字节的字符串。 RAWTONHEX 和 HEXTORAW是两个相反的函数。
测试： OK.

         4.11 ROWIDTOCHAR(rowid)

SELECT * FROM TEST;

---->
ID   AGE   NAME
1    18    ces
2    11    11


SELECT ROWID, ROWIDTOCHAR(ROWID) R1, AGE, NAME FROM TEST;

---->
ROWID			     R1                   AGE   NAME
AABEKZAE1AAAAOzAAA   AABEKZAE1AAAAOzAAA   18    ces
AABEKZAE1AAAAO2AAA   AABEKZAE1AAAAO2AAA   11    11

功能： 9i新增函数，将 ROWID 类型的数值 rowid 转换为 varchar2 的字符串表示，在 oracle7 和 oracle8 之间有些不一样的地方。 ROWIDTOCHAR 和 CHARTOROWID 是两个相反的函数。
测试： OK.

         4.12 ROWIDTONCHAR(rowid)

SELECT * FROM TEST;

---->
ID   AGE   NAME
1    18    ces
2    11    11


SELECT ROWID, ROWIDTONCHAR(ROWID) R1, AGE, NAME FROM TEST;

---->
ROWID			     R1                   AGE   NAME
AABEKZAE1AAAAOzAAA   AABEKZAE1AAAAOzAAA   18    ces
AABEKZAE1AAAAO2AAA   AABEKZAE1AAAAO2AAA   11    11

功能： 9i新增函数，将 ROWID 类型的数值 rowid 转换为 Nvarchar2 的字符串表示，在 oracle7 和 oracle8 之间有些不一样的地方。
测试： OK.

         4.13 SCN_TO_TIMESTAMP(n)

SELECT 
	SCN_TO_TIMESTAMP(49413363909) R1, /* 取自 4.14 TIMESTAMP_TO_SCN 的值 */
	SCN_TO_TIMESTAMP(49413364472) R2,
	SCN_TO_TIMESTAMP(49413364591) R3,
	SCN_TO_TIMESTAMP(49413364591) R4,
	SCN_TO_TIMESTAMP(49416820735) R5
FROM DUAL;

---->  /* 可以看出 转换后的时间 并不和 原来的(4.14 TIMESTAMP_TO_SCN)完全相等 */
R1								R2								R3
2020-10-08 08:43:37.000000000   2020-10-08 08:43:58.000000000   2020-10-08 08:44:01.000000000

R4								R5
2020-10-08 08:44:01.000000000   2020-10-08 09:43:59.000000000

功能： 10g新增函数，根据输入的 scn 值返回对应的大概日期时间，其中 number 用于指定 scn 值。

测试： OK.

         4.14 TIMESTAMP_TO_SCN(timestamp)

SELECT 
	TIMESTAMP_TO_SCN(TO_DATE('2020-10-08 08:43:39', 'yyyy-mm-dd hh24:mi:ss')) R1,
	TIMESTAMP_TO_SCN(TO_DATE('2020-10-08 08:44:00', 'yyyy-mm-dd hh24:mi:ss')) R2,
	TIMESTAMP_TO_SCN(TO_DATE('2020-10-08 08:44:01', 'yyyy-mm-dd hh24:mi:ss')) R3,
	TIMESTAMP_TO_SCN(TO_DATE('2020-10-08 08:44:02', 'yyyy-mm-dd hh24:mi:ss')) R4,
	TIMESTAMP_TO_SCN(TO_DATE('2020-10-08 09:44:00', 'yyyy-mm-dd hh24:mi:ss')) R5
FROM DUAL;

---->
R1			  R2		    R3 			  R4 			R5
49413363909   49413364472   49413364591   49413364591   49416820735

功能： 10g新增函数，用于根据输入的 timestamp 返回所对应的 scn 值，其中 timestamp 用于指定日期时间。

补充：

Oracle中的SCN（system change number）和我们的北京时间的意义是相同的，SCN是Oracle中的时间号。

为什么Oracle不用时间来界定呢？

我在北京时间8：00的时候执行一条DML语句，然后修改机器上的时间为7：00，再执行一条DML语句。如果用机器上的时间区分的话，那Oracle根本区分不出来这两条DML语句的执行顺序——而这一点对于Oracle是很重要的。所以它采用自己产生的SCN来区分所有操作的先后顺序。

Oracle ORA_ROWSCN 行记录的更新时间

测试： OK.

         4.15 TO_CHAR(x)

/* 功能1 */
SELECT 
	TO_CHAR(n'风') R1,
	TO_CHAR(N'雨') R2
FROM DUAL;

---->
R1   R2
风   雨


/* 功能2 */
SELECT  
        ename, 
        to_char(system, 'yyyy') 年，
		to_char(hiredate, 'mm') 月，
		to_char(hiredate, 'dd') 日，
		to_char(hiredate, 'HH') 时，
		to_char(hiredate, 'mi') 时，
		to_char(hiredate, 'ss') 时，
FROM emp; 

SELECT to_char(sysdate, 'yyyy-mm-dd HH24:mi:ss') FROM dual;  /* 24小时制 */
SELECT to_char(sysdate, 'yyyy-mm-dd HH:mi:ss') FROM dual;    /* 12小时制 */

SELECT ename, to_char(hiredate, 'fmyyyy-mm-dd') FROM emp; /* fm去掉 前导0， 比如月份 09  就成了 9*/

SELECT 
	SYSDATE, /* 2020-10-08 10:33:49 */
	TO_CHAR(SYSDATE, 'yyyy-mm-fmdd hh24:mi:ss')  /* 2020-10-8 10:33:49 */
FROM DUAL


/* 功能4：格式化日期 */
SELECT 
	SYSDATE,
	TO_CHAR(SYSDATE, 'fmDdthsp "of" Month, Year') R1, /* 需要注意的是不属于转换日期格式标识符需要使用双引号，如 "of" */
	TO_CHAR(SYSDATE, 'Ddthsp "of" Month, Year') R2,   /* Ddthsp的前缀fm 去掉. 月份后边会带着2个空格. */
	TO_CHAR(SYSDATE, '"年份: " Year ", 月份: " Month ", 哪一天: " fmDdthsp' ) R3,
	
	TO_CHAR(TO_DATE('2019-08-08', 'yyyy-mm-dd'), 'Month') R4, /* 英文月份; Month、Day转换后 后边会带着空格的, 可以使用 fmMonth (有trim的作用) 去掉空格; Year转换 不会带空格. */
	TO_CHAR(TO_DATE('2019-08-08', 'yyyy-mm-dd'), 'mm') R5,
	TO_CHAR(TO_DATE('2019-08-08', 'yyyy-mm-dd'), 'yyyy') R6,
	TO_CHAR(TO_DATE('2019-08-08', 'yyyy-mm-dd'), 'Year') R7   /* 英文年份 */
FROM DUAL

---->
SYSDATE      		  R1   								 R2
2020-10-08 10:43:05   Eighth of October  , Twenty Twenty   Eighth of October, Twenty Twenty

R3
年份: Twenty Twenty , 月份: October , 哪一天: Eighth

R4		    R5   R6	    R7
August      08   2019   Twenth Nineteen


/* 功能4：格式化数字 */
SELECT
	TO_CHAR('13140', '99,999') R1,
	TO_CHAR('13140', '99G999') R2,
	TO_CHAR('13140', '$99,999') R3,
	TO_CHAR('13140', 'L99,999') R4,
	'$' || TO_CHAR('13140', '99,999') R5, /* 拼接 TO_CHAR的结果, 中间会有1个空格; 如果只单独的拼接字符(如 '$' || '13140') 是不会有空格的. 所以 不如上面加一个L 方便的多. */
	'￥' || TO_CHAR('13140', '99,999') R6,
	REPLACE('￥' || TO_CHAR('13140', '99,999'), ' ', '') R7  /* 去掉所有的空格. */

FROM DUAL

---->
R1       R2       R3        R4        R5         R6          R7
13,140   13,140   $13,140   $13,140   $ 13,140   ￥ 13,140   ￥13,140

功能：

功能1:用于将NCHAR,NVARCHAR2,CLOB,NCLOB数据转变为数据库字符集数据，当用于NCHAR,NVARCHAR2,NCLOB时字符用单引号括起来，前面加上n。

功能2:将日期d转换为一个VARCHAR2类型的字符串.format指定日期格式,.如果没有给定format,使用的就是该会话的缺省日期格式.nlsparams指定NLS参数. nlsparams的格式是:“NLS_DATE_LANGUAGE”

功能3:将MISLABEL的LABEL转换为一个VARCHAR2类型的变量.

使用位置:在trusted数据库的过程性语句和SQL语句。

功能4:将NUMBER类型的参数num转换为一个VARCHAR2类型的变量.如果指定了format,那么它会控制这个转换处理.表5-5列除了可以使用的数字格式.如果没有指定format,它会控制这个转换过程.下面列出了可以使用的数字格式.如果没有指定format,那么结果字符串将包含和num中有效位的个数相同的字符. nlsparams用来指定小数点和千分位分隔符和货币符号.可以使用的格式:`NLS_NUMERIC_CHARS=”dg”NLS_CURRENCY=”string”

d和g分别表示列小数点和千分位分隔符. String表示了货币的符号.例如,在美国小数点分隔符通常是一个句点(.),分组分隔符通常是一个逗号(,),而千分位符号通常是一个$.

测试： OK.

         4.16 TO_CLOB(ch)

SELECT 
	TO_CLOB(n'风') R1,
	TO_CLOB(N'风') R2,
	TO_CLOB('风') R3
FROM DUAL;

---->
R1   R2   R3
风   风    风

功能： 9i新增函数，将字符串转变为 CLOB 类型。ch 参数使用 NCHAR、NVARCHAR2、NCLOB 类型，字符串需要单引号括起来，且在前面加上n.
测试： OK.

         4.17 TO_DATE(ch, fmt)

SELECT 
	SYSDATE,
	TO_DATE(SYSDATE) R1,
	TO_DATE('01-JANUARY-2007') R2, /* 正确的缺省值格式. */
	TO_DATE('01-JANURAY-2007') R3,
	TO_DATE('1985-04-22', 'yyyy-mm-dd') R4,
	TO_DATE('1985-04-22 12:30:20', 'yyyy-mm-dd HH24:mi:ss') R5
FROM DUAL;

---->
SYSDATE   	          R1				    R2
2020-10-08 16:50:50   2020-10-08 00:00:00   2007-01-01 00:00:00

R3					  R4					R5
2007-01-01 00:00:00   1985-04-22 00:00:00   1985-04-22 12:30:20

功能： 将符合特定日期格式的字符串转变为 date 类型。 fmt 是一个日期格式字符串，当不指定 fmt 的时候，使用该会话的缺省日期格式，需要特别注意的，缺省格式并不适用’2015-03-03’这种形式。
测试： OK.

         4.18 TO_LOB(long)

/* 例子：to_lob转化long */

select VIEW_NAME,to_lob(text) text from user_views;  --会报错

create table temp_liutao nologging as select VIEW_NAME,to_lob(text) text from user_views  --通过

功能： 9i新增函数，将 LONG 或 LONG ROW 列的数据转变为相应的 LOB 类型。但需要注意的是，在单纯的select 语句中会报错，如例子所示。

         4.19 TO_MULTI_BYTE(ch)

SELECT 
	TO_MULTI_BYTE('1234d') R1
FROM DUAL;

----> 
R1
１２３４ｄ (半角：1234d)

功能： 将字符串中的半角转化为全角，只是针对字母和数字有效。

测试： OK.

         4.20 TO_NUMBER(expr, fmt)

SELECT 
	TO_NUMBER('1314') R1,
	TO_NUMBER('$1314', '$9999') R2,
	TO_NUMBER('$1314', 'L9999') R3,
	TO_NUMBER('$1314.25', 'L9999.99') R4,
	TO_NUMBER('$1314.25', 'L9999.00') R5,
	TO_NUMBER('1,314', '9,999') R6, /* 这个千位分隔符没效果, 用 TO_CHAR */
	TO_NUMBER('a', 'x') R7, /* 十六进制 转换成 十进制 */
	TO_NUMBER('f', 'x') R8,
	TO_NUMBER('ff', 'xx') R9, /* 15 * 1 + 15 * 16 = 255 */
	TO_CHAR('100', 'xxx') R10 /* 十进制 转换成 十六进制. */
FROM DUAL;

---->
R1     R2     R3     R4        R5        R6     R7   R8   R9    R10
1314   1314   1314   1314.25   1314.25   1314   10   15   255   64

功能： 将 CHAR 或者 VARCHAR2 类型的 String 转换为一个 NUMBER 类型的数值。如果指定了 format，那么String 应该遵循相应的数字格式。Nlsparams 的行为方式和 TO_CHAR 中的完全相同。TO_NUMBER 和 TO_CHAR 是两个相反的函数。
测试： OK.

         4.21 TO_SINGLE_BYTE(ch)

SELECT 
	TO_SINGEL_BYTE('１２３４ｄ') R1
FROM DUAL;

---->
R1
1234d

**功能： **将字符串中的全角转化为半角，只是针对字母和数字有效。
测试： OK.

         4.22 UNISTR(str)

SELECT 
	ASCIISTR('中') R1,
	ASCIISTR('国') R2,
	UNISTR('\4E2D\56FD') R3
FROM DUAL;

---->
R1      R2      R3
\4E2D   \56FD   中国

功能： 9i新增函数，输入UNICODE编码 转换 中文。

补充： Unicode 值 \u开头的， 如 中： \u4E2D.

测试： OK.