本文來源于網頁設計愛好者web開發社區http://www.html.org.cn收集整理，歡迎訪問。
▲創建游標
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---循環步進變量
---創建臨時表
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)
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
---欄位出現'*'
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
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 '[email protected]_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
▲使用游標
/注:在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_name
說明:上述代碼在mss sql server7.0上通過。其它數據庫只需修改一下抓取欄位及其類型的系統表就可以了。
