mysql存储过程执行有变量的查询_mysql – 我的SQL动态查询执行并获取存储过程中的变量...

看看这个例子 –

CREATE TABLE table1(

column1 VARCHAR(255) DEFAULT NULL,

column2 VARCHAR(255) DEFAULT NULL,

column3 VARCHAR(255) DEFAULT NULL

);

INSERT INTO table1 VALUES

('1', 'value1', 'value2'),

('2', 'value3', 'value4');

DELIMITER $$

CREATE PROCEDURE procedure1(IN Param1 VARCHAR(255), OUT Param2 VARCHAR(255), OUT Param3 VARCHAR(255))

BEGIN

SET @c2 = '';

SET @c3 = '';

SET @query = 'SELECT column2, column3 INTO @c2, @c3 FROM table1 WHERE column1 = ?';

PREPARE stmt FROM @query;

SET @c1 = Param1;

EXECUTE stmt USING @c1;

DEALLOCATE PREPARE stmt;

SET Param2 = @c2;

SET Param3 = @c3;

END$$

DELIMITER ;

-- Call procedure and use variables

SET @Param1 = 2;

SET @Param2 = '';

SET @Param3 = '';

CALL procedure1(@Param1, @Param2, @Param3);

SELECT @Param2, @Param3;

+---------+---------+

| @Param2 | @Param3 |

+---------+---------+

| value3 | value4 |

+---------+---------+