上文讲了DM数据的日期相关的运算以及利用分析函数 lead() over() 进行范围问题的处理，这里再继续说说DM数据库函数
函数
DM 数据库函数可以帮助用户更加方便地处理表中的数据。函数不但可以在 SELECT 查询语句中使用，同样可以在 INSERT、UPDATE、DELETE 等语句中使用。
函数的使用
使用 LENGTH 函数计算出员工姓名名字的字数
select ename,LENGTH(ename)from employee;
自定义函数的创建
语法结构如下
create[or replace] function 函数名 ([p1,p2...pn])return datatype is|as--声明部分begin--程序块end
语法说明：
1.function 是创建函数的关键字。
2.p1,p2...pn 是函数的入参，DM 数据库创建的函数也可以不需要入参。
3.return datatype 是函数的返回值的类型。
4.通过 is 或者 as 承接着程序块。这部分是函数的计算内容。
自建 GET_SEX 函数，展示员工性别，原始数据
select*from employee;
创建自定义函数
CREATEOR REPLACE FUNCTION GET_SEX(mgr inINT)RETURN CHAR(2)AS v_sex CHAR(2);BEGIN IF mgr=1 THEN v_sex:='男'; ELSE v_sex:='女'; END IF; RETURN v_sex;END;
执行完成后可以看到模式-函数下已经创建的自定义函数
获取函数执行结果
select ename,GET_SEX(mgr)from employee;
DM内置加密解密函数
首先需要查询一下当前DM数据库版本支持的加密方式
SELECT*FROM V$CIPHERS;
这里我们用加密函数CFALGORITHMSENCRYPT('明文内容',加密方式id,'自定义密钥') 514加密方式
select CFALGORITHMSENCRYPT('111111',514,'secret key');--9FF46AA8D86FB25ADED775837F400BB5FE
解密用同样的解密函数CFALGORITHMSDECRYPT('密文内容',加密方式id,'自定义密钥') 514解密
select CFALGORITHMSDECRYPT('9FF46AA8D86FB25ADED775837F400BB5FE',514,'secret key');
后续详细讲解加密这块的内容。
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
