SQLAnywhere：游標在執行 DDL 語句後關閉

在這裡有這個程式碼：

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 語句進行了隱式送出。由於我需要通過腳本創建多個程序，是否有解決此問題的方法？

打開游標時，存在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選項，也可以切換為會話或公共。

引用自：https://dba.stackexchange.com/questions/73487