函数

本章节主要介绍 DM 数据库函数的使用。

适用场景

软件 版本
操作系统 Redhat 7 及以上版本
DM 数据库 DM 8.0 及以上版本
CPU 架构 x86、ARM、龙芯、飞腾等国内外主流 CPU

函数说明

DM 数据库函数可以帮助用户更加方便地处理表中的数据。函数不但可以在 SELECT 查询语句中使用,同样可以在 INSERT、UPDATE、DELETE 等语句中使用。这些函数丰富了 SQL 的语言功能,为 DMSQL 提供了更多的操作性。DM 数据库函数可以接受零个或者多个输入参数,并返回一个输出结果。DM 数据库除了内置丰富的数据库函数外,也支持自定义函数,可为开发者扩展更多便捷的功能。

函数的使用

使用 LENGTH 函数计算出员工姓名名字的字数。示例语句如下所示:

SELECT employee_name, LENGTH(employee_name) FROM "DMHR"."EMPLOYEE";

输出结果:

LENGTH 函数

自定义函数的创建

函数主要有下面几部分组成:

  • 输入部分:函数可以有输入参数,在调用函数时,必须给输入参数赋值。
  • 逻辑计算部分:逻辑计算部分是由 DMSQL 块组成业务逻辑计算部分。这部分主要是通过输入参数、表数据、SQL 计算函数等进行逻辑计算得到想要的结果。
  • 输出部分:通过逻辑计算部分,我们会得到一个函数的唯一返回值进行返回(函数必须要有返回值)。

语法结构如下:

create [or replace] function 函数名
([p1,p2...pn])
return datatype
is|as
--声明部分
begin
--程序块
end

语法解析:

  • function 是创建函数的关键字。
  • p1,p2...pn 是函数的入参,DM 数据库创建的函数也可以不需要入参。
  • return datatype 是函数的返回值的类型。
  • 通过 is 或者 as 承接着程序块。这部分是函数的计算内容。

自建 GET_SEX 函数,可以根据身份证号计算出员工的性别。示例语句如下所示:

CREATE OR REPLACE FUNCTION GET_SEX(id_card IN VARCHAR(50))
RETURN CHAR(2)
AS
    v_sex CHAR(2);
BEGIN
    IF to_number(substr(id_card,17,1))%2=1 THEN
     v_sex:= '男';
    ELSE  
     v_sex:= '女';
    END IF;
    RETURN v_sex;
END;

使用函数:

select identity_card ,GET_SEX(identity_card) from DMHR.EMPLOYEE;

输出结果:

LENGTH 函数

DM 数据库常用内置函数和说明

数值函数

序号 函数名 功能简要说明
1 ABS(n) 求数值 n 的绝对值
2 ACOS(n) 求数值 n 的反余弦值
3 ASIN(n) 求数值 n 的反正弦值
4 ATAN(n) 求数值 n 的反正切值
5 ATAN2(n1,n2) 求数值 n1/n2 的反正切值
6 CEIL(n) 求大于或等于数值 n 的最小整数
7 CEILING(n) 求大于或等于数值 n 的最小整数,等价于 CEIL(n)
8 COS(n) 求数值 n 的余弦值
9 COSH(n) 求数值 n 的双曲余弦值
10 COT(n) 求数值 n 的余切值
11 DEGREES(n) 求弧度 n 对应的角度值
12 EXP(n) 求数值 n 的自然指数
13 FLOOR(n) 求小于或等于数值 n 的最大整数
14 GREATEST(n1,n2,n3) 求 n1、n2 和 n3 三个数中最大的一个
15 GREAT (n1,n2) 求 n1、n2 两个数中最大的一个
16 LEAST(n1,n2,n3) 求 n1、n2 和 n3 三个数中最小的一个
17 LN(n) 求数值 n 的自然对数
18 LOG(n1[,n2]) 求数值 n2 以 n1 为底数的对数
19 LOG10(n) 求数值 n 以 10 为底的对数
20 MOD(m,n) 求数值 m 被数值 n 除的余数
21 PI() 得到常数 π
22 POWER(n1,n2)/POWER2(n1,n2) 求数值 n2 以 n1 为基数的指数
23 RADIANS(n) 求角度 n 对应的弧度值
24 RAND([n]) 求一个 0 到 1 之间的随机浮点数
25 ROUND(n[,m]) 求四舍五入值函数
26 SIGN(n) 判断数值的数学符号
27 SIN(n) 求数值 n 的正弦值
28 SINH(n) 求数值 n 的双曲正弦值
29 SQRT(n) 求数值 n 的平方根
30 TAN(n) 求数值 n 的正切值
31 TANH(n) 求数值 n 的双曲正切值
32 TO_NUMBER (char [,fmt]) 将 CHAR、VARCHAR、VARCHAR2 等类型的字符串转换为 DECIMAL 类型的数值
33 TRUNC(n[,m]) 截取数值函数
34 TRUNCATE(n[,m]) 截取数值函数,等价于 TRUNC(n[,m])
35 TO_CHAR(n [, fmt [,'nls' ] ]) 将数值类型的数据转换为 VARCHAR 类型输出
36 BITAND(n1, n2) 求两个数值型数值按位进行 AND 运算的结果

字符串函数

序号 函数名 功能简要说明
1 ASCII(char) 返回字符对应的整数
2 ASCIISTR(char) 将字符串 char 中,非 ASCII 的字符转成 \XXXX (UTF-16) 格式,ASCII 字符保持不变
3 BIT_LENGTH(char) 求字符串的位长度
4 CHAR(n) 返回整数 n 对应的字符
5 CHAR_LENGTH(char)/CHARACTER_LENGTH(char) 求字符串的串长度
6 CHR(n) 返回整数 n 对应的字符,等价于 CHAR(n)
7 CONCAT(char1,char2,char3,…) 顺序联结多个字符串成为一个字符串
8 DIFFERENCE(char1,char2) 比较两个字符串的 SOUNDEX 值之差异,返回两个 SOUNDEX 值串同一位置出现相同字符的个数。
9 INITCAP(char) 将字符串中单词的首字符转换成大写的字符
10 INS(char1,begin,n,char2) 删除在字符串 char1 中以 begin 参数所指位置开始的n 个字符,再把 char2 插入到 char1 串的 begin 所指位置
11 INSERT(char1,n1,n2,char2)/INSSTR(char1,n1,n2,char2) 将字符串 char1 从 n1 的位置开始删除 n2 个字符,并将 char2 插入到 char1 中 n1 的位置
12 INSTR(char1,char2[,n,[m]]) 从输入字符串 char1 的第 n 个字符开始查找字符串char2 的第 m 次出现的位置,以字符计算
13 INSTRB(char1,char2[,n,[m]]) 从 char1 的第 n 个字节开始查找字符串 char2 的第 m次出现的位置,以字节计算
14 LCASE(char) 将大写的字符串转换为小写的字符串
15 LEFT(char,n) /LEFTSTR(char,n) 返回字符串最左边的 n 个字符组成的字符串
16 LEN(char) 返回给定字符串表达式的字符(而不是字节)个数(汉字为一个字符),其中不包含尾随空格
17 LENGTH(char) 返回给定字符串表达式的字符(而不是字节)个数(汉字为一个字符),其中包含尾随空格
18 OCTET_LENGTH(char) 返回输入字符串的字节数
19 LOCATE(char1,char2[,n]) 返回 char1 在 char2 中首次出现的位置
20 LOWER(char) 将大写的字符串转换为小写的字符串
21 LPAD(char1,n,char2) 在输入字符串的左边填充上 char2 指定的字符,将其拉伸至 n 个字节长度
22 LTRIM(char1,char2) 从输入字符串中删除所有的前导字符,这些前导字符由
23 POSITION(char1,/ IN char2) 求串 1 在串 2 中第一次出现的位置
24 REPEAT(char,n) /REPEATSTR(char,n) 返回将字符串重复 n 次形成的字符串
25 REPLACE(STR, search[,replace] ) 将输入字符串 STR 中所有出现的字符串 search 都替换成字符串 replace,其中 STR 为 char、clob 或 text 类型
26 REPLICATE(char,times) 把字符串 char 自己复制 times 份
27 REVERSE(char) 将字符串反序
28 RIGHT / RIGHTSTR(char,n) 返回字符串最右边 n 个字符组成的字符串
29 RPAD(char1,n,char2) 类似 LPAD 函数,只是向右拉伸该字符串使之达到 n 个字节长度
30 RTRIM(char1,char2) 从输入字符串的右端开始删除 char2 参数中的字符
31 SOUNDEX(char) 返回一个表示字符串发音的字符串
32 SPACE(n) 返回一个包含 n 个空格的字符串
33 STRPOSDEC(char) 把字符串 char 中最后一个字符的值减一
34 STRPOSDEC(char,pos) 把字符串 char 中指定位置 pos 上的字符值减一
35 STRPOSINC(char) 把字符串 char 中最后一个字符的值加一
36 STRPOSINC(char,pos) 把字符串 char 中指定位置 pos 上的字符值加一
37 STUFF(char1,begin,n,char2) 删除在字符串 char1 中以 begin 参数所指位置开始的 n 个字符,再把 char2 插入到 char1 串的 begin 所指位置
38 SUBSTR(char,m,n)/ SUBSTRING(char FROM m [FORn]) 返回 char 中从字符位置 m 开始的 n 个字符
39 SUBSTRB(char,n,m) SUBSTR 函数等价的单字节形式
40 TO_CHAR(character) 将 VARCHAR、CLOB、TEXT 类型的数据转化为 VARCHAR 类型输出
41 TRANSLATE(char,from,to) 将所有出现在搜索字符集中的字符转换成字符集中的相应字符
42 TRIM([LEADING|TRAILING|BOTH] [exp] [] FROM char2]) 删去字符串 char2 中由串 char1 指定的字符
43 UCASE(char) 将小写的字符串转换为大写的字符串
44 UPPER(char) 将小写的字符串转换为大写的字符串
45 REGEXP 根据符合 POSIX 标准的正则表达式进行字符串匹配
46 OVERLAY(char1 PLACINGchar2 FROM int [FOR int]) 字符串覆盖函数,用 char2 覆盖 char1 中指定的子串,返回修改后的 char1
47 TEXT_EQUAL 返回两个 LONGVARCHAR 类型的值的比较结果,相同返回 1,否则返回 0
48 BLOB_EQUAL 返回两个 LONGVARBINARY 类型的值的比较结果,相同返回 1,否则返回 0
49 NLSSORT(str1[,nls_sort=str2]) 返回对汉字排序的编码
50 GREATEST(char1, char2,char3) 求 char 1、char 2 和 char 3 中最大的字符串
51 GREAT (char1, char2) 求 char 1、char 2 中最大的字符串
52 TO_SINGLE_BYTE (char) 将多字节形式的字符(串)转换为对应的单字节形式
53 TO_MULTI_BYTE (char) 将单字节形式的字符(串)转换为对应的多字节形式
54 EMPTY_CLOB () 初始化 clob 字段
55 EMPTY_BLOB () 初始化 blob 字段
56 UNISTR (char) 将字符串 char 中,ascii 码(‗\XXXX‘4 个 16 进制字符格式)转成本地字符。对于其他字符保持不变。
57 ISNULL(char) 判断表达式是否为 NULL

日期时间函数

序号 函数名 功能简要说明
1 ADD_DAYS(date,n) 返回日期加上 n 天后的新日期
2 ADD_MONTHS(date,n) 在输入日期上加上指定的几个月返回一个新日期
3 ADD_WEEKS(date,n) 返回日期加上 n 个星期后的新日期
4 CURDATE() 返回系统当前日期
5 CURTIME(n) 返回系统当前时间
6 CURRENT_DATE() 返回系统当前日期
7 CURRENT_TIME(n) 返回系统当前时间
8 CURRENT_TIMESTAMP(n) 返回系统当前带会话时区信息的时间戳
9 DATEADD(datepart,n,date) 向指定的日期加上一段时间
10 DATEDIFF(datepart,date1,date2) 返回跨两个指定日期的日期和时间边界数
11 DATEPART(datepart,date) 返回代表日期的指定部分的整数
12 DAY(date) 返回日期中的天数
13 DAYNAME(date) 返回日期的星期名称
14 DAYOFMONTH(date) 返回日期为所在月份中的第几天
15 DAYOFWEEK(date) 返回日期为所在星期中的第几天
16 DAYOFYEAR(date) 返回日期为所在年中的第几天
17 DAYS_BETWEEN(date1,date2) 返回两个日期之间的天数
18 EXTRACT(时间字段 FROM date) 抽取日期时间或时间间隔类型中某一个字段的值
19 GETDATE(n) 返回系统当前时间戳
20 GREATEST(date1,date2,date3) 求 date1、 date2 和 date3 中的最大日期
21 GREAT (date1,date2) 求 date1、 date2 中的最大日期
22 HOUR(time) 返回时间中的小时分量
23 LAST_DAY(date) 返回输入日期所在月份最后一天的日期
24 LEAST(date1, date2, date3) 求 date1、 date2 和 date3 中的最小日期
25 MINUTE(time) 返回时间中的分钟分量
26 MONTH(date) 返回日期中的月份分量
27 MONTHNAME(date) 返回日期中月分量的名称
28 MONTHS_BETWEEN(date1,date2) 返回两个日期之间的月份数
29 NEXT_DAY(date1,char2) 返回输入日期指定若干天后的日期
30 NOW(n) 返回系统当前时间戳
31 QUARTER(date) 返回日期在所处年中的季节数
32 SECOND(time) 返回时间中的秒分量
33 ROUND (date1[, fmt]) 把日期四舍五入到最接近格式元素指定的形式
34 TIMESTAMPADD(datepart,n,timestamp) 返回时间戳 timestamp 加上 n 个 datepart 指定的时间段的结果
35 TIMESTAMPDIFF(datepart,timeStamp1,timestamp2) 返回一个表明 timestamp2 与 timestamp1 之间的指定 datepart 类型时间间隔的整数
36 SYSDATE() 返回系统的当前日期
37 TO_DATE(CHAR[,fmt[,'nls']])
/TO_TIMESTAMP(CHAR[,fmt[,'nls']])
/TO_TIMESTAMP_TZ(CHAR[,fmt])
字符串转换为日期时间数据类型
38 FROM_TZ(timestamp,timezone|tz_name]) 将时间戳类型 timestamp 和时区类型 timezone(或时区名称 tz_name )转化为 timestamp withtimezone 类型
39 TRUNC(date[,fmt]) 把日期截断到最接近格式元素指定的形式
40 WEEK(date) 返回日期为所在年中的第几周
41 WEEKDAY(date) 返回当前日期的星期值
42 WEEKS_BETWEEN(date1,date2) 返回两个日期之间相差周数
43 YEAR(date) 返回日期的年分量
44 YEARS_BETWEEN(date1,date2) 返回两个日期之间相差年数
45 LOCALTIME(n) 返回系统当前时间
46 LOCALTIMESTAMP(n) 返回系统当前时间戳
47 OVERLAPS 返回两个时间段是否存在重叠
48 TO_CHAR(date[,fmt[,nls]]) 将日期数据类型 DATE 转换为一个在日期语法 fmt 中指定语法的 VARCHAR 类型字符串。
49 SYSTIMESTAMP(n) 返回系统当前带数据库时区信息的时间戳
50 NUMTODSINTERVAL(dec,interval_unit) 转换一个指定的 DEC 类型到 INTERVAL DAY TOSECOND
51 NUMTOYMINTERVAL(dec,interval_unit) 转换一个指定的 DEC 类型值到 INTERVAL YEAR TOMONTH
52 WEEK(date, mode) 根据指定的 mode 计算日期为年中的第几周
53 UNIX_TIMESTAMP (datetime) 返回自标准时区的'1970-01-01 00:00:00 +0:00'的到本地会话时区的指定时间的秒数差
54 FROM_UNIXTIME(unixtime) 返回将自'1970-01-01 00:00:00'的秒数差转成本地会话时区的时间戳类型
55 FROM_UNIXTIME(unixtime,fmt) 将自'1970-01-01 00:00:00'的秒数差转成本地会话时区的指定 fmt 格式的时间串
56 SESSIONTIMEZONE 返回当前会话的时区
57 DATE_FORMAT(d, format) 以不同的格式显示日期/时间数据
58 TIME_TO_SEC(d) 将时间换算成秒
59 SEC_TO_TIME(sec) 将秒换算成时间
60 TO_DAYS(timestamp) 转换成公元 0 年 1 月 1 日的天数差

空值判断函数

序号 函数名 功能简要说明
1 COALESCE(n1,n2,…nx) 返回第一个非空的值
2 IFNULL(n1,n2) 当 n1 为非空时,返回 n1;若 n1 为空,则返回 n2
3 ISNULL(n1,n2) 当 n1 为非空时,返回 n1;若 n1 为空,则返回 n2
4 NULLIF(n1,n2) 如果 n1=n2 返回 NULL,否则返回 n1
5 NVL(n1,n2) 返回第一个非空的值
6 NULL_EQU 返回两个类型相同的值的比较

类型转换函数

序号 函数名 功能简要说明
1 CAST(value AS 类型说明) 将 value 转换为指定的类型
2 CONVERT(类型说明,value) 将 value 转换为指定的类型
3 HEXTORAW(exp) 将 exp 转换为 BLOB 类型
4 RAWTOHEX(exp) 将 exp 转换为 VARCHAR 类型
5 BINTOCHAR(exp) 将 exp 转换为 CHAR
6 TO_BLOB(value) 将 value 转换为 blob

杂类函数

序号 函数名 功能简要说明
1 DECODE(exp, search1,result1, … searchn, resultn[,default]) 查表译码
2 ISDATE(exp) 判断表达式是否为有效的日期
3 ISNUMERIC(exp) 判断表达式是否为有效的数值
4 DM_HASH (exp) 根据给定表达式生成 HASH 值
5 LNNVL(condition) 根据表达式计算结果返回布尔值
6 LENGTHB(value) 返回 value 的字节数
7 FIELD(value, e1, e2, e3,e4...en) 返回 value 在列表 e1,e2,e3,e4...en 中的位置序号,不在输入列表时则返回 0

参考文档

更多 SQL 语言使用说明,请参考《DM_SQL 语言使用手册》,手册位于数据库安装路径 /dmdbms/doc 文件夹下。如有其他问题,请在社区内咨询。

微信扫码
分享文档
扫一扫
联系客服