主页 / dba / 问题 / 73487
Accepted
frlan
Asked: 2014-08-09 00:43:45 +0800 CST

SQLAnywhere：游标在执行 DDL 语句后关闭

  • 772

在这里有这个代码：

DECLARE @rowfoo integer
DECLARE @query varchar (32000)

SET @query = 'CREATE OR REPLACE PROCEDURE testprocedure AS
            BEGIN
                print ''Just a dummy text''
            END'

DECLARE test_cursor CURSOR FOR
        SELECT row_num FROM sa_rowgenerator(1, 31, 1)

OPEN test_cursor
    FETCH NEXT test_cursor into @rowfoo
    WHILE @@FETCH_STATUS = 0
    BEGIN
        execute(@query)
        FETCH NEXT test_cursor into @rowfoo
    END
CLOSE test_cursor
DEALLOCATE test_cursor

execute(@query)我已经认识到，在语句之后游标正在关闭。根据我到目前为止的发现，这是因为对 DDL 语句进行了隐式提交。由于我需要通过脚本创建多个程序，是否有解决此问题的方法？

sybase-sql-anywhere

1 个回答

  1. Best Answer

    打开游标时，存在WITH HOLD防止问题。所以

    DECLARE @rowfoo integer
DECLARE @query varchar (32000)

SET @query = 'CREATE OR REPLACE PROCEDURE testprocedure AS
            BEGIN
                print ''Just a dummy text''
            END'

DECLARE test_cursor CURSOR FOR
        SELECT row_num FROM sa_rowgenerator(1, 31, 1)

OPEN test_cursor WITH HOLD
    FETCH NEXT test_cursor into @rowfoo
    WHILE @@FETCH_STATUS = 0
    BEGIN
        execute(@query)
        FETCH NEXT test_cursor into @rowfoo
    END
CLOSE test_cursor
DEALLOCATE test_cursor

    对我的情况很好。作为替代close_on_endtrans选项，也可以切换为会话或公共。

    • 2

相关问题