mysql begin end 语法错误_MySQL过程语法错误“ Missing END”

我必须按照以下过程尝试动态创建视图.

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

我收到错误“ Missing END”,我不知道为什么.

语法检查器在行尾加下划线

DEALLOCATE PREPARE stmt;

当我将dealloc移到末尾时,句法检查器会在前一行突出显示分号.

EXECUTE stmt;

如果我在dealloc之后删除所有内容,它将起作用.

最佳答案

一些问题：

> DECLARE仅在BEGIN … END复合语句内部允许,并且必须在其开始处,然后再执行其他任何语句.参见13.6.3 DECLARE Syntax.

>错误1193(HY000)：未知的系统变量“完成”.

>检查游标的语法.参见13.6.6.2 Cursor DECLARE Syntax.

>列是关键字和保留字.参见9.3 Keywords and Reserved Words.

>缺少一些；

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 ;