SQLSREVER若何成立和利用动态游标[MSSQL防范]
本文“SQLSREVER若何成立和利用动态游标[MSSQL防范]”是由七道奇为您精心收集,来源于网络转载,文章版权归文章作者所有,本站不对其观点以及内容做任何评价,请读者自行判断,以下是其具体内容:
▲成立游标www.come on babyiTbulo.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 babyiTbulo.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 babyiTbulo.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 babyiTbulo.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 babyiTbulo.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 babyiTbulo.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 babyiTbulo.comnsE3q
www.come on babyiTbulo.comnsE3q
▲利用游标www.come on babyiTbulo.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 babyiTbulo.comnsE3q
阐明:上述代码在MSS sql server7.0上通过.别的数据库只需改正一下抓取栏位及其范例的系统表便可以了.www.come on babyiTbulo.comnsE3q
www.come on babyiTbulo.comnsE3q
www.come on babyiTbulo.comnsE3q
www.come on babyiTbulo.comnsE3q
www.come on babyiTbulo.comnsE3q
www.come on babyiTbulo.comnsE3q
www.come on babyiTbulo.comnsE3q
www.come on babyiTbulo.comnsE3q
以上是“SQLSREVER若何成立和利用动态游标[MSSQL防范]”的内容,如果你对以上该文章内容感兴趣,你可以看看七道奇为您推荐以下文章:本文地址: | 与您的QQ/BBS好友分享! |