SQLSREVER如何创建和使用动态游标

▲创建游标www.come on babychinai tp ow er.comnsE3q

CREATE PROCEDURE Usp_CreateCursor(@Select_Command varchar(8000),@Cursor_Return CURSOR VARYING OUTPUT) AS
/*存储过程名称：Usp_CreateCursor
  功能描述：    根据指定的SELECT创建一个动态游标
  参数描述：    @Select_Command ---SELECT语句;@Cursor_Return ---要返回的游标变量
  思路：        动态游标的关键是不知如何去构造它的SELECT语句，因为SELECT是个字符串表量，定义时不能直接用它，但它可以来源于表。
                所以我的目的就是创建一个统一的表，从中取数据不就可以了。建表有一定的语法规则，所以就应该根据栏位列表生成相应的
                格式，这个可以从系统表中获取。关键的问题是如何将数据插入到临时表，我摸索出一条语句可
                实现这个功能，那就是INSERT INTO <TABLE_NAME> EXECUTE <SQL>，而SQL7.0的帮助未讲。有表有数据就可以创建了。
  创建人：      康剑民
  创建日期：    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,---存放from的位置
        @Where_Pos int,---存放where的位置
        @Having_Pos int,---存放having的位置
        @Groupby_Pos int,---存放groupby的位置
        @Orderby_Pos int,---存放orderby的位置
        @Temp_Pos int,---临时变量
        @Column_Count int,---存放栏位总数
        @Loop_Seq int---循环步进变量www.come on babychinai tp ow er.comnsE3q

---创建临时表
Create Table #Test(a int)
---如果传来的SELECT语句不是以'select'开头，自动修改
If Left(Lower(Ltrim(@Select_Command)),6) <> 'select' Select @Select_Command = 'Select ' + @Select_Command
---将开头‘SELECT’去掉
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(' groupby ',@Select_Command_Temp)
Select @Orderby_Pos = CHARINDEX(' orderby ',@Select_Command_Temp)www.come on babychinai tp ow er.comnsE3q

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
   Begin
   Select @Table_List = SUBSTRING(@Select_Command_Temp,@From_Pos + 6 ,@Temp_Pos - @From_Pos - 1)
   End
Else
   Begin
   Select @Table_List = SUBSTRING(@Select_Command_Temp,@From_Pos + 6 ,Len(@Select_Command_Temp) - @From_Pos - 1)
   Endwww.come on babychinai tp ow er.comnsE3q

Select @Column_Syntax = '
---只列出栏位
Select @Select_Command_Temp = Left(@Select_Command_Temp,@From_Pos - 1)
While Len(@Select_Command_Temp) > 0
   Begin
   ---取逗号位置
   Select @Temp_Pos = CHARINDEX(',',@Select_Command_Temp)
   ---初次取栏位名称
   If @Temp_Pos > 0
      Begin
      Select @Column_Name = Left(@Select_Command_Temp,@Temp_Pos - 1)
      End
   Else
      Begin
      Select @Column_Name = @Select_Command_Temp
      End
   ---取表名和栏位名（可能是‘*’）
   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
      Begin
      Select @Table_Name = @Table_List
      Endwww.come on babychinai tp ow er.comnsE3q

   ---栏位出现'*'
   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 + ','www.come on babychinai tp ow er.comnsE3q

      End
      ---处理栏位列表
      If @Temp_Pos > 0
         Begin
         Select @Select_Command_Temp = Right(@Select_Command_Temp,Len(@Select_Command_Temp) - @Temp_Pos)
         End
      Else
         Begin
         Select @Select_Command_Temp = '
         End
   End
   ---形成正确的栏位创建语法
   Select @Column_Syntax = Left(@Column_Syntax,Len(@Column_Syntax) - 1)
   ---修改临时表的结构
   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_Returnwww.come on babychinai tp ow er.comnsE3q

 www.come on babychinai tp ow er.comnsE3q

▲使用游标www.come on babychinai tp ow er.comnsE3q

/注:在SELECT中有几项,fetch from @cursor_name into @cust_id就应该声明几个变量,而且顺序和类型必须一致.*/
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
   fetch from @cursor_name into @cust_id
   end
close @cursor_name
deallocate cursor_namewww.come on babychinai tp ow er.comnsE3q

说明:上述代码在MSS SQL SERVER7.0上通过。其它数据库只需修改一下抓取栏位及其类型的系统表就可以了。www.come on babychinai tp ow er.comnsE3q

 www.come on babychinai tp ow er.comnsE3q

 www.come on babychinai tp ow er.comnsE3q

 www.come on babychinai tp ow er.comnsE3q

 www.come on babychinai tp ow er.comnsE3q

 www.come on babychinai tp ow er.comnsE3q

 www.come on babychinai tp ow er.comnsE3q

 www.come on babychinai tp ow er.comnsE3q