devbox
笔触狂放9
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

Oracle的学习心得和知识总结（三）|Oracle数据库重要函数详解 单行函数（一）（建议收藏 持续更新...）_单行函数学习心得

作者：笔触狂放9 | 2024-05-17 18:43:00

赞

踩

单行函数学习心得
目录结构

注：提前言明 本文借鉴了以下博主、书籍或网站的内容，其列表如下：

1、Oracle Database Documentation，点击前往
2、修改oracle 数据库默认时间格式，点击前往
注：本文大家可以根据需要 复制粘贴以及作为其他个人用途，但不允许转载 （写作不易，还请见谅ヽ(*ｰ)人(ｰ*)ノ）

Oracle数据库重要函数

文章快速说明索引

学习目标：

目的：因为接下来想在PostgreSQL的兼容扩展orafce上实践实现一下 Oracle有的而PostgreSQL没有的一些函数。Orafce作为一款开源插件且一直在维护中 功能很强大 实用性极高，大家有兴趣也可以去看看！本文主要记录在Oracle数据库上面的 重要函数 的使用体验和基于开发者的设计思考，以期在PostgreSQL数据库的Orafce上面支持这些函数 关键字等功能！

意思就是说：本文主要是记录Oracle函数（官方手册 第5章 Functions，点击前往）的使用和注意点，关于Orafce的介绍和在Orafce上做的新的开发会在其他博客里面说明。本文将会是一个 进行时 的状态，持续记录Oracle的重要函数的使用和特性。

学习内容：（详见目录）

1、Oracle数据库的函数部分（第一部分 单行函数(Single-Row Functions)的数值函数和日期函数）

学习时间：

2020年11月21日13:22:13

学习产出：

1、Oracle数据库重要函数的学习
2、CSDN 技术博客 1篇
3、PostgreSQL数据库Orafce插件新特性功能实现设计思考

学习之前背景说明

鉴于Internet上面内容繁多 鱼龙混杂且众说纷纭，我们这里学习Oracle函数将采用 Oracle官方文档+Oracle数据库实际操作 的方式进行铺展。

下面是一个展示如何在Oracle在线文档上查看所需函数的步骤：

1、Oracle Database Documentation，点击前往
2、然后选择自己使用的数据库版本
3、选择Application Development
4、选择SQL and PL/SQL里面的SQL Language Reference
5、目录里面的5 Functions就是所需的函数章节

下面是使用在线文档查看SYSDATE函数的使用教程：

在这里插入图片描述
我们这里的学习环境是Centos7 + Oracle11g：

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE	11.2.0.1.0	Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

Oracle的函数详解

注：这里的顺序（最终版）会严格的按照上面Oracle官方文档的顺序，因为工作量大 优先级和其他需要的缘故，我们的文档会暂时只铺排一部分（将来肯定是全部）内容。

函数 function 类似于运算符，它们操作数据项（即：对零个、一个、两个或多个参数）并返回结果，但是函数与运算符的参数格式不同。其语法格式如下：

function(argument1, argument2, ...) 

  • 1

关于 SQL 函数

SQL函数内置于Oracle数据库中，可以在各种适当的SQL语句中使用。不要混淆SQL函数和用PL/SQL编写的用户定义函数。如果调用的SQL函数的参数类型不是SQL函数期望的数据类型，则Oracle会在执行SQL函数之前尝试将参数转换为期望的数据类型。

而这就涉及到数据类型转换的范畴，通常情况下 表达式不能包含不同数据类型的值。但是Oracle支持值从一种数据类型到另一种数据类型的隐式和显式转换（但是Oracle建议您指定显式转换，而不要依赖隐式或自动转换）。两种转换：隐式和显式数据转换比较如下所示：

1、使用显式数据类型转换函数时，SQL语句更易于理解
2、隐式数据类型转换可能会对性能产生负面影响，尤其是如果将列值的数据类型转换为常量而不是相反的数据类型
3、隐式转换取决于发生转换的上下文，并且在每种情况下都可能无法以相同的方式起作用。例如，从日期时间值到VARCHAR2值的隐式转换可能返回意外的年份，具体取决于NLS_DATE_FORMAT参数的值
4、隐式转换算法可能会在软件版本之间以及Oracle产品之间发生变化。而明确转换的行为则更容易预测
5、如果在索引表达式中发生隐式数据类型转换，则Oracle数据库可能不使用该索引，因为它是为转换前数据类型定义的。这可能会对性能产生负面影响

隐式数据类型转换:

下图是Oracle隐式转换的矩阵表。该表显示了所有可能的转换，而与转换的方向或进行转换的上下文无关。在此规则的约束下，Oracle数据库会自动将值从一种数据类型转换为另一种数据类型：
在这里插入图片描述

字符串类型间的转换如下图所示：

在这里插入图片描述
需要注意的点有如下这些：

1、不能直接转换 LONG 为INTERVAL，但可以使用TO_CHAR(interval)来将LONG转换VARCHAR2，然后将结果值VARCHAR2转换为INTERVAL
2、在INSERT和UPDATE操作期间，Oracle将值转换为受影响列的数据类型
3、在SELECT FROM操作期间，Oracle将列中的数据转换为目标变量的类型
4、在处理数值时，Oracle通常会调整精度和小数位数，以实现最大容量。在这种情况下，由此类操作产生的数字数据类型可能与在基础表中找到的数字数据类型有所不同
5、在将字符值与数字值进行比较时，Oracle将字符数据转换为数字值
6、字符值或NUMBER值与浮点数值之间的转换可能是不精确的，因为字符类型 和 NUMBER使用十进制精度表示numeric数值，而浮点数则使用二进制精度
7、将CLOB值转换为字符数据类型（例如VARCHAR2）或转换BLOB为RAW数据时，如果要转换的数据大于目标数据类型，则数据库返回错误
8、在从时间戳值转换为DATE值的过程中，时间戳值的小数秒部分被截断。此行为与Oracle数据库的早期版本不同，后者将时间戳值的小数秒部分进行了四舍五入
9、从BINARY_FLOAT到的转换BINARY_DOUBLE是准确的
10、如果BINARY_DOUBLE值使用多于BINARY_FLOAT所支持的精度位，则从BINARY_DOUBLE到BINARY_FLOAT的转换是不精确的
11、将字符值与DATE值进行比较时，Oracle会将字符数据转换为DATE
12、当你传递给 SQL函数 或 运算符 使用的参数类型与其接受的数据类型不同时，Oracle会将参数转换为可接受的数据类型
13、进行分配时，Oracle将等号（=）右侧的值转换为左侧分配目标的数据类型
14、在串联操作期间，Oracle将noncharacter数据类型转换为CHAR或NCHAR
15、在对character 和 noncharacter数据类型进行算术运算 或者 进行比较时，Oracle会根据需要从任何character数据类型转换为numeric date rowid。在CHAR/VARCHAR2 和 NCHAR/NVARCHAR2之间的算术运算中，Oracle转换为NUMBER
16、大多数SQL字符函数都可以接受CLOB作为参数，并且Oracle在CLOB和字符类型之间执行隐式转换。因此，尚未为CLOB启用的函数可以通过隐式转换来接受CLOB。在这种情况下，Oracle会将CLOB转换为CHAR或VARCHAR2在调用函数之前。如果CLOB大于4000字节，则Oracle仅将前4000字节转换为CHAR
17、CHAR 和 VARCHAR2之间以及NCHAR 和 NVARCHAR2类型之间的比较可能需要不同的字符集。在这种情况下，默认的转换方向是从数据库字符集到国家字符集。上面第二个图就显示了不同字符类型之间隐式转换的方向
18、不能隐式转换诸如集合之类的用户定义类型，但必须要强制转换 就需要使用CAST ... MULTISET

显式数据类型转换：

可以使用SQL转换函数来显式地指定数据类型的转换。下图展示了：显式地将值从一种数据类型转换为另一种数据类型的SQL函数。在Oracle可以执行隐式数据类型转换的情况下，不可以指定 LONG 和 LONG RAW。例如，LONG和LONG RAW不能出现在包含函数或运算符的表达式中。

在这里插入图片描述
如上，转换函数将值从一种数据类型转换为另一种数据类型。通常，函数名的形式遵循数据类型到数据类型的约定。第一个数据类型是输入数据类型，第二种数据类型是输出数据类型。

注：上图中的这些函数也将是我们接下来学习的内容（后面会讲到的），对此感兴趣的小伙伴们可以直接 Conversion Functions，点击前往。

下面看一下数据转换中的安全注意事项：

1、当datetime值通过隐式转换或显式转换（未指定格式模型）转换为文本时，格式模型由其中一个全球化会话参数来定义。根据源数据类型，参数名称为NLS_DATE_FORMAT、NLS_TIMESTAMP_FORMAT 或 NLS_TIMESTAMP_TZ_FORMAT。这些参数的值可以在客户机环境 或 在ALTER SESSION语句中指定
2、当不使用显式格式模型的转换应用于连接到动态SQL语句文本的datetime值时，格式模型在会话参数上的依赖性可能会对数据库安全性产生负面影响。动态SQL语句指的是：在传递到数据库执行之前的那些文本片段连接起来的语句。动态SQL经常与内置的PL/SQL包DBMS_SQL 或 带有PL/SQL语句EXECUTE IMMEDIATE相关联，但动态构建的SQL文本并不是唯一可以作为参数传递的地方。

举一个例子：

EXECUTE IMMEDIATE
'SELECT last_name FROM employees WHERE hire_date > ''' || start_date || '''';


# where start_date has the data type DATE.

  • 1
  • 2
  • 3
  • 4
  • 5

在上面的示例中，start_date的值使用 session参数 NLS_date_format 中指定的格式模型被转换为文本。转换的结果被连接到SQL文本中。日期时间格式模型可以为简单地由双引号括起来的文本组成。

1、因此，任何可以为一个会话显式设置全球化参数的用户都可以决定由上述转换生成的文本（的这个过程）
2、但是如果SQL语句是由PL/SQL过程执行的，那么该过程很容易受到通过session参数进行的SQL注入攻击
3、如果该过程以definer的权限运行，并且具有比会话本身更高的权限，则这个用户可以获得对敏感数据的未经授权的访问能力
4、这种安全风险也适用于中间层应用程序，这些应用程序从数据库或OCI datetime函数转换为文本的datetime值来构造SQL文本。如果从用户首选项获取会话全球化参数，则这些应用程序也易受攻击
5、numeric 类型的值的隐式和显式转换也可能遇到类似的问题，因为转换结果可能取决于会话参数NLS_NUMERIC_CHARACTERS。此参数定义十进制和组分隔符。如果十进制分隔符被定义为引号或双引号，则可能出现SQL注入

SQL函数中的空值 ：

给定null参数时，大多数标量函数都将返回null。当然这里我们可以使用NVL函数 或 NVL2函数在出现null时返回一个值。具体如下：

NVL(commission_pct,0)

# 功能：从两个表达式返回一个非 null 值

# 如果commission_pct为null ，则表达式返回0
# 如果不为null ，则表达式返回值commission_pct

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

而NVL2的语法格式如下：

# nvl2函数的是nvl函数的拓展，基本语法为：

nvl2(commission_pct1,commission_pct2,commission_pct3)

# 意思是commission_pct1为null,就返回commission_pct3,不为null就返回commission_pct2

  • 1
  • 2
  • 3
  • 4
  • 5

而函数的划分则分为以下几种：

{ single_row_function
| aggregate_function
| analytic_function
| object_reference_function
| model_function
| user_defined_function
| OLAP_function
| data_cartridge_function
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

而我们主要的关注点是放在 Single-Row Functions 上，下面是每一种函数的详细展开：

单行函数(Single-Row Functions)

所谓单行函数，指的是：单行函数为查询的表或视图的每一行返回一个结果行。这些函数可以出现在选择列表、WHERE子句、START WITH和CONNECT BY子句以及HAVING子句中。

这一部分所包含的内容很多，诸如：

{ numeric_function
| character_function
| datetime_function
| comparison_function
| conversion_function
| large_object_function
| collection_function
| hierarchical_function
| data_mining_function
| XML_function
| encoding_decoding_function
| NULL_related_function
| environment_id_function
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14

数值函数(numeric_function)

数值函数功能：接受数值输入并返回数值。

1、大多数数值函数返回的数值精确到38位十进制数字
2、超越函数COS COSH EXP LN LOG SIN SINH SQRT TAN TANH精确到36位小数
3、超越函数ACOS ASIN ATAN ATAN2精确到30位小数

数值函数有:

ABS
ACOS
ASIN
ATAN
ATAN2
BITAND
CEIL
COS
COSH
EXP
FLOOR
LN
LOG
MOD
NANVL
POWER
REMAINDER
ROUND (number)
SIGN
SIN
SINH
SQRT
TAN
TANH
TRUNC (number)
WIDTH_BUCKET

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26

下面就是每一个对应函数的详细说明：（并附有实例）

ABS

功能：ABS返回n的绝对值。其语法格式如下：
在这里插入图片描述
此函数接受可以隐式转换为数值数据类型的任何数值数据类型或任何非数值数据类型作为参数。它将返回与参数的数值数据类型相同的数据类型，示例如下：

SQL> SELECT ABS(-15) "Absolute" FROM DUAL;

  Absolute
----------
	15

SQL> SELECT ABS(0) "Absolute" FROM DUAL;

  Absolute
----------
	 0

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
ACOS

功能：ACOS返回的是arccos(n)，参数n必须在-1到1的范围内，并且函数返回的值在0到π的范围内，用弧度表示。其语法格式如下：
在这里插入图片描述

此函数接受可以隐式转换为数值数据类型的任何数值数据类型或任何非数值数据类型作为参数。如果参数是BINARY_FLOAT，则函数返回BINARY_DOUBLE。否则，函数将返回与参数相同的数值数据类型，示例如下：

SQL> SELECT ACOS(1) "Arc_Cosine" FROM DUAL;

Arc_Cosine
----------
	 0

SQL> SELECT ACOS(0) "Arc_Cosine" FROM DUAL;

Arc_Cosine
----------
1.57079633

SQL> SELECT ACOS(-1) "Arc_Cosine" FROM DUAL;

Arc_Cosine
----------
3.14159265

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
ASIN

功能：ASIN返回arcsin(n)，参数n必须在-1到1的范围内，函数返回的值在- π/2到π/2的范围内，用弧度表示。其语法格式如下：
在这里插入图片描述
此函数接受可以隐式转换为数值数据类型的任何数值数据类型或任何非数值数据类型作为参数。如果参数是BINARY_FLOAT，则函数返回BINARY_DOUBLE。否则，函数将返回与参数相同的数值数据类型，示例如下：

SQL> SELECT ASIN(-1) "Arc_Sine" FROM DUAL;

  Arc_Sine
----------
-1.5707963

SQL> SELECT ASIN(0) "Arc_Sine" FROM DUAL;

  Arc_Sine
----------
	 0

SQL> SELECT ASIN(1) "Arc_Sine" FROM DUAL;

  Arc_Sine
----------
1.57079633

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
ATAN

功能：ATAN返回arctan n，参数n可以在一个无界范围内，并返回一个范围为- π/2到π/2的值，用弧度表示。其语法格式如下：
在这里插入图片描述
此函数接受可以隐式转换为数值数据类型的任何数值数据类型或任何非数值数据类型作为参数。如果参数是BINARY_FLOAT，则函数返回BINARY_DOUBLE。否则，函数将返回与参数相同的数值数据类型，示例如下：

SQL> SELECT ATAN(-1) "Arc_Tangent" FROM DUAL;

Arc_Tangent
-----------
 -.78539816

SQL> SELECT ATAN(0) "Arc_Tangent" FROM DUAL;

Arc_Tangent
-----------
	  0

SQL> SELECT ATAN(1) "Arc_Tangent" FROM DUAL;

Arc_Tangent
-----------
 .785398163

SQL> SELECT ATAN(1)*4 "Arc_Tangent" FROM DUAL;

Arc_Tangent
-----------
 3.14159265

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
ATAN2

功能：ATAN2返回n1和n2的正切值（atan2(y,x)所表达的意思是坐标原点为起点，指向(x,y)的射线在坐标平面上与x轴正方向之间的角的角度）。参数n1可以在一个无限的范围内，根据n1和n2的符号(用弧度表示)返回一个范围为-pi到pi的值。其语法格式如下：
在这里插入图片描述

此函数接受可以隐式转换为数值数据类型的任何数值数据类型或任何非数值数据类型作为参数。如果任何参数是BINARY_FLOAT或BINARY_DOUBLE，则函数返回BINARY_DOUBLE。否则，函数返回NUMBER，示例如下：

SQL> SELECT ATAN2(1,1) "Arc_Tangent2"  FROM DUAL;

Arc_Tangent2
------------
  .785398163

SQL> SELECT ATAN2(-1,-1) "Arc_Tangent2"  FROM DUAL;

Arc_Tangent2
------------
  -2.3561945

SQL> SELECT ATAN2(2,2) "Arc_Tangent2"  FROM DUAL;

Arc_Tangent2
------------
  .785398163

SQL> SELECT ATAN2(1,0) "Arc_Tangent2"  FROM DUAL;

Arc_Tangent2
------------
  1.57079633

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
BITAND

功能：比特函数把它的输入和输出当作比特的向量，输出是输入的位和。expr1和expr2的类型是NUMBER，结果的类型是NUMBER。如果BITAND的任何一个参数为NULL，则结果为NULL。参数的范围必须是-(2^(n-1)) .. ((2^(n-1))-1)，BITAND的当前实现定义了n = 128。如果参数超出此范围，则结果未定义。

# 例如 n=3,范围就是：

-4 ... 3，共2^3=8

  • 1
  • 2
  • 3

计算结果分为几个步骤：

1、首先，用SIGN(A)*FLOOR(ABS(A))替换每个参数A。这种转换的效果是将每个参数截断为零
2、接下来，每个参数A(现在必须是一个整数值)被转换为一个n位2的补码二进制整数值。这两个位值使用按位和操作组合
3、最后，得到的n位2的补值被转换回数字

其语法格式如下：
在这里插入图片描述
示例如下：

# & 运算

SQL> SELECT BITAND(6,3)  FROM DUAL;

BITAND(6,3)
-----------
	  2

SQL> SELECT BITAND(1,2)  FROM DUAL;

BITAND(1,2)
-----------
	  0

SQL> SELECT BITAND(1,3)  FROM DUAL;

BITAND(1,3)
-----------
	  1

SQL> SELECT BITAND(-1,0)  FROM DUAL;

BITAND(-1,0)
------------
	   0

SQL> SELECT BITAND(-1,1)  FROM DUAL;

BITAND(-1,1)
------------
	   1

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33

该函数还有另一个变形展示，实例如下：

SQL> SELECT BITAND(BIN_TO_NUM(1,1,0),BIN_TO_NUM(0,1,1)) "Binary" FROM DUAL;

    Binary
----------
	 2

SQL> SELECT BITAND(BIN_TO_NUM(0,0,0,1),BIN_TO_NUM(0,1,1)) "Binary" FROM DUAL;

    Binary
----------
	 1

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
CEIL

功能：CEIL 函数返回 大于或等于 的最小整数n，因此也称之为 天花板函数（↑） 其语法格式如下：
在这里插入图片描述
此函数接受可以隐式转换为数值数据类型的任何数值数据类型或任何非数值数据类型作为参数。该函数返回与参数的数值数据类型相同的数据类型，示例如下：

SQL> select CEIL(1.2)   FROM DUAL;

 CEIL(1.2)
----------
	 2

SQL>  select CEIL(1)  FROM DUAL;

   CEIL(1)
----------
	 1

SQL> select CEIL(-1.6)  FROM DUAL;

CEIL(-1.6)
----------
	-1

SQL>  select CEIL('1')  FROM DUAL;

 CEIL('1')
----------
	 1

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
COS

功能：COS函数返回的是 cos(n) (用弧度表示的角度)。其语法格式如下：
在这里插入图片描述

此函数接受可以隐式转换为数值数据类型的任何数值数据类型或任何非数值数据类型作为参数。如果参数是BINARY_FLOAT，则函数返回BINARY_DOUBLE。否则，函数将返回与参数相同的数值数据类型，示例如下：

SQL> SELECT COS(180 * 3.14159265359/180) "Cosine of 180 degrees" from dual;

Cosine of 180 degrees
---------------------
		   -1

SQL> SELECT COS(90 * 3.14159265359/180) "Cosine of 90 degrees" from dual;

Cosine of 90 degrees
--------------------
	  -1.034E-13

SQL> SELECT COS(0) "Cosine of 0  degrees" from dual;

Cosine of 0  degrees
--------------------
		   1

SQL> SELECT COS(2 * 3.14159265359) "Cosine of 360 degrees" from dual;

Cosine of 360 degrees
---------------------
		    1

SQL> SELECT COS(1.5 * 3.14159265359) "Cosine of 270  degrees" from dual;

Cosine of 270  degrees
----------------------
	    3.1014E-13

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
COSH

在这里插入图片描述

功能：COSH函数返回双曲余弦 COSH(n)。其语法格式如下：
在这里插入图片描述
此函数接受可以隐式转换为数值数据类型的任何数值数据类型或任何非数值数据类型作为参数。如果参数是BINARY_FLOAT，则函数返回BINARY_DOUBLE。否则，函数将返回与参数相同的数值数据类型，示例如下：

SQL> SELECT COSH(0) "Hyperbolic cosine of 0" from dual;

Hyperbolic cosine of 0
----------------------
		     1

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
EXP

功能：EXP返回e的n次方，其中e = 2.71828183…该函数返回与参数类型相同的值，其语法格式如下：
在这里插入图片描述
此函数接受可以隐式转换为数值数据类型的任何数值数据类型或任何非数值数据类型作为参数。如果参数是BINARY_FLOAT，则函数返回BINARY_DOUBLE。否则，函数将返回与参数相同的数值数据类型。示例如下：

SQL> SELECT EXP(4) "e to the 4th power" from dual;

e to the 4th power
------------------
	  54.59815

SQL> SELECT EXP(0) "e to the 0th power" from dual;

e to the 0th power
------------------
		 1

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
FLOOR

功能：FLOOR函数返回 等于或小于n 的最大的整数，可以类似于天花板函数，它被称为地板函数 其语法格式如下：
在这里插入图片描述

此函数接受可以隐式转换为数值数据类型的任何数值数据类型或任何非数值数据类型作为参数。该函数返回与参数的数值数据类型相同的数据类型。示例如下：

SQL> SELECT FLOOR(15.7) "Floor" from dual;

     Floor
----------
	15

SQL> SELECT FLOOR(0.5) "Floor" from dual;

     Floor
----------
	 0

SQL> SELECT FLOOR(-0.5) "Floor" from dual;

     Floor
----------
	-1

SQL> SELECT FLOOR(-2) "Floor" from dual;

     Floor
----------
	-2

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
LN

功能：LN返回n的自然对数，其中n大于0。其语法格式如下：
在这里插入图片描述

此函数接受可以隐式转换为数值数据类型的任何数值数据类型或任何非数值数据类型作为参数。如果参数是BINARY_FLOAT，则函数返回BINARY_DOUBLE。否则，函数将返回与参数相同的数值数据类型。示例如下：

SQL> SELECT EXP(4) "e to the 4th power" from dual;

e to the 4th power
------------------
	  54.59815

SQL> SELECT LN(54.59815) "Natural log of 54.59815" FROM DUAL;

Natural log of 54.59815
-----------------------
		      4

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
LOG

功能：LOG返回以n2为底n1的对数。基数n1可以是0或1以外的任何正数n2可以是任何正数。其语法格式如下：
在这里插入图片描述

此函数接受可以隐式转换为数值数据类型的任何数值数据类型或任何非数值数据类型作为参数。如果任何参数是BINARY_FLOAT或BINARY_DOUBLE，则函数返回BINARY_DOUBLE。否则，函数返回NUMBER。示例如下：

SQL> SELECT LOG(10,100) "Log base 10 of 100" from dual;

Log base 10 of 100
------------------
		 2

SQL> SELECT LOG(2,1024) "Log base 2 of 1024" from dual;

Log base 2 of 1024
------------------
		10

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
MOD

功能：MOD返回n2除以n1的余数。如果n1为0，返回n2。其语法格式如下：
在这里插入图片描述
此函数接受可以隐式转换为数值数据类型的任何数值数据类型或任何非数值数据类型作为参数。Oracle确定具有最高数值优先级的参数，隐式地将其余参数转换为该数据类型，并返回该数据类型。示例如下：

SQL> SELECT MOD(11,4) "Modulus" from dual;

   Modulus
----------
	 3

SQL> SELECT MOD(11,0) "Modulus" from dual;

   Modulus
----------
	11

SQL> SELECT MOD(-11,-4) "Modulus" from dual;

   Modulus
----------
	-3

SQL> SELECT MOD(-11,4) "Modulus" from dual;

   Modulus
----------
	-3

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25

上面函数在效果上等同于如下：

# n1 - n2 * FLOOR(n1/n2)

SQL> SELECT (11-4*FLOOR(11/4)) "Modulus" from dual;

   Modulus
----------
	 3

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
NANVL

功能：NANVL函数只对类型为BINARY_FLOAT或BINARY_DOUBLE的浮点数有用。如果输入值n2是NaN(不是数字)，它指示Oracle数据库返回一个替代值n1。如果n2不是NaN，那么Oracle返回n2。其语法格式如下：
在这里插入图片描述

此函数接受可以隐式转换为数值数据类型的任何数值数据类型或任何非数值数据类型作为参数。Oracle确定具有最高数值优先级的参数，隐式地将其余参数转换为该数据类型，并返回该数据类型。示例如下：

SQL> CREATE TABLE test1 (id int, bin_double BINARY_DOUBLE, bin_float BINARY_FLOAT);

Table created.

SQL> insert into test1 values  (1,1.2,1.3);

1 row created.

SQL> insert into test1 values (2,'NaN','NaN');

1 row created.

SQL> select * from test1;

	ID BIN_DOUBLE  BIN_FLOAT
---------- ---------- ----------
	 1   1.2E+000	1.3E+000
	 2	  Nan	     Nan

SQL> SELECT bin_float, NANVL(bin_float,0) FROM test1;

 BIN_FLOAT NANVL(BIN_FLOAT,0)
---------- ------------------
  1.3E+000	     1.3E+000
       Nan		    0

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
POWER

功能：求次方函数。基数n2和指数n1可以是任何数，但如果n2是负数，那么n1必须是整数。其语法格式如下：
在这里插入图片描述
此函数接受可以隐式转换为数值数据类型的任何数值数据类型或任何非数值数据类型作为参数。如果任何参数是BINARY_FLOAT或BINARY_DOUBLE，则函数返回BINARY_DOUBLE。否则，函数返回NUMBER。示例如下：

SQL> SELECT POWER(3,2) "Raised" from dual;

    Raised
----------
	 9

SQL> SELECT POWER(-3,2) "Raised" from dual;

    Raised
----------
	 9

SQL> SELECT POWER(0,2) "Raised" from dual;                       

    Raised
----------
	 0

SQL> SELECT POWER(2,-1) "Raised" from dual;

    Raised
----------
	.5

SQL> SELECT POWER(-2,-1) "Raised" from dual;

    Raised
----------
       -.5

SQL> SELECT POWER(0,-2) "Raised" from dual;
SELECT POWER(0,-2) "Raised" from dual
       *
ERROR at line 1:
ORA-01426: numeric overflow


SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
REMAINDER

功能：返回n2除以n1的余数。其语法格式如下：
在这里插入图片描述
此函数接受可以隐式转换为数值数据类型的任何数值数据类型或任何非数值数据类型作为参数。Oracle确定具有最高数值优先级的参数，隐式地将其余参数转换为该数据类型，并返回该数据类型。MOD函数与余数函数相似，除了它的公式中使用了地板函数FLOOR，而余数使用了四舍五入ROUND。

说的再详细一些：与 MOD 函数的区别为：在用 REMAINDER (x,y) 和 MOD (x,y) 函数在进行运算时，都用了一个公式 result=x-y*(x/y)，但是区别在于计算 x/y 时的处理方式不同。

1、在 REMAINDER (x,y) 函数中，采用 ROUND(x/y)；而在 MOD (x,y) 函数中采用 FLOOR(x/y)
2、在REMAINDER 函数中，当 ROUND(x/y) 的参数 x/y 的值的小数部分恰好为 0.5 时，如果 x/y 的值的整数部分为偶数，不向前一位进位，当 x/y 的值的整数部分为奇数，向前一位进位
例如：
ROUND(1.5)=2、ROUND(2.5)=2、ROUND(3.5)=4、ROUND(4.5)=4
FLOOR(1.5)=1、FLOOR(2.5)=2、FLOOR(3.5)=3、FLOOR(4.5)=4

下面是REMAINDER函数的示例：

# 11-4*(11/4=2.75)=11-12
SQL> select REMAINDER(11,4) "REMAINDER"  from dual;           

 REMAINDER
----------
	-1

# 11-5*(11/5=2.2)=11-10
SQL> select REMAINDER(11,5) "REMAINDER"  from dual;

 REMAINDER
----------
	 1

# 11-3*(11/3=3.66)=11-12
SQL> select REMAINDER(11,3) "REMAINDER"  from dual;

 REMAINDER
----------
	-1

SQL>

# 10-4*(10/4=2.5)=10-8
SQL> select REMAINDER(10,4) "REMAINDER"  from dual;

 REMAINDER
----------
	 2

# 6-4*(6/4=1.5)=6-8
SQL> select REMAINDER(6,4) "REMAINDER"  from dual;

 REMAINDER
----------
	-2

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
ROUND (number)

功能：四舍五入函数。其语法格式如下：
在这里插入图片描述
针对于只有一个参数的示例如下：

SQL> select ROUND(1.2) "ROUND" from dual;

     ROUND
----------
	 1

SQL> select ROUND(1.5) "ROUND" from dual;

     ROUND
----------
	 2

SQL> select ROUND(2.5) "ROUND" from dual;

     ROUND
----------
	 3

SQL> select ROUND(1.0) "ROUND" from dual;

     ROUND
----------
	 1

SQL> select ROUND(-1.2) "ROUND" from dual;

     ROUND
----------
	-1

SQL> select ROUND(-2.5) "ROUND" from dual;

     ROUND
----------
	-3

SQL>
SQL> select ROUND(-1.2,0) "ROUND" from dual;

     ROUND
----------
	-1

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44

参考上面的语法格式，也是可以有一个 int 类型的参数2的，示例如下：
在这里插入图片描述
解释一下：

1、n 为0，结果总是0 没有意义不研究
2、在没有参数2 或者 参数2=0的时候 或者 参数2 是负的时候，就是说在小数部分不要了，然后在整数部分 相应位置 四舍五入 示例1如下
3、参数2 是一个正整数的时候（整数部分还是要的），n 为正 或 负的示例2如下：

SQL> SELECT ROUND(1546.193) "Round" FROM DUAL;   

     Round
----------
      1546

SQL> SELECT ROUND(1546.193,-1) "Round" FROM DUAL;

     Round
----------
      1550

SQL> SELECT ROUND(1546.193,-2) "Round" FROM DUAL;

     Round
----------
      1500

SQL> SELECT ROUND(1546.193,-3) "Round" FROM DUAL;

     Round
----------
      2000

SQL> SELECT ROUND(1546.193,0) "Round" FROM DUAL; 

     Round
----------
      1546

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
# 这个参数2为正 就是说四舍五入到哪一位

SQL> select ROUND(1.23456,4)  "ROUND" from dual;

     ROUND
----------
    1.2346

SQL> select ROUND(1.23456,3)  "ROUND" from dual;

     ROUND
----------
     1.235

SQL> select ROUND(1.23456,2)  "ROUND" from dual;

     ROUND
----------
      1.23

SQL> select ROUND(-1.23456,4)  "ROUND" from dual;

     ROUND
----------
   -1.2346

SQL> select ROUND(-1.23456,3)  "ROUND" from dual;

     ROUND
----------
    -1.235

SQL> select ROUND(-1.23456,2)  "ROUND" from dual;

     ROUND
----------
     -1.23

SQL> 

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
SIGN

功能：返回数字n的符号。这个函数接受任何数值数据类型或任何可以隐式转换为NUMBER的非数值数据类型作为参数，并返回NUMBER。其语法格式如下：
在这里插入图片描述
作用如下：

# NUMBER 类型数据而言：

-1 if n<0

0 if n=0

1 if n>0
-----------------------------------------------------

# BINARY_FLOAT and BINARY_DOUBLE而言：
-1 if n<0

+1 if n>=0 or n=NaN

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13

示例如下：

SQL> select sign(-1) from dual;

  SIGN(-1)
----------
	-1

SQL> select sign(0) from dual;

   SIGN(0)
----------
	 0

SQL> select sign(1) from dual;

   SIGN(1)
----------
	 1

SQL> select sign(TO_BINARY_FLOAT(0)) from dual;

SIGN(TO_BINARY_FLOAT(0))
------------------------
		       1

SQL> select sign(TO_BINARY_DOUBLE('Nan')) from dual;     

SIGN(TO_BINARY_DOUBLE('NAN'))
-----------------------------
			    1

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
SIN

功能：SIN返回sin(n)(用弧度表示的角度)，其语法格式如下：
在这里插入图片描述

此函数接受可以隐式转换为数值数据类型的任何数值数据类型或任何非数值数据类型作为参数。如果参数是BINARY_FLOAT，则函数返回BINARY_DOUBLE。否则，函数将返回与参数相同的数值数据类型 示例如下：

SQL> SELECT SIN(30 * 3.14159265359/180) "Sine of 30 degrees" FROM DUAL;

Sine of 30 degrees
------------------
		.5

SQL> SELECT SIN(3.14159265359/2) "Sine of 90 degrees" FROM DUAL;

Sine of 90 degrees
------------------
		 1

SQL> SELECT SIN(-30 * 3.14159265359/180) "Sine of -30 degrees" FROM DUAL;

Sine of -30 degrees
-------------------
		-.5

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
SINH

功能：SINH返回双曲正弦 SINH(n)，其语法格式如下：
在这里插入图片描述
此函数接受可以隐式转换为数值数据类型的任何数值数据类型或任何非数值数据类型作为参数。如果参数是BINARY_FLOAT，则函数返回BINARY_DOUBLE。否则，函数将返回与参数相同的数值数据类型 示例如下：

SQL> SELECT SINH(1) "Hyperbolic sine of 1" FROM DUAL;

Hyperbolic sine of 1
--------------------
	  1.17520119

SQL> SELECT SINH(0) "Hyperbolic sine of 0" FROM DUAL;

Hyperbolic sine of 0
--------------------
		   0

SQL> SELECT SINH(-1) "Hyperbolic sine of -1" FROM DUAL;

Hyperbolic sine of -1
---------------------
	   -1.1752012

SQL> SELECT SINH(0.5) "Hyperbolic sine of 0.5" FROM DUAL;

Hyperbolic sine of 0.5
----------------------
	    .521095305

SQL>  SELECT SINH(0.5)*6 FROM DUAL;

SINH(0.5)*6
-----------
 3.12657183

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
SQRT

功能：SQRT返回n的平方根，其语法格式如下：
在这里插入图片描述

此函数接受可以隐式转换为数值数据类型的任何数值数据类型或任何非数值数据类型作为参数。该函数返回与参数的数值数据类型相同的数据类型。

# 对于NUMBER类型 n不可以为负的

# 对于BINARY_FLOAT or BINARY_DOUBLE,如下：
If n >= 0, then the result is positive.
If n = -0, then the result is -0.
If n < 0, then the result is NaN.

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

示例如下：

SQL> SELECT SQRT(25) "Square root" FROM DUAL;

Square root
-----------
	  5

SQL> SELECT SQRT(0) "Square root" FROM DUAL;

Square root
-----------
	  0

SQL> SELECT SQRT(TO_BINARY_FLOAT(-0)) "Square root" FROM DUAL;

Square root
-----------
	  0

SQL> SELECT SQRT(TO_BINARY_DOUBLE(-1)) "Square root" FROM DUAL;

Square root
-----------
	Nan

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
TAN

功能：TAN返回tan(n) (用弧度表示的角度)，其语法格式如下：
在这里插入图片描述

此函数接受可以隐式转换为数值数据类型的任何数值数据类型或任何非数值数据类型作为参数。如果参数是BINARY_FLOAT，则函数返回BINARY_DOUBLE。否则，函数将返回与参数相同的数值数据类型 示例如下：

SQL> SELECT TAN(135 * 3.14159265359/180) "Tangent of 135 degrees"  FROM DUAL;

Tangent of 135 degrees
----------------------
		    -1

SQL> SELECT TAN(3.14159265359/4) "Tangent of 45 degrees"  FROM DUAL;

Tangent of 45 degrees
---------------------
		    1

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
TANH

双曲正切函数，公式如下：
在这里插入图片描述
其图像如下：
在这里插入图片描述
功能：TANH返回n的双曲正切，其语法格式如下：
在这里插入图片描述
此函数接受可以隐式转换为数值数据类型的任何数值数据类型或任何非数值数据类型作为参数。如果参数是BINARY_FLOAT，则函数返回BINARY_DOUBLE。否则，函数将返回与参数相同的数值数据类型。

实例如下：

SQL> SELECT TANH(.5) "Hyperbolic tangent of 0.5"  FROM DUAL;

Hyperbolic tangent of 0.5
-------------------------
	       .462117157

SQL> SELECT TANH(0) "Hyperbolic tangent of 0"  FROM DUAL;

Hyperbolic tangent of 0
-----------------------
		      0

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
TRUNC (number)

功能：函数的作用是:返回n1被截断到n2的小数位数。如果n2被省略，那么n1被截断到0个位置。n2可以是负数，截断(使零)小数点左边的n2位。其语法格式如下：
在这里插入图片描述

此函数接受可以隐式转换为数值数据类型的任何数值数据类型或任何非数值数据类型作为参数。如果省略n2，则函数返回与参数的数值数据类型相同的数据类型。如果包含n2，则函数返回NUMBER。示例如下：

SQL> SELECT TRUNC(1234.5678) "Truncate" FROM DUAL;

  Truncate
----------
      1234

SQL> SELECT TRUNC(1234.5678,1) "Truncate" FROM DUAL;

  Truncate
----------
    1234.5

SQL> SELECT TRUNC(1234.5678,2) "Truncate" FROM DUAL;

  Truncate
----------
   1234.56

SQL> SELECT TRUNC(1234.5678,-1) "Truncate" FROM DUAL;

  Truncate
----------
      1230

SQL> SELECT TRUNC(1234.5678,-2) "Truncate" FROM DUAL;

  Truncate
----------
      1200

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
WIDTH_BUCKET

功能：WIDTH_BUCKET允许构造等宽直方图，其中直方图范围被划分为大小相同的间隔。(将此函数与NTILE进行比较，NTILE会创建等高直方图)理想情况下，每个桶都是实数轴的开闭区间。例如，一个bucket可以被分配到10.00到19.999之间的分数…表示区间中包含10，而不包含20。这有时被表示为[10,20)。对于给定的表达式，WIDTH_BUCKET返回计算后该表达式的值将归属的桶数。

1、expr是为其创建直方图的表达式。此表达式的计算结果必须为数值或日期时间值，或可隐式转换为数值或日期时间值的值。如果expr计算结果为null，则表达式返回null
2、min_value和max_value是解析到expr可接受范围的端点的表达式。这两个表达式还必须计算为数值或日期时间值，但都不能计算为null
3、num_buckets是一个表达式，它解析为一个常量，表示桶的数量。这个表达式的值必须为正整数
4、在需要时，Oracle数据库创建编号为0的下流桶和编号为num_buckets+1的溢出桶。这些桶处理小于min_value和大于max_value的值，有助于检查端点的合理性

其语法格式如下：
在这里插入图片描述
示例如下：

SQL> select * from test1;

	ID
----------
	 1
	 2
	 3
	 4
	 5
	 6

6 rows selected.

SQL> select WIDTH_BUCKET(id,0,4,3),id from test1;

WIDTH_BUCKET(ID,0,4,3)	       ID
---------------------- ----------
		     1		1
		     2		2
		     3		3
		     4		4
		     4		5
		     4		6

6 rows selected.

SQL> select WIDTH_BUCKET(id,1,4,3),id from test1;

WIDTH_BUCKET(ID,1,4,3)	       ID
---------------------- ----------
		     1		1
		     2		2
		     3		3
		     4		4
		     4		5
		     4		6

6 rows selected.

SQL> select WIDTH_BUCKET(id,0,5,3),id from test1;

WIDTH_BUCKET(ID,0,5,3)	       ID
---------------------- ----------
		     1		1
		     2		2
		     2		3
		     3		4
		     4		5
		     4		6

6 rows selected.

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53

解释一下：

1、上面有6行数据
2、在第一个例子中，0-4 长度为4，分为3段 0-1.3 1.3-2.6 2.6-3.9 粗略的计算，因此桶的盛放为：1,2,3 超出的都在溢出桶里面
3、在第二个例子中，1-4 长度为3，分为3段 1-1.9 1.9-2.9 2.9-3.9 粗略的计算，因此桶的盛放为：1,2,3 超出的都在溢出桶里面
4、在第三个例子中，0-5 长度为5，分为3段 0-1.6 1.6-3.2 3.2-4.8 粗略的计算，因此桶的盛放为：1,2,2 3 超出的都在溢出桶里面

日期函数(datetime_function)

Datetime函数对日期(DATE)、时间戳(TIMESTAMP, TIMESTAMP WITH TIME ZONE 和 TIMESTAMP WITH LOCAL TIME ZONE)和interval(INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH)值进行操作。

1、有些datetime函数是为Oracle DATE 数据类型(ADD_MONTHS、CURRENT_DATE、LAST_DAY、NEW_TIME和NEXT_DAY)设计的。如果提供时间戳值作为参数，则Oracle数据库在内部将输入类型转换为DATE 值并返回DATE 值。比较例外的是返回数字的MONTHS_BETWEEN函数，以及完全不接受时间戳或时间间隔值的ROUND和TRUNC函数

2、其余的datetime函数被设计为 接受三种类型的数据(date, timestamp, and interval)中的任何一种，并返回其中一种类型的值

3、所有返回当前系统日期时间信息的datetime函数，如SYSDATE、SYSTIMESTAMP、CURRENT_TIMESTAMP等，都将为每个SQL语句计算一次，而不管语句中引用了它们多少次

这些日期时间函数有：

ADD_MONTHS
CURRENT_DATE
CURRENT_TIMESTAMP
DBTIMEZONE
EXTRACT (datetime)
FROM_TZ
LAST_DAY
LOCALTIMESTAMP
MONTHS_BETWEEN
NEW_TIME
NEXT_DAY
NUMTODSINTERVAL
NUMTOYMINTERVAL
ORA_DST_AFFECTED
ORA_DST_CONVERT
ORA_DST_ERROR
ROUND (date)
SESSIONTIMEZONE
SYS_EXTRACT_UTC
SYSDATE
SYSTIMESTAMP
TO_CHAR (datetime)
TO_DSINTERVAL
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_YMINTERVAL
TRUNC (date)
TZ_OFFSET

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28

开始之前先行修改一下oracle 数据库默认时间格式（更符合我们的习惯），如下：

SQL> select sysdate from dual;

SYSDATE
-------------------
2020-11-22 15:38:01

SQL> show parameters nls

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
nls_calendar			     string
nls_comp			         string	 BINARY
nls_currency			     string
nls_date_format 		     string	 YYYY-MM-DD hh24:mi:ss
nls_date_language		     string
nls_dual_currency		     string
nls_iso_currency		     string
nls_language			     string	 AMERICAN
nls_length_semantics		 string	 BYTE
nls_nchar_conv_excp		     string	 FALSE
nls_numeric_characters		 string

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
nls_sort			         string
nls_territory			     string	 AMERICA
nls_time_format 		     string
nls_time_tz_format		     string
nls_timestamp_format		 string
nls_timestamp_tz_format 	 string
SQL>

# 具体做法就是：
alter system set nls_date_format='YYYY-MM-DD hh24:mi:ss' scope=spfile;
shutdown immediate;
startup;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36

下面是每一个对应函数的详细说明：（并附有实例）

ADD_MONTHS

功能：ADD_MONTHS 返回日期 date加上整数months。一个月由会话参数NLS_CALENDAR定义。date参数可以是一个日期时间值，也可以是任何可以隐式转换为date的值。整型参数可以是整数或任何可以隐式转换为整型的值。无论date的数据类型是什么，返回类型总是DATE。如果date是月份的最后一天，或者结果月份的天数少于date的day分量，那么结果就是结果月份的最后一天。否则，结果将具有与date相同的day组件。其语法格式如下：
在这里插入图片描述

add_months 有两个参数：第一个参数是日期，第二个参数是对日期进行加减的数字(以月为单位的)

示例如下：

SQL> select sysdate from dual;

SYSDATE
-------------------
2020-11-22 15:57:30

SQL> select add_months(sysdate,1) "new time" from dual;

new time
-------------------
2020-12-22 15:57:40

SQL> select add_months(sysdate,-1) "new time" from dual;

new time
-------------------
2020-10-22 15:57:51

SQL> SELECT ADD_MONTHS(TO_DATE('2012-01-31 19:15:26','yyyy-mm-dd HH24:MI:SS'), 1) "new time" from dual;

new time
-------------------
2012-02-29 19:15:26

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
CURRENT_DATE

功能：CURRENT_DATE返回会话时区中的当前日期，数据类型为date的公历中的值。其语法格式如下：
在这里插入图片描述

注：CURRENT_DATE对会话时区很敏感:

示例如下：

SQL> SELECT SESSIONTIMEZONE, CURRENT_DATE,sysdate FROM DUAL;

SESSIONTIMEZONE 							    CURRENT_DATE	SYSDATE
--------------------------------------------------------------------------- ------------------- -------------------
+08:00									    2020-11-22 16:07:05 2020-11-22 16:07:05

SQL> ALTER SESSION SET TIME_ZONE = '+07:00';

Session altered.

SQL> SELECT SESSIONTIMEZONE, CURRENT_DATE,sysdate FROM DUAL;

SESSIONTIMEZONE 							    CURRENT_DATE	SYSDATE
--------------------------------------------------------------------------- ------------------- -------------------
+07:00									    2020-11-22 15:08:07 2020-11-22 16:08:07

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
CURRENT_TIMESTAMP

功能：CURRENT_TIMESTAMP返回会话时区中的当前日期和时间，数据类型TIMESTAMP的值带有时区。时区偏移量反映SQL会话的当前本地时间。如果省略精度，则默认值为6。这个函数和LOCALTIMESTAMP的区别在于，CURRENT_TIMESTAMP返回带有时区值的时间戳，而LOCALTIMESTAMP返回时间戳值。其语法格式如下：
在这里插入图片描述

在可选参数中，precision指定返回的时间值的秒级精度
CURRENT_TIMESTAMP对会话时区很敏感

示例如下：

SQL> ALTER SESSION SET TIME_ZONE = '+08:00';

Session altered.

SQL> SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP, CURRENT_DATE,sysdate FROM DUAL;

SESSIONTIMEZONE
---------------------------------------------------------------------------
CURRENT_TIMESTAMP							    CURRENT_DATE	SYSDATE
--------------------------------------------------------------------------- ------------------- -------------------
+08:00
22-NOV-20 04.14.55.622940 PM +08:00					    2020-11-22 16:14:55 2020-11-25 16:14:55


SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

注：当使用带有格式掩码的CURRENT_TIMESTAMP时，要注意格式掩码与函数返回的值匹配。例如：

SQL> CREATE TABLE current_test (col1 TIMESTAMP WITH TIME ZONE);

Table created.

SQL> INSERT INTO current_test VALUES  (TO_TIMESTAMP_TZ(CURRENT_TIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM'));
INSERT INTO current_test VALUES  (TO_TIMESTAMP_TZ(CURRENT_TIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM'))
                                                  *
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string


# 上面的语句失败，因为掩码不包括函数返回的类型的时区部分


# 下面的语句使用正确的格式掩码来匹配CURRENT_TIMESTAMP的返回类型
SQL> INSERT INTO current_test VALUES (TO_TIMESTAMP_TZ(CURRENT_TIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM TZH:TZM'));

1 row created.

SQL> select * from current_test;

COL1
---------------------------------------------------------------------------
22-NOV-20 04.25.06.041285 PM +08:00

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
DBTIMEZONE

功能：DBTIMEZONE返回数据库时区的值。返回类型是时区偏移量(字符类型格式：'[+|-]TZH:TZM') 或 时区区域名称，具体取决于用户在最近的CREATE database 或 ALTER database语句中指定数据库时区值的方式，其语法格式如下：
在这里插入图片描述
示例如下：

# 下面的示例假设数据库时区设置为UTC时区:
SQL> SELECT DBTIMEZONE from dual;

DBTIME
------
+00:00

SQL> SELECT SESSIONTIMEZONE FROM DUAL;

SESSIONTIMEZONE
---------------------------------------------------------------------------
+08:00

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
EXTRACT (datetime)

功能：从 日期时间或间隔 表达式 中提取并返回指定的日期时间字段的值。expr可以是任何计算为与请求字段兼容的日期时间或间隔数据类型的表达式:

1、如果请求年或月，则expr必须计算为数据类型 DATE、时间戳、带时区的时间戳、带本地时区的时间戳或间隔年到月的表达式
2、如果请求DAY，则expr必须计算为数据类型 DATE、时间戳、带时区的时间戳、带本地时区的时间戳或从日到秒的间隔的表达式
3、如果请求小时、分钟或秒，则expr必须计算为数据类型 时间戳、带时区的时间戳、带本地时区的时间戳或从日到秒的间隔的表达式。DATE在这里是无效的，因为Oracle数据库将其视为ANSI DATE数据类型，它没有时间字段
4、如果请求TIMEZONE_HOUR、TIMEZONE_MINUTE、TIMEZONE_ABBR、TIMEZONE_REGION或TIMEZONE_OFFSET，则expr必须计算为带时区的数据类型 TIMESTAMP或带本地时区的时间戳的表达式
5、EXTRACT将expr解释为ANSI datetime数据类型。例如，EXTRACT将DATE处理为ANSI DATE而不是legacy Oracle DATE，没有时间元素。因此，您只能从日期值中提取年、月和日。同样，您仅可以从时区数据类型的时间戳中提取TIMEZONE_HOUR和TIMEZONE_MINUTE
6、当您指定TIMEZONE_REGION或TIMEZONE_ABBR(缩写)时，返回的值是一个VARCHAR2字符串，其中包含适当的时区区域名称或缩写。当指定任何其他datetime字段时，返回的值是数字数据类型的整数值，表示公历中的日期时间值。从带有时区值的日期时间中提取时，返回的值是UTC格式的。要获得时区区域名称及其对应缩写的列表，请查询V$TIMEZONE_NAMES动态性能视图
7、夏时制特性需要时区区域名称。这些名称存储在两种类型的时区文件中：大的和小的。其中一个文件是默认文件，这取决于您的环境和所使用的Oracle数据库的版本
8、某些日期时间字段和日期时间的组合或时间间隔值表达式会导致歧义。在这些情况下，Oracle数据库返回UNKNOWN(参见后面的示例了解更多信息)

此函数对于在非常大的表中操作日期时间字段值非常有用，其语法格式如下：
在这里插入图片描述
示例如下：

# 得到了年月日
SQL> SELECT EXTRACT(YEAR FROM DATE '1998-03-07') FROM DUAL;

EXTRACT(YEARFROMDATE'1998-03-07')
---------------------------------
			     1998

SQL> SELECT EXTRACT(month FROM DATE '1998-03-07') FROM DUAL;

EXTRACT(MONTHFROMDATE'1998-03-07')
----------------------------------
				 3

SQL> SELECT EXTRACT(day FROM DATE '1998-03-07') FROM DUAL;

EXTRACT(DAYFROMDATE'1998-03-07')
--------------------------------
			       7

SQL>

# 下面是得到时间戳的 时分秒
SQL> select CURRENT_TIMESTAMP from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
22-NOV-20 05.45.30.175655 PM +08:00

SQL> SELECT EXTRACT(hour FROM to_timestamp('22-NOV-20 05.45.30.175655', 'DD-Mon-RR HH24:MI:SS.FF')) FROM DUAL;

EXTRACT(HOURFROMTO_TIMESTAMP('22-NOV-2005.45.30.175655','DD-MON-RRHH24:MI:SS.FF'))
----------------------------------------------------------------------------------
										 5

SQL> SELECT EXTRACT(minute FROM to_timestamp('22-NOV-20 05.45.30.175655', 'DD-Mon-RR HH24:MI:SS.FF')),EXTRACT(second FROM to_timestamp('22-NOV-20 05.45.30.175655', 'DD-Mon-RR HH24:MI:SS.FF')) FROM DUAL;

EXTRACT(MINUTEFROMTO_TIMESTAMP('22-NOV-2005.45.30.175655','DD-MON-RRHH24:MI:SS.FF'))
------------------------------------------------------------------------------------
EXTRACT(SECONDFROMTO_TIMESTAMP('22-NOV-2005.45.30.175655','DD-MON-RRHH24:MI:SS.FF'))
------------------------------------------------------------------------------------
										  45
									   30.175655


SQL>

# 
SQL> select EXTRACT(TIMEZONE_MINUTE FROM TO_TIMESTAMP_TZ('01-JAN-2005 19:15:26 UTC', 'DD-MON-YYYY HH24:MI:SS TZR'))from dual;

EXTRACT(TIMEZONE_MINUTEFROMTO_TIMESTAMP_TZ('01-JAN-200519:15:26UTC','DD-MON-YYYYHH24:MI:SSTZR'))
------------------------------------------------------------------------------------------------
											       0

SQL> select EXTRACT(TIMEZONE_HOUR FROM TO_TIMESTAMP_TZ('01-JAN-2005 19:15:26 UTC', 'DD-MON-YYYY HH24:MI:SS TZR'))from dual;

EXTRACT(TIMEZONE_HOURFROMTO_TIMESTAMP_TZ('01-JAN-200519:15:26UTC','DD-MON-YYYYHH24:MI:SSTZR'))
----------------------------------------------------------------------------------------------
											     0

SQL> select EXTRACT(TIMEZONE_MINUTE FROM TO_TIMESTAMP_TZ('01-JAN-2005 19:15:26 PST', 'DD-MON-YYYY HH24:MI:SS TZR'))from dual;

EXTRACT(TIMEZONE_MINUTEFROMTO_TIMESTAMP_TZ('01-JAN-200519:15:26PST','DD-MON-YYYYHH24:MI:SSTZR'))
------------------------------------------------------------------------------------------------
											       0

SQL> select EXTRACT(TIMEZONE_HOUR FROM TO_TIMESTAMP_TZ('01-JAN-2005 19:15:26 PST', 'DD-MON-YYYY HH24:MI:SS TZR'))from dual;

EXTRACT(TIMEZONE_HOURFROMTO_TIMESTAMP_TZ('01-JAN-200519:15:26PST','DD-MON-YYYYHH24:MI:SSTZR'))
----------------------------------------------------------------------------------------------
											    -8

SQL> select EXTRACT(TIMEZONE_REGION FROM TO_TIMESTAMP_TZ('01-JAN-2005 19:15:26 PST', 'DD-MON-YYYY HH24:MI:SS TZR'))from dual;

EXTRACT(TIMEZONE_REGIONFROMTO_TIMESTAMP_TZ('01-JAN-200519:15:26P
----------------------------------------------------------------
PST

SQL> select EXTRACT(TIMEZONE_ABBR FROM TO_TIMESTAMP_TZ('01-JAN-2005 19:15:26 PST', 'DD-MON-YYYY HH24:MI:SS TZR'))from dual;

EXTRACT(TI
----------
PST

SQL> select EXTRACT(TIMEZONE_REGION FROM TO_TIMESTAMP_TZ('01-JAN-2005 19:15:26 UTC', 'DD-MON-YYYY HH24:MI:SS TZR'))from dual;

EXTRACT(TIMEZONE_REGIONFROMTO_TIMESTAMP_TZ('01-JAN-200519:15:26U
----------------------------------------------------------------
UTC

SQL> select EXTRACT(TIMEZONE_ABBR FROM TO_TIMESTAMP_TZ('01-JAN-2005 19:15:26 UTC', 'DD-MON-YYYY HH24:MI:SS TZR'))from dual;

EXTRACT(TI
----------
GMT

SQL>

# 下面的例子导致了歧义，所以Oracle返回UNKNOWN:
SQL> SELECT EXTRACT(TIMEZONE_REGION FROM TIMESTAMP '1999-01-01 10:00:00 -08:00') result from dual;

RESULT
----------------------------------------------------------------
UNKNOWN

SQL>
# 产生歧义是因为在表达式中提供了时区数字偏移量，并且该数字偏移量可以映射到多个时区区域名

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89
  • 90
  • 91
  • 92
  • 93
  • 94
  • 95
  • 96
  • 97
  • 98
  • 99
  • 100
  • 101
  • 102
  • 103
  • 104
  • 105
  • 106

在这里插入图片描述

FROM_TZ

功能：FROM_TZ将时间戳值和时区转换为带有时区值的时间戳。time_zone_value是格式为‘TZH:TZM’的字符串，或者是返回TZR格式的字符串(带有可选TZD格式)的字符表达式。其语法格式如下：
在这里插入图片描述
示例如下：

SQL> SELECT FROM_TZ(TIMESTAMP '2000-03-28 10:25:00', '8:00') from dual;

FROM_TZ(TIMESTAMP'2000-03-2810:25:00','8:00')
---------------------------------------------------------------------------
28-MAR-00 10.25.00.000000000 AM +08:00

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
LAST_DAY

功能：LAST_DAY返回包含日期的月份的最后一天的日期。每个月的最后一天是由会话参数NLS_CALENDAR定义的。无论date的数据类型是什么，返回类型总是DATE。其语法格式如下：
在这里插入图片描述
示例如下：

SQL> SELECT SYSDATE,LAST_DAY(SYSDATE) "Last",LAST_DAY(SYSDATE) - SYSDATE "Days Left" FROM DUAL;

SYSDATE 	  	       Last		 	     Days Left
------------------- ------------------- ----------
2020-12-02 18:05:53 2020-12-31 18:05:53 	29

SQL> select ADD_MONTHS(LAST_DAY(SYSDATE),1) FROM DUAL;

ADD_MONTHS(LAST_DAY
-------------------
2021-01-31 18:07:03

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
LOCALTIMESTAMP

功能：LOCALTIMESTAMP以数据类型TIMESTAMP的值返回会话时区中的当前日期和时间。这个函数和CURRENT_TIMESTAMP的区别在于LOCALTIMESTAMP返回一个时间戳值，而CURRENT_TIMESTAMP返回一个带有时区值的时间戳。可选参数timestamp_precision指定返回的时间值的秒级精度。其语法格式如下：
在这里插入图片描述
示例如下：

# 这个例子说明了LOCALTIMESTAMP和CURRENT_TIMESTAMP之间的区别:
SQL> SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;

CURRENT_TIMESTAMP				  		   LOCALTIMESTAMP
-------------------------------------------------- ----------------------------------------
06-DEC-20 05.23.33.023404 PM +08:00		   06-DEC-20 05.23.33.023404 PM

SQL>

# 在使用LOCALTIMESTAMP和格式掩码时，要注意格式掩码与函数返回的值匹配。例如，考虑下表：
SQL> CREATE TABLE local_test (col1 TIMESTAMP WITH LOCAL TIME ZONE);

Table created.

# 下面的语句失败了，因为掩码不包括函数返回类型的时区部分:
SQL> INSERT INTO local_test VALUES (TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF'));
INSERT INTO local_test VALUES (TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF'))
                                            *
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

# 下面的语句使用正确的格式掩码来匹配LOCALTIMESTAMP的返回类型:
SQL> INSERT INTO local_test VALUES (TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM'));

1 row created.

SQL> select * from local_test;

COL1
---------------------------------------------------------------------------
06-DEC-20 05.28.39.793153 PM

SQL>
SQL> INSERT INTO local_test VALUES (TO_TIMESTAMP(LOCALTIMESTAMP, 'DD-MON-RR HH.MI.SSXFF AM'));

1 row created.

SQL> select * from local_test;

COL1
---------------------------------------------------------------------------
06-DEC-20 05.28.39.793153 PM
06-DEC-20 05.33.18.962999 PM

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
MONTHS_BETWEEN

功能：MONTHS_BETWEEN返回日期date1和date2之间的月份数。月份和该月的最后一天由参数NLS_CALENDAR定义。如果date1晚于date2，则结果为正数。如果date1早于date2，则结果为负。如果date1和date2是某月的同一天或者是某月的最后一天，那么结果总是一个整数。否则，Oracle数据库将基于31天的月份计算结果的小数部分，并考虑时间组件date1和date2的差异。其语法格式如下：
在这里插入图片描述
示例如下：

SQL> SELECT MONTHS_BETWEEN (TO_DATE('02-02-1995','MM-DD-YYYY'), TO_DATE('01-01-1995','MM-DD-YYYY') ) "Months" FROM DUAL;

    Months
----------
1.03225806

SQL> SELECT MONTHS_BETWEEN (TO_DATE('01-02-1995','MM-DD-YYYY'), TO_DATE('01-01-1995','MM-DD-YYYY') ) "Months" FROM DUAL;

    Months
----------
.032258065

SQL> SELECT MONTHS_BETWEEN (TO_DATE('01-02-1995','MM-DD-YYYY'), TO_DATE('01-02-1995','MM-DD-YYYY')) "Months" FROM DUAL;

    Months
----------
	 0

SQL> SELECT MONTHS_BETWEEN (TO_DATE('01-01-1995','MM-DD-YYYY'), TO_DATE('02-02-1995','MM-DD-YYYY') ) "Months" FROM DUAL;

    Months
----------
-1.0322581

SQL> SELECT MONTHS_BETWEEN (TO_DATE('02-01-1995','MM-DD-YYYY'), TO_DATE('01-01-1995','MM-DD-YYYY') ) "Months" FROM DUAL;

    Months
----------
	 1

SQL> SELECT MONTHS_BETWEEN (TO_DATE('03-01-1995','MM-DD-YYYY'), TO_DATE('01-01-1995','MM-DD-YYYY') ) "Months" FROM DUAL;

    Months
----------
	 2

SQL> SELECT MONTHS_BETWEEN (TO_DATE('02-28-1995','MM-DD-YYYY'), TO_DATE('01-31-1995','MM-DD-YYYY') ) "Months" FROM DUAL;

    Months
----------
	 1

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
NEW_TIME

功能：当date和time在timezone timezone1中是date时，NEW_TIME返回在时区timezone2中的日期和时间。在使用此函数之前，必须设置NLS_DATE_FORMAT参数以显示24小时的时间。无论DATE的数据类型是什么，返回类型总是DATE。这个函数只接受有限数量的时区作为输入。通过组合FROM_TZ函数和datetime表达式，您可以访问更多的时区。其语法格式如下：
在这里插入图片描述
其参数timezone1和timezone2可以是这些文本字符串中的任何一个：

AST, ADT: Atlantic Standard or Daylight Time

BST, BDT: Bering Standard or Daylight Time

CST, CDT: Central Standard or Daylight Time

EST, EDT: Eastern Standard or Daylight Time

GMT: Greenwich Mean Time

HST, HDT: Alaska-Hawaii Standard Time or Daylight Time.

MST, MDT: Mountain Standard or Daylight Time

NST: Newfoundland Standard Time

PST, PDT: Pacific Standard or Daylight Time

YST, YDT: Yukon Standard or Daylight Time

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19

示例如下：

# 以下示例返回大西洋标准时间，给定等效的太平洋标准时间:

# 必须是24小时制

  • 1
  • 2
  • 3

在这里插入图片描述
在这里插入图片描述

NEXT_DAY

功能：NEXT_DAY返回char指向的第一个工作日的日期，这个日期晚于date。无论DATE的数据类型是什么，返回类型总是DATE。参数char在会话的日期语言中必须是星期几，可以是全名，也可以是缩写。所需的最小字母数是缩写版本的字母数。任何紧跟在有效缩写之后的字符都将被忽略。返回值具有与参数date相同的小时、分钟和秒组件。其语法格式如下：
在这里插入图片描述

星期一 Mon.（全称Monday）
星期二 Tues.（全称Tuesday)
星期三 Wed.（全称Wednesday）
星期四 Thur.（全称Thursday）
星期五bai Fri.（全称Friday）
星期六 Sat.（全程Saturday）
星期日 Sun.（全称Sunday）

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

示例如下：

# 例如今天 周日
SQL> select sysdate from dual;

SYSDATE
-------------------
2020-12-06 18:21:08

SQL>
# 于是两天后 就是星期二
SQL> SELECT NEXT_DAY(TO_DATE('2020-12-06 18:21:08','YYYY-MM-DD hh24:mi:ss'),'TUESDAY') "NEXT DAY" FROM DUAL;

NEXT DAY
-------------------
2020-12-08 18:21:08

SQL>
SQL>  SELECT NEXT_DAY(TO_DATE('2020-12-06 18:21:08','YYYY-MM-DD hh24:mi:ss'),'sun') "NEXT DAY" FROM DUAL;

NEXT DAY
-------------------
2020-12-13 18:21:08

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
NUMTODSINTERVAL

功能：NUMTODSINTERVAL将n转换为一个间隔日到第二个字面值。参数n可以是任何数字值或可以隐式转换为数字值的表达式。参数interval_unit可以是CHAR VARCHAR2 NCHAR NVARCHAR2数据类型。interval_unit的值指定单位n，必须解析为以下字符串值之一:

'DAY'
'HOUR'
'MINUTE'
'SECOND'

  • 1
  • 2
  • 3
  • 4

interval_unit不区分大小写。括号内的前置和后置值将被忽略。默认情况下，返回的精度是9。其语法格式如下：
在这里插入图片描述
示例如下：

SQL> select NUMTODSINTERVAL(100, 'day') from dual;

NUMTODSINTERVAL(100,'DAY')
---------------------------------------------------------------------------
+000000100 00:00:00.000000000

SQL> select NUMTODSINTERVAL(24,'HOUR') from dual;

NUMTODSINTERVAL(24,'HOUR')
---------------------------------------------------------------------------
+000000001 00:00:00.000000000

SQL> select NUMTODSINTERVAL(25,'HOUR') from dual;

NUMTODSINTERVAL(25,'HOUR')
---------------------------------------------------------------------------
+000000001 01:00:00.000000000

SQL> select NUMTODSINTERVAL(25.5,'HOUR') from dual;

NUMTODSINTERVAL(25.5,'HOUR')
---------------------------------------------------------------------------
+000000001 01:30:00.000000000

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
NUMTOYMINTERVAL

功能：NUMTOYMINTERVAL将数字n转换为年到月的文字间隔。参数n可以是任何数字值或可以隐式转换为数字值的表达式。参数interval_unit可以是CHAR VARCHAR2 NCHAR NVARCHAR2数据类型。interval_unit的值指定单位n，必须解析为以下字符串值之一:

'YEAR'
'MONTH'

  • 1
  • 2

interval_unit不区分大小写。括号内的前置和后置值将被忽略。默认情况下，返回的精度是9。其语法格式如下：
在这里插入图片描述

示例如下：

SQL> select NUMTOYMINTERVAL(1.5,'year') from dual;

NUMTOYMINTERVAL(1.5,'YEAR')
---------------------------------------------------------------------------
+000000001-06

SQL> select NUMTOYMINTERVAL(1.75,'year') from dual;

NUMTOYMINTERVAL(1.75,'YEAR')
---------------------------------------------------------------------------
+000000001-09

SQL> select NUMTOYMINTERVAL(13,'month') from dual;

NUMTOYMINTERVAL(13,'MONTH')
---------------------------------------------------------------------------
+000000001-01

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
ORA_DST_AFFECTED

功能：在更改数据库的时区数据文件时，ORA_DST_AFFECTED非常有用。该函数接受一个datetime表达式作为参数，该表达式解析为带有时区值的时间戳（TIMESTAMP WITH TIME ZONE），或一个包含带有时区值的时间戳的VARRAY对象。如果datetime值受到新时区数据的"nonexisting time" or "duplicate time"错误的影响，则函数返回1。否则，它返回0。此函数仅在更改数据库的时区数据文件和使用时区数据更新时间戳时发布，且仅在以下两种的执行期间发布：

1、the DBMS_DST.BEGIN_PREPARE and the DBMS_DST.END_PREPARE procedures
2、the DBMS_DST.BEGIN_UPGRADE and the DBMS_DST.END_UPGRADE procedures

其语法格式如下：
在这里插入图片描述
示例如下：

无

ORA_DST_CONVERT

功能：ORA_DST_CONVERT在更改数据库的时区数据文件时非常有用。该函数允许您为指定的日期时间表达式指定错误处理。

1、对于datetime_expr，指定一个解析为带有时区值的时间戳的日期时间表达式，或一个包含带有时区值的时间戳的VARRAY对象
2、第二个可选参数指定对 "duplicate time"错误的处理。指定0 (false)以通过返回源日期时间值来抑制错误。这是默认值。指定1 (true)允许数据库返回重复的时间错误
3、可选的第三个参数指定对 "nonexisting time"错误的处理。指定0 (false)以通过返回源日期时间值来抑制错误。这是默认值。指定1 (true)允许数据库返回不存在的时间错误

如果没有发生错误，此函数返回与datetime_expr(带时区值的时间戳或包含带时区值的时间戳的VARRAY对象)相同的数据类型的值。用新时区文件解释时返回的datetime值对应于用旧时区文件解释的datetime_expr。

注：此函数仅在更改数据库的时区数据文件和使用时区数据更新时间戳时发布，且仅在以下这种的执行期间发布：

1、the DBMS_DST.BEGIN_UPGRADE and the DBMS_DST.END_UPGRADE procedures

其语法格式如下：
在这里插入图片描述

示例如下：

无

ORA_DST_ERROR

功能：ORA_DST_ERROR在更改数据库的时区数据文件时非常有用。该函数以一个datetime表达式作为参数，该表达式解析为带有时区值的时间戳（TIMESTAMP WITH TIME ZONE），或一个包含带有时区值的时间戳的VARRAY对象，并指示datetime值是否会在使用新时区数据时导致错误。返回值为:

0: datetime值不会导致新时区数据出现错误。
1878: datetime值导致“不存在时间”错误。
1883: datetime值会导致“重复时间”错误。

注：此函数仅在更改数据库的时区数据文件和使用时区数据更新时间戳时发布，且仅在以下这种的执行期间发布：

1、the DBMS_DST.BEGIN_PREPARE and the DBMS_DST.END_PREPARE procedures
2、the DBMS_DST.BEGIN_UPGRADE and the DBMS_DST.END_UPGRADE procedures

其语法格式如下：
在这里插入图片描述

示例如下：

无

ROUND (date)

功能：返回日期四舍五入到格式模型fmt指定的单位。这个函数对NLS_CALENDAR会话参数不敏感。它根据公历的规则运作。返回的值总是数据类型DATE，即使您为DATE指定了不同的datetime数据类型。如果省略fmt，则日期将四舍五入到最近的日期。日期表达式必须解析为日期值。其语法格式如下：
在这里插入图片描述
示例如下：

# 下面的例子将日期四舍五入到下一年的第一天:（注意格式）

SQL> show parameters nls    

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
nls_calendar			     string
nls_comp			     string	 BINARY
nls_currency			     string
nls_date_format 		     string	 YYYY-MM-DD hh24:mi:ss
nls_date_language		     string
nls_dual_currency		     string
nls_iso_currency		     string
nls_language			     string	 AMERICAN
nls_length_semantics		     string	 BYTE
nls_nchar_conv_excp		     string	 FALSE
nls_numeric_characters		     string

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
nls_sort			     string
nls_territory			     string	 AMERICA
nls_time_format 		     string
nls_time_tz_format		     string
nls_timestamp_format		     string
nls_timestamp_tz_format 	     string
SQL>

SQL> SELECT ROUND(TO_DATE ('2019-06-30 23:39:07'),'YEAR') FROM DUAL;

ROUND(TO_DATE('2019
-------------------
2019-01-01 00:00:00

SQL> SELECT ROUND(TO_DATE ('2019-07-01 23:39:07'),'YEAR') FROM DUAL;

ROUND(TO_DATE('2019
-------------------
2020-01-01 00:00:00

SQL>
SQL> SELECT ROUND(TO_DATE ('2019-12-03 23:39:07'),'YEAR') "New Year" FROM DUAL;

New Year
-------------------
2020-01-01 00:00:00

SQL> SELECT ROUND(TO_DATE ('2019-12-03 23:39:07'),'day') "New Year" FROM DUAL;

New Year
-------------------
2019-12-01 00:00:00

SQL> SELECT ROUND(TO_DATE ('2019-12-23 23:39:07'),'day') "New Year" FROM DUAL;

New Year
-------------------
2019-12-22 00:00:00

SQL>  SELECT ROUND(TO_DATE ('2019-12-29 23:39:07'),'day') "New Year" FROM DUAL;

New Year
-------------------
2019-12-29 00:00:00

SQL> SELECT ROUND(TO_DATE ('2019-12-03 23:39:07'),'YEAR') FROM DUAL;

ROUND(TO_DATE('2019
-------------------
2020-01-01 00:00:00

SQL> SELECT ROUND(TO_DATE ('2019-02-03 23:39:07'),'YEAR') FROM DUAL;

ROUND(TO_DATE('2019
-------------------
2019-01-01 00:00:00

SQL> SELECT ROUND(TO_DATE ('2019-06-03 23:39:07'),'YEAR') FROM DUAL;

ROUND(TO_DATE('2019
-------------------
2019-01-01 00:00:00

SQL>  SELECT ROUND(TO_DATE ('2019-06-23 23:39:07'),'YEAR') FROM DUAL;

ROUND(TO_DATE('2019
-------------------
2019-01-01 00:00:00

SQL> SELECT ROUND(TO_DATE ('2019-10-03 23:39:07'),'YEAR') FROM DUAL;

ROUND(TO_DATE('2019
-------------------
2020-01-01 00:00:00

SQL> SELECT ROUND(TO_DATE ('2019-09-03 23:39:07'),'YEAR') FROM DUAL;

ROUND(TO_DATE('2019
-------------------
2020-01-01 00:00:00

SQL>  SELECT ROUND(TO_DATE ('2019-07-23 23:39:07'),'YEAR') FROM DUAL;

ROUND(TO_DATE('2019
-------------------
2020-01-01 00:00:00

SQL> SELECT ROUND(TO_DATE ('2019-06-30 23:39:07'),'YEAR') FROM DUAL;

ROUND(TO_DATE('2019
-------------------
2019-01-01 00:00:00

SQL> SELECT ROUND(TO_DATE ('2019-07-01 23:39:07'),'YEAR') FROM DUAL;

ROUND(TO_DATE('2019
-------------------
2020-01-01 00:00:00

SQL> SELECT ROUND(TO_DATE ('2019-06-15 23:39:07'),'DAY') FROM DUAL;

ROUND(TO_DATE('2019
-------------------
2019-06-16 00:00:00

SQL> SELECT ROUND(TO_DATE ('2019-06-15 03:39:07'),'DAY') FROM DUAL;

ROUND(TO_DATE('2019
-------------------
2019-06-16 00:00:00

SQL> SELECT ROUND(TO_DATE ('2019-06-14 03:39:07'),'DAY') FROM DUAL;

ROUND(TO_DATE('2019
-------------------
2019-06-16 00:00:00

SQL> SELECT ROUND(TO_DATE ('2019-06-13 03:39:07'),'DAY') FROM DUAL;

ROUND(TO_DATE('2019
-------------------
2019-06-16 00:00:00

SQL> SELECT ROUND(TO_DATE ('2019-06-17 03:39:07'),'DAY') FROM DUAL;

ROUND(TO_DATE('2019
-------------------
2019-06-16 00:00:00

SQL> SELECT ROUND(TO_DATE ('2019-06-27 03:39:07'),'DAY') FROM DUAL;

ROUND(TO_DATE('2019
-------------------
2019-06-30 00:00:00

SQL> SELECT ROUND(TO_DATE ('2019-06-27 03:39:07'),'MONTH') FROM DUAL;

ROUND(TO_DATE('2019
-------------------
2019-07-01 00:00:00

SQL> SELECT ROUND(TO_DATE ('2019-11-27 03:39:07'),'MONTH') FROM DUAL;

ROUND(TO_DATE('2019
-------------------
2019-12-01 00:00:00

SQL> SELECT ROUND(TO_DATE ('2019-01-27 03:39:07'),'MONTH') FROM DUAL;

ROUND(TO_DATE('2019
-------------------
2019-02-01 00:00:00

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89
  • 90
  • 91
  • 92
  • 93
  • 94
  • 95
  • 96
  • 97
  • 98
  • 99
  • 100
  • 101
  • 102
  • 103
  • 104
  • 105
  • 106
  • 107
  • 108
  • 109
  • 110
  • 111
  • 112
  • 113
  • 114
  • 115
  • 116
  • 117
  • 118
  • 119
  • 120
  • 121
  • 122
  • 123
  • 124
  • 125
  • 126
  • 127
  • 128
  • 129
  • 130
  • 131
  • 132
  • 133
  • 134
  • 135
  • 136
  • 137
  • 138
  • 139
  • 140
  • 141
  • 142
  • 143
  • 144
  • 145
  • 146
  • 147
  • 148
  • 149
  • 150
  • 151
  • 152
  • 153
  • 154
  • 155
  • 156
  • 157
  • 158
  • 159
  • 160
  • 161
  • 162
  • 163
  • 164
  • 165
  • 166
  • 167
  • 168
  • 169
  • 170
  • 171
  • 172
  • 173
  • 174

注：也许大家对上面有些疑虑的就是关于DAY的四舍五入，大家只需要记住一点：它四舍五入的目的就是为了找离它最近的星期日！

SESSIONTIMEZONE

功能：返回当前会话的时区。返回类型是时区偏移量('[+|-]TZH:TZM'格式的字符类型)或时区名称，具体取决于用户在最近的ALTER session语句中指定会话时区值的方式。其语法格式如下：
在这里插入图片描述
示例如下：

# 下面的示例返回当前会话的时区:
SQL> SELECT SESSIONTIMEZONE FROM DUAL;

SESSIONTIMEZONE
----------------------------------------
+08:00

SQL> ALTER SESSION SET TIME_ZONE = '+07:00';

Session altered.

SQL> SELECT SESSIONTIMEZONE FROM DUAL;

SESSIONTIMEZONE
----------------------------------------
+07:00

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
SYS_EXTRACT_UTC

功能：SYS_EXTRACT_UTC从带有时区偏移或时区区域名称的日期时间值中提取UTC(协调世界时——以前的格林威治标准时间)。如果未指定时区，则日期时间与会话时区相关联。其语法格式如下：
在这里插入图片描述
示例如下：

SQL> SELECT SESSIONTIMEZONE,current_timestamp,sysdate from DUAL;

SESSIONTIMEZONE 			 CURRENT_TIMESTAMP			  SYSDATE
---------------------------------------- ---------------------------------------- -------------------
+08:00					 10-DEC-20 05.41.13.238106 PM +08:00	  2020-12-10 17:41:13

SQL> SELECT SYS_EXTRACT_UTC(TIMESTAMP '2020-12-10 17:41:13 +08:00')  FROM DUAL;

SYS_EXTRACT_UTC(TIMESTAMP'2020-12-1017:41:13+08:00')
---------------------------------------------------------------------------
10-DEC-20 09.41.13.000000000 AM

SQL>  SELECT SYS_EXTRACT_UTC(TIMESTAMP '2020-12-10 17:41:13')  FROM DUAL;

SYS_EXTRACT_UTC(TIMESTAMP'2020-12-1017:41:13')
---------------------------------------------------------------------------
10-DEC-20 09.41.13.000000000 AM

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
SYSDATE

功能：SYSDATE返回数据库服务器所在操作系统设置的当前日期和时间。返回值的数据类型是DATE，返回的格式取决于NLS_DATE_FORMAT初始化参数的值。该函数不需要参数。在分布式SQL语句中，该函数返回为本地数据库的操作系统设置的日期和时间。不能在检查约束的条件下使用此函数。其语法格式如下：
在这里插入图片描述
示例如上！

SYSTIMESTAMP

功能：SYSTIMESTAMP返回数据库所在系统的系统日期，包括小数秒和时区。其返回值类型是：TIMESTAMP WITH TIME ZONE。其语法格式如下：
在这里插入图片描述
示例如下：

# 下面的例子返回系统时间戳:
SQL> SELECT SYSTIMESTAMP,SYSDATE from DUAL;

SYSTIMESTAMP						 SYSDATE
---------------------------------------- -------------------
10-DEC-20 05.51.16.313084 PM +08:00	 2020-12-10 17:51:16

SQL>

# 下面的示例展示了如何显式指定小数秒:
SQL> SELECT TO_CHAR(SYSTIMESTAMP, 'SSSSS.FF') FROM DUAL;

TO_CHAR(SYSTIME
---------------
64497.666859

SQL>

# 下面的示例返回指定时区的当前时间戳:
SQL> select sysdate,SYSTIMESTAMP AT TIME ZONE 'UTC' FROM DUAL;

SYSDATE 	        SYSTIMESTAMPATTIMEZONE'UTC'
------------------- ------------------------------------------------
2020-12-10 17:56:45 10-DEC-20 09.56.45.348216 AM UTC

SQL>

# 本例中的输出格式取决于会话的NLS_TIMESTAMP_TZ_FORMAT

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
TO_CHAR (datetime)

功能：TO_CHAR (datetime)将日期、时间戳、带时区的时间戳、带本地时区的时间戳、从日到秒的间隔或从年到月的间隔数据类型转换为VARCHAR2数据类型的值，格式由日期格式fmt指定。如果您省略了fmt，那么日期将被转换为VARCHAR2值，如下所示：

1、DATE值转换为默认日期格式的值
2、TIMESTAMP 和 TIMESTAMP WITH LOCAL TIME ZONE值被转换为默认时间戳格式的值
3、TIMESTAMP WITH TIME ZONE值转换为具有时区格式的默认时间戳的值
4、时间间隔值被转换为时间间隔文字的数值表示

注：nlsparam参数指定返回月、日名称和缩写的语言。这个论证可以是这样的形式：'NLS_DATE_LANGUAGE = language'，但是如果您省略了nlsparam，那么这个函数将为您的会话使用默认的日期语言（可以将此函数与任何XML函数结合使用，以数据库格式而不是XML模式标准格式生成日期）。其语法格式如下：
在这里插入图片描述

示例如下：

SQL> desc date_tab
 Name				Null?    Type
 ----------------------------------------------------------------
 TS_COL 					TIMESTAMP(6)
 TSLTZ_COL					TIMESTAMP(6) WITH LOCAL TIME ZONE
 TSTZ_COL					TIMESTAMP(6) WITH TIME ZONE

SQL> ALTER SESSION SET TIME_ZONE = '-8:00';

Session altered.

SQL> INSERT INTO date_tab VALUES (TIMESTAMP'1999-12-01 10:00:00',TIMESTAMP'1999-12-01 10:00:00',TIMESTAMP'1999-12-01 10:00:00');

1 row created.

SQL> INSERT INTO date_tab VALUES (TIMESTAMP'1999-12-02 10:00:00 -8:00',TIMESTAMP'1999-12-02 10:00:00 -8:00',TIMESTAMP'1999-12-02 10:00:00 -8:00');

1 row created.

SQL> SELECT TO_CHAR(ts_col, 'DD-MON-YYYY HH24:MI:SSxFF') AS ts_date,TO_CHAR(tstz_col, 'DD-MON-YYYY HH24:MI:SSxFF TZH:TZM') AS tstz_date FROM date_tab ORDER BY ts_date, tstz_date;

TS_DATE 						TSTZ_DATE
--------------------------------------- ----------------------------------------------
01-DEC-1999 10:00:00.000000		01-DEC-1999 10:00:00.000000 -08:00
02-DEC-1999 10:00:00.000000		02-DEC-1999 10:00:00.000000 -08:00

SQL> select * from date_tab;

TS_COL		  							TSLTZ_COL						TSTZ_COL
---------------------------------------------------------------------------------------------
01-DEC-99 10.00.00.000000 AM	01-DEC-99 10.00.00.000000 AM    01-DEC-99 10.00.00.000000 AM -08:00
02-DEC-99 10.00.00.000000 AM	02-DEC-99 10.00.00.000000 AM	02-DEC-99 10.00.00.000000 AM -08:00


SQL> SELECT SESSIONTIMEZONE,TO_CHAR(tsltz_col, 'DD-MON-YYYY HH24:MI:SSxFF') AS tsltz FROM date_tab ORDER BY sessiontimezone, tsltz;

SESSIONTIMEZONE   				TSLTZ
--------------- ---------------------------------------
-08:00		  				01-DEC-1999 10:00:00.000000
-08:00		  				02-DEC-1999 10:00:00.000000

SQL> ALTER SESSION SET TIME_ZONE = '-5:00';

Session altered.

SQL> SELECT TO_CHAR(ts_col, 'DD-MON-YYYY HH24:MI:SSxFF') AS ts_col,TO_CHAR(tstz_col, 'DD-MON-YYYY HH24:MI:SSxFF TZH:TZM') AS tstz_col FROM date_tab ORDER BY ts_col, tstz_col;

TS_COL							TSTZ_COL
--------------------------------------- ----------------------------------------------
01-DEC-1999 10:00:00.000000		01-DEC-1999 10:00:00.000000 -08:00
02-DEC-1999 10:00:00.000000		02-DEC-1999 10:00:00.000000 -08:00

SQL> SELECT SESSIONTIMEZONE,TO_CHAR(tsltz_col, 'DD-MON-YYYY HH24:MI:SSxFF') AS tsltz_col FROM date_tab ORDER BY sessiontimezone, tsltz_col;

SESSIONTIMEZONE   				TSLTZ_COL
--------------- ---------------------------------------
-05:00		  				01-DEC-1999 13:00:00.000000
-05:00		  				02-DEC-1999 13:00:00.000000

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60

注：上面的例子就说明了 将TO_CHAR应用于不同时间戳数据类型会出现不同的结果。（TIMESTAMP WITH LOCAL TIME ZONE）带有本地时区列的时间戳的结果对会话时区敏感，而（TIMESTAMP and TIMESTAMP WITH TIME ZONE）带有时区列的时间戳和时间戳的结果对会话时区不敏感。

# 下面的例子将间隔文字转换为文本文字:
SQL> SELECT TO_CHAR(interval '2020' year(4), 'YYYY-MM-DD', 'nls_language = AMERICAN') FROM DUAL;

TO_CHAR(
--------
+2020-00

SQL>  SELECT TO_CHAR(INTERVAL '1234-2' YEAR(4) TO MONTH) FROM DUAL;

TO_CHAR(
--------
+1234-02

SQL>  SELECT TO_CHAR(interval '12' MONTH(2)  TO MONTH) FROM DUAL;

TO_CHA
------
+01-00

SQL> SELECT TO_CHAR(interval '6' MONTH(1)  TO MONTH) FROM DUAL;

TO_CH
-----
+0-06

SQL> SELECT TO_CHAR(INTERVAL '31' MONTH(2)  TO MONTH) FROM DUAL;

TO_CHA
------
+02-07

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
TO_DSINTERVAL

功能：TO_DSINTERVAL将CHAR、VARCHAR2、NCHAR或NVARCHAR2数据类型的字符串转换为INTERVAL DAY TO SECOND类型。TO_DSINTERVAL接受以下两种格式之一的参数：

1、SQL interval格式兼容SQL标准(ISO/ iec9075:2003)
2、ISO持续时间格式兼容ISO 8601:2004标准

在SQL格式中，天是0到999999999之间的整数，小时是0到23之间的整数，分钟和秒是0到59之间的整数。frac_secs是秒的小数部分，介于.0 and .999999999之间。一个或多个空格将天与小时分开。允许在格式元素之间添加空白。

在ISO格式中，天、小时、分钟和秒是0到999999999之间的整数。frac_secs是秒的小数部分，介于.0 and .999999999之间。值中不允许有空格。如果指定T，则必须指定至少一个小时、分钟或秒值。

其语法格式如下：
在这里插入图片描述
示例如下：

# 下面是两种格式的示例：
SQL> select TO_DSINTERVAL('100 10:25:01') from dual;

TO_DSINTERVAL('10010:25:01')
---------------------------------------------------------------------------
+000000100 10:25:01.000000000

SQL>

# 下面的例子使用ISO格式显示2009年开始后100天5小时的时间戳:
SQL> SELECT TO_CHAR(TIMESTAMP '2009-01-01 00:00:00' + TO_DSINTERVAL('P100DT05H'),'YYYY-MM-DD HH24:MI:SS') "Time Stamp" FROM DUAL;

Time Stamp
-------------------
2009-04-11 05:00:00

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
TO_TIMESTAMP

功能：TO_TIMESTAMP将char、VARCHAR2、NCHAR或NVARCHAR2数据类型中的char转换为时间戳数据类型（TIMESTAMP）的值。

  1. 可选的fmt指定char的格式。如果省略了fmt，那么char必须采用时间戳数据类型的默认格式，该格式由NLS_TIMESTAMP_FORMAT初始化参数确定
  2. 可选的nlsparam参数在这个函数中与TO_CHAR函数中用于日期转换的目的相同
  3. 此函数不直接支持CLOB数据。但是，可以通过隐式数据转换将clob作为参数传递进来

其语法格式如下：
在这里插入图片描述

示例如下：

# 下面的示例将字符串转换为时间戳。字符串不是默认的时间戳格式，所以必须指定格式掩码:
SQL> SELECT TO_TIMESTAMP ('10-Sep-02 14:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF')  from dual;

TO_TIMESTAMP('10-SEP-0214:10:10.123000','DD-MON-RRHH24:MI:SS.FF')
---------------------------------------------------------------------------
10-SEP-02 02.10.10.123000000 PM

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
TO_TIMESTAMP_TZ

功能：TO_TIMESTAMP_TZ将char、VARCHAR2、NCHAR或NVARCHAR2数据类型转换为具有时区数据类型的时间戳值。

  1. 可选的fmt指定char的格式。如果省略了fmt，则char必须使用具有时区数据类型(TIMESTAMP WITH TIME ZONE)的时间戳的默认格式
  2. 可选的nlsparam在这个函数中与TO_CHAR函数中用于日期转换的目的相同

其语法格式如下：
在这里插入图片描述
示例如下：

# 下面的示例将字符串转换为带有时区的时间戳值:
SQL> SELECT TO_TIMESTAMP_TZ('1999-12-01 11:00:00 -8:00','YYYY-MM-DD HH:MI:SS TZH:TZM') FROM DUAL;

TO_TIMESTAMP_TZ('1999-12-0111:00:00-8:00','YYYY-MM-DDHH:MI:SSTZH:TZM')
---------------------------------------------------------------------------
01-DEC-99 11.00.00.000000000 AM -08:00

SQL> SELECT TO_TIMESTAMP_TZ('1999-12-01 21:00:00 -8:00','YYYY-MM-DD HH24:MI:SS TZH:TZM') FROM DUAL;

TO_TIMESTAMP_TZ('1999-12-0121:00:00-8:00','YYYY-MM-DDHH24:MI:SSTZH:TZM')
---------------------------------------------------------------------------
01-DEC-99 09.00.00.000000000 PM -08:00

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
TO_YMINTERVAL

功能：TO_YMINTERVAL将CHAR、VARCHAR2、NCHAR或NVARCHAR2数据类型的字符串转换为年到月的间隔类型。它接受以下两种格式之一的参数：

  1. SQL interval格式兼容SQL标准(ISO/ iec9075:2003)
  2. ISO持续时间格式兼容ISO 8601:2004标准

在SQL格式中，年是0到999999999之间的整数，月是0到11之间的整数。允许在格式元素之间添加空白。

在ISO格式中，年和月是0到999999999之间的整数。天、小时、分钟、秒和分形秒都是非负整数，如果指定，则忽略它们。值中不允许有空格。如果指定T，则必须指定至少一个小时、分钟或秒值。

其语法格式如下：
在这里插入图片描述
示例如下：

# 两种格式的示例如下：
SQL> SELECT to_date('1999-12-01') + TO_YMINTERVAL('01-02') "14 months" FROM DUAL;

14 months
-------------------
2001-02-01 00:00:00

SQL> SELECT to_date('1999-12-01') + TO_YMINTERVAL('P1Y2M') FROM DUAL;

TO_DATE('1999-12-01
-------------------
2001-02-01 00:00:00

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
TRUNC (date)

功能：TRUNC (date)函数返回日期，日期的时间部分被截断为fmt格式模型指定的单元。

1、这个函数对NLS_CALENDAR会话参数不敏感。它根据公历的规则运作。返回的值总是数据类型DATE，即使您为DATE指定了不同的datetime数据类型
2、如果省略fmt，则使用默认格式模型DD，返回的值是日期截断为一天，时间为午夜。请参考ROUND和TRUNC日期函数了解fmt中允许使用的格式模型

其语法格式如下：
在这里插入图片描述
示例如下：

SQL> SELECT TRUNC(TO_DATE('27-OCT-20','DD-MON-YY'), 'YEAR')  "New Year" FROM DUAL;

New Year
-------------------
2020-01-01 00:00:00

SQL> SELECT TRUNC(TO_DATE('27-OCT-20','DD-MON-YY'), 'month') FROM DUAL;                        

TRUNC(TO_DATE('27-O
-------------------
2020-10-01 00:00:00

SQL> SELECT TRUNC(TO_DATE('27-OCT-20','DD-MON-YY'), 'DAY') FROM DUAL;

TRUNC(TO_DATE('27-O
-------------------
2020-10-25 00:00:00

SQL> SELECT TRUNC(TO_DATE('31-OCT-20','DD-MON-YY'), 'DAY') FROM DUAL;

TRUNC(TO_DATE('31-O
-------------------
2020-10-25 00:00:00

SQL> SELECT TRUNC(TO_DATE('24-OCT-20','DD-MON-YY'), 'DAY') FROM DUAL;

TRUNC(TO_DATE('24-O
-------------------
2020-10-18 00:00:00

SQL>
# 下面几个是去找本周的周日

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
TZ_OFFSET

功能：TZ_OFFSET根据语句执行的日期返回与参数对应的时区偏移量。您可以输入一个有效的时区区域名称、UTC的时区偏移量(它只是返回本身)，或者关键字SESSIONTIMEZONE或DBTIMEZONE。要获得time_zone_name的有效值清单，请查询V$TIMEZONE_NAMES动态性能视图的TZNAME列。

注：夏时制特性需要时区区域名称。这些名称存储在两种类型的时区文件中:大的和小的。其中一个文件是默认文件，这取决于您的环境和所使用的Oracle数据库的版本。

其语法格式如下：
在这里插入图片描述
示例如下：

# 下面的示例返回美国/东部时区与UTC的时区偏移量:

SQL> ALTER SESSION SET TIME_ZONE = '+08:00';                                       

Session altered.

SQL> SELECT TZ_OFFSET(DBTIMEZONE) FROM DUAL;

TZ_OFFS
-------
+00:00

SQL> SELECT TZ_OFFSET(SESSIONTIMEZONE) FROM DUAL;

TZ_OFFS
-------
+08:00

SQL> SELECT TZ_OFFSET('US/Eastern') FROM DUAL;

TZ_OFFS
-------
-05:00

SQL> select tz_offset('Asia/Hong_Kong') from dual;

TZ_OFFS
-------
+08:00

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31

一般比较函数(General Comparison Functions)

一般的比较函数从一组值中确定最大值和最小值。一般比较函数为:

GREATEST
LEAST

  • 1
  • 2
GREATEST

功能：GREATEST返回一个或多个表达式列表中的最大值

  1. Oracle数据库使用第一个expr来确定返回类型。如果第一个expr是数值，则Oracle确定具有最高数值优先级的参数，在比较之前隐式地将其余参数转换为该数据类型，并返回该数据类型
  2. 如果第一个expr不是数字，那么第一个expr之后的每个expr都隐式转换为比较前的第一个expr的数据类型
  3. Oracle数据库使用非填充的比较语义比较每个expr。如果NLS_COMP参数被设置为language，而NLS_SORT参数的设置不是binary，则该比较默认为二进制比较，并且是语言比较
  4. 字符比较基于数据库字符集中字符的数字代码，并在作为一个字节序列而不是逐个字符处理的整个字符串上执行。如果这个函数返回的值是字符数据，那么它的数据类型是VARCHAR2(如果第一个expr是字符数据类型)，如果第一个expr是国家字符数据类型，那么它的数据类型是NVARCHAR2

其语法格式如下：
在这里插入图片描述

示例如下：

# 下面的语句选择值最大的字符串:
SQL> show parameters nls

NAME				     TYPE		  VALUE
------------------------------------ -------------------- ------------------------------
nls_calendar			     string
nls_comp			     string		  BINARY
nls_currency			     string
nls_date_format 		     string		  YYYY-MM-DD hh24:mi:ss
nls_date_language		     string
nls_dual_currency		     string
nls_iso_currency		     string
nls_language			     string		  AMERICAN
nls_length_semantics		     string		  BYTE
nls_nchar_conv_excp		     string		  FALSE
nls_numeric_characters		     string

NAME				     TYPE		  VALUE
------------------------------------ -------------------- ------------------------------
nls_sort			     string
nls_territory			     string		  AMERICA
nls_time_format 		     string
nls_time_tz_format		     string
nls_timestamp_format		     string
nls_timestamp_tz_format 	     string
SQL> SELECT GREATEST('HARRY', 'HARRIOT', 'HAROLD') "Greatest" FROM DUAL;

Great
-----
HARRY

SQL>

# 在下面的语句中，第一个参数是数值的。Oracle数据库确定数值优先级最高的参数是第二个参数，将其余参数转换为第二个参数的数据类型，并返回该数据类型的最大值:
SQL> SELECT GREATEST (1, '3.925', '2.4') "Greatest" FROM DUAL;

  Greatest
----------
     3.925

SQL>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
LEAST

功能：返回一个或多个表达式列表中的最小值。

  1. Oracle数据库使用第一个expr来确定返回类型。如果第一个expr是数值，则Oracle确定具有最高数值优先级的参数，在比较之前隐式地将其余参数转换为该数据类型，并返回该数据类型
  2. 如果第一个expr不是数字，那么第一个expr之后的每个expr都隐式转换为比较前的第一个expr的数据类型
  3. Oracle数据库使用非填充的比较语义比较每个expr。如果NLS_COMP参数被设置为language，而NLS_SORT参数的设置不是binary，则该比较默认为二进制比较，并且是语言比较
  4. 字符比较基于数据库字符集中字符的数字代码，并在作为一个字节序列而不是逐个字符处理的整个字符串上执行。如果这个函数返回的值是字符数据，那么它的数据类型是VARCHAR2(如果第一个expr是字符数据类型)，如果第一个expr是国家字符数据类型，那么它的数据类型是NVARCHAR2

其语法格式如下：
在这里插入图片描述
示例如下：

# 下面的语句选择值最小的字符串:
SQL> show parameters nls

NAME				     TYPE		  VALUE
------------------------------------ -------------------- ------------------------------
nls_calendar			     string
nls_comp			     string		  BINARY
nls_currency			     string
nls_date_format 		     string		  YYYY-MM-DD hh24:mi:ss
nls_date_language		     string
nls_dual_currency		     string
nls_iso_currency		     string
nls_language			     string		  AMERICAN
nls_length_semantics		     string		  BYTE
nls_nchar_conv_excp		     string		  FALSE
nls_numeric_characters		     string

NAME				     TYPE		  VALUE
------------------------------------ -------------------- ------------------------------
nls_sort			     string
nls_territory			     string		  AMERICA
nls_time_format 		     string
nls_time_tz_format		     string
nls_timestamp_format		     string
nls_timestamp_tz_format 	     string
SQL> SELECT LEAST('HARRY','HARRIOT','HAROLD') "Least" from dual;

Least
------
HAROLD

SQL>
# 在下面的语句中，第一个参数是数值的。Oracle数据库确定数值优先级最高的参数是第三个参数，将其余参数转换为第三个参数的数据类型，并返回该数据类型的最小值:
SQL> SELECT LEAST (1, '2.1', '.000832') "Least" from dual;

     Least
----------
   .000832

SQL> 

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
本文内容由网友自发贡献，转载请注明出处：【wpsshop博客】
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号