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

  1. 函数名(参数,参数,...)

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

关于函数

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

函数中的空值

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

函数分类与列表

在如下的函数分类列表中,每一类函数的参数和最终的函数返回值都有其特定的数据类型。

注意

在 SQL 语句中对 LOB 列使用函数时,OceanBase 数据库将在 SQL 和 PL/SQL 处理期间创建临时 LOB 列,并有一定的使用限制,详情信息请参考文档 与 Oracle 兼容性对比

本章中函数分成了两大类:

  • 单行函数:包括 数字函数、返回字符串的字符串函数、返回数字的字符串函数、 时间日期函数、通用比较函数、转换函数、编码解码函数和空值相关函数。
  • 统计函数:包括聚合函数和分析函数。

单行函数对于被查询的表或者视图每一行均返回一个结果值,这些函数可以使用在 SQL 语句的 SELECTWHERESTART WITHCONNECT BYHAVING 等子句当中。

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

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

数字函数

数字函数的变量输入与函数输出结果均为数字类型,绝大部分的数字函数的返回值的数据类型为 NUMBER,可以精确到小数点后 38 位。一些高等代数相关函数 COSCOSHEXPLNLOGSINSINHSQRTTANTANH 等函数的结果精确到小数点后 36 位,其他代数相关函数 ACOSASINATANATAN2。其结果返回值精确到小数点后 30 位。

函数分类

函数子分类

函数名

功能描述

单行函数

数字函数

ABS

返回指定数值表达式的绝对值(正值)的数学函数。

单行函数

数字函数

ACOS

返回以弧度表示的角,其余弦为指定的

NUMBER

表达式,也称为反余弦。

单行函数

数字函数

ASIN

OceanBase 暂不支持。

单行函数

数字函数

ATAN

OceanBase 暂不支持。

单行函数

数字函数

ATAN2

OceanBase 暂不支持。

单行函数

数字函数

BITAND

运算符按位进行“与”操作。输入和输出类型均为 INT 整型,且类型一致。

单行函数

数字函数

CEIL

返回值大于等于数值 numeric_expression 的最小整数。

单行函数

数字函数

COS

OceanBase 暂不支持。

单行函数

数字函数

COSH

OceanBase 暂不支持。

单行函数

数字函数

EXP

返回 e 的 numeric_expression 次幂。

单行函数

数字函数

FLOOR

返回小于等于数值 numeric_expression 的最大整数。

单行函数

数字函数

LN

返回以 e 为底的 numeric_expression 的对数。

单行函数

数字函数

LOG

返回以 x 为底的 y 的对数。

单行函数

数字函数

MOD

返回 x 除以 y 的余数。

单行函数

数字函数

POWER

返回 xy 次幂。

单行函数

数字函数

REMAINDER

返回 x 除以 y 的余数。

单行函数

数字函数

ROUND

返回 numberic 四舍五入后的值。

单行函数

数字函数

SIGN

返回数字 n 的符号,大于 0 返回 1,小于 0 返回 -1 ,等于 0 返回 0。

单行函数

数字函数

SIN

OceanBase 暂不支持。

单行函数

数字函数

SINH

OceanBase 暂不支持。

单行函数

数字函数

SQRT

返回 n 的平方根。

单行函数

数字函数

TAN

OceanBase 暂不支持。

单行函数

数字函数

TANH

OceanBase 暂不支持。

单行函数

数字函数

TRUNC

返回 numeric 按精度 precision 截取后的值。

单行函数

数字函数

WIDTH_BUCKET

OceanBase 暂不支持。

返回字符串的字符串函数

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

注意

如果返回值的数据类型是 CLOB,当返回值长度超过了最大限制时,OceanBase 不会返回数据且显示错误提示。

函数分类

函数子分类

函数名

功能描述

单行函数

返回字符串的字符串函数

CHR

n 转换为等价的一个或多个字符返回,且返回值与当前系统的字符集相关。

单行函数

返回字符串的字符串函数

CONCAT

连接两个字符串。

单行函数

返回字符串的字符串函数

INITCAP

返回字符串并将字符串中每个单词的首字母大写,其他字母小写。

单行函数

返回字符串的字符串函数

LOWER

将字符串全部转为小写。

单行函数

返回字符串的字符串函数

LPAD

在字符串 c1 的左边用字符串 c2 填充,直到长度为 n 时为止。

单行函数

返回字符串的字符串函数

LTRIM

删除左边出现的字符串。

单行函数

返回字符串的字符串函数

REGEXP_REPLACE

用于正则表达式替换。

单行函数

返回字符串的字符串函数

REGEXP_SUBSTR

OceanBase 暂不支持。

单行函数

返回字符串的字符串函数

REPLACE

将字符表达式值中,部分相同字符串,替换成新的字符串。

单行函数

返回字符串的字符串函数

RPAD

在字符串 c1 的右边用字符串 c2 填充,直到长度为 n 时为止。

单行函数

返回字符串的字符串函数

RTRIM

删除右边出现的字符串,此函数对于格式化查询的输出非常有用。

单行函数

返回字符串的字符串函数

SUBSTR

截取子字符串。其中多字节符(汉字、全角符等)按 1 个字符计算。

单行函数

返回字符串的字符串函数

TRANSLATE

将字符表达式值中,指定字符替换为新字符。多字节符(汉字、全角符等),按 1 个字符计算。

单行函数

返回字符串的字符串函数

TRIM

删除一个字符串的开头或结尾(或两者)的字符。

单行函数

返回字符串的字符串函数

UPPER

将字符串全部转为大写。

返回数字的字符串函数

函数分类函数子分类函数名功能描述

单行函数

返回数字的字符串函数

ASCII

返回字符表达式最左端字符的 ASCII 码值。

单行函数

返回数字的字符串函数

INSTR

在一个字符串中搜索指定的字符,返回发现指定的字符的位置。

单行函数

返回数字的字符串函数

LENGTH

返回字符串的长度。

单行函数

返回数字的字符串函数

REGEXP_COUNT

OceanBase 暂不支持。

单行函数

返回数字的字符串函数

REGEXP_INSTR

OceanBase 暂不支持。

时间日期函数

时间日期函数支持的入参数据类型有三类:日期时间 (DATE)、时间戳 (TIMESTAMPTIMESTAMP WITH TIME ZONETIMESTAMP WITH LOCAL TIME ZONE),以及间隔 (INTERVAL DAY TO SECONDINTERVAL YEAR TO MONTH)

仅支持 DATE 数据类型入参的函数列表:ADD_MONTHSCURRENT_DATE, LAST_DAYNEW_TIMENEXT_DAY

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

在使用时间函数前,建议执行 SELECT * FROM NLS_DATABASE_PARAMETERS 查看当前的 NLS 格式:

  1. obclient> SELECT * FROM NLS_DATABASE_PARAMETERS;
  2. +-------------------------+------------------------------+
  3. | PARAMETER | VALUE |
  4. +-------------------------+------------------------------+
  5. | NLS_DATE_FORMAT | DD-MON-RR |
  6. | NLS_TIMESTAMP_FORMAT | YYYY-MM-DD HH24:MI:SS |
  7. | NLS_TIMESTAMP_TZ_FORMAT | DD-MON-RR HH.MI.SSXFF AM TZR |
  8. | NLS_TERRITORY | AMERICA |
  9. | NLS_SORT | BINARY |
  10. | NLS_COMP | BINARY |
  11. | NLS_CHARACTERSET | AL32UTF8 |
  12. | NLS_NCHAR_CHARACTERSET | AL16UTF16 |
  13. | NLS_DATE_LANGUAGE | AMERICAN |
  14. | NLS_LENGTH_SEMANTICS | BYTE |
  15. | NLS_NCHAR_CONV_EXCP | FALSE |
  16. | NLS_CALENDAR | GREGORIAN |
  17. | NLS_NUMERIC_CHARACTERS | ., |
  18. +-------------------------+------------------------------+
  19. 13 rows in set (0.05 sec)

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

  1. obclient>ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
  2. Query OK, 0 rows affected (0.00 sec)
  3. obclient>ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
  4. Query OK, 0 rows affected (0.00 sec)
  5. /*将返回值中秒的小数位设置为 9 位*/
  6. obclient> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF TZR TZD';
  7. Query OK, 0 rows affected (0.00 sec)

注意

  • MONTHS_BETWEEN 返回值为 1 个数字。

  • ROUNDTRUNC 并不能进行隐式转换,必须传输 DATE 类型的数值,否则会报错。

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

函数分类

函数子分类

函数名

功能描述

单行函数

时间日期函数

ADD_MONTHS

返回在日期 date 基础上 n 个月后的日期值,如果 n 的值为负数则返回日期 date 基础上 n 个月前的日期值(date 减去 n 个月)。

单行函数

时间日期函数

CURRENT_DATE

返回当前会话时区中的当前日期。

单行函数

时间日期函数

CURRENT_TIMESTAMP

返回 TIMESTAMP WITH TIME ZONE 数据类型的当前会话时区中的当前日期,返回值中包含当前的时区信息。

单行函数

时间日期函数

DBTIMEZONE

返回当前数据库实例的时区,在 OceanBase 中数据库时区恒为+00:00,且不支持修改。

单行函数

时间日期函数

EXTRACT (datetime)

从指定的时间字段或表达式中抽取年、月、日、时、分、秒等元素。

单行函数

时间日期函数

FROM_TZ

将一个 TIMSTAMP 数据类型的值和时区信息拼成一个 TIMESTAMP WITH TIME ZONE 数据类型的时间值。

单行函数

时间日期函数

LAST_DAY

返回日期 date 所在月份的最后一天的日期。

单行函数

时间日期函数

LOCALTIMESTAMP

返回当前会话时区中的当前日期,返回 TIMESTAMP 数据类型的值。

单行函数

时间日期函数

MONTHS_BETWEEN

返回返回参数 date1date2 之间的月数。

单行函数

时间日期函数

NEW_TIME

OceanBase 暂不支持。

单行函数

时间日期函数

NEXT_DAY

返回日期 d1 的下一周中 c1(星期值)所在的日期值。

单行函数

时间日期函数

NUMTODSINTERVAL

把参数 n 转为以参数 interval_unit 为单位的 INTERVAL DAY TO SECOND 数据类型的值。

单行函数

时间日期函数

NUMTOYMINTERVAL

把参数 n 转为以 interval_unit 为单位的 INTERVAL YEAR TO MONTH 数据类型的值。

单行函数

时间日期函数

ROUND (date)

返回以参数 fmt 为单位距离的离指定日期 date 最近的日期时间值。

单行函数

时间日期函数

SESSIONTIMEZONE

返回当前会话时区。

单行函数

时间日期函数

SYS_EXTRACT_UTC

返回与指定时间相对应的的标准 UTC 时间。

单行函数

时间日期函数

SYSDATE

返回当前日期。

单行函数

时间日期函数

SYSTIMESTAMP

返回系统当前日期,返回值的秒的小数位包含 6 位精度,且包含当前时区信息。

单行函数

时间日期函数

TO_CHAR (datetime)

DATETIMESTAMPTIMESTAMP WITH TIME ZONETIMESTAMP WITH LOCAL TIME ZONEINTERVAL DAY TO SECONDINTERVAL YEAR TO MONTH 等数据类型的值按照参数 fmt 指定的格式转换为 VARCHAR2 数据类型的值。

单行函数

时间日期函数

TO_DSINTERVAL

将一个 CHARVARCHAR2NCHARNVARCHAR2 数据类型的字符串转换为一个 INTERVAL DAY TO SECOND 数据类型的值,该函数可以用来对一个日期时间值进行加减计算。

单行函数

时间日期函数

TO_TIMESTAMP

将字符串转换为 TIMESTAMP 数据类型。

单行函数

时间日期函数

TO_TIMESTAMP_TZ

将字符串转换为 TIMESTAMP WITH TIME ZONE 数据类型,包含时区信息。

单行函数

时间日期函数

TO_YMINTERVAL

将一个 CHARVARCHAR2NCHARNVARCHAR2 数据类型的字符串转换为一个 INTERVAL YEAR TO MONTH 数据类型的值,该函数可以用来对一个日期时间值进行加减计算。

单行函数

时间日期函数

TRUNC (date)

返回以参数 fmt 为单位距离的离指定日期 date 最近的日期时间值,并且返回的日期值在 date 之前。

单行函数

时间日期函数

TZ_OFFSET

返回时区 n 的时区偏移量。时区偏移量是指与格林尼治标准时间 GMT 的差(小时和分钟)。

通用比较函数

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

函数分类

函数子分类

函数名

功能描述

单行函数

通用比较函数

GREATEST

返回一个或多个表达式列表中的最大值。

单行函数

通用比较函数

LEAST

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

转换函数

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

函数分类

函数子分类

函数名

功能描述

函数分类函数子分类函数名功能描述

单行函数

转换函数

ASCIISTR

OceanBase 暂不支持。

单行函数

转换函数

BIN_TO_NUM

OceanBase 暂不支持。

单行函数

转换函数

CHARTOROWID

OceanBase 暂不支持。

单行函数

转换函数

HEXTORAW

CHARVARCHAR2NCHARNVARCHAR2 数据类型中包含十六进制数字的字符转换为 RAW 数据类型。

单行函数

转换函数

RAWTOHEX

将二进制数转换为一个相应的十六进制表示的字符串。

单行函数

转换函数

TO_BINARY_DOUBLE

返回一个双精度的 64 位浮点数.

单行函数

转换函数

TO_BINARY_FLOAT

返回一个单精度的 32 位浮点数。

单行函数

转换函数

TO_CHAR (character)

NCHARNVARCHAR2CLOB 数据转换为数据库字符集。

单行函数

转换函数

TO_CHAR (datetime)

DATETIMESTAMPTIMESTAMP WITH TIME ZONETIMESTAMP WITH LOCAL TIME ZONEINTERVAL DAY TO SECONDINTERVAL YEAR TO MONTH 等数据类型的值按照参数 fmt 指定的格式转换为 VARCHAR2 数据类型的值。

单行函数

转换函数

TO_CHAR (number)

NUMBERBINARY_FLOATBINARY_DOUBLE 类型的数值 n 按照指定数值格式 fmt 转换为 varchar2 数据类型的值。

单行函数

转换函数

TO_DATE

CHARVARCHARNCHARNVARCHAR2 数据类型的字符转换为日期数据类型的值。

单行函数

转换函数

TO_DSINTERVAL

将一个 CHARVARCHAR2NCHARNVARCHAR2 数据类型的字符串转换为一个 INTERVAL DAY TO SECOND 数据类型的值,该函数可以用来对一个日期时间值进行加减计算。

单行函数

转换函数

TO_NUMBER

expr 转换为数值数据类型的值。

单行函数

转换函数

TO_TIMESTAMP

将字符串转换为 TIMESTAMP 数据类型。

单行函数

转换函数

TO_TIMESTAMP_TZ

将字符串转换为 TIMESTAMP WITH TIME ZONE 数据类型,包含时区信息。

单行函数

转换函数

TO_YMINTERVAL

将一个 CHARVARCHAR2NCHARNVARCHAR2 数据类型的字符串转换为一个 INTERVAL YEAR TO MONTH 数据类型的值,该函数可以用来对一个日期时间值进行加减计算。

编码解码函数

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

函数分类

函数子分类

函数名

功能描述

单行函数

编码解码函数

DECODE

会根据条件返回相应值。

单行函数

编码解码函数

ORA_HASH

获取对应表达式的 HASH 值。

单行函数

编码解码函数

VSIZE

返回 X 的字节大小数。

空值相关函数

函数分类

函数子分类

函数名

功能描述

单行函数

空值相关函数

COALESCE

返回参数列表中第一个非空表达式,必须指定最少两个参数。

单行函数

空值相关函数

LNNVL

判断条件中的一个或者两个操作数是否为 NULL

单行函数

空值相关函数

NULLIF

OceanBase 暂不支持。

单行函数

空值相关函数

NVL

从两个表达式返回一个非 NULL 值。如果 expr1expr2 的结果皆为 NULL 值,则 NVL 函数返回 NULL。

单行函数

空值相关函数

NVL2

根据表达式是否为空,返回不同的值。如果 expr1 不为空,则返回 expr2 的值,如果 expr1 为空,则返回 expr3 的值。expr2expr3 类型不同的话,expr3 会转换为 expr1 的类型。

环境相关函数

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

函数分类

函数子分类

函数名

功能描述

单行函数

环境相关函数

SYS_CONTEXT

OceanBase 暂不支持。

单行函数

环境相关函数

UID

OceanBase 暂不支持。

单行函数

环境相关函数

USER

OceanBase 暂不支持。

聚合函数

函数分类

函数子分类

函数名

功能描述

统计函数

聚合函数

AVG

返回数值列的平均值。

统计函数

聚合函数

COUNT

用于查询参数 expr 的行数。

统计函数

聚合函数

SUM

返回参数中指定列的和。

统计函数

聚合函数

GROUPING

OceanBase 暂不支持。

统计函数

聚合函数

MAX

返回参数中指定的列中的最大值。

统计函数

聚合函数

MIN

返回参数中指定列的最小值。

统计函数

聚合函数

LISTAGG

用于列转行,LISTAGGORDER BY 子句中指定的每个组内的数据进行排序,然后合并度量列的值。

统计函数

聚合函数

ROLLUP

在数据统计和报表生成过程中,它可以为每个分组返回一个小计,同时为所有分组返回总计。

统计函数

聚合函数

STDDEV

用于计算总体标准差。

统计函数

聚合函数

STDDEV_POP

计算总体标准差。

统计函数

聚合函数

STDDEV_SAMP

计算样本标准差。

统计函数

聚合函数

VARIANCE

返回参数指定列的方差。

统计函数

聚合函数

APPROX_COUNT_DISTINCT

计算某一列去重后的行数,返回的值是一个近似值,该函数可以进一步用于计算被引用的列的选择性。

分析函数

函数分类

函数子分类

函数名

功能描述

统计函数

分析函数

AVG

返回数值列的平均值。

统计函数

分析函数

COUNT

用于查询参数 expr 的行数。

统计函数

分析函数

CUME_DIST

计算一个值在一组值中的累积分布。

统计函数

分析函数

DENSE_RANK

计算有序行组中行的秩,并将秩作为 NUMBER 返回。

统计函数

分析函数

MAX

返回参数中指定的列中的最大值。

统计函数

分析函数

MIN

返回参数中指定列的最小值。

统计函数

分析函数

SUM

返回参数中指定列的和。

统计函数

分析函数

FIRST_VALUE

返回有序值中的第一个值。

统计函数

分析函数

LAG

提供对多行表的访问,而不需要自连接。

统计函数

分析函数

LAST_VALUE

返回一组有序值中的最后一个值。

统计函数

分析函数

LEAD

它提供了对表多行的访问,而无需进行自我连接。给定从查询返回的一些列行和光标的位置,LEAD 提供超出该位置的物理偏移量的行的访问。

统计函数

分析函数

LISTAGG

用于列转行。

统计函数

分析函数

NTH_VALUE

返回 analytic_clause 定义的窗口中第 n 行的 measure_expr 值。

统计函数

分析函数

NTILE

将有序数据集划分为 expr 指示的若干桶,并为每一行分配适当的桶号。

统计函数

分析函数

PERCENT_RANK

类似于 CUME_DIST(累积分布)函数。 它的返回值范围为 0~1。任何集合中的第一行的PERCENT_RANK 函数为 0,返回值为 NUMBER。

统计函数

分析函数

SUM

返回参数中指定列的和。

统计函数

分析函数

RANK

基于 OVER 子句中的 ORDER BY 表达式确定一组值的排名。

统计函数

分析函数

RATIO_TO_REPORT

计算一个值与一组值之和的比率。

统计函数

分析函数

ROW_NUMBER

为应用它的每一行分配一个唯一的数字。

统计函数

分析函数

STDDEV

用于计算总体标准差。

统计函数

分析函数

STDDEV_POP

计算总体标准差。

统计函数

分析函数

STDDEV_SAMP

计算样本标准差。

统计函数

分析函数

VARIANCE

返回参数指定列的方差。

更多信息

分析函数中的关键字 OVER,请参阅 窗口函数说明