重用的t-sql游标的开始位置？ [英] Start position for a reused t- sql cursor?

问题描述

我正在使用一个存储过程，在临时表上使用游标（我已经读了一些为什么游标是不受欢迎的，但在这种情况下，我相信我还需要使用一个）。

I'm working on a stored procedure that uses a cursor on a temporary table (I have read a bit about why cursors are undesirable, but in this situation I believe I still need to use one).

在我的过程中，我需要遍历表的行两次。

In my procedure I need to step through the rows of the table twice.

已经声明游标，已经跨过临时表一次并关闭游标，当重新打开时游标的位置将保留在表的末尾，是否将自身重新定位到初始起始位置（即：在第一行之前）？

Having declared the cursor, already stepped through the temporary table once and closed the cursor, would the position of the cursor remain at the end of the table when re-opened or does it reposition itself to the initial starting position (ie: before the first row)?

或者，要重新定位游标，必须在逐步执行之前执行FETCH FIRST再次？

Alternatively, to reposition the cursor must I do a 'FETCH FIRST' before stepping through again?

我是否可以假设做这种重新定位的成本，并重复使用游标将比释放和重新分配游标？

Am I right to assume the 'cost' of doing this repositioning and reusing the cursor would be less than deallocating and reallocating the cursor?

推荐答案

分配和释放成本是微不足道的。游标的坏处来自于您没有以最佳方式与数据库进行交互，而不是从创建或处理游标的任何特定开销。

The allocation and deallocation costs are trivial. The "badness" of a cursor comes from the fact that you're not interacting with the DB in an optimal manner, not from any particular overhead of creating or disposing of a cursor.

我不认为关闭和重新打开的游标的位置的行为是记录在案的，所以你不应该依赖它以任何给定的方式行动。所以，当你再次使用它时，你应该重新定位它。

I don't think the behavior of the position of a closed and reopened cursor is documented, so you shouldn't depend on it acting in any given way. So, you should reposition it yourself when you start using it again.

而且，你可能没有光标就可以做。我会考虑问一个不同的问题，如果我是你。 ：）

And, it's possible that what you're doing might be doable without a cursor. I'd consider asking (a different) question related to that if I were you. :)

这篇关于重用的t-sql游标的开始位置？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！