SET NOCOUNT ONDECLARE @au_id varchar(11), @au_fname varchar(20), @au_lname varchar(40), @message varchar(80), @title varchar(80)PRINT "-------- Utah Authors report --------"DECLARE authors_cursor CURSOR FOR SELECT au_id, au_fname, au_lname FROM authors WHERE state = "UT" ORDER BY au_idOPEN authors_cursorFETCH NEXT FROM authors_cursor INTO @au_id, @au_fname, @au_lnameWHILE @@FETCH_STATUS = 0 BEGIN PRINT " " SELECT @message = "----- Books by Author: " + @au_fname + " " + @au_lname PRINT @message -- Declare an inner cursor based -- on au_id from the outer cursor. DECLARE titles_cursor CURSOR FOR SELECT t.title FROM titleauthor ta, titles t WHERE ta.title_id = t.title_id AND ta.au_id = @au_id -- Variable value from the outer cursor OPEN titles_cursor FETCH NEXT FROM titles_cursor INTO @title IF @@FETCH_STATUS <> 0 PRINT " <<No Books>>" WHILE @@FETCH_STATUS = 0 BEGIN
SELECT @message = " " + @title PRINT @message FETCH NEXT FROM titles_cursor INTO @title
END CLOSE titles_cursor DEALLOCATE titles_cursor
-- Get the next author. FETCH NEXT FROM authors_cursor INTO @au_id, @au_fname, @au_lname ENDCLOSE authors_cursor DEALLOCATE authors_cursor GO-------- Utah Authors report --------
----- Books by Author: Anne Ringer The Gourmet Microwave Is Anger the Enemy?
----- Books by Author: Albert Ringer Is Anger the Enemy? Life Without Fear
DECLARE @BIANLIANG1 INT DECLARE @BIANLIANG2 varchar(20)DECLARE Employee_Cursor CURSOR FOR --定义一个游标 SELECT EmployeeID, Title FROM AdventureWorks.HumanResources.Employee; OPEN Employee_Cursor; --打开游标 FETCH NEXT FROM Employee_Cursor INTO @BIANLIANG1,@BIANLIANG2 ; --获取游标中的记录 WHILE @@FETCH_STATUS = 0 --如果能成功取到记录 BEGIN /* 这里写你要进行的操作代码 */ FETCH NEXT FROM Employee_Cursor INTO @BIANLIANG1,@BIANLIANG2; -- 获取下一条游标记录 END; CLOSE Employee_Cursor; --关闭游标 DEALLOCATE Employee_Cursor; --销毁游标 GO这个问题问过好几次了哦
create table paylog ( gold int not null, paydate datetime not null, des nvarchar(10) not null ) insert into paylog select '80','2010-09-10 13:18','付费' union all select '100','2010-09-11 14:18','赠送' union all select '30','2010-09-12 14:20','赠送' union all select '40','2010-09-13 14:20','付费' union all select '90','2010-09-14 14:20','付费' union all select '30','2010-09-15 14:20','赠送' gocreate table costlog ( gold int not null, costdate datetime not null ) insert into costlog select '50','2010-09-12 14:00' union all select '80','2010-09-13 14:18' union all select '10','2010-09-14 14:20' union all select '60','2010-09-15 14:20' union all select '50','2010-09-16 14:20' union all select '1000','2010-09-17 14:20' go--游标写的先进先出!最好不要用游标,影响效率!create table cun(gold int,paydate datetime,[des] nvarchar(10),go int,costdate datetime,details int) godeclare my_cursor cursor scroll for select * from paylog open my_cursor declare @gold int declare @paydate datetime declare @des nvarchar(10) set @gold = 0 fetch next from my_cursor into @gold,@paydate,@des while (@@fetch_status = 0) begin declare next_cursor cursor scroll for select * from costlog open next_cursor declare @go int declare @costdate datetime declare @details int declare @gocun int declare @decun int set @decun = 0 set @go = 0 set @details = 0 set @gocun = @gold fetch next from next_cursor into @go,@costdate set @gocun = @gocun - @go while(@@fetch_status = 0) begin if(@gocun = 0) begin set @details = @gold insert into cun select @gold,@paydate,@des,@go,@costdate,@details fetch next from my_cursor into @gold,@paydate,@des fetch next from next_cursor into @go,@costdate set @gocun = @gold - @go set @decun = 0 end if(@gocun > 0) begin set @details = @go - @decun insert into cun select @gold,@paydate,@des,@go,@costdate,@details fetch next from next_cursor into @go,@costdate set @gocun = @gocun - @go set @decun = 0 end if(@gocun < 0) begin set @details = @go + @gocun - @decun insert into cun select @gold,@paydate,@des,@go,@costdate,@details fetch next from my_cursor into @gold,@paydate,@des set @gocun = @gold + @gocun set @decun = @details end end close next_cursor deallocate next_cursor end close my_cursor deallocate my_cursor select * from cun/***********结果: gold paydate des go costdate details ----------- ----------------------- ---------- ----------- ----------------------- ----------- 80 2010-09-10 13:18:00.000 付费 50 2010-09-12 14:00:00.000 50 80 2010-09-10 13:18:00.000 付费 80 2010-09-13 14:18:00.000 30 100 2010-09-11 14:18:00.000 赠送 80 2010-09-13 14:18:00.000 50 100 2010-09-11 14:18:00.000 赠送 10 2010-09-14 14:20:00.000 10 100 2010-09-11 14:18:00.000 赠送 60 2010-09-15 14:20:00.000 40 30 2010-09-12 14:20:00.000 赠送 60 2010-09-15 14:20:00.000 20 30 2010-09-12 14:20:00.000 赠送 50 2010-09-16 14:20:00.000 10 40 2010-09-13 14:20:00.000 付费 50 2010-09-16 14:20:00.000 40 90 2010-09-14 14:20:00.000 付费 1000 2010-09-17 14:20:00.000 90 30 2010-09-15 14:20:00.000 赠送 1000 2010-09-17 14:20:00.000 30 ********/
WHILE @@FETCH_STATUS = 0 --如果能成功取到记录 BEGIN /* 这里写你要进行的操作代码 */ FETCH NEXT FROM Employee_Cursor INTO @BIANLIANG1,@BIANLIANG2; -- 获取下一条游标记录 END;里面已经标注了执行的SQL语句,/* 这里写你要进行的操作代码 */,在这里写你要执行的SQL
@message varchar(80), @title varchar(80)PRINT "-------- Utah Authors report --------"DECLARE authors_cursor CURSOR FOR
SELECT au_id, au_fname, au_lname
FROM authors
WHERE state = "UT"
ORDER BY au_idOPEN authors_cursorFETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lnameWHILE @@FETCH_STATUS = 0
BEGIN
PRINT " "
SELECT @message = "----- Books by Author: " +
@au_fname + " " + @au_lname PRINT @message -- Declare an inner cursor based
-- on au_id from the outer cursor. DECLARE titles_cursor CURSOR FOR
SELECT t.title
FROM titleauthor ta, titles t
WHERE ta.title_id = t.title_id AND
ta.au_id = @au_id -- Variable value from the outer cursor OPEN titles_cursor
FETCH NEXT FROM titles_cursor INTO @title IF @@FETCH_STATUS <> 0
PRINT " <<No Books>>" WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @message = " " + @title
PRINT @message
FETCH NEXT FROM titles_cursor INTO @title
END CLOSE titles_cursor
DEALLOCATE titles_cursor
-- Get the next author.
FETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lname
ENDCLOSE authors_cursor
DEALLOCATE authors_cursor
GO-------- Utah Authors report --------
----- Books by Author: Anne Ringer
The Gourmet Microwave
Is Anger the Enemy?
----- Books by Author: Albert Ringer
Is Anger the Enemy?
Life Without Fear
DECLARE @BIANLIANG2 varchar(20)DECLARE Employee_Cursor CURSOR FOR --定义一个游标
SELECT EmployeeID, Title FROM AdventureWorks.HumanResources.Employee;
OPEN Employee_Cursor; --打开游标
FETCH NEXT FROM Employee_Cursor INTO @BIANLIANG1,@BIANLIANG2 ; --获取游标中的记录
WHILE @@FETCH_STATUS = 0 --如果能成功取到记录
BEGIN
/* 这里写你要进行的操作代码 */
FETCH NEXT FROM Employee_Cursor INTO @BIANLIANG1,@BIANLIANG2; -- 获取下一条游标记录
END;
CLOSE Employee_Cursor; --关闭游标
DEALLOCATE Employee_Cursor; --销毁游标
GO这个问题问过好几次了哦
创建游标的CREATE PROCEDURE Usp_CreateCursor(@ Select_Command为varchar(8000),@光标变输出Cursor_Return)作为
/ *存储过程名称:Usp_CreateCursor
功能描述:根据指定的选择创建一个动态游标
参数描述:@ Select_Command ---选择语句; @ Cursor_Return ---要返回的游标变量
思路:动态游标的关键是不知如何去构造它的选择语句,因为选择是个字符串表量,定义时不能直接用它,但它可以来源于表。
所以我的目的就是创建一个统一的表,从中取数据不就可以了。建表有一定的语法规则,所以就应该根据栏位列表生成相应的
格式,这个可以从系统表中获取。关键的问题是如何将数据插入到临时表,我摸索出一条语句可
实现这个功能,那就是插入的EXECUTE <SQL>探索INQUIRY <TABLE_NAME>,而SQL7.0的帮助未讲。有表有数据就可以创建了。
创建人:康剑民
创建日期:2001年7月11日
* /
声明@ Select_Command_Temp为varchar(8000),---存放选择临时语法
@ 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诠释,---存放栏位长度
@ Column_Xprec_Temp诠释,---存放栏位精度
@ Column_Xscale_Temp诠释,---存放栏位小数位数
@ From_Pos诠释,从存放的位置---
@ Where_Pos诠释,存放在那里的位置---
@ Having_Pos诠释,存放后的位置---
@ Groupby_Pos诠释,---存放groupby的位置
@ Orderby_Pos诠释,---存放的OrderBy的位置
@ Temp_Pos诠释,---临时变量
@ Column_Count诠释,---存放栏位总数
@ Loop_Seq诠释---循环步进变量---创建临时表
创建表#考试(A int)的
---如果传来的选择语句不是以'选择'开头,自动修改
如果左(下(LTrim时(@ Select_Command)),6)<>“选择'选择@ Select_Command ='选择'+ @ Select_Command
---将开头'选择'去掉
@ Select_Command_Temp =更低的选择(LTrim时(@ Select_Command))
如果左(@ Select_Command_Temp,6)='选择'选择@ Select_Command_Temp =右(@ Select_Command_Temp,莱恩(@ Select_Command_Temp) - 7)
---取各保留字位置,以便获得表的列表
选择@ From_Pos = CHARINDEX('从',@ Select_Command_Temp)
选择@ Where_Pos = CHARINDEX('这里',@ Select_Command_Temp)
选择@ Having_Pos = CHARINDEX('有',@ Select_Command_Temp)
选择@ Groupby_Pos = CHARINDEX('groupby',@ Select_Command_Temp)
选择@ Orderby_Pos = CHARINDEX('的OrderBy',@ Select_Command_Temp)如果@ Where_Pos> 0选择@ Temp_Pos = @ Where_Pos
如果@ Having_Pos> 0和@ Having_Pos <@ Temp_Pos选择@ Temp_Pos = @ Having_Pos
如果@ Groupby_Pos> 0和@ Groupby_Pos <@ Temp_Pos选择@ Temp_Pos = @ Groupby_Pos
如果@ Orderby_Pos> 0和@ Orderby_Pos <@ Temp_Pos选择@ Temp_Pos = @ Orderby_Pos
---取表列表
如果@ Temp_Pos> 0
开始
选择@ Table_List =子(@ Select_Command_Temp,@ From_Pos + 6,@ Temp_Pos - @ From_Pos - 1)
完
其他
开始
选择@ Table_List =子(@ Select_Command_Temp,@ From_Pos + 6,莱恩(@ Select_Command_Temp) - @ From_Pos - 1)
完选择@ Column_Syntax = ''
---只列出栏位
@ Select_Command_Temp =选择左(@ Select_Command_Temp,@ From_Pos - 1)
虽然莱恩(@ Select_Command_Temp)“0
开始
---取逗号位置
选择@ Temp_Pos = CHARINDEX (',',@ Select_Command_Temp)
---初次取栏位名称
如果@ Temp_Pos>零
开始
选择@ column_name的=左(@ Select_Command_Temp,@ Temp_Pos - 1)
完
其他
开始
column_name的选择@ = @ Select_Command_Temp
完
---取表名和栏位名(可能是'*')
如果CHARINDEX ('.',@栏位)“0
开始
选择@ table_name的=左(@栏位,CHARINDEX ('.',@栏位) - 1)
= @ column_name的选择权(@栏位,莱恩(@ column_name中) - CHARINDEX ('.',@栏位))
完
其他
开始
选择@ table_name的= @ Table_List
完 ---栏位出现'*'
如果CHARINDEX ('*',@栏位)“0
开始
= '' @ column_name的选择
选择@ Loop_Seq = 1
---取栏位个数
选择@ Column_Count =计数(*)
从syscolumns中
其中id =的object_id(@ table_name的)
虽然@ Loop_Seq <= @ Column_Count
开始
---取栏位名称,栏位类型,长度,精度,小数位
@ Column_Name_Temp = SysColumns.Name选择,
@ Column_Type_Temp =下(SysTypes.Name)
@ Column_Length_Temp = SysColumns.Length,
@ Column_Xprec_Temp = SysColumns.Xprec,
@ Column_Xscale_Temp = SysColumns.Xscale
从syscolumns中,systypes中
凡SysColumns.Id =的object_id(@ table_name的)和
SysColumns.Colid = @ Loop_Seq And
SysColumns.XuserType = SysTypes.XuserType
---形成栏位语法表达式
选择@ Column_Syntax_Temp =案例当@ Column_Type_Temp在('日期时间','形象','诠释')则@ Column_Name_Temp +''+ @ Column_Type_Temp
当@ Column_Type_Temp在('二进制','位','炭','为varchar),使用@ Column_Name_Temp +''+ @ Column_Type_Temp +'('+转换(数据类型为varchar(10),@ Column_Length_Temp)+')'
否则@ Column_Name_Temp +''+ @ Column_Type_Temp +'('+转换(数据类型为varchar(10),@ Column_Xprec_Temp)+','+转换(数据类型为varchar(10),@ Column_Xscale_Temp)+')'
完
选择@ Column_Syntax = @ @ Column_Syntax_Temp Column_Syntax + +','
选择@ Loop_Seq + 1 = @ loop_Seq
完
完
其他
开始
---取栏位名称
选择@ Column_Name_Temp = @ column_name中
---取栏位类型,长度,精度,小数位
选择@ Column_Type_Temp =下(SysTypes.Name)
@ Column_Length_Temp =的isNull(SysColumns.Length,0),
@ Column_Xprec_Temp =的isNull(SysColumns.Xprec,0),
@ Column_Xscale_Temp =的isNull(SysColumns.Xscale,零),
从syscolumns中,systypes中
凡SysColumns.Id =的object_id(@ table_name的)和
SysColumns.Name = @ Column_Name_Temp和
SysColumns.XuserType = SysTypes.XuserType
---形成栏位语法表达式
选择@ Column_Syntax_Temp =案例当@ Column_Type_Temp在('日期时间','形象','诠释')则@ Column_Name_Temp +''+ @ Column_Type_Temp
当@ Column_Type_Temp在('二进制','位','炭','为varchar),使用@ Column_Name_Temp +''+ @ Column_Type_Temp +'('+转换(数据类型为varchar(10),@ Column_Length_Temp)+')'
否则@ Column_Name_Temp +''+ @ Column_Type_Temp +'('+转换(数据类型为varchar(10),@ Column_Xprec_Temp)+','+转换(数据类型为varchar(10),@ Column_Xscale_Temp)+')'
完
选择@ Column_Syntax = @ @ Column_Syntax_Temp Column_Syntax + +',' 完
---处理栏位列表
如果@ Temp_Pos> 0
开始
选择@ Select_Command_Temp =右(@ Select_Command_Temp,莱恩(@ Select_Command_Temp) - @ Temp_Pos),
完
其他
开始
选择@ Select_Command_Temp = ''
完
完
---形成正确的栏位创建语法
选择@ Column_Syntax =左(@ Column_Syntax,莱恩(@ Column_Syntax) - 1)
---修改临时表的结构
执行('#测试新增的ALTER TABLE'+ @ Column_Syntax)
执行('的ALTER TABLE#测试跌落栏的')
---将选择执行的结构集插入到临时表
插入#测试
执行(@ Select_Command)
---创建游标
将@ Cursor_Return =光标的局部涡旋为READ_ONLY
选择*
从#测试
---打开游标
打开@ Cursor_Return ▲使用游标/注:在选择中有几项,取到@从@ cursor_name的cust_id就应该声明几个变量,而且顺序和类型必须一致.* /
@ cursor_name声明游标,
@ select_command为varchar(8000),
@的cust_id为varchar(20)
选择@ select_command ='选择的cust_id从so_cust'
执行usp_createcursor @ select_command,@ cursor_name输出
从提取到@ @ cursor_name的cust_id
而@ @ FETCH_STATUS的= 0
开始
从提取到@ @ cursor_name的cust_id
结束
密切@ cursor_name
释放cursor_name
create table paylog (
gold int not null,
paydate datetime not null,
des nvarchar(10) not null
)
insert into paylog
select '80','2010-09-10 13:18','付费'
union all
select '100','2010-09-11 14:18','赠送'
union all
select '30','2010-09-12 14:20','赠送'
union all
select '40','2010-09-13 14:20','付费'
union all
select '90','2010-09-14 14:20','付费'
union all
select '30','2010-09-15 14:20','赠送'
gocreate table costlog (
gold int not null,
costdate datetime not null
)
insert into costlog
select '50','2010-09-12 14:00'
union all
select '80','2010-09-13 14:18'
union all
select '10','2010-09-14 14:20'
union all
select '60','2010-09-15 14:20'
union all
select '50','2010-09-16 14:20'
union all
select '1000','2010-09-17 14:20'
go--游标写的先进先出!最好不要用游标,影响效率!create table cun(gold int,paydate datetime,[des] nvarchar(10),go int,costdate datetime,details int)
godeclare my_cursor cursor scroll
for
select * from paylog
open my_cursor
declare @gold int
declare @paydate datetime
declare @des nvarchar(10)
set @gold = 0
fetch next from my_cursor into @gold,@paydate,@des
while (@@fetch_status = 0)
begin
declare next_cursor cursor scroll
for
select * from costlog
open next_cursor
declare @go int
declare @costdate datetime
declare @details int
declare @gocun int
declare @decun int
set @decun = 0
set @go = 0
set @details = 0
set @gocun = @gold
fetch next from next_cursor into @go,@costdate
set @gocun = @gocun - @go
while(@@fetch_status = 0)
begin
if(@gocun = 0)
begin
set @details = @gold
insert into cun select @gold,@paydate,@des,@go,@costdate,@details
fetch next from my_cursor into @gold,@paydate,@des
fetch next from next_cursor into @go,@costdate
set @gocun = @gold - @go
set @decun = 0
end
if(@gocun > 0)
begin
set @details = @go - @decun
insert into cun select @gold,@paydate,@des,@go,@costdate,@details
fetch next from next_cursor into @go,@costdate
set @gocun = @gocun - @go
set @decun = 0
end
if(@gocun < 0)
begin
set @details = @go + @gocun - @decun
insert into cun select @gold,@paydate,@des,@go,@costdate,@details
fetch next from my_cursor into @gold,@paydate,@des
set @gocun = @gold + @gocun
set @decun = @details
end
end
close next_cursor
deallocate next_cursor
end
close my_cursor
deallocate my_cursor
select * from cun/***********结果:
gold paydate des go costdate details
----------- ----------------------- ---------- ----------- ----------------------- -----------
80 2010-09-10 13:18:00.000 付费 50 2010-09-12 14:00:00.000 50
80 2010-09-10 13:18:00.000 付费 80 2010-09-13 14:18:00.000 30
100 2010-09-11 14:18:00.000 赠送 80 2010-09-13 14:18:00.000 50
100 2010-09-11 14:18:00.000 赠送 10 2010-09-14 14:20:00.000 10
100 2010-09-11 14:18:00.000 赠送 60 2010-09-15 14:20:00.000 40
30 2010-09-12 14:20:00.000 赠送 60 2010-09-15 14:20:00.000 20
30 2010-09-12 14:20:00.000 赠送 50 2010-09-16 14:20:00.000 10
40 2010-09-13 14:20:00.000 付费 50 2010-09-16 14:20:00.000 40
90 2010-09-14 14:20:00.000 付费 1000 2010-09-17 14:20:00.000 90
30 2010-09-15 14:20:00.000 赠送 1000 2010-09-17 14:20:00.000 30
********/
WHILE @@FETCH_STATUS = 0 --如果能成功取到记录
BEGIN
/* 这里写你要进行的操作代码 */
FETCH NEXT FROM Employee_Cursor INTO @BIANLIANG1,@BIANLIANG2; -- 获取下一条游标记录
END;里面已经标注了执行的SQL语句,/* 这里写你要进行的操作代码 */,在这里写你要执行的SQL
所以你要做的就是定义N个变量,取出每一行的记录 给这N个变量赋值,然后操作你这N个变量 是保存这N个变量记录到另外一张表或是用这N个变量作为参数执行其他存储过程等等。要是练习就先从简单做起,先打印出这N个变量