本文来源于网页设计爱好者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上通过。其它数据库只需修改一下抓取栏位及其类型的系统表就可以了。
