在DBeaver中创建MySQL存储过程时遇语法错误求助

问题描述 #

熟悉SQL Server，刚接触MySQL，在DBeaver的脚本窗口运行存储过程创建语句时触发语法错误，报错信息如下：

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IN clntCode VARCHAR(200),
IN clntName VARCHAR(200),
IN pgCode VA' at line 2

原存储过程代码：

delimiter //
create procedure sp_cppo_Oracle_Assignment(
    IN clntCode VARCHAR(200),
    IN clntName VARCHAR(200),
    IN pgCode VARCHAR(200),
    IN pgName VARCHAR(200),
    IN prjCode VARCHAR(200),
    IN prjName VARCHAR(200),
    IN startDate date,
    IN endDate date,
    IN proportion int,
    IN userData Json
) BEGIN DECLARE oracleExists INT;
DECLARE prjID, i INT DEFAULT 0;
DECLARE ids_length INT DEFAULT 0; DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
      ROLLBACK;
      SELECT 'Fail';
    END;
    START TRANSACTION; SET i=0;  
  SELECT 'Succcessss step1';
  Call sp_cppo_insert_cpp(clntCode, clntName, pgCode, pgName, prjCode,prjName);
 select 'Success-1';
 SET prjID = (select ProjectID from CPPO_Project where ProjectCode = prjCode);  
 select 'Success-2';
    -- Retrieve values from JSON
 SET @ids_length = JSON_LENGTH(JSON_EXTRACT(userData, '$.assignees'));
 select 'Success-3';
 WHILE i < @ids_length DO
 select 'Success-4';
      SET @oracleId = JSON_UNQUOTE(JSON_EXTRACT(userData, CONCAT('$.assignees[',i,'].oracleId')));
      select 'Success-5';
      SET oracleExists= (select EmployeeID from CPPO_EmployeeAssignments
        where EmployeeID = @oracleId and ProjectID = prjID);
        select 'Success-6';
      insert into CPPO_EmployeeAssignments(ProjectID, EmployeeID, StartDate, EndDate, Proportion, Active, Created_By) 
      values(prjID, @oracleId, startDate, endDate, proportion, 1,'Admin');
    select 'Success-7';
      SELECT i + 1 INTO i;
 END WHILE;
 select 'Success-8';
COMMIT;
SELECT 'Success';
END //
DELIMITER ;

问题排查与修正 #

1. DBeaver分隔符冲突（核心原因） #

DBeaver默认会自动处理SQL分隔符，手动设置delimiter //会和工具的自动逻辑冲突，导致存储过程的参数列表被错误解析。解决方式：

• 打开脚本窗口的执行设置（点击工具栏"执行"按钮旁的下拉箭头，选择"执行设置"）
• 勾选"手动指定分隔符"，将分隔符改为//，再执行整个脚本
• 或者删除开头的delimiter //和结尾的DELIMITER ;，直接用DBeaver的"执行脚本"功能（不要单条执行），工具会自动识别存储过程的边界

2. 语法细节修正 #

• 代码中的<是HTML转义字符，MySQL无法识别，需改为原生的<
• 变量oracleExists赋值时，若查询无结果会返回NULL，建议添加LIMIT 1避免潜在的多值返回问题
• 变量声明和代码块适当换行，提升可读性同时避免语法解析错误

修正后的完整代码 #

delimiter //
create procedure sp_cppo_Oracle_Assignment(
    IN clntCode VARCHAR(200),
    IN clntName VARCHAR(200),
    IN pgCode VARCHAR(200),
    IN pgName VARCHAR(200),
    IN prjCode VARCHAR(200),
    IN prjName VARCHAR(200),
    IN startDate date,
    IN endDate date,
    IN proportion int,
    IN userData Json
) BEGIN 
    DECLARE oracleExists INT;
    DECLARE prjID, i INT DEFAULT 0;
    DECLARE ids_length INT DEFAULT 0; 
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
      ROLLBACK;
      SELECT 'Fail';
    END;

    START TRANSACTION; 
    SET i=0;  
    SELECT 'Succcessss step1';
    Call sp_cppo_insert_cpp(clntCode, clntName, pgCode, pgName, prjCode,prjName);
    select 'Success-1';
    SET prjID = (select ProjectID from CPPO_Project where ProjectCode = prjCode);  
    select 'Success-2';

    -- Retrieve values from JSON
    SET @ids_length = JSON_LENGTH(JSON_EXTRACT(userData, '$.assignees'));
    select 'Success-3';

    WHILE i < @ids_length DO
        select 'Success-4';
        SET @oracleId = JSON_UNQUOTE(JSON_EXTRACT(userData, CONCAT('$.assignees[',i,'].oracleId')));
        select 'Success-5';
        SET oracleExists= (select EmployeeID from CPPO_EmployeeAssignments
            where EmployeeID = @oracleId and ProjectID = prjID LIMIT 1);
        select 'Success-6';
        insert into CPPO_EmployeeAssignments(ProjectID, EmployeeID, StartDate, EndDate, Proportion, Active, Created_By) 
        values(prjID, @oracleId, startDate, endDate, proportion, 1,'Admin');
        select 'Success-7';
        SELECT i + 1 INTO i;
    END WHILE;

    select 'Success-8';
    COMMIT;
    SELECT 'Success';
END //
DELIMITER ;

内容的提问来源于stack exchange，提问作者Ravindra Vardha