如何在 MySQL 存储过程中模拟打印
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/627248/
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
How to simulate a print in a MySQL Stored Procedure
提问by Brian Boatright
I have a MySQL stored procedure with a few cursors. I want to print a value to send output back to the client. SQLyog Enterprise.
我有一个带有几个游标的 MySQL 存储过程。我想打印一个值以将输出发送回客户端。SQLyog 企业版。
I tried declaring a variable as TEXT and concatenating inside the loop but that does not work, at least not the way I was trying to do it.
我尝试将一个变量声明为 TEXT 并在循环内连接，但这不起作用，至少不是我试图这样做的方式。
DECLARE _output TEXT;
DECLARE _ID INT DEFAULT 0;
DECLARE cur1 CURSOR FOR SELECT ID FROM CodeID;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO _ID;
IF NOT done THEN
SET _output = _ID; /*SEE ALT BELOW*/
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
SELECT _output;
I've tried:
我试过了：
SET _output = _output + _ID
and
和
SET _output = CONCAT(_output,_ID)
but they both just return NULL
但他们都只返回NULL
SET _output = _ID;just gives me the last fetched row. Which is helpful but not entirely what I wanted.
SET _output = _ID;只给我最后提取的行。这很有帮助，但不完全是我想要的。
What's the best way to have each fetched row output to screen to reproduce the MySQL print in a MySQL Stored Procedure?
让每个提取的行输出到屏幕以在 MySQL 存储过程中重现 MySQL 打印的最佳方法是什么？
回答by Harrison Fisk
You are doing it correctly with your SELECT _output; Anything that is selected without an INTO clause will be returned to the client.
你用你的 SELECT _output 正确地做这件事；在没有 INTO 子句的情况下选择的任何内容都将返回给客户端。
To get all of them, you could either move the SELECT into the loop (to print each individually), or you could concat them together. The problem with your concat returning NULL was because you didn't initialize the _output to anything so it was NULL. Concatting anything with NULL will return NULL.
要获得所有这些，您可以将 SELECT 移动到循环中（单独打印每个），或者您可以将它们连接在一起。您的 concat 返回 NULL 的问题是因为您没有将 _output 初始化为任何内容，因此它是 NULL。将任何内容与 NULL 连接将返回 NULL。
Try the following:
请尝试以下操作：
DECLARE _output TEXT DEFAULT '';
DECLARE _ID INT DEFAULT 0;
DECLARE cur1 CURSOR FOR SELECT ID FROM CodeID;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO _ID;
IF NOT done THEN
SET _output = CONCAT(",", _ID); /*SEE ALT BELOW*/
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
SELECT _output;
