从视图中调用存储过程 [英] Call a stored procedure from within a view

问题描述

我有一个创建表的过程，是否可以有一个视图(或类似视图)来调用该过程然后从表中选择?

I have a procedure that creates a table, is it possible to have a view (or similar) that can call the procedure then select from the table?

我已经尝试过了:

DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `new_routine`(p1 INT) RETURNS int(1)
BEGIN
    CALL rMergeDateFields();
    RETURN 1;
END


CREATE VIEW `db`.`vIntervals` AS
  SELECT new_routine(0) AS col1;
  SELECT * FROM MergedData;

但我收到此错误

错误1422:在存储的函数或触发器中不允许显式或隐式提交.

Error 1422: Explicit or implicit commit is not allowed in stored function or trigger.

有什么想法吗?

推荐答案

否，您不能.视图通常是 只读操作；如果调用存储过程，则无法保证该行为.

No, you cannot. Views are typically read-only operations; and that behavior cannot be guaranteed if stored-procedures are invoked.

相关问题:

如何在视图中调用存储过程?

是否可以在视图中调用存储过程?

以下是规范资源:

http://dev.mysql.com/doc/refman/5.1/en/view-updatability.html

有些观点是可更新的.也就是说，您可以在诸如UPDATE，DELETE或INSERT之类的语句中使用它们来更新基础表的内容.为了使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系.还有某些其他构造会使视图不可更新.

Some views are updatable. That is, you can use them in statements such as UPDATE, DELETE, or INSERT to update the contents of the underlying table. For a view to be updatable, there must be a one-to-one relationship between the rows in the view and the rows in the underlying table. There are also certain other constructs that make a view nonupdatable.

由于调用存储过程不能确保与视图行的1:1关系，因此不允许进行更新.

As invoking the stored procedure cannot assure 1:1 relationships with view rows, the update is not permitted.

这篇关于从视图中调用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！