mysql - SQL Server 存储过程到 MySQL
问题描述
我需要将 SQL Server 存储过程转换为 MySQL,找到了这个工具:http ://www.sqllines.com/online但转换后 Workbench 给我一个错误,DELIMITER 说它在这个位置无效:创建预期的。
不幸的是,无法迁移 SQL Server 数据库,因此我需要转换此过程的整个代码 - 有没有一种简单的方法可以转换它,或者有人知道我为什么会收到 DELIMITER 错误?
DELIMITER //
CREATE PROCEDURE CreateQuestionnairForCourse (
p_courseId bigint,
p_openDate bigint /* = 1573884000 */, -- 16 Nov 2019
p_closeDate bigint /* = 1575150900 */, -- 30 Nov 2019
p_servey_etalon bigint /* =20 */, -- Greek English together
p_questionnair_etalon bigint/* =20 */)
sp_lbl:
BEGIN
DECLARE NOT_FOUND INT DEFAULT 0;
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
DECLARE v_surveyId bigint;
DECLARE v_questionnairId bigint;
DECLARE v_courseModuleId bigint;
DECLARE v_sequence longtext;
DECLARE v_section bigint;
DECLARE v_added bigint;
DECLARE v_questionId bigint;
DECLARE v_new_questionId bigint;
DECLARE v_IsVisible tinyint DEFAULT 1
declare curs cursor local;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET NOT_FOUND = 1;;
if p_courseId = 0 then
LEAVE sp_lbl;
end if;
ELSE
DECLARE v_UnixTS bigint DEFAULT [dbo].[DTtoUnixTS](NOW());
BEGIN TRAN T1;
INSERT INTO mdl_questionnaire_survey
(
`name`,
`courseid`,
`realm`,
`status`,
`title`,
`email`,
`subtitle`,
`info`,
`theme`
)
SELECT
name,
p_courseId,
'private',
`status`,
title,
email,
subtitle,
info,
theme
FROM mdl_questionnaire_survey
WHERE id = p_servey_etalon;
SET v_surveyId = LAST_INSERT_ID();
INSERT INTO mdl_questionnaire
(`course`,
`name`,
intro,
`qtype` ,
`respondenttype`,
`resp_eligible`,
`resp_view`,
`opendate`,
`closedate`,
`resume`,
`navigate`,
`grade`,
`sid`
)
Select
p_courseId,
name,
intro,
qtype,respondenttype,
resp_eligible,
resp_view,
p_openDate,
p_closeDate,
resume,
navigate,
grade,
v_surveyId
FROM mdl_questionnaire
WHERE id=p_questionnair_etalon;
SET v_questionnairId = LAST_INSERT_ID();
fast_forward for
select id
FROM `mdl_questionnaire_question`
where deleted='n' and survey_id=p_servey_etalon
open curs;
fetch next from; curs into v_questionId
while Not_found = 0
do
INSERT INTO mdl_questionnaire_question (survey_id,name ,type_id,result_id,`length`,`precise`,`position` ,`content` ,`required` ,`deleted`)
SELECT v_surveyId,`name`,`type_id` ,`result_id`,`length`,`precise`,`position` ,`content` ,`required` ,`deleted`
FROM mdl_questionnaire_question where id = v_questionId;
SET v_new_questionId = LAST_INSERT_ID();
INSERT INTO mdl_questionnaire_quest_choice
SELECT v_new_questionId
,`content`
,`value`
FROM mdl_questionnaire_quest_choice where question_id = v_questionId;
fetch next from; curs into v_questionId
end while;
CLOSE curs;
SELECT TOP 1 id INTO v_section from mdl_course_sections where course=p_courseId
ORDER By id ASC;
INSERT INTO mdl_course_modules
(
course,
module,
instance,
section,
idnumber,
added,
score,
indent,
visible,
visibleold,
groupmode,
groupingid,
-- groupmembersonly,
completion,
completionview,
completionexpected,
`showdescription`,
`availability`
)
VALUES (
p_courseId,
23,
v_questionnairId,
v_section,
0,
0,
0,
0,
v_IsVisible,
v_IsVisible,
0,
0,
-- 0,
0,
0,
0,
0,
''
);
SET v_courseModuleId = LAST_INSERT_ID();
SELECT sequence INTO v_sequence from mdl_course_sections where id = v_section;
if v_sequence = '' then
SET v_sequence = CAST(v_courseModuleId as varchar(1));
ELSE
SET v_sequence = Concat(v_sequence , ',' , CAST(v_courseModuleId as varchar(1)));
end if; -- COLLATE DATABASE_DEFAULT
UPDATE mdl_course_sections
SET sequence = v_sequence
WHERE id=v_section;
UPDATE mdl_course
SET cacherev = (;CASE
WHEN cacherev IS NULL THEN v_UnixTS
WHEN cacherev < v_UnixTS THEN v_UnixTS
WHEN cacherev > v_UnixTS + 3600 THEN v_UnixTS
ELSE cacherev + 1 END) WHERE id = p_courseId
COMMIT; T1;
END;
END IF;
//
DELIMITER ;
解决方案
在我看来,问题在于代码中的注释。可能是转换工具无法处理 /* = whatewer.. */
同样在转换后根据建议重构代码 - varchar(1) 总是一个坏主意......在你的情况下,它可能也是错误的,因为我预计课程模块将比从 int 转换的 1 个字符长......
推荐阅读
- google-apps-script - 有没有办法让 Google Forms 中的复选框中的每个选项成为 Google Sheets 中的标题?
- python - 如何将 STL 导入 pygltflib
- r - r控制台中汉字的输出问题,r版本4
- flutter - 无法为 TimeofDay 设置值,它始终为空
- arduino - ESP32 服务中断例程需要多快?
- loops - 邮递员测试。循环遍历嵌套对象数组以查找空值
- javascript - 检查 URL 中的查询字符串以及查询字符串是否在 javascript (vue) 中具有值
- python-3.x - Python异步函数循环+返回参数
- google-drive-api - 获取第三方网络应用程序的谷歌驱动文件的链接
- python - 使用 Geopandas 计算重叠特征