生命不息,折腾不止。jQuery Mobile的报销单可以进入数据了,现在要做统计的数据。
需求说明
一张主表和对应关联的明细表,目标要得到主表一行记录,对应不同的费用明细,同类的费用要合并明细,总和费用和发票张数。
主表:
明细表:
目标结果:
游标的使用
使用游标的顺序: 声名游标、打开游标、读取数据、关闭游标、删除游标。
声明游标
最简单游标声明: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的标量函数,表值函数,存储过程,视图等知识。今后将陆续更新,做个笔记,也给大家个参考。
时间紧迫,也许这些代码中存在着很多不够完善的地方,先解决问题,之后再优化吧。
It’s really a great and useful piece of info. I’m satisfied that you simply shared this helpful info with us. Please stay us up to date like this. Thanks for sharing.
Glad that this info would help you!