为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:DM8
【操作系统】:WINDOWS10
【CPU】:I7
【问题描述】*:mysql函数转达梦一直报语法错误。跪求个大佬帮个忙吧,这玩意儿折腾我好久了,实在是心累了,函数如下:
drop function if EXISTS getWcallrecordRound;
CREATE DEFINER=root
@%
FUNCTION getWcallrecordRound
(soid varchar(64)) RETURNS int
READS SQL DATA
BEGIN
– 定义变量
DECLARE s int DEFAULT 0; – 循环里的临时变量,默认0
DECLARE nowSenderTypeId int DEFAULT 0; – 当前的senderTypeID
DECLARE result int DEFAULT 0; – 最终返回结果(回合数)
DECLARE lastSenderTypeId int DEFAULT 0; – 上一个senderTypeID
– 定义游标,并将sql结果集赋值到游标中
DECLARE report CURSOR FOR select SenderTypeID from wcall_sessiondetail where SessionID = soid and SenderTypeID in (1,2) order by SessionTime asc;
– 声明当游标遍历完后将标志变量置成某个值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
if(soid = ‘’ or soid is null) then
return 0;
end if;
– 打开游标
open report;
– 将游标中的值赋值给变量,注意:变量名不要和返回的列名同名,变量顺序要和sql结果列的顺序一致
fetch report into lastSenderTypeId;-- 第一条是系统以坐席的名义发的,把这条废弃掉
if(lastSenderTypeId is null ) then
return 0;
end if;
set lastSenderTypeId = 2;-- 第一条应该是有一个客户发起过程,这里塞一个客户的数据进去
fetch report into nowSenderTypeId;
– 当s不等于true,也就是未遍历完时,会一直循环
while (s != 1) do
if(nowSenderTypeId != lastSenderTypeId) then
if(nowSenderTypeId = 2) then
set result = (result +1);
end if;
end if;
set lastSenderTypeId = nowSenderTypeId;
– 当s等于1时表明遍历已完成,退出循环
fetch report into nowSenderTypeId;
end while;
– 关闭游标
close report;
return result+1;
END;
推荐一款专用于mysql迁移达梦的中间件:https://blog.csdn.net/xiaojia1001/article/details/132516102
参考dm函数定义语法:https://eco.dameng.com/docs/zh-cn/pm/dm8_sql-definition-deletion.html#3-2-%E5%AD%98%E5%82%A8%E5%87%BD%E6%95%B0
参考dm游标使用语法:https://eco.dameng.com/docs/zh-cn/pm/dm8_sql-sql-statement.html#5-2-1-2-%E6%98%BE%E5%BC%8F%E6%B8%B8%E6%A0%87