TSQL 更改游标定义

发布于 2024-10-19 16:37:51 字数 548 浏览 1 评论 0原文

我正在使用光标。

DECLARE @column1 NVARCHAR(MAX);

DECLARE cursor_name CURSOR FOR
    SELECT c1
    FROM   table_name;

OPEN cursor_name;
FETCH cursor_name INTO @column1;

WHILE @@FETCH_STATUS = 0
BEGIN

    FETCH cursor_name INTO @column1;
END

CLOSE cursor_name;
DEALLOCATE cursor_name;

现在我的问题是，使用后可以更改光标cursor_name的定义吗？我的意思类似于：

DECLARE cursor_name CURSOR FOR
    SELECT c2
    FROM   table_name2;

使用相同的游标名称 cursor_name，但定义已更改。如果这可能的话，该怎么做？

谢谢。

原文

I'm using a cursor.

DECLARE @column1 NVARCHAR(MAX);

DECLARE cursor_name CURSOR FOR
    SELECT c1
    FROM   table_name;

OPEN cursor_name;
FETCH cursor_name INTO @column1;

WHILE @@FETCH_STATUS = 0
BEGIN

    FETCH cursor_name INTO @column1;
END

CLOSE cursor_name;
DEALLOCATE cursor_name;

Now my question is, can I change the cursor cursor_name's definition after using it? I mean something similar to:

DECLARE cursor_name CURSOR FOR
    SELECT c2
    FROM   table_name2;

Using the same cursor name cursor_name, but the defination is changed.
If this is possible, how to do this?

Thanks.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

权谋诡计 2024-10-26 16:37:51

是的，这是可能的，但它有在您解除分配之后。你试过这个但不起作用还是什么？

您可能还想看看是否真的需要光标。它们会损害性能，并且使用它们的 SQL 通常可以在没有它们的情况下重写。

看看这篇文章详细介绍如何去做。他们使用不太常见的 DECLARE/SET 语法来声明游标（至少在我的世界中不常见）。这是他们提供的示例：

USE pubs
GO
DECLARE @MyCursor CURSOR
SET @MyCursor = CURSOR LOCAL SCROLL FOR
SELECT * FROM titles

DEALLOCATE @MyCursor

SET @MyCursor = CURSOR LOCAL SCROLL FOR
SELECT * FROM sales
GO

Yes it's possible, but it has to be after your DEALLOCATE it. Have you tried this and it's not working or something?

You may also want to look at whether you really need a cursor. They hurt performance and SQL that uses them can often be rewritten without them.

Take a look at this article which goes over how to do it. They use the less common DECLARE/SET syntax for declaring a cursor (uncommon in my world at least). Here is the example they provide:

USE pubs
GO
DECLARE @MyCursor CURSOR
SET @MyCursor = CURSOR LOCAL SCROLL FOR
SELECT * FROM titles

DEALLOCATE @MyCursor

SET @MyCursor = CURSOR LOCAL SCROLL FOR
SELECT * FROM sales
GO

回复收藏 0 原文