mysql - 在 MySQL 存储过程中合并视图
问题描述
我正在创建一个 MySQL 存储过程,它接收两个视图的名称,并在它们上执行联合到两个视图中的第一个。
我使用准备好的语句将视图名称作为字符串,因此我可以将此存储过程与大量其他存储过程一起使用,这些存储过程会生成不同名称的视图。
以下代码按预期工作:
DROP view if exists test1;
DROP view if exists test2;
CREATE VIEW test1 as SELECT "Cows";
CREATE VIEW test2 as SELECT "Horses";
DROP VIEW if exists tempView;
CREATE VIEW tempView AS SELECT * FROM test1 UNION SELECT * FROM test2;
SELECT * from tempView;
但是,当我执行以下代码时:
DELIMITER //
DROP PROCEDURE IF EXISTS SP_unionViews //
CREATE PROCEDURE SP_unionViews(IN viewname varchar(255),
IN viewname2 varchar(255))
BEGIN
DROP VIEW IF EXISTS tempView;
SET @in1 = viewname;
SET @in2 = viewname2;
SET @str = 'CREATE VIEW tempView AS
SELECT * FROM ? UNION SELECT * FROM ?';
PREPARE stmt FROM @str;
EXECUTE stmt USING @in1, @in2;
SET @str2 = 'DROP VIEW ?';
SET @in3 = viewname;
PREPARE stmt2 FROM @str2;
EXECUTE stmt2 USING @in3;
SET @str3 = 'CREATE VIEW ? AS SELECT * FROM tempView';
PREPARE stmt3 FROM @str3;
EXECUTE stmt3 USING @in3;
DEALLOCATE PREPARE stmt;
DEALLOCATE PREPARE stmt2;
DEALLOCATE PREPARE stmt3;
END //
DELIMITER ;
DROP view if exists test1;
DROP view if exists test2;
CREATE VIEW test1 as SELECT "Cows";
CREATE VIEW test2 as SELECT "Horses";
CALL SP_unionViews(test1, test2);
SELECT * from test1;
我收到以下错误:
#1054 - Unknown column 'test1' in 'field list'
这似乎表明此存储过程正在尝试将 test1 用作列,而它并不打算成为一个列。但我不知道在哪里。
更新:如下编辑时,我希望解决方案能够工作,但它给了我另一个错误。
新代码如下:
DELIMITER //
DROP PROCEDURE IF EXISTS SP_unionViews //
CREATE PROCEDURE SP_unionViews(IN viewname varchar(255),
IN viewname2 varchar(255))
BEGIN
DROP VIEW IF EXISTS tempView;
SET @str = CONCAT('
CREATE VIEW tempView AS
SELECT * FROM ', viewname, ' UNION SELECT * FROM ', viewname2);
PREPARE stmt FROM @str;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @str2 = CONCAT('DROP VIEW ', viewname, ';');
PREPARE stmt2 FROM @str2;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;
SET @str3 = CONCAT('CREATE VIEW ', viewname ,' AS SELECT * FROM tempView');
PREPARE stmt3 FROM @str3;
EXECUTE stmt3;
DEALLOCATE PREPARE stmt3;
END //
DELIMITER ;
DROP view if exists test1;
DROP view if exists test2;
CREATE VIEW test1 as SELECT "Cows";
CREATE VIEW test2 as SELECT "Horses";
CALL SP_unionViews("test1", "test2");
SELECT * from test1;
它的错误是:
#1615 - Prepared statement needs to be re-prepared
解决方案
您不能在准备好的语句中使用表名参数,因此您需要在准备语句之前将表名放入查询字符串中。尝试将您的查询更改为:
SET @str = CONCAT('
CREATE VIEW tempView AS
SELECT * FROM (
SELECT * FROM ', viewname, ' UNION SELECT * FROM ', viewname2, '
)');
SET @str2 = CONCAT('DROP VIEW ', viewname);
SET @str3 = CONCAT('CREATE VIEW ', viewname, ' AS SELECT * FROM tempView');
完成此操作后,您将不再需要EXECUTE
s 的任何参数