光标是最好的方式吗？ [英] Is Cursor Best Way To Go?
问题描述
我需要从复杂的SQL语句中获取两个值，该语句返回单个
记录，并使用这两个值来更新表中的单个记录。在
命令将这两个值分配给变量然后在UPDATE语句中使用这些变量
，我创建了一个游标并使用了Fetch Next .... Into。
这样，我只需要调用复杂的SQL一次而不是两次。
这似乎是最好的方法。但是，我总是使用游标来滚动浏览结果集。但是在这种情况下，只返回一条
记录，并且光标不会滚动。
这是最有效的方式要去，还是有更好的方法能够使用SQL语句中的两个值而不必调用它两次？
谢谢。
嗨Neil
我需要更多有关查询/ DDL的详细信息才能说出来
有意义，但肯定是基于集合的解决方案总是优于
迭代/游标解决方案。
这是猜测而没有看到你的代码。
声明@ v1 int，@ v2 int
选择@ v1 = [col1]， @ v2 = [colx]
来自（
- 您的复杂查询
）as derived_table
-
-oj
" Neil" <无**** @ nospam.net>在消息中写道
news：vh ***************** @ newsread1.news.pas.earthl ink.net ...我需要从复杂的SQL语句中获取两个值，这些语句返回
单个记录并使用这两个值来更新
表中的单个记录。为了将这两个值分配给变量，然后在UPDATE语句中使用这些变量，我创建了一个游标并使用了Fetch
Next .... Into。这样，我只需要调用复杂的SQL一次，而不是两次。
这似乎是最好的方法。但是，我总是使用游标来滚动结果集。但是在这种情况下，只返回一条
记录，光标不会滚动。
这是最有效的方法，还是有更好的方法能够使用SQL语句中的两个值而不必调用它两次的方法吗？
谢谢。
< blockquote> SQL Server不支持标准的SQL语法，但它确实
有专有的语法来完成同样的工作：
UPDATE T1
SET x = foo，
y = bar
FROM
（SELECT foo，bar / *你的查询在这里* /
FROM ...）AS T2
WHERE T2.key_col = T1.key_col
/ *连接条件应该产生一行来自T2的每一行
T1 * /
我总是使用游标来滚动结果集
真的吗？出于什么目的？游标应该是罕见的例外，而不是规则的b $ b。通常有更好的基于集合的解决方案。
-
David Portas
SQL Server MVP
-
I need to get two values from a complex SQL statement which returns a single
record and use those two values to update a single record in a table. In
order to assign those two values to variables and then use those variables
in the UPDATE statement, I created a cursor and used Fetch Next.... Into.
This way, I only have to call the complex SQL once instead of twice.
This seems like the best way to go. However, I''ve always used cursors for
scrolling through resultsets. In this case, though, there is just a single
record being returned, and the cursor doesn''t scroll.
Is that the most efficient way to go, or is there a better way to be able to
use both values from the SQL statement without having to call it twice?
Thanks.
Hi Neil
I''d need more details regarding the query/DDL to say anything too
meaningful, but certainly a set-based solution is always preferable to
an iterative/cursor-solution.
here is a guess without seeing your code.
declare @v1 int, @v2 int
select @v1=[col1], @v2=[colx]
from (
-- your complex query
) as derived_table
--
-oj
"Neil" <no****@nospam.net> wrote in message
news:vh*****************@newsread1.news.pas.earthl ink.net...I need to get two values from a complex SQL statement which returns a
single record and use those two values to update a single record in a
table. In order to assign those two values to variables and then use those
variables in the UPDATE statement, I created a cursor and used Fetch
Next.... Into. This way, I only have to call the complex SQL once instead
of twice.
This seems like the best way to go. However, I''ve always used cursors for
scrolling through resultsets. In this case, though, there is just a single
record being returned, and the cursor doesn''t scroll.
Is that the most efficient way to go, or is there a better way to be able
to use both values from the SQL statement without having to call it twice?
Thanks.
SQL Server doesn''t support the standard SQL syntax for this but it does
have a proprietary syntax to do the same job:
UPDATE T1
SET x = foo,
y = bar
FROM
(SELECT foo, bar /* your query here */
FROM ... ) AS T2
WHERE T2.key_col = T1.key_col
/* join condition should yield a single row from T2 for each row in
T1 */
I''ve always used cursors for
scrolling through resultsets
Really? For what purpose? Cursors should be the rare exception rather
than the rule. Usually there are better set-based solutions.
--
David Portas
SQL Server MVP
--
这篇关于光标是最好的方式吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
