根据查询设置变量结果 [英] Set the variable result, from query

问题描述

当我创建保存的过程时，我可以创建一些变量，是吗?例如:

When I create the saved procedure, i can create some variable yes? for example:

CREATE PROCEDURE `some_proc` ()  
BEGIN  

   DECLARE some_var INT; 
   SET some_var = 3;
....

问题:但是如何从查询中设置变量结果，也就是如何进行如下设置:

QUESTION: but how to set variable result from the query, that is how to make some like this:

DECLARE some_var INT;
SET some_var = SELECT COUNT(*) FROM mytable ;

?

推荐答案

有多种方法可以做到这一点.

There are multiple ways to do this.

您可以使用子查询:

SET @some_var = (SELECT COUNT(*) FROM mytable);

(就像您的原始照片一样，只需在查询周围添加括号)

(like your original, just add parenthesis around the query)

或使用SELECT INTO语法分配多个值:

or use the SELECT INTO syntax to assign multiple values:

SELECT COUNT(*), MAX(col)
INTO   @some_var, @some_other_var
FROM   tab;

子查询语法稍微快一些(我不知道为什么)，但是只能分配一个值. select into语法允许您一次设置多个值，因此，如果您需要从查询中获取多个值，则应该这样做，而不是对每个变量一次又一次地执行查询.

The sub query syntax is slightly faster (I don't know why) but only works to assign a single value. The select into syntax allows you to set multiple values at once, so if you need to grab multiple values from the query you should do that rather than execute the query again and again for each variable.

最后，如果查询返回的不是单个行而是一个结果集，则可以使用光标.

Finally, if your query returns not a single row but a result set, you can use a cursor.

这篇关于根据查询设置变量结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！