如何在MySQL存储过程中模拟打印

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打印的最佳方法是什么？

1 个解决方案

#1

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;

#1