动态获取存储过程的参数值

Question

我正在为我所有的存储过程创建某种审计功能。我能够获取存储过程所具有的参数的名称(从: information_schema.parameters表)。但是，我希望为所有存储过程创建通用代码，这些存储过程将获取参数的名称，并相应地获取该调用的这些参数的值，然后登录到另一个表中。

例如：

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `TestSP`(IN `name` VARCHAR(255), IN `userid` INT(255), IN `isnew` VARCHAR(11))
BEGIN

#DECLARE exit handler for sqlexception  ROLLBACK;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;

INSERT INTO app_db_log(TYPE,MESSAGE, INFO) VALUES ('ERROR','An error has occurred, operation rollback and the stored procedure was terminated',<INSERT ALL THE PARAMETER VALUES AS SINGLE STRING HERE> );
COMMIT;

SELECT 'An error has occurred, operation rollback and the stored procedure was terminated';
END;

START TRANSACTION;
SIGNAL SQLSTATE '45000';
COMMIT;
END$$
DELIMITER ;

提前感谢！

Answer 1

您可以从INFORMATION_SCHEMA.PARAMETERS获取例程的参数列表，但您必须知道该过程的模式和名称：

mysql> delimiter $$

mysql> create procedure myproc (in foo int, in bar int)
    -> begin
    -> select group_concat(parameter_name order by ordinal_position) as params
    -> from INFORMATION_SCHEMA.PARAMETERS
    -> where specific_schema='test' and specific_name='myproc';
    -> end$$

mysql> call myproc(123, 456)$$
+---------+
| params  |
+---------+
| foo,bar |
+---------+

为此，您需要使用动态SQL，但不能在动态SQL中引用存储的proc参数：

演示：

mysql> create procedure myproc (in foo int, in bar int)
    -> begin
    -> set @sql = 'SELECT foo, bar';
    -> prepare stmt from @sql;
    -> execute stmt;
    -> end$$

mysql> call myproc(123, 456)$$
ERROR 1054 (42S22): Unknown column 'foo' in 'field list'

我通常不鼓励人们使用MySQL存储过程。实际上，在任何应用程序编程语言中，这项任务都要容易得多。