动态视图在mySQL中的替代 [英] Dynamic Views Alternative in mySQL

问题描述

我想在mySQL中创建一个视图，当用户输入特定值（woeid）时创建或更新该视图。我尝试使用下面的存储过程：

I am trying to create a view in mySQL that gets created or updated when a user enters a specific value (woeid). I've tried to do this with the stored procedure below:

DELIMITER $$

CREATE DEFINER=`root`@`%` PROCEDURE `sp_getChildren`(
    IN woeid INTEGER(15)
)
BEGIN
    CREATE  OR REPLACE VIEW `test`.`children` 
    AS
    SELECT * 
    FROM geoplanet_places gp
    WHERE gp.parent_id = woeid;
END

上面，我已经阅读，在当前版本是不可能的的mySQL，因为在存储的例程中，一个视图不能引用例程参数或局部变量。我的问题是这是什么原因，如果有任何有效的替代品。我真的不想创建一个新的表，并有过程更新表。

The above, or so I have read, is not possible in the current version of mySQL because a view, within a stored routine, cannot refer to routine parameters or local variables. My question would be what the reason for this is, and if there are any effective alternatives. I don't really want to create a new table and have the procedure update said table.

任何帮助将非常感谢，谢谢。

Any help would be much appreciated, thanks.

推荐答案

尝试使用预备语句 -

CREATE PROCEDURE sp_getChildren(IN woeid INTEGER(15))
BEGIN
  SET @sql = CONCAT('CREATE  OR REPLACE VIEW `test`.`children` AS SELECT * FROM geoplanet_places gp WHERE gp.parent_id = ', woeid);
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END

这篇关于动态视图在mySQL中的替代的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！