mysql - 使用带有 out 变量的 sp 会出现以下错误消息 : Error Code: 1241. 操作数应包含 1 列

Question

我在 MySql 中编写了一个存储过程，其中包含一个 out 变量，但是当我想调用它时，出现以下错误。有人可以帮助我理解我做错了什么吗？这是sp:

CREATE DEFINER=`root`@`localhost` PROCEDURE `storedp2`(out shift nvarchar(40))
begin
set shift= (SELECT * FROM myblog.computed);
end

我是这样调用它的:

set @test='';
call storedp2 (@test) ;
select @test as t;

这是错误:

Error Code: 1241. Operand should contain 1 column(s)

Answer 1

您需要返回单个值:

CREATE DEFINER=`root`@`localhost` PROCEDURE `storedp2`(out shift nvarchar(40))
begin
set shift= (SELECT col_name FROM myblog.computed WHERE id = ?);
-- (single column/single row)
-- set shift = (SELECT col_name FROM myblog.computed WHERE ... LIMIT 1);
end;

您无法将 SELECT * FROM tab 的结果分配给 NVARCHAR(40):

scalar :=  (col1, col2, col3)         -- won't work (multiple cols, single row)
scalar :=  (col1, col2), (col1, col2) -- won't work (multiple cols, multiple rows)
sclara :=  (col1), (col1)             -- won't work (single col, multiple rows)

编辑:

what should I do if I want to return the whole select sentance

CREATE DEFINER=`root`@`localhost` PROCEDURE `storedp2`()
begin
  -- some logic
  SELECT * FROM myblog.computed;
end

call storedp2 ();

<强> DBFiddle Demo