游标的一个例子

 

-- -Author:Sam Lin
-- -Date:Nov.08,2007
-- -Memo:导入通用查询数据

CREATE   Procedure  GetCommonQueryItems
(
     @TableName   VarChar ( 100 ),    -- 表/视图名称
     @BusinessPageName   VarChar ( 255 )  -- 业务页面名称
)
AS
Begin
print   @TableName
     Declare   @ItemID   int       -- 生成数据的ID
     DeClare   @ItemsCount   int   -- 对某一类数据的统计
     DeClare   @Items_Cursor   Cursor      -- 声明游标
     DeClare   @ItemName   VarChar ( 50 )    -- 字段名
     DeClare   @ItemDataType   VarChar ( 50 )
     Set   @ItemID   =   0
              Select   @ItemsCount   =   Count (ID)  From  Sys_Query
     If ( @ItemsCount   >   0 )
         Select   @ItemID   =   Max (ID)  from  Sys_Query
print   @ItemID
     Select   @ItemsCount   =   Count (ID)  From  Sys_Query  Where  TableName  =   @TableName   And  BusinessPageName  =   @BusinessPageName
     If  ( @ItemsCount   >   0 )
     Begin
         Select   @ItemID   =  ID  From  Sys_Query 
             Where  TableName  =   @TableName   And  BusinessPageName  =   @BusinessPageName     
     End
     Else
     Begin     
     Set   @ItemID   =   Convert ( int , @ItemID )  +   1
     End
print   @ItemID
     -- -开始执行游标
     Begin   Tran
         Set   @Items_Cursor   =   Cursor   For
             Select   -- @ItemID,
                A.Name  As  FieldName,
                C.Name  As  FieldType -- ,
                 -- '50' As FieldSize,
                 -- B.Name AS TableName,
                 -- @BusinessPageName As BusinessPageName,
                 -- Null,
                 -- 99 As OrderID,
                 -- 0 As IsVisible
             From  SysColumns A,
                 SysObjects B,
                 SysTypes C
             Where  A.ID  =  B.ID  And  A.xtype  =  C.xtype
             And  B.Name  =   @TableName
             And  C.Name  Not   Like   ' %DOM% '
             And  C.Name  Not   Like   ' %IDE% '
             And  C.Name  Not   Like   ' %sysname% '
         -- 打开游标
         Open   @Items_Cursor
         -- 取数据
         Fetch   Next   From   @Items_Cursor   Into    @ItemName , @ItemDataType
            
         While   @@Fetch_Status   =   0
         Begin

             Select   @ItemsCount   =   Count ( * )  From  Sys_Query  Where  
                    FieldName  =   @ItemName   And  FieldType  =   @ItemDataType   And  
                    TableName  =   @TableName   And  BusinessPageName  =   @BusinessPageName

             If  ( @ItemsCount   >   0 )
             Begin
                 Fetch   Next   From   @Items_Cursor   Into    @ItemName , @ItemDataType
                 Continue
             End
                
             If   @ItemsCount   =   0
             Begin
                 Insert   Into  Sys_Query(ID,FieldName,FieldType,FieldSize,TableName,BusinessPageName)
                         Values (
                             @ItemID ,
                             @ItemName ,
                             @ItemDataType ,    
                             ' 50 ' ,
                             @TableName ,
                             @BusinessPageName
                            )
             End
         End
        
         If   @@Error   !=   0
         Begin
             RollBack   Tran
             Return
         End     
         Fetch   Next   From   @Items_Cursor   Into    @ItemName , @ItemDataType     
     Commit   Tran
     -- 关闭游标
     Close   @Items_Cursor
     -- 释放游标
     Deallocate   @Items_Cursor
    
     Select   *   from  Sys_Query  where  ID  =   @ItemID     
End

GO