MySQL 我的 SQL 动态查询执行并将输出输出到存储过程中的变量中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5591338/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
My SQL Dynamic query execute and get ouput into a variable in stored procedure
提问by Sency
I generate a dynamic query in My sql Stored procedure. I wanna get the result of this query into a out parameter. How to do this ?
我在我的 sql 存储过程中生成一个动态查询。我想将此查询的结果放入一个 out 参数中。这该怎么做 ？
CREATE PROCEDURE 'searchInvoice'
(
OUT numOfRecords INT
)
BEGIN
DECLARE query1 TEXT;
DECLARE query2 TEXT;
SET query1 = 'SELECT COUNT(*) bla bla bla.....';
// Query1 to select the count of matching tuples..
SET query2 = 'SELECT * from bla bla bla....';
// Query2 to select original records...
// later part of this both queries generate dynamically according to some IN parameters..
// now I wanna assign the output of the query1 into numOfRecords
// and I wanna execute the query2 as well.. like this
SET @Sql = query2;
PREPARE STMT FROM @Sql;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
// output of the query2 can be read in PHP
END
How to get the output of the query1 into OUT parameter(numOfRecords) ??
如何将 query1 的输出放入 OUT parameter( numOfRecords) ??
回答by Devart
Have a look at this example -
看看这个例子——
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 |
+---------+---------+
回答by Nicola Cossu
select count(*) into @numOfRecords from ....
You have do declare the variable within stored procedure
您已经在存储过程中声明了变量
I hope I've understood your question.
我希望我已经理解了你的问题。
