函数概述

OceanBase 数据库内嵌的函数可以直接在 SQL 语句中使用。每个函数的入参传入值均有期望的数据类型，如果传入的数据类型不是期望的数据类型，则 OceanBase 数据库会在实际执行 SQL 函数之前尝试将参数传入的数值转换为期望的数据类型。

函数与运算符相似，即给定一些数据元素作为参数的输入并返回结果。然而函数在入参形式上与运算符有比较大的区别。函数允许包含的参数数量不定，一个函数里可以有一到两个，甚至更多的参数。

函数名（参数，参数，...）

函数也可以没有任何参数，类似于伪列。但是，伪列通常为结果集中的每一行返回不同的值，而没有任何变量的函数通常为每一行返回相同的值。

本章中函数分成了两大类，每一类函数的参数和最终的函数返回值都有其特定的数据类型：

• 单行函数 ：包括数字函数、返回字符串的字符串函数、返回数字的字符串函数、时间日期函数、通用比较函数、转换函数、编码解码函数、空值相关函数、环境和标识函数以及层次函数等。

• 统计函数 ：包括聚合函数和分析函数。

单行函数对于被查询的表或者视图每一行均返回一个结果值，这些函数可以使用在 SQL 语句的 SELECT、WHERE、START WITH、CONNECT BY、HAVING 等子句当中。

分析函数与聚合函数，都是对行集组（一组行的集合）进行聚合计算。不同的是，聚合函数每组只能返回一个值（一行），而分析函数每组可以返回多个值（多行）。行集组又称为窗口（Window）。聚合函数通常和 SELECT 语句中的 GROUP BY 子句一起使用，使用时数据库将查询表或视图的行分为几组，并将聚合函数应用于每组行，同时为每组返回一个结果行。

使用分析函数时需要用特殊的关键字 OVER 来指定窗口。更多关于窗后函数的信息，请参见 分析函数说明。

数字函数

数字函数的变量输入与函数输出结果均为数字类型，绝大部分的数字函数的返回值的数据类型为 NUMBER，可以精确到小数点后 38 位。一些高等代数相关函数 COS()、COSH()、EXP()、LN()、LOG()、SIN()、SINH()、SQRT()、TAN()、TANH() 等函数的结果精确到小数点后 36 位，其他代数相关函数 ACOS()，ASIN()，ATAN() 和 ATAN2()。其结果返回值精确到小数点后 30 位。

返回字符串的字符串函数

函数的返回值的最大长度受数据类型的影响、例如，函数的返回值的数据类型是 VARCHAR2，但是返回值实际的大小超过了 VARCHAR2 数据类型的最大限制，此时 OceanBase 数据库会对结果进行截断处理并返回，但是在客户端上并不会提示。

返回数字的字符串函数

返回数字的字符串函数根据指定条件的返回数字，例如 ASCII 码值、字符（串）的位置等。

时间日期函数

时间日期函数支持的入参数据类型有三类，包括日期时间 (DATE)、时间戳 (TIMESTAMP、TIMESTAMP WITH TIME ZONE、TIMESTAMP WITH LOCAL TIME ZONE)，以及间隔 (INTERVAL DAY TO SECOND、INTERVAL YEAR TO MONTH)。

仅支持 DATE 数据类型输入参数的函数包括：ADD_MONTHS、CURRENT_DATE, LAST_DAY 和 NEXT_DAY。

如果对于以上函数，您尝试给入的是 TIMESTAMP 类型的数据，OceanBase 数据库内部会先进行隐式数据类型转换后带入函数进行运算，并返回 DATE 类型的返回值。

在使用时间日期函数前，建议执行 SELECT * FROM NLS_DATABASE_PARAMETERS 查看当前的 NLS 格式，如下所示：

obclient> SELECT * FROM NLS_DATABASE_PARAMETERS;
+-------------------------+------------------------------+
| PARAMETER               | VALUE                        |
+-------------------------+------------------------------+
| NLS_DATE_FORMAT         | DD-MON-RR                    |
| NLS_TIMESTAMP_FORMAT    | YYYY-MM-DD HH24:MI:SS        |
| NLS_TIMESTAMP_TZ_FORMAT | DD-MON-RR HH.MI.SSXFF AM TZR |
| NLS_TERRITORY           | AMERICA                      |
| NLS_SORT                | BINARY                       |
| NLS_COMP                | BINARY                       |
| NLS_CHARACTERSET        | AL32UTF8                     |
| NLS_NCHAR_CHARACTERSET  | AL16UTF16                    |
| NLS_DATE_LANGUAGE       | AMERICAN                     |
| NLS_LENGTH_SEMANTICS    | BYTE                         |
| NLS_NCHAR_CONV_EXCP     | FALSE                        |
| NLS_CALENDAR            | GREGORIAN                    |
| NLS_NUMERIC_CHARACTERS  | .,                           |
+-------------------------+------------------------------+
13 rows in set

如果与文中示例的显示格式不一致，可以执行如下命令更改格式：

obclient>ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
Query OK, 0 rows affected

obclient>ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
Query OK, 0 rows affected
/*将返回值中秒的小数位设置为 9 位*/

obclient> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF TZR TZD';
Query OK, 0 rows affected

剩余的函数对于三种参数数据类型均可以支持，且返回和输入参数相同的数据类型。

通用比较函数

可以通过本类别函数快速的在集合中寻找到最大值和最小值。

转换函数

可以通过本类型的函数将原本的数据类型转换为另外一种数据类型。

编码解码函数

可以通过本类型的函数在 OceanBase 数据库中实现数据的编码解密需求

空值相关函数

空值相关函数用于处理参数中的空值。对于绝大多数的函数当入参为空值 NULL 的时候，其返回的结果也为 NULL。这种情况下，您可以使用 NVL 函数返回一个非空值。例如，一张记录佣金的表的佣金列 commission_pct 为空值 NULL，则表达式NVL(commission_pct，0) 返回 0；如果 commission_pct 的值不为 NULL，则返回实际的佣金值。

环境相关函数

环境相关函数主要提供会话或者租户实例相关的环境信息。

层次函数

JSON 函数

JSON 函数对 JSON 值进行创建、操作和搜索等操作。

XML 函数

XML 函数可以高效地查询、更新和搜索 XML 文档。

聚合函数

聚合函数对一组值执行计算并返回单一的值。聚合函数忽略空值。聚合函数经常与 SELECT 语句的 GROUP BY 子句一同使用。

分析函数

分析函数（某些数据库下也叫做窗口函数）与聚合函数类似，计算总是基于一组行的集合，不同的是，聚合函数一组只能返回一行，而分析函数每组可以返回多行，组内每一行都是基于窗口的逻辑计算的结果。分析函数可以显著优化需要 Self-Join 的查询。