SQL Server快进游标（sqlserver跳出游标循环）

25-03-12 9

本文的目的是介绍SQLServer快进游标的详细情况，特别关注sqlserver跳出游标循环的相关信息。我们将通过专业的研究、有关数据的分析等多种方式，为您呈现一个全面的了解SQLServer快进游标

本文的目的是介绍SQL Server快进游标的详细情况，特别关注sqlserver跳出游标循环的相关信息。我们将通过专业的研究、有关数据的分析等多种方式，为您呈现一个全面的了解SQL Server快进游标的机会，同时也不会遗漏关于SQL Server - SQL Server 2019 启用SQL Server验证Super Administrator(sa)用户登录、SQL Server sql 游标使用笔记、SQL Server 开窗函数 Over()代替游标的使用详解、SQL Server 游标的知识。

本文目录一览：

SQL Server快进游标（sqlserver跳出游标循环）
SQL Server - SQL Server 2019 启用SQL Server验证Super Administrator(sa)用户登录
SQL Server sql 游标使用笔记
SQL Server 开窗函数 Over()代替游标的使用详解
SQL Server 游标

SQL Server快进游标（sqlserver跳出游标循环）

一般认为，应尽可能避免在存储过程中使用游标（用基于集合的逻辑等代替）。如果您遇到需要迭代某些数据并且可以以只读方式进行操作的情况，那么快进（只读向前）游标的效率比while循环差多少？从我的调查来看，似乎光标选项通常更快，并且使用的读取次数和CPU时间更少。我没有做任何广泛的测试，但这是别人发现的吗？这种类型的游标（快进）是否会带来额外的开销或资源，而这些开销或资源可能是我不知道的昂贵资源。

所有关于不使用游标的讨论都是关于在基于集合的方法可用时避免使用游标，以及使用可更新的游标等。

谢谢

SQL Server - SQL Server 2019 启用SQL Server验证Super Administrator(sa)用户登录

SQL Server 2019 启用SQL Server验证Super Administrator(sa)用户登录

在项目开发过程中通常连接SQL Server时需要用SQL Server方式，所以我们需要先开启这种验证方式，然后再根据需要添加项目用户，设定相关权限。

先用Windows身份验证方式连接数据库，然后按照如下几步设置，完成后重启服务，切换验证方式即可使用sa登录。

作者：Jeremy.Wu
出处：https://www.cnblogs.com/jeremywucnblog/
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。

SQL Server sql 游标使用笔记

感兴趣的小伙伴，下面一起跟随小编 jb51.cc的小编两巴掌来看看吧！

代码如下:

 
declare @pdtid nvarchar(50) 
declare @ptype nvarchar(50) 
declare my_cursor CURSOR for 
select pdtid,ptype from dbo.Product 
open my_cursor 
fetch next from my_cursor 
into @pdtid,@ptype 
while @@fetch_status=0 
begin 
fetch next from my_cursor 
end 
close my_cursor

SQL Server 开窗函数 Over()代替游标的使用详解

前言：

今天在优化工作中遇到的sql慢的问题，发现以前用了挺多游标来处理数据，这样就导致在数据量多的情况下，需要一行一行去遍历从而计算需要的数据，这样处理的结果就是数据慢，容易卡死。

语法介绍：

1、与Row_Number() 函数结合使用，对结果进行排序，这个是我们使用的非常多的

2、与聚合函数结合使用，利用over子句的分组和排序，对需要的数据进行操作

例如：SUM() Over() 累加值、AVG() Over() 平均数
MAX() Over() 最大值、MIN() Over() 最小值

具体介绍：

下面模拟工作中通过开窗函数代替游标的例子，通过期初余额与单据的预收金额、应收金额、实收金额来计算截止本单的期末余额，在以往就是通过游标一行一行去遍历，计算需要的期末余额，现在使用SUM() Over()来代替，最终要实现的效果图如下：

第一行表示标题；第二行表示客户，是一行空行；第三行是期初余额，只显示期末余额的数据，第四至第六行表示的是每种单据的余额情况，并逐步汇总当前行的期末余额数据；最后一行表示的是对客户的合计。

1、构建需要用到的表和数据（简略版）

--客户表
CREATE TABLE Organization(
 FItemID  INT NOT NULL PRIMARY KEY IDENTITY(1,1),
 FNumber  NVARCHAR(255),
 FName  NVARCHAR(255)
)
 
--期初数据表
CREATE TABLE InitialData(
 FID   INT NOT NULL PRIMARY KEY IDENTITY(1,1),
 FCustId   INT NOT NULL,
 FPreAmount  DECIMAL(28,10) NOT NULL DEFAULT(0),  --预收金额
 FReceivableAmount DECIMAL(28,10) NOT NULL DEFAULT(0),  --应收金额
 FReceiveAmount  DECIMAL(28,10) NOT NULL DEFAULT(0)  --实收金额
)
 
--单据明细表
CREATE TABLE DetailData(
 FID   INT NOT NULL PRIMARY KEY IDENTITY(1,1),
 FCustId   INT NOT NULL,
 FDate   DATETIME NOT NULL,
 FBillType  NVARCHAR(64) NOT NULL,
 FBillNo   NVARCHAR(64) NOT NULL,
 FPreAmount  DECIMAL(28,10) NOT NULL DEFAULT(0),  --预收金额
 FReceivableAmount DECIMAL(28,10) NOT NULL DEFAULT(0),  --应收金额
 FReceiveAmount  DECIMAL(28,10) NOT NULL DEFAULT(0)  --实收金额
)
 
INSERT INTO Organization(FNumber,FName) VALUES(''001'',''北京客户'')
INSERT INTO Organization(FNumber,FName) VALUES(''002'',''上海客户'')
INSERT INTO Organization(FNumber,FName) VALUES(''003'',''广州客户'')
 
INSERT INTO InitialData(FCustId,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(1,0,0,0)
INSERT INTO InitialData(FCustId,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(2,8000,7245,0)
INSERT INTO InitialData(FCustId,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(3,0,1068.21,1068.00)
 
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(1,''2020-06-30'',''委托结算'',''XSD20200700008'',0,1221.56,0)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(1,''2020-06-30'',''委托结算'',''XSD20200700009'',0,373.46,0)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(1,''2020-06-30'',''委托结算退货'',''XSD20200700010'',0,-427.05,0)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(1,''2020-07-30'',''销售商品返利'',''XSFL20200700005'',0,-17.9,0)
 
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(2,''2020-06-25'',''预收退款'',''SKD20200700002'',-755,0,0)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(2,''2020-06-20'',''销售发货'',''XSD20200700006'',0,6169.50,6169.50)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(2,''2020-07-30'',''销售总额返利'',''XSFL20200700002'',0,-493.56,-421.85)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(2,''2020-07-31'',''其他应收'',''QTYS20200900001'',0,6000.00,0)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(2,''2020-06-20'',''预收冲应收'',''HXD20200700006'',-7245.00,0,7245.00)
 
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(3,''2020-06-30'',''销售收款'',''SKD20200700003'',0,0,2386.96)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(3,''2020-06-30'',''应收转应收'',''HXD20200700007'',0,2386.75,0)
INSERT INTO DetailData(FCustId,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount)
VALUES(3,''2020-07-08'',''销售退货'',''XSD20200700014'',0,-46.80,0)
GO

2、以往的游标写法

SET NOCOUNT ON
--建立临时表处理获取数据
CREATE TABLE #DATA(
 FID   INT NOT NULL PRIMARY KEY IDENTITY(1,1),
 FClassTypeId  INT NOT NULL,
 FCustId   INT NOT NULL,
 FNumber   NVARCHAR(255),
 FName   NVARCHAR(255),
 FDate   DATETIME NULL,
 FBillType  NVARCHAR(64) NULL,
 FBillNo   NVARCHAR(64) NULL,
 FPreAmount  DECIMAL(28,10) NOT NULL DEFAULT(0),  --预收金额
 FReceivableAmount DECIMAL(28,10) NOT NULL DEFAULT(0),  --应收金额
 FReceiveAmount  DECIMAL(28,10) NOT NULL DEFAULT(0),  --实收金额
 FBalanceAmount  DECIMAL(28,10) NOT NULL DEFAULT(0)    --期末余额
)
 
Declare @Id     INT
Declare @CustId    INT
Declare @PreAmount   decimal(28,10)
Declare @ReceivableAmount decimal(28,10)
Declare @ReceiveAmount  decimal(28,10)
Declare @OldCustId   int
Declare @Count    int
Declare @LastAmount   decimal(28,10)
Declare @SumPreAmount  decimal(28,10)
Declare @SumReceivableAmount decimal(28,10)
Declare @SumReceiveAmount decimal(28,10)
Declare @SumBalanceAmount decimal(28,10)
 
--使用游标
Declare Data_cursor Cursor
For Select FID,FCustId,FPreAmount,FReceivableAmount,FReceiveAmount
 From DetailData
 Order By FCustId,FDate,FID
OPEN Data_cursor
FETCH NEXT FROM Data_Cursor INTO @Id,@CustId,@PreAmount,@ReceivableAmount,@ReceiveAmount
SET @OldCustId = @CustId
SET @Count = 0
SET @LastAmount = 0
SET @SumPreAmount = 0
SET @SumReceivableAmount = 0
SET @SumReceiveAmount = 0
SET @SumBalanceAmount = 0
WHILE @@FETCH_STATUS = 0
BEGIN 
 IF @Count > 0
 BEGIN
  IF @OldCustId <> @CustId 
  BEGIN
   --表示客户已经变了，要插入小计
   SET @Count = 0
   INSERT INTO #DATA(FClassTypeId,FBillType,FCustId,FNumber,FName,FPreAmount,FReceivableAmount,FReceiveAmount,FBalanceAmount)
   SELECT -9999,FName + ''小计'',FItemID,FNumber,FName,@SumPreAmount,@SumReceivableAmount,@SumReceiveAmount,@LastAmount
   FROM Organization
   WHERE FItemID = @OldCustId
   Select @SumPreAmount=0,@SumReceivableAmount=0,@SumReceiveAmount=0,@SumBalanceAmount=0,@LastAmount=0
  END  
 END 
 IF @Count = 0
 BEGIN
  Set @OldCustId=@CustId
  --插入一行空行
  INSERT INTO #DATA(FClassTypeId,FBillType,FCustId,FNumber,FName)
  SELECT -1000,FName,FItemID,FNumber,FName
  FROM Organization
  WHERE FItemID = @CustId
 
  --获取期初的期末余额
  SELECT @LastAmount=isnull(FReceivableAmount,0) - isnull(FPreAmount,0) - isnull(FReceiveAmount,0),@PreAmount=isnull(FPreAmount,0),@ReceivableAmount=isnull(FReceivableAmount,0),@ReceiveAmount=isnull(FReceiveAmount,0)
  FROM InitialData
  WHERE FCustId = @CustId
 
  INSERT INTO #DATA(FClassTypeId,FBillType,FCustId,FNumber,FName,FBalanceAmount)
  VALUES(-1000,''期初余额'',@CustId,'''','''',@LastAmount)
 
  SELECT @Count = 1
  SELECT @SumBalanceAmount = @LastAmount
 END 
 
 --插入单据明细
 INSERT INTO #DATA(FClassTypeId,FCustId,FNumber,FName,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount,FBalanceAmount)
 SELECT 0,d.FCustId,o.FNumber,o.FName,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount,@LastAmount + FReceivableAmount - FPreAmount - FReceiveAmount
 FROM DetailData d
 INNER JOIN Organization o ON d.FCustId = o.FItemID
 WHERE d.FCustId = @CustId AND FID = @Id
 
 SELECT
 @LastAmount = @LastAmount + FReceivableAmount - FPreAmount - FReceiveAmount,
 @SumPreAmount=@SumPreAmount + FPreAmount,@SumReceivableAmount=@SumReceivableAmount + FReceivableAmount,
 @SumReceiveAmount=@SumReceiveAmount + FReceiveAmount
 FROM DetailData
 WHERE FCustId = @CustId AND FID = @Id
 
 FETCH NEXT FROM Data_cursor INTO @Id,@CustId,@PreAmount,@ReceivableAmount,@ReceiveAmount
END
IF @Count > 0
BEGIN
 INSERT INTO #DATA(FClassTypeId,FBillType,FCustId,FNumber,FName,FPreAmount,FReceivableAmount,FReceiveAmount,FBalanceAmount)
 SELECT -9999,FName + ''小计'',FItemID,FNumber,FName,@SumPreAmount,@SumReceivableAmount,@SumReceiveAmount,@LastAmount
 FROM Organization
 WHERE FItemID = @OldCustId
 Select @SumPreAmount=0,@SumReceivableAmount=0,@SumReceiveAmount=0,@SumBalanceAmount=0,@LastAmount=0
END
CLOSE Data_cursor
DEALLOCATE Data_cursor
 
SELECT * FROM #DATA
ORDER BY FCustId,FID
 
DROP TABLE #DATA

代码说明：创建了一个临时表，使用游标遍历我们的DetailData数据表，为了呈现我们最终需要的数据样式，插入客户空行、期初余额、单据信息、客户小计等，逐行计算期末余额值的情况，最终效果如下：

3、使用SUM() Over()的写法

SET NOCOUNT ON
--建立临时表处理获取数据
CREATE TABLE #DATA(
 FID     INT NOT NULL PRIMARY KEY IDENTITY(1,1),
 FClassTypeId  INT NOT NULL,
 FCustId    INT NOT NULL,
 FNumber    NVARCHAR(255),
 FName    NVARCHAR(255),
 FDate    DATETIME NULL,
 FBillType   NVARCHAR(64) NULL,
 FBillNo    NVARCHAR(64) NULL,
 FPreAmount   DECIMAL(28,10) NOT NULL DEFAULT(0),  --预收金额
 FReceivableAmount DECIMAL(28,10) NOT NULL DEFAULT(0),  --应收金额
 FReceiveAmount  DECIMAL(28,10) NOT NULL DEFAULT(0),  --实收金额
 FBalanceAmount  DECIMAL(28,10) NOT NULL DEFAULT(0)  --期末余额
)
 
--插入空行
INSERT INTO #DATA(FClassTypeId,FBillType,FCustId,FNumber,FName)
SELECT -1000,FName,FItemID,FNumber,FName
FROM Organization o
INNER JOIN (SELECT FCustId FROM DetailData GROUP BY FCustId) d ON d.FCustId = o.FItemID
 
--插入期初余额
INSERT INTO #DATA(FClassTypeId,FBillType,FCustId,FNumber,FName,FBalanceAmount)
SELECT -1000,''期初余额'',FItemID,'''','''',i.FReceivableAmount - i.FPreAmount -i.FReceiveAmount
FROM Organization o
INNER JOIN InitialData i ON o.FItemID = i.FCustId
INNER JOIN (SELECT FCustId FROM DetailData GROUP BY FCustId) d ON d.FCustId = o.FItemID
 
--插入单据明细(关键代码SUM() Over() )
INSERT INTO #DATA(FClassTypeId,FCustId,FNumber,FName,FDate,FBillType,FBillNo,FPreAmount,FReceivableAmount,FReceiveAmount,FBalanceAmount)
SELECT 0,d.FCustId,o.FNumber,o.FName,d.FDate,d.FBillType,d.FBillNo,d.FPreAmount,d.FReceivableAmount,d.FReceiveAmount,
SUM(d.FReceivableAmount - d.FPreAmount - d.FReceiveAmount) OVER(PARTITION BY d.FCustId ORDER BY d.FCustId,d.FDate,d.FID)
+ i.FReceivableAmount - i.FPreAmount - i.FReceiveAmount
FROM DetailData d WITH(NOLOCK)
INNER JOIN Organization o WITH(NOLOCK) ON o.FItemID = d.FCustId
INNER JOIN InitialData i WITH(NOLOCK) ON o.FItemID = i.FCustId
ORDER BY d.FCustId,d.FDate,d.FID
 
--插入小计
INSERT INTO #DATA(FClassTypeId,FBillType,FCustId,FNumber,FName,FPreAmount,FReceivableAmount,FReceiveAmount,FBalanceAmount)
SELECT -9999,FName + ''小计'',d.FCustId,FNumber,FName,SUM(FPreAmount),SUM(FReceivableAmount),SUM(FReceiveAmount),0
FROM dbo.DetailData d
INNER JOIN dbo.Organization o ON d.FCustId = o.FItemID
GROUP BY d.FCustId,o.FName,o.FNumber
 
--更新小计的期末余额
UPDATE d SET d.FBalanceAmount = d.FReceivableAmount - d.FPreAmount - d.FReceiveAmount + i.FReceivableAmount - i.FPreAmount - i.FReceiveAmount
FROM #DATA d
INNER JOIN InitialData i ON d.FCustId = i.FCustId
WHERE d.FClassTypeId = -9999
 
SELECT * FROM #DATA
ORDER BY FCustId,FID
 
DROP TABLE #DATA

代码说明：相比第二种，去除了游标的写法，通过了

SUM(d.FReceivableAmount - d.FPreAmount - d.FReceiveAmount) OVER(PARTITION BY d.FCustId ORDER BY d.FCustId,d.FDate,d.FID)

来计算我们需要的值，这个语法说明一下，sum是累加计算，计算应收金额 - 预收金额 - 实收金额（第二行计算出来的结果要加上第一行计算出来的结果，第三行计算出来的结果要加上第二行计算出来的结果，依次类推，所以，其他聚合函数也是这种用法哦），PARTITION BY分组统计客户，并通过Order by指定排序
这个PARTITION BY和Order By结果的用法就很关键了，不然计算就不是预期想要的
再举个例子：比如使用Count() Over() 计算客户的订单号

SELECT DISTINCT FCustId,COUNT(FBillNo) OVER(PARTITION BY FCustId) FBillNum FROM DetailData

总结：

1、游标的使用场景可以很广，但是在数据量大的时候，就会显得很慢，一行一行遍历的速度还是挺久的

2、使用开窗函数来实现一些功能，还是很方便能实现效果，并且它的速度也是很快，值得推荐。

到此这篇关于SQL Server 开窗函数 Over()代替游标的使用的文章就介绍到这了,更多相关SQL Server 开窗函数 Over()内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！

您可能感兴趣的文章:

SQL server 自增ID--序号自动增加的字段操作
SQL Server中identity(自增)的用法详解
SqlServer生成连续数字根据指定的数字操作
SQL Server中row_number函数的常见用法示例详解
SQL Server如何通过创建临时表遍历更新数据详解
解决sql server保存对象字符串转换成uniqueidentifier失败的问题

SQL Server 游标

SQL 游标概念

SQL 的游标是一种临时的数据库对象，既可以存放储存在数据库表中数据行的副本，也指向数据行的指针。

SQL 游标作用

1. 遍历数据行；

2. 保存查询结果，方便下文调用。概念中提到使用游标会保存数据行的副本，那么创建游标后，下文查询即可从副本中查询，要比直接查数据库快很多。

创建游标

语法：

declare cursor_name  --游标名称，唯一标识
[insensitive] [scroll] cursor 
for
select_statement --查询语句
[for {read only| update [of column_name [,...n]]}]

insensitive

告诉 DBMS 产生查询结果的临时副本，而不是使用指针指向数据库表中源数据。

指定 insensitive 时，对底层表任何改动都不会反映到游标数据中。反之，对底层表的改动都会反映到游标数据中。

insensitive 游标是只读的，因此不能修改其内容，也不能通过它修改底层表数据。

scroll

表明所有的提取操作，即 fetch 选项（具体选项在下文提到），若不指定只能进行 next 提取。

read only

设置游标数据只读，指定 read only 后，对底层表的改动不会更新其游标数据。

update [of column_name[,...n]]

定义在游标中可被更改的列。如果只指定了 update，表示所有列都可以更新。

FETCH 语句检索数据

刚才讲了创建游标的语法，结合 fetch 语句先写个例子吧：

--创建游标
declare cursor_school scroll cursor 
for
select Num,ChineseName from School order by Num

--打开游标
open cursor_school

--定义变量
declare @num bigint, @schoolname nvarchar(50)

--提取最后一行学校信息
fetch last from cursor_school into @num, @schoolname

print ''学校编号：'' + cast(@num as varchar) + ''学校名称：'' + @schoolname

--关闭游标
close cursor_school

结果集：

输出结果：

学校编号：609781000000 学校名称：香港凱莉山學校

注意上文中 order by 之后的列必须在 select 中出现。

fetch 语句中，SQL Server 提供了 6 种定位选项：

next

返回结果集当前行的下一行，首次提取返回第一行。

frior

返回结果集的上一行，首次提取无数据返回。

first

返回结果集第一行。

last

返回结果集最后一行。

absolute

移动到结果集的第 n 行。如果 n 为正数，从结果集的第一行（包含第一行）起移到第 n 行；如果 n 为负数，则从结果集的最后一行起移到第 n 行。

relative

从游标指针的当前位置移动 n 行。如果 n 为正数，则读取游标当前位置起向后的第 n 行数据；如果 n 为负数，则读取游标当前位置起向前的第 n 行数据。

基于游标的定位 UPDATE 语句和定位 DELETE 语句

//update
update table_name set column_name,... 
where current of cursor_name

//delete
delete from table_name
where current of cursor_name

使用 update 或 delete 语句时要先创建游标。

游标关闭与释放

CLOSE（关闭）游标后不会释放其占用的数据结构。那么想要释放占用的数据结构需要用 DEALLOCATE 语句，该语句不仅删除游标中的数据，还会将游标作为对象从数据库中删除。

释放游标语句：deallocate cusor_name

今天关于SQL Server快进游标和sqlserver跳出游标循环的分享就到这里，希望大家有所收获，若想了解更多关于SQL Server - SQL Server 2019 启用SQL Server验证Super Administrator(sa)用户登录、SQL Server sql 游标使用笔记、SQL Server 开窗函数 Over()代替游标的使用详解、SQL Server 游标等相关知识，可以在本站进行查询。

本文标签：