设置变量结果，来自查询 [英] 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屋！