更新时间:2022-08-16 GMT+08:00

函数

本章节介绍了在PostgreSQL11开源版本的基础上,PostgreSQL增强版支持的内置函数和高级函数包。

表1 内置函数

内置函数

说明

add_months(date,integer)

返回date加上integer的值,返回值为DATE类型。

appendchildxml(XMLType_instance, XPath_string, value_expr[, namespace_string])

在XMLType类型数据XMLType_instance指定位置XPath_string处追加value_expr节点,其中namespace_string用于描述XPath_string的命名空间信息。

asciistr(string)

返回给定字符串string对应的ASCII值,不支持非ASCII字符。

bin_to_num(expr_list)

将expr_list中的二进制串转换为对应的十进制数,返回值类型为NUMBER。

bitand(number1,number2)

返回number1和number2按位与的值,返回值为BIT类型。

convert(char, dest_char_set[, source_char_set])

将source_char_set字符集编码格式的输入字符串char,转换为dest_char_set字符集编码格式,该函数只在服务端生效。

cosh(n)

返回参数n的双曲余弦值;

decode(expr,search1, result1[[,search2, result2],......][, default])

将expr表达式值依次与所有search表达式(search1,search2......)进行比较,如果与searchn进行匹配,则返回resultn,否则返回default。如果没有default,返回null。

empty_blob()

返回一个空BLOB类型。

hextoraw(char)

将十六进制字符串转换为RAW类型的值。

instrb(string, substring[, position[, occurrence]])

返回从字符string的第position位置开始,第occurrence次出现的子串substring起始位置,默认从字符串string首部开始第一次出现的位置。

last_day(date)

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

lengthb(char)

返回char字符的字节长度,char支持的类型为所有字符串类型(如CHAR, VARCHAR2, NCHAR, NVARCHAR2等)或可隐式转换为字符串的类型(如integer等)。

listagg(measure_expr[, 'delimiter']) within group(order_by_clause) [over query_partition_clause]

将query_partition_clause分组中的每个列表达式measure_expr值按order_by_clause规则排序后,再合并成一行,值之间使用delimiter分隔符进行分割。

lnnvl(condition)

返回条件表达式condition的取反值,返回类型为BOOLEAN。

mod(n2, n1)

支持除数为0的情况,即当n1为0时,直接返回n2。

months_between(date1, date2)

返回date1与date2之间相差的月数,当date2早于date1,返回值为负数。

nanvl(n2, n1)

当单精度或双精度浮点数n2的值为NAN,则返回n1,否则返回n2。

nchr(number)

返回本地字符集中二进制与number相等的字符。

new_time(date, timezone1, timezone2)

返回timezone1的date时间对应的timezone2的时间,返回值类型为DATE。

next_day(date, char)

返回date后的第一个自然日(包括工作日、休息日和节假日),返回值为DATE类型。

numtodsinterval(n, interval_unit)

根据INTERVAL数据类型单元interval_unit(取值范围:'DAY'、'HOUR'、'MINUTE'、'SECOND'),将数字n转换为数据类型INTERVAL DAY TO SECOND。

numtoyminterval(n, 'interval_unit')

将数字n按照指定单位interval_unit转为数据类型INTERVAL YEAR TO MONTH,其中interval_unit取值为:YEAR、MONTH。

nlssort(char[, nlsparam])

按照nlsparam指定的排序字符集对字符串char进行排序,默认使用char字符串字符集排序;

nls_upper(char[, nlsparam])

根据nlsparam指定的排序规则将字符串char中的字母转换为大写,其中char字符串类型为CHAR, VARCHAR2, NCHAR, NVARCHAR2,CLOB或NCLOB,nlsparam的取值为'NLS_SORT = sort'。

nls_lower(char[, nlsparam])

根据nlsparam指定的排序规则将字符串char中的字母转换为小写,其中char字符串类型为CHAR, VARCHAR2, NCHAR, NVARCHAR2,CLOB或NCLOB,nlsparam的取值为'NLS_SORT = sort'。

nvl(expr1, expr2)

返回expr1, expr2中第一个非空值。

rawtohex(raw)

将RAW类型的值转换为十六进制字符串。

regexp_count(source_char, pattern, position, match_param)

返回指定字符串source_char中的指定位置position开始,匹配正则表达式模式pattern的次数。其中,match_param参数会影响正则表达式匹配规则,比如match_param='i'忽略大小写等。

regexp_instr(source_char, pattern[, position[, occurrence[, return_opt[, match_param[, subexpr]]]]])

该函数拓展了INSTR函数的功能,允许使用正则表达式匹配,返回值类型为INTEGER。

  • position表示查找起始位置。
  • occurrence表示查找pattern在source_char的第几次出现。
  • return_opt:
    • 取值为0表示返回模式匹配的起始位置。
    • 取值为1表示返回模式匹配的结束位置。
  • match_param表示正则表达式模式匹配控制参数,如区分大小写等。
  • subexpr表示正则表达式分组匹配的组号。

regexp_like(source_char, pattern[,match_param])

source_char为字符串表达式,pattern为正则表达式,match_param为正则表达式控制参数,返回字符串source_char是否可按照正则表达式pattern进行匹配。

regexp_substr(source_char, pattern[,position[,occurrence[,match_param[,subexpr]]]])

按正则表达式在source_char字符串中匹配子字符串。

  • source_char为查找的输入字符串,支持所有字符串类型(如CHAR, VARCHAR2, NCHAR, NVARCHAR2等)或可隐式转换为字符串的类型(如integer等)。
  • pattern为子字符串匹配的正则表达式。
  • position为指定匹配的起始字符位置。
  • occurrence为pattern在source_char出现的次数。
  • match_parameter为正则表达式控制参数。
  • subexpr为pattern的第几个子表达式,范围为0~9。

raise_application_error(errnum, errmsg)

将错误码errnum与错误消息errmsg输出到客户端。

remainder(n2, n1)

返回n2/n1的余数,类似于mod,区别在于,mod取余时用了floor处理,而remainder使用round处理。返回值类型为NUMERIC或双精度浮点数(由入参类型决定)。

round(n,precision)

返回n的四舍五入值,precision为精度值。

scn_to_timestamp(number)

返回SCN号number产生的最近时间戳;

sinh(n)

返回数字n的双曲正弦值,当n类型为BINARY_FLOAT,返回类型BINARY_DOUBLE,否则返回值类型为NUMERIC。

substr(char,position[,substring_length])

返回char字符串中第position个字符开始,长度为substring_length的子字符串。若不指定substring_length,则截取到字符串结尾。

substrb(char, position[, substring_length])

返回char字符串中第position个字节开始,长度为substring_length字节的子字符串。若不指定substring_length,则截取到字符串结尾。

sys_context(namespace, parameter)

返回指定参数parameter在命名空间namespace下的值,返回值类型为VARCHAR2。

sys_guid()

返回RAW类型的全局唯一标识。

sys_connect_by_path(column, char)

仅适用于CONNECT BY查询,返回column列的根节点;

tanh(n)

返回参数n的双曲正切值;

to_blob(char)

将char字符串转换为BLOB类型,char支持的类型为所有字符串类型(如CHAR, VARCHAR2, NCHAR, NVARCHAR2等)或可隐式转换为字符串的类型(如integer等)。

to_binary_float(expr)

将数字字符串expr转换为单精度float类型。

to_binary_double(expr)

将数字字符串expr转换为双精度float类型。

to_clob(char)

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

to_char(char)

增加char支持的类型:char、character、varchar。

to_date(char[,fmt])

将char时间字符串按照fmt格式转换为date数据类型,char支持的类型有CHAR、VARCHAR2、NCHAR、 NVARCHAR2、TIMESTAMP。如果省略fmt,则char必须采用DATE数据类型的默认格式。

to_dsinterval('sql_format' | 'ds_iso_format')

将SQL标准(如'100 00:00:00')或ISO标准(如'P100DT05H')的时间字符串转换为数据类型INTERVAL DAY TO SECOND。

to_multi_byte(char)

将单字节字符char转换成对应的多字节字符。

to_number(expr)

将expr表达式值转换为number类型。

to_number(expr, fmt, 'nlsparam')

按照指定格式fmt转换expr字符串为数字,返回类型为NUMBER,其中nlsparam为国际化语言参数,支持的参数分别为:NLS_NUMERIC_CHARACTERS、NLS_CURRENCY、NLS_ISO_CURRENCY。

to_timestamp(char[,fmt])

将char时间字符串按照fmt格式转换为timestamp数据类型,char支持的类型有CHAR、VARCHAR2、NCHAR、 NVARCHAR2、TIMESTAMP。如果省略fmt,则char必须采用TIMESTAMP数据类型的默认格式。

to_single_byte(char)

将多字节输入字符串转换为单字节字符串。

to_yminterval('sql_format' | 'ym_iso_format')

将SQL标准(如'01-02')或ISO标准(如'P1Y2M')的时间字符串转换为数据类型INTERVAL MONTH TO YEAR。

timestamp_to_scn(timestamp)

根据时间戳timestamp返回系统变更号SCN;

trunc(date[, fmt])

按照fmt指定的日期格式对date进行截断处理,返回值类型为DATE。如果省略fmt,则默认日期格式为'DDD'。

tz_offset({time_zone_name | '{+|-}hh:mi'})

返回指定时区的偏移量,返回值类型VARCHAR2,参数可以是时区名time_zone_name或者'{+|-}hh:mi'格式字符串。

value(correlation_variable)

以对象表的方式返回correlation_variable所关联标的记录行,返回类型为correlation_variable所关联的对象表。

表2 高级函数包

高级函数包

说明

DBMS_OUTPUT.PUT(item)

将item字符串放入本地缓冲区;item为所有可以转换为字符串的类型。

DBMS_OUTPUT.PUT_LINE(item)

将item字符串放入本地缓冲区,然后将整个本地缓冲区的内容整体输出;item为所有可以转换为字符串的类型。

DBMS_RANDOM.SEED(val)

val为生成随机数的种子,可为字符串和数字类型。

DBMS_RANDOM.VALUE([low,high])

返回low和high之间长度为16位的随机数,如果不指定low和high的范围,则默认范围为0~1。

dbms_lob.getlength(lob_loc {clob|blob})

返回lob_loc指定的LOB对象长度;

dbms_lob.read(lob_loc, amount, offset, buffer)

从指定偏移offset读取LOB对象lob_loc指定长度amount的内容写入缓冲区buffer;

dbms_lob.write(lob_loc, amount, offset, buffer)

将buffer缓冲器的内容写入大对象lob_loc缓冲区(并不影响所引用的大对象),从offset开始写入长度为amount。

utl_raw.cast_to_raw(char)

将VARCHAR2类型字符串char转换为RAW类型,返回值类型为RAW。

utl_raw.length(raw)

返回raw数据类型的字节长度,返回值类型为NUMBER。

utl_raw.cast_from_binary_integer(n, endianess)

按照endianess指定的内存对齐方式将整数n转换为RAW类型,其中endianess取值分别为:

  • 1,大端对齐;
  • 2,小端对齐;
  • 3,按机器对齐方式;