mysql - SQL 存储过程抛出错误

Question

我正在尝试在 mySQL 中创建存储过程中运行以下命令，但出现错误:

"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 'AS BEGIN DECLARE col_name VARCHAR(255)' at line 2"

这是存储过程:

CREATE PROCEDURE getColumnUniqueValuePercentage 
AS
BEGIN
DECLARE col_name VARCHAR(255);

DECLARE col_names CURSOR FOR
  SELECT column_name
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'full_db3'
  ORDER BY ordinal_position;

SET @query = '';
SET @n := (SELECT COUNT(*) FROM foo);

OPEN col_names;
SET @i = 0;
the_loop: LOOP

   IF @i > @num_rows THEN
        CLOSE col_names;
        LEAVE the_loop;
    END IF;

    FETCH FROM col_names INTO col_name;     
        SET @query = CONCAT('SELECT ', col_name, ', COUNT(*)*100/', @n, ' as count from full_db3 group by ', col_name,' order by count desc');
        PREPARE stmt FROM @query;
        EXECUTE stmt;
    SET @i = @i + 1;  
END LOOP the_loop;
END

我在这里做错了什么？

Answer 1

语法不正确。尝试:

DELIMITER //

-- CREATE PROCEDURE getColumnUniqueValuePercentage
CREATE PROCEDURE getColumnUniqueValuePercentage()
-- AS
BEGIN
  DECLARE col_name VARCHAR(255);

  DECLARE col_names CURSOR FOR
    SELECT column_name
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_name = 'full_db3'
    ORDER BY ordinal_position;

  SET @query = '';
  SET @n := (SELECT COUNT(*) FROM foo);

  OPEN col_names;
  SET @i = 0;
  the_loop: LOOP

     IF @i > @num_rows THEN
          CLOSE col_names;
          LEAVE the_loop;
      END IF;

      FETCH FROM col_names INTO col_name;     
          SET @query = CONCAT('SELECT ', col_name, ', COUNT(*)*100/', @n, ' as count from full_db3 group by ', col_name,' order by count desc');
          PREPARE stmt FROM @query;
          EXECUTE stmt;
      SET @i = @i + 1;  
  END LOOP the_loop;
END//

DELIMITER ;

更新

-- CREATE PROCEDURE getColumnUniqueValuePercentage
CREATE PROCEDURE `getColumnUniqueValuePercentage`()
-- AS
BEGIN
    DECLARE `done` BOOL DEFAULT FALSE;
    -- DECLARE `col_name` VARCHAR(255);
    DECLARE `col_name` VARCHAR(64);
    DECLARE `n` INT UNSIGNED DEFAULT 0;    
    DECLARE `col_names` CURSOR FOR
        SELECT `column_name`
        FROM `information_schema`.`COLUMNS`
        WHERE `table_name` = 'full_db3'
        ORDER BY `ordinal_position`;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET `done` := TRUE;

    -- SET @query = '';

    SET `n` := (SELECT COUNT(*) FROM `foo`);
    OPEN `col_names`;

    -- SET @i = 0;

    `the_loop`: LOOP
        FETCH FROM `col_names` INTO `col_name`;

        -- IF @i > @num_rows THEN
        IF `done` THEN
            CLOSE `col_names`;
            LEAVE `the_loop`;
        END IF;

        -- FETCH FROM col_names INTO col_name;

        SET @`query` := CONCAT('SELECT ', `col_name`, ', COUNT(*) * 100 / ', `n`, ' `COUNT`
                                FROM `full_db3`
                                GROUP BY ', `col_name`,
                                ' ORDER BY `COUNT` DESC');
        PREPARE `stmt` FROM @`query`;
        EXECUTE `stmt`;
        DEALLOCATE PREPARE `stmt`;
        -- SET @i = @i + 1;  
    END LOOP `the_loop`;
END//