达梦(DM) SQL函数相关_达梦数据库创建函数

上文讲了DM数据的日期相关的运算以及利用分析函数 lead() over() 进行范围问题的处理，这里再继续说说DM数据库函数

函数

DM 数据库函数可以帮助用户更加方便地处理表中的数据。函数不但可以在 SELECT 查询语句中使用，同样可以在 INSERT、UPDATE、DELETE 等语句中使用。

函数的使用

使用 LENGTH 函数计算出员工姓名名字的字数

select ename,LENGTH(ename) from employee;
1

自定义函数的创建

语法结构如下

create [or replace] function 函数名

([p1,p2...pn])

return datatype

is|as

--声明部分

begin

--程序块

end
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

语法说明：
1.function 是创建函数的关键字。
2.p1,p2…pn 是函数的入参，DM 数据库创建的函数也可以不需要入参。
3.return datatype 是函数的返回值的类型。
4.通过 is 或者 as 承接着程序块。这部分是函数的计算内容。
自建 GET_SEX 函数，展示员工性别，原始数据

select * from employee;
1

创建自定义函数

CREATE OR REPLACE FUNCTION GET_SEX(mgr in INT)

RETURN CHAR(2)

AS

    v_sex CHAR(2);

BEGIN

    IF mgr=1 THEN

     v_sex:= '男';

    ELSE  

     v_sex:= '女';

    END IF;

    RETURN v_sex;

END;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

执行完成后可以看到模式-函数下已经创建的自定义函数

获取函数执行结果

select ename,GET_SEX(mgr) from employee;
1

DM内置加密解密函数

首先需要查询一下当前DM数据库版本支持的加密方式

SELECT * FROM V$CIPHERS;
1

这里我们用加密函数CFALGORITHMSENCRYPT(‘明文内容’,加密方式id,‘自定义密钥’) 514加密方式

select CFALGORITHMSENCRYPT('111111',514,'secret key');

--9FF46AA8D86FB25ADED775837F400BB5FE
1
2
3

解密用同样的解密函数CFALGORITHMSDECRYPT(‘密文内容’,加密方式id,‘自定义密钥’) 514解密

select CFALGORITHMSDECRYPT('9FF46AA8D86FB25ADED775837F400BB5FE',514,'secret key');
1

后续详细讲解加密这块的内容。

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

数值函数
序号 函数名 功能简要说明
01 ABS(n) 求数值 n 的绝对值
02 ACOS(n) 求数值 n 的反余弦值
03 ASIN(n) 求数值 n 的反正弦值
04 ATAN(n) 求数值 n 的反正切值
05 ATAN2(n1,n2) 求数值 n1/n2 的反正切值
06 CEIL(n) 求大于或等于数值 n 的最小整数
07 CEILING(n) 求大于或等于数值 n 的最小整数，等价于 CEIL(n)
08 COS(n) 求数值 n 的余弦值
09 COSH(n) 求数值 n 的双曲余弦值
10 COT(n) 求数值 n 的余切值
11 DEGREES(n) 求弧度 n 对应的角度值
12 EXP(n) 求数值 n 的自然指数
13 FLOOR(n) 求小于或等于数值 n 的最大整数
14 GREATEST(n {,n}) 求一个或多个数中最大的一个
15 GREAT (n1,n2) 求 n1、n2 两个数中最大的一个
16 LEAST(n {,n}) 求一个或多个数中最小的一个
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 运算的结果
37 NANVL(n1, n2) 有一个参数为空则返回空，否则返回 n1 的值
38 REMAINDER(n1, n2) 计算 n1 除 n2 的余数，余数取绝对值更小的那一个
39 TO_BINARY_FLOAT(n) 将 number、real 或 double 类型数值转换成 binary float 类型
40 TO_BINARY_DOUBLE(n) 将 number、real 或 float 类型数值转换成 binary double 类型

字符串函数
序号 函数名 功能简要说明
01 ASCII(char) 返回字符对应的整数
02 ASCIISTR(char) 将字符串 char 中，非 ASCII 的字符转成\XXXX(UTF-16)格式，ASCII 字符保持不变
03 BIT_LENGTH(char) 求字符串的位长度
04 CHAR(n) 返回整数 n 对应的字符
05 CHAR_LENGTH(char)/ CHARACTER_LENGTH(char) 求字符串的串长度
06 CHR(n) 返回整数 n 对应的字符，等价于 CHAR(n)
07 CONCAT(char1,char2,char3,…) 顺序联结多个字符串成为一个字符串
08 DIFFERENCE(char1,char2) 比较两个字符串的 SOUNDEX 值之差异，返回两个 SOUNDEX 值串同一位置出现相同字符的个数。
09 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(clob) 返回给定字符串表达式的字符(而不是字节)个数（汉字为一个字符），其中包含尾随空格
18 OCTET_LENGTH(char) 返回输入字符串的字节数
19 LOCATE(char1,char2[,n]) 返回 char1 在 char2 中首次出现的位置
20 LOWER(char) 将大写的字符串转换为小写的字符串
21 LPAD(char1,n,char2) 在输入字符串的左边填充上 char2 指定的字符，将其拉伸至 n 个字节长度
22 LTRIM(str[,set]) 删除字符串 str 左边起，出现在 set 中的任何字符，当遇到不在 set 中的第一个字符时返回结果
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(str[,set]) 删除字符串 str 右边起出现的 set 中的任何字符，当遇到不在 set 中的第一个字符时返回结果
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 [FOR n]) 返回 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> [char] | char> FROM] str) 删去字符串 str 中由 char 指定的字符
43 UCASE(char) 将小写的字符串转换为大写的字符串
44 UPPER(char) 将小写的字符串转换为大写的字符串
45 NLS_UPPER(char) 将小写的字符串转换为大写的字符串
46 REGEXP 根据符合 POSIX 标准的正则表达式进行字符串匹配
47 OVERLAY(char1 PLACING char2 FROM int [FOR int]) 字符串覆盖函数，用 char2 覆盖 char1 中指定的子串，返回修改后的 char1
48 TEXT_EQUAL 返回两个 LONGVARCHAR 类型的值的比较结果，相同返回 1，否则返回 0
49 BLOB_EQUAL 返回两个 LONGVARBINARY 类型的值的比较结果，相同返回 1，否则返回 0
50 NLSSORT(str1 [,nls_sort=str2]) 返回对自然语言排序的编码
51 GREATEST(char {,char}) 求一个或多个字符串中最大的字符串
52 GREAT (char1, char2) 求 char 1、char 2 中最大的字符串
53 to_single_byte (char) 将多字节形式的字符（串）转换为对应的单字节形式
54 to_multi_byte (char) 将单字节形式的字符（串）转换为对应的多字节形式
55 EMPTY_CLOB () 初始化 clob 字段
56 EMPTY_BLOB () 初始化 blob 字段
57 UNISTR (char) 将字符串 char 中，ASCII 编码或 Unicode 编码（‘\XXXX’4 个 16 进制字符格式）转成本地字符。对于其他字符保持不变。
58 ISNULL(char) 判断表达式是否为 NULL
59 CONCAT_WS(delim, char1,char2,char3,…) 顺序联结多个字符串成为一个字符串，并用 delim 分割
60 SUBSTRING_INDEX(char, delim, count) 按关键字截取字符串，截取到指定分隔符出现指定次数位置之前
61 COMPOSE(varchar str) 在 UTF8 库下，将 str 以本地编码的形式返回

日期时间函数
序号 函数名 功能简要说明
01 ADD_DAYS(date,n) 返回日期加上 n 天后的新日期
02 ADD_MONTHS(date,n) 在输入日期上加上指定的几个月返回一个新日期
03 ADD_WEEKS(date,n) 返回日期加上 n 个星期后的新日期
04 CURDATE() 返回系统当前日期
05 CURTIME(n) 返回系统当前时间
06 CURRENT_DATE() 返回系统当前日期
07 CURRENT_TIME(n) 返回系统当前时间
08 CURRENT_TIMESTAMP(n) 返回系统当前带会话时区信息的时间戳
09 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(date {,date}) 求一个或多个日期中的最大日期
21 GREAT (date1,date2) 求 date1、date2 中的最大日期
22 HOUR(time) 返回时间中的小时分量
23 LAST_DAY(date) 返回输入日期所在月份最后一天的日期
24 LEAST(date {,date}) 求一个或多个日期中的最小日期
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 with timezone 类型
39 TZ_OFFSET(timezone| [tz_name]) 返回给定的时区或时区名和标准时区(UTC)的偏移量
40 TRUNC(date[,fmt]) 把日期截断到最接近格式元素指定的形式
41 WEEK(date) 返回日期为所在年中的第几周
42 WEEKDAY(date) 返回当前日期的星期值
43 WEEKS_BETWEEN(date1,date2) 返回两个日期之间相差周数
44 YEAR(date) 返回日期的年分量
45 YEARS_BETWEEN(date1,date2) 返回两个日期之间相差年数
46 LOCALTIME(n) 返回系统当前时间
47 LOCALTIMESTAMP(n) 返回系统当前时间戳
48 OVERLAPS 返回两个时间段是否存在重叠
49 TO_CHAR(date[,fmt[,nls]]) 将日期数据类型 DATE 转换为一个在日期语法 fmt 中指定语法的 VARCHAR 类型字符串。
50 SYSTIMESTAMP(n) 返回系统当前带数据库时区信息的时间戳
51 NUMTODSINTERVAL(dec,interval_unit) 转换一个指定的 DEC 类型到 INTERVAL DAY TO SECOND
52 NUMTOYMINTERVAL (dec,interval_unit) 转换一个指定的 DEC 类型值到 INTERVAL YEAR TO MONTH
53 WEEK(date, mode) 根据指定的 mode 计算日期为年中的第几周
54 UNIX_TIMESTAMP (datetime) 返回自标准时区的’1970-01-01 00:00:00 +0:00’的到本地会话时区的指定时间的秒数差
55 from_unixtime(unixtime) 返回将自’1970-01-01 00:00:00’的秒数差转成本地会话时区的时间戳类型
from_unixtime(unixtime, fmt) 将自’1970-01-01 00:00:00’的秒数差转成本地会话时区的指定 fmt 格式的时间串
56 SESSIONTIMEZONE 返回当前会话的时区
57 DBTIMEZONE 返回当前数据库的时区
58 DATE_FORMAT(d, format) 以不同的格式显示日期/时间数据
59 TIME_TO_SEC(d) 将时间换算成秒
60 SEC_TO_TIME(sec) 将秒换算成时间
61 TO_DAYS(timestamp) 转换成公元 0 年 1 月 1 日的天数差
62 DATE_ADD(datetime, interval) 返回一个日期或时间值加上一个时间间隔的时间值
63 DATE_SUB(datetime, interval) 返回一个日期或时间值减去一个时间间隔的时间值
64 SYS_EXTRACT_UTC(d timestamp) 将所给时区信息转换为 UTC 时区信息
65 TO_DSINTERVAL(d timestamp) 转换一个 timestamp 类型值到 INTERVAL DAY TO SECOND
66 TO_YMINTERVAL(d timestamp) 转换一个 timestamp 类型值到 INTERVAL YEAR TO MONTH

空值判断函数
序号 函数名 功能简要说明
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 返回两个类型相同的值的比较

类型转换函数
序号 函数名 功能简要说明
01 CAST(value AS 类型说明) 将 value 转换为指定的类型
02 CONVERT(类型说明,value)；
CONVERT(char, dest_char_set [,source_char_set ] ) 用于 INI 参数 ENABLE_CS_CVT=0 时，将 value 转换为指定的类型；
用于 INI 参数 ENABLE_CS_CVT=1 时，将字符串从原串编码格式转换成目的编码格式
03 HEXTORAW(exp) 将 exp 转换为 BLOB 类型
04 RAWTOHEX(exp) 将 exp 转换为 VARCHAR 类型
05 BINTOCHAR(exp) 将 exp 转换为 CHAR
06 TO_BLOB(value) 将 value 转换为 blob
07 UNHEX(exp) 将十六进制的 exp 转换为格式字符串
08 HEX(exp) 将字符串的 exp 转换为十六进制字符串

其他函数
序号 函数名 功能简要说明
01 DECODE(exp, search1, result1, … searchn, resultn [,default]) 查表译码
02 ISDATE(exp) 判断表达式是否为有效的日期
03 ISNUMERIC(exp) 判断表达式是否为有效的数值
04 DM_HASH (exp) 根据给定表达式生成 HASH 值
05 LNNVL(condition) 根据表达式计算结果返回布尔值
06 LENGTHB(value) 返回 value 的字节数
07 FIELD(value, e1, e2, e3, e4…en) 返回 value 在列表 e1, e2, e3, e4…en 中的位置序号，不在输入列表时则返回 0
08 ORA_HASH(exp [,max_bucket [,seed_value]]) 为表达式 exp 生成 HASH 桶值

参考文档：https://eco.dameng.com/document/dm/zh-cn/sql-dev/practice-func.html