大数据|达梦数据库(DM8)存储过程函数使用 database|sql|数据库

DM8数据库的过程函数的编写主要分为4个部分：过程头部分，声明定义部分，执行部分和异常处理部分。
存储过程创建与使用存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升。
创建带参数储存过程
– 创建测试表

create table test_tab (id int primary key, name varchar(30));

– 创建有参数储存过程p_test

create or replace procedure p_test(i in int) as j int; begin for j in 1 ..i loop insert into test_tab values(j,'p_test'||j); end loop; end;

– 执行调用过程

p_test(3);

– 对表进行查询

select * from test_tab;

运行结果如下图所示：

文章图片

创建无参数储存过程
– 创建无参数储存过程p_test2

create or replace procedure p_test2 as j int; begin for j in 10 ..13 loop insert into test_tab values(j,'p_test2:'||j); end loop; end;

– 执行调用过程

p_test2;

– 对表进行查询

select * from test_tab;

运行结果如下图所示

文章图片

匿名块测试
– 匿名块测试语句

begin print('hello world'); end;

运行结果如下图所示

文章图片

– 语句块测试循环输出

begin for i in 1..3 loop print('输出:'||i); end loop; end;

运行结果如下图所示

文章图片

DM函数使用DM 数据库函数可以帮助用户更加方便地处理表中的数据。函数不但可以在 SELECT 查询语句中使用，同样可以在 INSERT、UPDATE、DELETE 等语句中使用。这些函数丰富了 SQL 的语言功能，为 DMSQL 提供了更多的操作性。DM 数据库函数可以接受零个或者多个输入参数，并返回一个输出结果。
– 函数演示

select len('hello world'); --LEN(char)：返回char的长度，不包括尾部的空字符串。

结果输出如下图所示

文章图片

select length('hello world'); --LENGTH(char)：返回char的长度，包含尾部的空字符串。

结果输出如下图所示

文章图片

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