You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
DELIMITER $$
USE `fwdb`$$
DROP PROCEDURE IF EXISTS `_GS_GM_Check`$$
CREATE DEFINER=`root`@`%` PROCEDURE `_GS_GM_Check`(vi_uid INT,vi_pwd VARCHAR(32),vi_ip VARCHAR(100),OUT vo_level INT,OUT vo_code INT)
BEGINSET vo_level =0;
-- JinSQ 2016-04-08 CHECK IP ADD START
IF IFNULL(vi_ip,'')='' THEN
BEGIN-- JinSQ 2016-04-08 CHECK IP ADD END-- if exists(select 1 from T_GM_Info where f_userid = vi_uid and f_pwd = vi_pwd and F_ip = vi_ip and F_Active = 1)
IF EXISTS(SELECT1FROM T_GM_Info WHERE f_userid = vi_uid AND F_Active =1)
THEN
-- select f_level into vo_level from T_GM_Info where f_userid = vi_uid AND f_pwd = vi_pwd AND F_ip = vi_ip AND F_Active = 1;SELECT f_level INTO vo_level FROM T_GM_Info WHERE f_userid = vi_uid AND F_Active =1;
SET vo_code =1000; -- success
ELSE
SET vo_code =1001; -- fail
END IF;
-- JinSQ 2016-04-08 CHECK IP ADD START
END;
ELSE
BEGIN
IF EXISTS(SELECT1FROM T_GM_Info WHERE f_userid = vi_uid AND F_ip = vi_ip AND F_Active =1)
THEN
SELECT f_level INTO vo_level FROM T_GM_Info WHERE f_userid = vi_uid AND F_ip = vi_ip AND F_Active =1;
SET vo_code =1000; -- success
ELSE
SET vo_code =1001; -- fail
END IF;
END;
END IF;
-- JinSQ 2016-04-08 CHECK IP ADD END
END$$
DELIMITER ;
审核后切分的片段如下:
DELIMITER $$
USE `fwdb`$$
DROP PROCEDURE IF EXISTS `_GS_GM_Check`$$
CREATE DEFINER=`root`@`%` PROCEDURE `_GS_GM_Check`(vi_uid INT,vi_pwd VARCHAR(32),vi_ip VARCHAR(100),OUT vo_level INT,OUT vo_code INT)
BEGINSET vo_level =0;
-- JinSQ 2016-04-08 CHECK IP ADD START
IF IFNULL(vi_ip,'')='' THEN
BEGIN-- JinSQ 2016-04-08 CHECK IP ADD END-- if exists(select 1 from T_GM_Info where f_userid = vi_uid and f_pwd = vi_pwd and F_ip = vi_ip and F_Active = 1)
IF EXISTS(SELECT1FROM T_GM_Info WHERE f_userid = vi_uid AND F_Active =1)
THEN
-- select f_level into vo_level from T_GM_Info where f_userid = vi_uid AND f_pwd = vi_pwd AND F_ip = vi_ip AND F_Active = 1;SELECT f_level INTO vo_level FROM T_GM_Info WHERE f_userid = vi_uid AND F_Active =1;
SET vo_code =1000; -- success
ELSE
SET vo_code =1001; -- fail
END IF;
-- JinSQ 2016-04-08 CHECK IP ADD START
END;
ELSE
BEGIN
IF EXISTS(SELECT1FROM T_GM_Info WHERE f_userid = vi_uid AND F_ip = vi_ip AND F_Active =1)
THEN
SELECT f_level INTO vo_level FROM T_GM_Info WHERE f_userid = vi_uid AND F_ip = vi_ip AND F_Active =1;
SET vo_code =1000; -- success
ELSE
SET vo_code =1001; -- fail
END IF;
END;
END IF;
-- JinSQ 2016-04-08 CHECK IP ADD END
END$$
DELIMITER ;
版本信息(Version)
main
问题描述(Describe)
MySQL审核存储过程脚本时SQL片段切分不正确
截图或日志(Log)
输入的存储过程脚本如下:
审核后切分的片段如下:
-- JinSQ 2016-04-08 CHECK IP ADD END END$$ DELIMITER ;
如何复现(To Reproduce)
通过快捷审核随意创建一个MySQL的静态审核,输入上面的SQL脚步即可复现
实现方案
无法正确切分存储过程原因:
1、解析器对于一条存储过程语句的匹配规则是根据"BEGIN"和"END"关键字,但是复杂存储过程可以嵌套,解析器无法处理嵌套的情况(https://dev.mysql.com/doc/refman/5.7/en/begin-end.html)
2、如果一条存储过程定义中本身包含默认分隔符";",所以必须重新定义分隔符,需用到关键字delimiter,但是解析器无法解析该关键字。(https://dev.mysql.com/doc/refman/5.7/en/stored-programs-defining.html)
如:
解决方案:
变更影响面
受影响的模块或功能
外部引用的潜在问题或风险
版本兼容性
测试建议
The text was updated successfully, but these errors were encountered: