将SQL结果分配给MySQL中已准备好的语句的变量？
为此，请使用存储过程。让我们首先创建一个表-
mysql> create table DemoTable(Id int, Name varchar(100));
使用插入命令在表中插入一些记录-
mysql> insert into DemoTable values(10,'John'); mysql> insert into DemoTable values(11,'Chris');
使用select语句显示表中的所有记录-
mysql> select *from DemoTable;
这将产生以下输出-
+------+-------+ | Id | Name | +------+-------+ | 10 | John | | 11 | Chris | +------+-------+ 2 rows in set (0.00 sec)
以下是将SQL结果分配给MySQL中已准备好的语句的变量的查询-
mysql> DELIMITER //
mysql> CREATE PROCEDURE Prepared_Statement_Demo( nameOfTable VARCHAR(20), IN nameOfColumn VARCHAR(20), IN idColumnName INT)
BEGIN
SET @holdResult=CONCAT('SELECT ', nameOfColumn, ' INTO @value FROM ', nameOfTable, ' WHERE id = ', idColumnName);
PREPARE st FROM @holdResult;
EXECUTE st;
DEALLOCATE PREPARE st;
END //
mysql> DELIMITER ;使用调用命令调用存储过程-
mysql> call Prepared_Statement_Demo('DemoTable','Name',10);现在检查变量@value的值-
mysql> select @value;
这将产生以下输出-
+--------+ | @value | +--------+ | John | +--------+ 1 row in set (0.00 sec)