▲創(chuàng)建游標(biāo)

CREATE PROCEDURE Usp_CreateCursor(@Select_Command varchar(8000),@Cursor_Return CURSOR VARYING OUTPUT) AS
/*存儲過程名稱：Usp_CreateCursor
? 功能描述：??? 根據(jù)指定的SELECT創(chuàng)建一個(gè)動(dòng)態(tài)游標(biāo)
? 參數(shù)描述：??? @Select_Command ---SELECT語句;@Cursor_Return ---要返回的游標(biāo)變量
? 思路：??????? 動(dòng)態(tài)游標(biāo)的關(guān)鍵是不知如何去構(gòu)造它的SELECT語句，因?yàn)镾ELECT是個(gè)字符串表量，定義時(shí)不能直接用它，但它可以來源于表。
??????????????? 所以我的目的就是創(chuàng)建一個(gè)統(tǒng)一的表，從中取數(shù)據(jù)不就可以了。建表有一定的語法規(guī)則，所以就應(yīng)該根據(jù)欄位列表生成相應(yīng)的
??????????????? 格式，這個(gè)可以從系統(tǒng)表中獲取。關(guān)鍵的問題是如何將數(shù)據(jù)插入到臨時(shí)表，我摸索出一條語句可
??????????????? 實(shí)現(xiàn)這個(gè)功能，那就是INSERT INTO <TABLE_NAME> EXECUTE <SQL>，而SQL7.0的幫助未講。有表有數(shù)據(jù)就可以創(chuàng)建了。
? 創(chuàng)建人：????? 康劍民
? 創(chuàng)建日期：??? 2001-07-11
*/
Declare @Select_Command_Temp Varchar(8000), ---存放SELECT臨時(shí)語法
??????? @Table_List varchar(255), ---存放表的列表
??????? @Column_List varchar(8000),---存放欄位列表
??????? @Table_Name varchar(30),---存放單獨(dú)表名
??????? @Column_Name varchar(30),---存放單獨(dú)欄位名（但有可能是*）
??????? @Column_Syntax varchar(8000),---存放欄位建表時(shí)的語法（綜合）
??????? @Column_Name_Temp varchar(30),---存放欄位名稱
??????? @Column_Type_Temp varchar(30),----存放欄位類型
??????? @Column_Syntax_Temp varchar(8000),---存放欄位建表時(shí)的語法（單個(gè)）
??????? @Column_Length_Temp int,---存放欄位長度
??????? @Column_Xprec_Temp int,---存放欄位精度
??????? @Column_Xscale_Temp int,---存放欄位小數(shù)位數(shù)
??????? @From_Pos int,---存放from的位置
??????? @Where_Pos int,---存放where的位置
??????? @Having_Pos int,---存放having的位置
??????? @Groupby_Pos int,---存放groupby的位置
??????? @Orderby_Pos int,---存放orderby的位置
??????? @Temp_Pos int,---臨時(shí)變量
??????? @Column_Count int,---存放欄位總數(shù)
??????? @Loop_Seq int---循環(huán)步進(jìn)變量

---創(chuàng)建臨時(shí)表
Create Table #Test(a int)
---如果傳來的SELECT語句不是以'select'開頭，自動(dòng)修改
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)

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)
?? End

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
????? End

?? ---欄位出現(xiàn)'*'
?? If CHARINDEX('*',@Column_Name) > 0
????? Begin
????? Select @Column_Name = ''
????? Select @Loop_Seq = 1
????? ---取欄位個(gè)數(shù)
????? Select @Column_Count = Count(*)
??????? From SysColumns
?????? Where Id = Object_Id(@Table_name)
????? While @Loop_Seq <= @Column_Count
???????? Begin
???????? ---取欄位名稱,欄位類型，長度，精度，小數(shù)位
???????? 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
???????? ---形成欄位語法表達(dá)式
???????? 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
????? ---取欄位類型，長度，精度，小數(shù)位
????? 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
????? ---形成欄位語法表達(dá)式
????? 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
???????? Begin
???????? Select @Select_Command_Temp = Right(@Select_Command_Temp,Len(@Select_Command_Temp) - @Temp_Pos)
???????? End
????? Else
???????? Begin
???????? Select @Select_Command_Temp = ''
???????? End
?? End
?? ---形成正確的欄位創(chuàng)建語法
?? Select @Column_Syntax = Left(@Column_Syntax,Len(@Column_Syntax) - 1)
?? ---修改臨時(shí)表的結(jié)構(gòu)
?? Execute('Alter Table #Test Add '+@Column_Syntax)
?? Execute('Alter Table #Test Drop Column a')
?? ---將SELECT執(zhí)行的結(jié)構(gòu)集插入到臨時(shí)表
?? Insert Into #Test
?? Execute(@Select_Command)
?? ---創(chuàng)建游標(biāo)
?? Set @Cursor_Return =? CURSOR LOCAL SCROLL READ_ONLY FOR
???????????????????????? Select *
?????????????????????????? From #Test???????
?? ---打開游標(biāo)????????????????
?? Open @Cursor_Return

?

▲使用游標(biāo)

/注:在SELECT中有幾項(xiàng),fetch from @cursor_name into @cust_id就應(yīng)該聲明幾個(gè)變量,而且順序和類型必須一致.*/
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_name

說明:上述代碼在MSS SQL SERVER7.0上通過。其它數(shù)據(jù)庫只需修改一下抓取欄位及其類型的系統(tǒng)表就可以了。

?

?

SQLSREVER如何創(chuàng)建和使用動(dòng)態(tài)游標(biāo)