SQL Server 動態游標創建與使用完整指南

創建動態游標存儲過程：Usp_CreateCursor

功能：根據傳入的 SELECT 語句動態創建一個 LOCAL SCROLL READ_ONLY 游標並返回。

核心思路：動態游標關鍵在於構造 SELECT 語句。因 SELECT 為字符串，定義時不能直接使用，但可從表獲取。因此創建一個統一臨時表，從中取數據即可。建表語法可從系統表獲取，關鍵是如何將數據插入臨時表——使用 INSERT INTO EXECUTE <SQL> 實現（SQL 7.0 幫助未記載）。有表有數據即可創建游標。

存儲過程完整代碼

CREATE PROCEDURE Usp_CreateCursor
    @Select_Command varchar(8000),
    @Cursor_Return CURSOR VARYING OUTPUT
AS
/* 存儲過程名稱：Usp_CreateCursor
   功能：根據指定 SELECT 創建動態游標
   參數：@Select_Command -- SELECT 語句; @Cursor_Return -- 返回游標變量
   創建人：康劍民  日期：2001-07-11
*/
DECLARE @Select_Command_Temp Varchar(8000),  -- 存放 SELECT 臨時語法
        @Table_List varchar(255),             -- 存放表列表
        @Column_List varchar(8000),           -- 存放欄位列表
        @Table_Name varchar(30),              -- 單獨表名
        @Column_Name varchar(30),             -- 單獨欄位名（可能為 *）
        @Column_Syntax varchar(8000),         -- 欄位建表語法（綜合）
        @Column_Name_Temp varchar(30),        -- 欄位名稱暫存
        @Column_Type_Temp varchar(30),        -- 欄位類型暫存
        @Column_Syntax_Temp varchar(8000),    -- 單個欄位建表語法
        @Column_Length_Temp int,              -- 欄位長度
        @Column_Xprec_Temp int,               -- 欄位精度
        @Column_Xscale_Temp int,              -- 欄位小數位數
        @From_Pos int, @Where_Pos int, @Having_Pos int,
        @Groupby_Pos int, @Orderby_Pos int,
        @Temp_Pos int, @Column_Count int, @Loop_Seq int

-- 創建臨時表
CREATE TABLE #Test(a int)

-- 修正 SELECT 語句開頭
IF Left(Lower(Ltrim(@Select_Command)),6) <> 'select'
    SELECT @Select_Command = 'Select ' + @Select_Command
SELECT @Select_Command_Temp = Lower(Ltrim(@Select_Command))
IF Left(@Select_Command_Temp,6) = 'select'
    SELECT @Select_Command_Temp = Right(@Select_Command_Temp, Len(@Select_Command_Temp)-7)

-- 定位保留字位置以獲取表列表
SELECT @From_Pos = CHARINDEX(' from ', @Select_Command_Temp)
SELECT @Where_Pos = CHARINDEX(' where ', @Select_Command_Temp)
SELECT @Having_Pos = CHARINDEX(' having ', @Select_Command_Temp)
SELECT @Groupby_Pos = CHARINDEX(' group by ', @Select_Command_Temp)
SELECT @Orderby_Pos = CHARINDEX(' order by ', @Select_Command_Temp)

-- 取最早出現的子句位置作為表列表結束點
IF @Where_Pos > 0 SELECT @Temp_Pos = @Where_Pos
IF @Having_Pos > 0 AND @Having_Pos < @Temp_Pos SELECT @Temp_Pos = @Having_Pos
IF @Groupby_Pos > 0 AND @Groupby_Pos < @Temp_Pos SELECT @Temp_Pos = @Groupby_Pos
IF @Orderby_Pos > 0 AND @Orderby_Pos < @Temp_Pos SELECT @Temp_Pos = @Orderby_Pos

-- 提取表列表
IF @Temp_Pos > 0
    SELECT @Table_List = SUBSTRING(@Select_Command_Temp, @From_Pos+6, @Temp_Pos-@From_Pos-1)
ELSE
    SELECT @Table_List = SUBSTRING(@Select_Command_Temp, @From_Pos+6, Len(@Select_Command_Temp)-@From_Pos-1)

-- 僅提取欄位部分
SELECT @Select_Command_Temp = Left(@Select_Command_Temp, @From_Pos-1)

-- 循環解析每個欄位，構建建表語法
SELECT @Column_Syntax = ''
WHILE Len(@Select_Command_Temp) > 0
BEGIN
    SELECT @Temp_Pos = CHARINDEX(',', @Select_Command_Temp)
    IF @Temp_Pos > 0
        SELECT @Column_Name = Left(@Select_Command_Temp, @Temp_Pos-1)
    ELSE
        SELECT @Column_Name = @Select_Command_Temp

    -- 分離表名與欄位名
    IF CHARINDEX('.', @Column_Name) > 0
    BEGIN
        SELECT @Table_Name = Left(@Column_Name, CHARINDEX('.', @Column_Name)-1)
        SELECT @Column_Name = Right(@Column_Name, Len(@Column_Name)-CHARINDEX('.', @Column_Name))
    END
    ELSE
        SELECT @Table_Name = @Table_List

    -- 處理 * 號：從系統表獲取該表所有欄位定義
    IF CHARINDEX('*', @Column_Name) > 0
    BEGIN
        SELECT @Column_Name = ''
        SELECT @Loop_Seq = 1
        SELECT @Column_Count = COUNT(*) FROM SysColumns WHERE Id = OBJECT_ID(@Table_Name)
        WHILE @Loop_Seq <= @Column_Count
        BEGIN
            SELECT @Column_Name_Temp = SysColumns.Name,
                   @Column_Type_Temp = Lower(SysTypes.Name),
                   @Column_Length_Temp = SysColumns.Length,
                   @Column_Xprec_Temp = SysColumns.Xprec,
                   @Column_Xscale_Temp = SysColumns.Xscale
            FROM SysColumns, SysTypes
            WHERE SysColumns.Id = OBJECT_ID(@Table_Name)
              AND SysColumns.Colid = @Loop_Seq
              AND SysColumns.XuserType = SysTypes.XuserType

            -- 根據類型生成欄位定義語法
            SELECT @Column_Syntax_Temp = CASE
                WHEN @Column_Type_Temp IN ('datetime','image','int') THEN @Column_Name_Temp + ' ' + @Column_Type_Temp
                WHEN @Column_Type_Temp IN ('binary','bit','char','varchar') THEN @Column_Name_Temp + ' ' + @Column_Type_Temp + '(' + CONVERT(VARCHAR(10), @Column_Length_Temp) + ')'
                ELSE @Column_Name_Temp + ' ' + @Column_Type_Temp + '(' + CONVERT(VARCHAR(10), @Column_Xprec_Temp) + ',' + CONVERT(VARCHAR(10), @Column_Xscale_Temp) + ')'
            END
            SELECT @Column_Syntax = @Column_Syntax + @Column_Syntax_Temp + ','
            SELECT @Loop_Seq = @Loop_Seq + 1
        END
    END
    ELSE
    BEGIN
        -- 單個欄位：從系統表獲取類型等信息
        SELECT @Column_Name_Temp = @Column_Name
        SELECT @Column_Type_Temp = Lower(SysTypes.Name),
               @Column_Length_Temp = ISNULL(SysColumns.Length,0),
               @Column_Xprec_Temp = ISNULL(SysColumns.Xprec,0),
               @Column_Xscale_Temp = ISNULL(SysColumns.Xscale,0)
        FROM SysColumns, SysTypes
        WHERE SysColumns.Id = OBJECT_ID(@Table_Name)
          AND SysColumns.Name = @Column_Name_Temp
          AND SysColumns.XuserType = SysTypes.XuserType

        SELECT @Column_Syntax_Temp = CASE
            WHEN @Column_Type_Temp IN ('datetime','image','int') THEN @Column_Name_Temp + ' ' + @Column_Type_Temp
            WHEN @Column_Type_Temp IN ('binary','bit','char','varchar') THEN @Column_Name_Temp + ' ' + @Column_Type_Temp + '(' + CONVERT(VARCHAR(10), @Column_Length_Temp) + ')'
            ELSE @Column_Name_Temp + ' ' + @Column_Type_Temp + '(' + CONVERT(VARCHAR(10), @Column_Xprec_Temp) + ',' + CONVERT(VARCHAR(10), @Column_Xscale_Temp) + ')'
        END
        SELECT @Column_Syntax = @Column_Syntax + @Column_Syntax_Temp + ','
    END

    -- 移動到下一欄位
    IF @Temp_Pos > 0
        SELECT @Select_Command_Temp = Right(@Select_Command_Temp, Len(@Select_Command_Temp)-@Temp_Pos)
    ELSE
        SELECT @Select_Command_Temp = ''
END

-- 修正欄位語法尾部逗號
SELECT @Column_Syntax = Left(@Column_Syntax, Len(@Column_Syntax)-1)

-- 修改臨時表結構：添加解析出的欄位，刪除初始佔位列 a
EXECUTE('ALTER TABLE #Test ADD ' + @Column_Syntax)
EXECUTE('ALTER TABLE #Test DROP COLUMN a')

-- 將原 SELECT 結果集插入臨時表
INSERT INTO #Test EXECUTE(@Select_Command)

-- 創建並打開游標
SET @Cursor_Return = CURSOR LOCAL SCROLL READ_ONLY FOR SELECT * FROM #Test
OPEN @Cursor_Return

使用動態游標示例

-- 注：FETCH 變量數量、順序、類型必須與 SELECT 列表一致
DECLARE @cursor_name CURSOR,
        @select_command VARCHAR(8000),
        @cust_id VARCHAR(20)

SELECT @select_command = 'SELECT cust_id FROM so_cust'
EXECUTE usp_createcursor @select_command, @cursor_name OUTPUT

FETCH FROM @cursor_name INTO @cust_id
WHILE @@FETCH_STATUS = 0
BEGIN
    -- 處理 @cust_id
    FETCH FROM @cursor_name INTO @cust_id
END
CLOSE @cursor_name
DEALLOCATE @cursor_name

說明：上述代碼在 MS SQL Server 7.0 測試通過。其他數據庫只需修改獲取欄位類型的系統表即可移植。