MySQL程序语法错误

I have to following procedure trying to dynamically create a view.

我必须遵循动态创建视图的过程。

CREATE DEFINER=`root`@`%` PROCEDURE `uspCreateViewFromTable`(IN ViewName varchar(255), IN TableName varchar(255))
BEGIN
#View Droppen falls sie schon erstellt wurde
SET @s = CONCAT('DROP VIEW IF EXISTS ',ViewName);
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


# Verwendete Spalten filtern und Statement bauen
#SET @columns = CAST('SELECT ' AS VARCHAR(10));

DECLARE column varchar(500);
DECLARE column_cursor FOR SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = Tablename;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN column_cursor

read_loop: LOOP
    FETCH column_cursor INTO column
    # do something
    SELECT column;
    IF done THEN
      LEAVE read_loop;
    END IF;
END LOOP;
CLOSE column_cursor;

END

I get the Error "Missing END" and I have no idea why.

我得到错误的“遗漏的一端”，我不知道为什么。

The syntax checker underlines the semikolon at the end of Line

语法检查器在行尾对semkolon进行下划线

DEALLOCATE PREPARE stmt;

When I move the dealloc to the end the syntac checker highlightes the semikolon at the line before.

当我将dealloc移到末尾时，syntac检查器会在前面的行中高亮显示semkolon。

EXECUTE stmt;

If I remove everthing after the dealloc it works.

如果我在dealloc之后删除所有内容，它就会工作。

1 个解决方案

#1

Some problems:

一些问题:

• DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements. See 13.6.3 DECLARE Syntax.
• 声明只允许在开始中……结束复合语句，并且必须在它的开始，在任何其他语句之前。看到13.6.3声明语法。
• ERROR 1193 (HY000): Unknown system variable 'done'.
• 错误1193 (HY000):未知的系统变量“done”。
• Check the syntax of cursors. See 13.6.6.2 Cursor DECLARE Syntax.
• 检查游标的语法。参见13.6.6.2游标声明语法。
• column is keyword and reserved word. See 9.3 Keywords and Reserved Words.
• 列是关键字和保留字。参见9.3关键词和保留词。
• Missing some ;.
• 缺少一些;。

DELIMITER //

CREATE PROCEDURE `uspCreateViewFromTable`(
  IN ViewName varchar(255),
  IN TableName varchar(255)
)
BEGIN
  /*
  #View Droppen falls sie schon erstellt wurde
  SET @s = CONCAT('DROP VIEW IF EXISTS ',ViewName);
  PREPARE stmt FROM @s;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  */

  # Verwendete Spalten filtern und Statement bauen
  #SET @columns = CAST('SELECT ' AS VARCHAR(10));

  -- DECLARE column varchar(500);
  DECLARE `column` varchar(500);
  DECLARE done BOOL DEFAULT FALSE;

  /*
  DECLARE column_cursor FOR
  SELECT column_name
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = Tablename;
  */
  DECLARE column_cursor CURSOR FOR
  SELECT column_name
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = Tablename;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  #View Droppen falls sie schon erstellt wurde
  SET @s = CONCAT('DROP VIEW IF EXISTS ',ViewName);
  PREPARE stmt FROM @s;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;

  -- OPEN column_cursor
  OPEN column_cursor;

  read_loop: LOOP
      -- FETCH column_cursor INTO column
      FETCH column_cursor INTO `column`;
      # do something
      -- SELECT column;
      SELECT `column`;
      IF done THEN
        LEAVE read_loop;
      END IF;
  END LOOP;
  CLOSE column_cursor;
END//

DELIMITER ;