全部产品
云市场

函数概述

更新时间:2020-04-08 18:12:05

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

  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 运算符按位进行“与”操作。输入和输出类型均为 NUMBER 数据类型。
单行函数 数字函数 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 返回 numeric 四舍五入后的值。
单行函数 数字函数 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 类型的返回值。

注意:MONTHS_BETWEEN 返回值为 1 个数字。ROUNDTRUNC 并不能进行隐式转换,必须传输 DATE 类型的数值,否则会报错。

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

函数分类 函数子分类 函数名 功能描述
单行函数 时间日期函数 ADD_MONTHS 返回在日期 date 基础上 n 个月后的日期值,如果 n 的值为负数则返回日期 date 基础上 n 个月前的日期值(date 减去 n 个月)。
单行函数 时间日期函数 CURRENT_DATE 返回当前会话时区中的当前日期。
单行函数 时间日期函数 CURRENT_TIMESTAMP 返回 TIMESTAMP WITH TIME ZONE 数据类型的当前会话时区中的当前日期,返回值中包含当前的时区信息。
单行函数 时间日期函数 DBTIMEZONE 返回当前数据库实例的时区,在 OceanBase 中数据库时区恒为 GMT+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 暂不支持。
单行函数 转换函数 CAST 用于将源数据类型的表达式显式转换为另一种数据类型。
单行函数 转换函数 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 的字节大小数。

空值相关函数

可以通过本类型的函数在 OceanBase 数据库中对 NULL 值进行相关的处理。

函数分类 函数子分类 函数名 功能描述
单行函数 空值相关函数 COALESCE 返回参数列表中第一个非空表达式,必须指定最少两个参数。
单行函数 空值相关函数 LNNVL 判断条件中的一个或者两个操作数是否为 NULL。也可以将条件作为参数,如果条件为 FALSE 或 UNKNOWN,则返回 TRUE;如果条件为 TRUE,则返回 FALSE。
单行函数 空值相关函数 NULLIF OceanBase 暂不支持。
单行函数 空值相关函数 NVL 从两个表达式返回一个非 NULL 值。如果 expr1expr2 的结果皆为 NULL 值,则 NVL 函数返回 NULL。
单行函数 空值相关函数 NVL2 根据表达式是否为空,返回不同的值。如果 expr1 不为空,则返回 expr2 的值,如果 expr1 为空,则返回 expr3 的值。expr2expr3 类型不同的话,expr3 会转换为 expr1 的类型。

环境相关函数

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

函数分类 函数子分类 函数名 功能描述
单行函数 环境相关函数 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。
统计函数 分析函数 RANK 基于 OVER 子句中的 ORDER BY 表达式确定一组值的排名。
统计函数 分析函数 RATIO_TO_REPORT 计算一个值与一组值之和的比率。
统计函数 分析函数 ROW_NUMBER 为应用它的每一行分配一个唯一的数字。
统计函数 分析函数 STDDEV 用于计算总体标准差。
统计函数 分析函数 STDDEV_POP 计算总体标准差。
统计函数 分析函数 STDDEV_SAMP 计算样本标准差。
统计函数 分析函数 VARIANCE 返回参数指定列的方差。

更多信息