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
4
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 ;
#1
4
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 ;