如何在MySQL * Prepared *存储过程中使用多个参数 [英] How to Use Multiple Parameters in a MySQL *Prepared* Stored Procedure

问题描述

尽管有一些在MySQL存储过程中使用多个参数的很好的例子，但我一直找不到一个简单的示例来说明如何在准备好的的存储过程中使用它们.

Although there are some good examples of multiple parameters being used in MySQL stored procedures, I have been unable to find a simple example that shows how to use them in a stored procedure that is prepared.

使用以下代码调用时，下面的代码将返回'Incorrect arguments to EXECUTE':`call test_parms('my report'，'example.com');

The code below returns 'Incorrect arguments to EXECUTE' when calling it using: `call test_parms('my report','example.com');

我尝试过在参数名称的前面加上和不加上'@'(只是给出了未知的列错误)，以及代码的不同变体.我究竟做错了什么?

I've tried with and without '@' in front of the parameter names (just gives an unknown column error), and different variations of the code . What am I doing wrong?

DELIMITER $$

DROP PROCEDURE IF EXISTS `test_parms`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `test_parms`(REPORT VARCHAR(255),DOMAIN_NAME VARCHAR(255))
BEGIN

SET @sql = "Select @DOMAIN_NAME,@REPORT";

set @REPORT=REPORT;
set @DOMAIN_NAME=DOMAIN_NAME;

PREPARE stmt FROM @sql;
EXECUTE stmt using @DOMAIN_NAME,@REPORT;
DEALLOCATE PREPARE stmt;

END$$

DELIMITER ;

推荐答案

文档的以下部分将对您有所帮助:

The following section of the documentation will be helpful: 13.5.1. PREPARE Syntax.

DELIMITER $$

DROP PROCEDURE IF EXISTS `test_parms`$$

CREATE PROCEDURE `test_parms`(`REPORT` VARCHAR(255), `DOMAIN_NAME` VARCHAR(255))
BEGIN
    SET @`sql` := 'SELECT ? `DOMAIN_NAME`, ? `REPORT`';
    SET @`REPORT` := `REPORT`;
    SET @`DOMAIN_NAME` := `DOMAIN_NAME`;
    PREPARE `stmt` FROM @`sql`;
    EXECUTE `stmt` USING @`DOMAIN_NAME`, @`REPORT`;
    DEALLOCATE PREPARE `stmt`;
END$$

DELIMITER ;

SQL Fiddle演示

更新

DELIMITER $$

DROP PROCEDURE IF EXISTS `test_parms`$$

CREATE PROCEDURE `test_parms`(`REPORT` VARCHAR(255), `DOMAIN_NAME` VARCHAR(255))
BEGIN
    SELECT `DOMAIN_NAME` `DOMAIN_NAME`, `REPORT` `REPORT`;
END$$

DELIMITER ;

SQL Fiddle演示

这篇关于如何在MySQL * Prepared *存储过程中使用多个参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！