动态游标

目前，UDB-TDS 无法识别在存储过程或函数中动态定义的游标。解决方法是使用临时表重写游标。

设置代码：

DROP TABLE IF EXISTS my_table

GO

CREATE TABLE my_table (c1 int, c2 varchar(64), c3 tinyint, c4 money, c5 datetime)

GO

在 SQL Server 中编译和工作的过程，但不是 UDB-TDS：

DROP PROC IF EXISTS sp_cur_test

GO

 

CREATE PROC sp_cur_test (@TableName varchar(100))

AS

DECLARE @exec_str VARCHAR(4000);

DECLARE @table_schema VARCHAR(100), @table_name VARCHAR(100), @column_name VARCHAR(100);

SELECT @exec_str = 'DECLARE fetch_cursor

CURSOR FOR SELECT table_schema, table_name, column_name

FROM INFORMATION_SCHEMA.COLUMNS

WHERE table_name = ''' + @TableName + '''';

 

EXEC(@exec_str);

OPEN fetch_cursor;

FETCH fetch_cursor INTO @table_schema, @table_name, @column_name;

WHILE @@FETCH_STATUS = 0

 BEGIN

  PRINT @table_schema;

  PRINT @table_name;

  PRINT @column_name;

  FETCH fetch_cursor INTO @table_schema, @table_name, @column_name;

 END;

CLOSE fetch_cursor;

DEALLOCATE fetch_cursor;

GO

 

execute sp_cur_test 'MY_TABLE';

GO

在 SQL Server 或 UDB-TDS 中使用临时表重写游标后的过程：

DROP PROC IF EXISTS sp_cur_test;

GO

 

CREATE PROC sp_cur_test (@TableName varchar(100))

AS

DECLARE @exec_str VARCHAR(4000);

DECLARE @table_schema VARCHAR(100), @table_name VARCHAR(100), @column_name VARCHAR(100);

CREATE TABLE #temp_cur (table_schema VARCHAR(100), table_name VARCHAR(100), column_name

VARCHAR(100));

SELECT @exec_str = 'INSERT INTO #temp_cur

SELECT table_schema, table_name, column_name

FROM INFORMATION_SCHEMA.COLUMNS

WHERE lower(table_name) = LOWER(''' + @TableName + ''')';

EXEC(@exec_str);

DECLARE fetch_cursor CURSOR FOR SELECT * FROM #temp_cur;

OPEN fetch_cursor;

FETCH fetch_cursor INTO @table_schema, @table_name, @column_name;

WHILE @@FETCH_STATUS = 0

 BEGIN

  PRINT @table_schema;

  PRINT @table_name;

  PRINT @column_name;

  FETCH fetch_cursor INTO @table_schema, @table_name, @column_name;

 END;

CLOSE fetch_cursor;

DEALLOCATE fetch_cursor;

GO

 

execute sp_cur_test 'MY_TABLE';

GO