MySQL创建存储过程在另一个存储过程内

Question

MySQL创建存储过程在另一个存储过程内

4

在MySQL中，是否有一种方法可以从另一个存储过程中创建一个存储过程？

我们使用临时存储过程来处理数据库更新，该存储过程检查表中的数据库版本：

DELIMITER #
DROP PROCEDURE IF EXISTS sp_temp_update_table#
CREATE PROCEDURE sp_temp_update_table()
BEGIN
    DECLARE v_db_version DECIMAL(10,6);

    SELECT CAST(`value` AS DECIMAL(10,6)) INTO v_db_version
    FROM `db_metadata` meta
    WHERE meta.`key` = 'db_version';

    IF(v_db_version < CAST('13.30' AS DECIMAL(10,6))) THEN
        -- UPDATE STATEMENTS HERE
        ALTER TABLE `foo` ADD COLUMN `bar` INT(10);

        UPDATE `etl_metadata` SET `value` = '13.30' WHERE `key` = 'db_version';
        SELECT CONCAT('13.30 Update Applied - Original db_version=', v_db_version);
    ELSE
        SELECT '13.30 Update Not Needed';
    END IF;
END#
CALL sp_temp_update_table()#
DROP PROCEDURE IF EXISTS sp_temp_update_table#
DELIMITER ;

现在我们需要在存储过程内部更新一个存储过程。

在MySQL中有没有一种方法可以实现这个目标？

- BHarman

使用MySQL存储过程可能尝试的每个任务，都可以通过一些脚本语言（如Python或Ruby）更容易地开发。 - Bill Karwin

2个回答

2

在MySQL中，您无法在准备好的语句中创建存储过程，通常情况下，您也无法在存储过程中执行无法在准备好的语句中执行的任何操作(can't do anything in a stored procedure that you can't do in a prepared statement)。

您可以将存储过程的创建语句读入字符串中（select routine_definition into myVar from information_schema.routines where routine_name='myRoutine'），例如，可以使用这种方法使存储过程创建包含更新存储过程命令的单个SQL脚本。然后，运行上面的SP之后，手动执行该SQL（或从shell脚本、cron-job等执行），即可进行所需更改。

编辑：所有这些都假定动态更新SP是您真正想要完成的操作。可能有更简洁的解决方案，例如使要修改的硬编码部分接受输入参数或从表中读取...

- A C

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Paul Campbell · Accepted Answer

我对推荐这种方法有些犹豫，因此请将以下内容视为概念验证而非经过测试的方法。但是，在存储过程中创建一个存储例程（过程或函数）是可能的。

如果您想从另一个过程中使用标准CREATE PROCEDURE p_name () ...语法，则@AC的答案是正确的。但是，如果您具有在mysql.proc表上插入权限，则可以将例程的参数、主体和相关元数据作为值直接添加到相应的列中。

只要您将例程分解为插入到正确列中的部分，它就会起作用（下面是概念验证）。然而，params_list、body和body_utf8列中的语法错误不会被此方法捕获，并且在尝试使用它时会导致一个难看的运行时错误。例如：

Error Code: 1457. Failed to load routine test.from_proc. The table mysql.proc is missing, corrupt, or contains bad data (internal code -6)

幸运的是，这里重要的是“坏数据”部分，它很容易通过UPDATE或简单地删除有问题的例程来纠正。然而，缺乏任何语法检查意味着您需要在部署此方法之前进行彻底的测试。过程

DELIMITER //

DROP PROCEDURE IF EXISTS create_routine //
CREATE PROCEDURE create_routine (
  _db CHAR(64),
  _name CHAR(64),
  _type CHAR(9),
  _sql_data_access CHAR(17),
  _is_deterministic CHAR(3),
  _security_type CHAR(7),
  _param_list BLOB,
  _returns LONGBLOB,
  _body LONGBLOB,
  _comment TEXT)
MODIFIES SQL DATA
BEGIN
  SET @sql_mode = (SELECT @@SESSION.sql_mode);
  SET @character_set_client = (SELECT @@SESSION.character_set_client);
  SET @collation_connection = (SELECT @@SESSION.collation_connection);
  SET @db_collation = (SELECT @@SESSION.collation_database);

  INSERT INTO `mysql`.`proc` SET 
    `db` = _db,
    `name` = _name,
    `type` = _type,
    `specific_name` = _name,
    `sql_data_access` = _sql_data_access,
    `is_deterministic` = _is_deterministic,
    `security_type` = _security_type,
    `param_list` = _param_list,
    `returns` = _returns,
    `body` = _body,
    `definer` = CURRENT_USER(),
    `modified` = NOW(),
    `sql_mode` = @sql_mode,
    `comment` = _comment,
    `character_set_client` = @character_set_client, 
    `collation_connection` = @collation_connection,
    `db_collation` = @db_collation,
    `body_utf8` = _body;

END //    

DELIMITER ;

使用方法

SET @SQL = 
"BEGIN
  SELECT UPPER(_in);
END";

call create_routine(
  'test',
  'proc_test',
  'PROCEDURE',
  'CONTAINS_SQL',
  'YES',
  'INVOKER',
  'IN _in CHAR(3)',
  '',
  @SQL,
  'Procedure generated by procedure'
);

call create_routine(
  'test',
  'func_test',
  'FUNCTION',
  'CONTAINS_SQL',
  'YES',
  'INVOKER',
  '_in CHAR(3)',
  'CHAR(3)',
  'RETURN (SELECT UPPER(_in));',
  'Function generated by procedure'
);

运行例程的结果

mysql> CALL test.proc_test('yyy');
+------------+
| UPPER(_in) |
+------------+
| YYY        |
+------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT test.func_test('aaa');
+-----------------------+
| test.func_test('aaa') |
+-----------------------+
| AAA                   |
+-----------------------+
1 row in set (0.00 sec)