[sqlserver]sql编程1-游标的使用和示例-建站笔记

生命不息，折腾不止。jQuery Mobile的报销单可以进入数据了，现在要做统计的数据。

需求说明

一张主表和对应关联的明细表，目标要得到主表一行记录，对应不同的费用明细，同类的费用要合并明细，总和费用和发票张数。

主表:

SqlServer游标的使用

明细表：

SqlServer游标示例代码

目标结果：

sqlserver游标

游标的使用

使用游标的顺序：声名游标、打开游标、读取数据、关闭游标、删除游标。

声明游标

最简单游标声明：DECLARE <游标名>CURSOR FOR<SELECT语句>；

其中select语句可以是简单查询，也可以是复杂的接连查询和嵌套查询

例子：[已表2 AddSalary为例子]

Declare mycursor cursor for select * from AddSalary

这样我就对表AddSalary申明了一个游标mycursor

【高级备注】

DECLARE <游标名> [INSENSITIVE] [SCROLL] CURSORFOR<SELECT语句>

这里我说一下游标中级应用中的[INSENSITIVE]和[SCROLL]

INSENSITIVE

表明MS SQL SERVER 会将游标定义所选取出来的数据记录存放在一临时表内（建立在tempdb 数据库下）。对该游标的读取操作皆由临时表来应答。因此，对基本表的修改并不影响游标提取的数据，即游标不会随着基本表内容的改变而改变，同时也无法通过游标来更新基本表。如果不使用该保留字，那么对基本表的更新、删除都会反映到游标中。

另外应该指出，当遇到以下情况发生时，游标将自动设定INSENSITIVE 选项。

a.在SELECT 语句中使用DISTINCT、 GROUP BY、 HAVING UNION 语句；

b.使用OUTER JOIN；

c.所选取的任意表没有索引；

d.将实数值当作选取的列。

SCROLL

表明所有的提取操作（如FIRST、 LAST、 PRIOR、 NEXT、 RELATIVE、 ABSOLUTE）都可用。如果不使用该保留字，那么只能进行NEXT 提取操作。由此可见，SCROLL 极大地增加了提取数据的灵活性，可以随意读取结果集中的任一行数据记录，而不必关闭再

重开游标。

打开游标

非常简单，我们就打开刚才我们声明的游标mycursor

OPEN mycursor

读取数据

FETCH [ NEXT | PRIOR | FIRST | LAST] FROM { 游标名 | @游标变量名 } [ INTO @变量名 [，…] ]

参数说明：

NEXT 取下一行的数据，并把下一行作为当前行(递增)。由于打开游标后，行指针是指向该游标第1行之前，所以第一次执行FETCH NEXT操作将取得游标集中的第1行数据。NEXT为默认的游标提取选项。

INTO @变量名[,…] 把提取操作的列数据放到局部变量中。列表中的各个变量从左到右与游标结果集中的相应列相关联。各变量的数据类型必须与相应的结果列的数据类型匹配或是结果列数据类型所支持的隐性转换。变量的数目必须与游标选择列表中的列的数目一致。

现在我们就取出mycursor游标的数据吧！

当游标被打开时，行指针将指向该游标集第1行之前，如果要读取游标集中的第1行数据，必须移动行指针使其指向第1行。就本例而言，可以使用下列操作读取第1行数据：

Eg: Fetch next from mycursor 或则 Fetch first from mycursor

这样我就取出了游标里的数据，但是光光这样可不够，我们还需要将取出的数据赋给变量

–声明2个变量

declare @O_ID NVARCHAR(20)

declare @A_Salary float

–将取出的值传入刚才声明的2个变量

Fetch next from mycursor into @ O_ID,@ A_Salary

关闭游标

CLOSE mycursor

删除游标

DEALLOCATE mycursor

游标的示例代码

CREATE FUNCTION [dbo].[Fun_DayPlanFee_All] 
(
	--@FeeID INT,
	--@FType NVARCHAR(50)
)
RETURNS 
@FeeTable TABLE 
(
	FeeID INT,
	PlanDate DATETIME,
	BelongType NVARCHAR(50),
	BelongNum NVARCHAR(50),
	CusName NVARCHAR(50),
	ContentType NVARCHAR(50),
	Effect NVARCHAR(50),
	ContentDes NVARCHAR(1000),
	FeeAfford NVARCHAR(50),
	UserName NVARCHAR(50),	
	
	FeeType NVARCHAR(50),
	Detail NVARCHAR(2000),
	FeeCount FLOAT,
	FeeNumCount int	
)
AS
BEGIN

	DECLARE @FeeID INT	
	DECLARE @JiaoTong NVARCHAR(2000)
	DECLARE @ZhuSu NVARCHAR(2000)
	DECLARE @ZhaoDai NVARCHAR(2000)
	DECLARE @YouJi NVARCHAR(2000)
	DECLARE @BiaoShu NVARCHAR(2000)
	DECLARE @QiTa NVARCHAR(2000)
	
	DECLARE cur_Fee CURSOR FOR SELECT ID FROM dbo.DayPlan_Fee
	OPEN cur_Fee
	FETCH NEXT FROM cur_Fee INTO @FeeID
	WHILE(@@FETCH_STATUS=0)
	BEGIN
		SELECT @JiaoTong=交通,@ZhuSu=住宿,@ZhaoDai=招待,@YouJi=邮寄费总和,@BiaoShu=标书费总和,@QiTa=其他费用总和 FROM dbo.View_DayPlanFee_Users WHERE ID=@FeeID
		IF (LEN(@JiaoTong)>0)
			INSERT INTO @FeeTable
			SELECT  ID ,日期 , 归集对象,对应编号,客户名称,内容类别,工作效果, 工作说明,费用承担人,填单人姓名,'交通费' , 交通,交通费总和 , 交通费发票张数总和
			FROM dbo.View_DayPlanFee_Users WHERE ID=@FeeID
		IF(LEN(@ZhuSu)>0)
			INSERT INTO @FeeTable
			SELECT  ID ,日期 , 归集对象,对应编号,客户名称,内容类别,工作效果, 工作说明,费用承担人,填单人姓名,'住宿费' , 住宿, 住宿费总和, 住宿费发票张数总和
			FROM dbo.View_DayPlanFee_Users WHERE ID=@FeeID
		IF (LEN(@ZhaoDai)>0)
			INSERT INTO @FeeTable
			SELECT  ID ,日期 , 归集对象,对应编号,客户名称,内容类别,工作效果, 工作说明,费用承担人,填单人姓名,'招待费' , 招待, 招待费总和,招待费发票张数总和 
			FROM dbo.View_DayPlanFee_Users WHERE ID=@FeeID
		IF(LEN(@YouJi)>0)
			INSERT INTO @FeeTable
			SELECT  ID ,日期 , 归集对象,对应编号,客户名称,内容类别,工作效果, 工作说明,费用承担人,填单人姓名,'邮寄费' ,'' ,邮寄费总和 , 邮寄费发票张数
			FROM dbo.View_DayPlanFee_Users WHERE ID=@FeeID
		IF(LEN(@QiTa)>0)
			INSERT INTO @FeeTable
			SELECT  ID ,日期 , 归集对象,对应编号,客户名称,内容类别,工作效果, 工作说明,费用承担人,填单人姓名,'其他' ,'' ,其他费用总和 ,其他发票张数总和 
			FROM dbo.View_DayPlanFee_Users WHERE ID=@FeeID
		
	FETCH NEXT FROM cur_Fee INTO @FeeID
	END
	CLOSE cur_Fee
	DEALLOCATE cur_Fee
	
	RETURN 
END

总结

这里是我建立了一个表值函数，利用了游标来循环遍历主表，然后抽取对应的明细总和，插入结果表集。当然里面也涉及了很多其他的知识点，sqlserver的标量函数，表值函数，存储过程，视图等知识。今后将陆续更新，做个笔记，也给大家个参考。

时间紧迫，也许这些代码中存在着很多不够完善的地方，先解决问题，之后再优化吧。