▲创建游标
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
1<table_name> EXECUTE <sql>，而SQL7.0的帮助未讲。有表有数据就可以创建了。
2创建人： 康剑民
3创建日期： 2001-07-11
4*/
5Declare @Select_Command_Temp Varchar(8000), ---存放SELECT临时语法
6@Table_List varchar(255), ---存放表的列表
7@Column_List varchar(8000),---存放栏位列表
8@Table_Name varchar(30),---存放单独表名
9@Column_Name varchar(30),---存放单独栏位名（但有可能是*）
10@Column_Syntax varchar(8000),---存放栏位建表时的语法（综合）
11@Column_Name_Temp varchar(30),---存放栏位名称
12@Column_Type_Temp varchar(30),----存放栏位类型
13@Column_Syntax_Temp varchar(8000),---存放栏位建表时的语法（单个）
14@Column_Length_Temp int,---存放栏位长度
15@Column_Xprec_Temp int,---存放栏位精度
16@Column_Xscale_Temp int,---存放栏位小数位数
17@From_Pos int,---存放from的位置
18@Where_Pos int,---存放where的位置
19@Having_Pos int,---存放having的位置
20@Groupby_Pos int,---存放groupby的位置
21@Orderby_Pos int,---存放orderby的位置
22@Temp_Pos int,---临时变量
23@Column_Count int,---存放栏位总数
24@Loop_Seq int---循环步进变量
25
26\---创建临时表
27Create Table #Test(a int)
28\---如果传来的SELECT语句不是以'select'开头，自动修改
29If Left(Lower(Ltrim(@Select_Command)),6) <> 'select' Select @Select_Command = 'Select ' + @Select_Command
30\---将开头‘SELECT’去掉
31Select @Select_Command_Temp = Lower(Ltrim(@Select_Command))
32If Left(@Select_Command_Temp,6) = 'select' Select @Select_Command_Temp = Right(@Select_Command_Temp,Len(@Select_Command_Temp) - 7)
33\---取各保留字位置，以便获得表的列表
34Select @From_Pos = CHARINDEX(' from ',@Select_Command_Temp)
35Select @Where_Pos = CHARINDEX(' where ',@Select_Command_Temp)
36Select @Having_Pos = CHARINDEX(' having ',@Select_Command_Temp)
37Select @Groupby_Pos = CHARINDEX(' groupby ',@Select_Command_Temp)
38Select @Orderby_Pos = CHARINDEX(' orderby ',@Select_Command_Temp)
39
40If @Where_Pos > 0 Select @Temp_Pos = @Where_Pos
41If @Having_Pos > 0 And @Having_Pos < @Temp_Pos Select @Temp_Pos = @Having_Pos
42If @Groupby_Pos > 0 And @Groupby_Pos < @Temp_Pos Select @Temp_Pos = @Groupby_Pos
43If @Orderby_Pos > 0 And @Orderby_Pos < @Temp_Pos Select @Temp_Pos = @Orderby_Pos
44\---取表列表
45If @Temp_Pos > 0
46Begin
47Select @Table_List = SUBSTRING(@Select_Command_Temp,@From_Pos + 6 ,@Temp_Pos - @From_Pos - 1)
48End
49Else
50Begin
51Select @Table_List = SUBSTRING(@Select_Command_Temp,@From_Pos + 6 ,Len(@Select_Command_Temp) - @From_Pos - 1)
52End
53
54Select @Column_Syntax = ''
55\---只列出栏位
56Select @Select_Command_Temp = Left(@Select_Command_Temp,@From_Pos - 1)
57While Len(@Select_Command_Temp) > 0
58Begin
59\---取逗号位置
60Select @Temp_Pos = CHARINDEX(',',@Select_Command_Temp)
61\---初次取栏位名称
62If @Temp_Pos > 0
63Begin
64Select @Column_Name = Left(@Select_Command_Temp,@Temp_Pos - 1)
65End
66Else
67Begin
68Select @Column_Name = @Select_Command_Temp
69End
70\---取表名和栏位名（可能是‘*’）
71If CHARINDEX('.',@Column_Name) > 0
72Begin
73Select @Table_Name = Left(@Column_Name,CHARINDEX('.',@Column_Name) - 1)
74Select @Column_Name = Right(@Column_Name,Len(@Column_Name) - CHARINDEX('.',@Column_Name))
75End
76Else
77Begin
78Select @Table_Name = @Table_List
79End
80
81\---栏位出现'*'
82If CHARINDEX('*',@Column_Name) > 0
83Begin
84Select @Column_Name = ''
85Select @Loop_Seq = 1
86\---取栏位个数
87Select @Column_Count = Count(*)
88From SysColumns
89Where Id = Object_Id(@Table_name)
90While @Loop_Seq <= @Column_Count
91Begin
92\---取栏位名称,栏位类型，长度，精度，小数位
93Select @Column_Name_Temp = SysColumns.Name,
94@Column_Type_Temp = Lower(SysTypes.Name),
95@Column_Length_Temp = SysColumns.Length,
96@Column_Xprec_Temp = SysColumns.Xprec,
97@Column_Xscale_Temp = SysColumns.Xscale
98From SysColumns,SysTypes
99Where SysColumns.Id = Object_Id(@Table_name) And
100SysColumns.Colid = @Loop_Seq And
101SysColumns.XuserType = SysTypes.XuserType
102\---形成栏位语法表达式
103Select @Column_Syntax_Temp = Case When @Column_Type_Temp In ('datetime','image','int') Then @Column_Name_Temp + ' ' + @Column_Type_Temp
104When @Column_Type_Temp In ('binary','bit','char','varchar') Then @Column_Name_Temp + ' ' + @Column_Type_Temp + '('+Convert(Varchar(10),@Column_Length_Temp) + ')'
105Else @Column_Name_Temp + ' ' + @Column_Type_Temp + '('+Convert(varchar(10),@Column_Xprec_Temp) + ',' + Convert(varchar(10),@Column_Xscale_Temp) + ')'
106End
107Select @Column_Syntax = @Column_Syntax + @Column_Syntax_Temp + ','
108Select @Loop_Seq = @loop_Seq + 1
109End
110End
111Else
112Begin
113\---取栏位名称
114Select @Column_Name_Temp = @Column_Name
115\---取栏位类型，长度，精度，小数位
116Select @Column_Type_Temp = Lower(SysTypes.Name),
117@Column_Length_Temp = Isnull(SysColumns.Length,0),
118@Column_Xprec_Temp = Isnull(SysColumns.Xprec,0),
119@Column_Xscale_Temp = Isnull(SysColumns.Xscale,0)
120From SysColumns,SysTypes
121Where SysColumns.Id = Object_Id(@Table_name) And
122SysColumns.Name = @Column_Name_Temp And
123SysColumns.XuserType = SysTypes.XuserType
124\---形成栏位语法表达式
125Select @Column_Syntax_Temp = Case When @Column_Type_Temp In ('datetime','image','int') Then @Column_Name_Temp + ' ' + @Column_Type_Temp
126When @Column_Type_Temp In ('binary','bit','char','varchar') Then @Column_Name_Temp + ' ' + @Column_Type_Temp + '('+Convert(Varchar(10),@Column_Length_Temp) + ')'
127Else @Column_Name_Temp + ' ' + @Column_Type_Temp + '('+Convert(varchar(10),@Column_Xprec_Temp) + ',' + Convert(varchar(10),@Column_Xscale_Temp) + ')'
128End
129Select @Column_Syntax = @Column_Syntax + @Column_Syntax_Temp + ','
130
131End
132\---处理栏位列表
133If @Temp_Pos > 0
134Begin
135Select @Select_Command_Temp = Right(@Select_Command_Temp,Len(@Select_Command_Temp) - @Temp_Pos)
136End
137Else
138Begin
139Select @Select_Command_Temp = ''
140End
141End
142\---形成正确的栏位创建语法
143Select @Column_Syntax = Left(@Column_Syntax,Len(@Column_Syntax) - 1)
144\---修改临时表的结构
145Execute('Alter Table #Test Add '+@Column_Syntax)
146Execute('Alter Table #Test Drop Column a')
147\---将SELECT执行的结构集插入到临时表
148Insert Into #Test
149Execute(@Select_Command)
150\---创建游标
151Set @Cursor_Return = CURSOR LOCAL SCROLL READ_ONLY FOR
152Select *
153From #Test
154\---打开游标
155Open @Cursor_Return
156
157▲使用游标
158
159/注:在SELECT中有几项,fetch from @cursor_name into @cust_id就应该声明几个变量,而且顺序和类型必须一致.*/
160declare @cursor_name cursor,
161@select_command varchar(8000),
162@cust_id varchar(20)
163select @select_command = 'select cust_id from so_cust'
164execute usp_createcursor @select_command,@cursor_name OUTPUT
165fetch from @cursor_name into @cust_id
166while @@fetch_status = 0
167begin
168fetch from @cursor_name into @cust_id
169end
170close @cursor_name
171deallocate cursor_name
172
173说明:上述代码在MSS SQL SERVER7.0上通过。其它数据库只需修改一下抓取栏位及其类型的系统表就可以了。</sql></table_name>