IDC资讯中心
▲创建游标
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 +@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_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上通过。其它数据库只需修改一下抓取栏位及其类型的系统表就可以了。
