mysql - 我无法创建这个过程

Question

我尝试使用此代码创建新的存储过程，但我总是出现此错误:

    CREATE PROCEDURE `retriveData`() NOT DETERMINISTIC CONTAINS SQL SQL
    SECURITY DEFINER  BEGIN

    SET @sql = NULL 

MySQL 说:文档

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 4

我使用此代码创建存储过程:

CREATE PROCEDURE `retriveData`() NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER 
BEGIN

    SET @sql = NULL;
    SELECT
      GROUP_CONCAT(DISTINCT
        CONCAT(
          'MAX(IF(pa.item = ''',
          item,
          ''', pa.comValue, 0)) AS ',
          item
        )
      ) INTO @sql
    FROM salaryStructureView;

    SET @sql = CONCAT('SELECT p.id
                        , p.fullname
                        , p.salary, ', @sql, ' 
                       FROM employee p
                       LEFT JOIN salaryStructureView AS pa 
                        ON p.id = pa.id
                       GROUP BY p.id');

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END

Answer 1

您忘记了分隔符:

DELIMITER $$
CREATE PROCEDURE `retriveData`() NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER 
BEGIN

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(pa.item = ''',
      item,
      ''', pa.comValue, 0)) AS ',
      item
    )
  ) INTO @sql
FROM salaryStructureView;

SET @sql = CONCAT('SELECT p.id
                    , p.fullname
                    , p.salary, ', @sql, ' 
                   FROM employee p
                   LEFT JOIN salaryStructureView AS pa 
                    ON p.id = pa.id
                   GROUP BY p.id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
$$