澳门太阳娱乐集团官网-太阳集团太阳娱乐登录

T-SQL用法三(游标和Fetch)
分类:数据库

CTE 也叫公用表表明式和派生表特别附近 先定义三个USACusts的CTE  

 

在生产SQLServer二零零六之后,微软概念了八个新的询问架构叫做公共表表明式--CTE。CTE是八个依据轻易询问的有的时候结果集,在叁个差相当的少的插入、更新、删除或许select语句的实践范围内选用。再本篇中,我们将看见什么样定义和选用CTE。

相似情况下,我们用SELECT那么些查询语句时,都以针对的一行记录来说,
设若要在询问分析器中对多行记录(即记录集)实行读取操作时,则须要采纳到游标或WHILE等循环
/
以下内容摘自

/
游标的类型:
  1、静态游标(不检查测试数据行的变型)
  2、动态游标(反映全数数据行的改换)
  3、仅向前游标(不扶助滚动)
  4、键集游标(能展现修改,但不可能标准反映插入、删除)

WITH USACusts AS
(
  SELECT custid, companyname
  FROM Sales.Customers
  WHERE country = N'USA'
)
SELECT * FROM USACusts;

概念和应用CTE

通过运用CTE你能写和命名贰个T-SQL select 语句,然后援引这一个命名的语句似乎使用二个表也许准备同样。

CTE上面正是定义两个CTE的语法:

WITH <expression_name> (Column1, Column2, …) AS (CTE Definition)

表示:

  • "<expression_name>"   CTE的命名
  • "Column 1, Column2,…"  查询语句再次来到结果集的列名称
  • "CTE Definition"             select语句再次来到的结果集.

概念CTE须要随着二个INSERT, UPDATE, DELETE, 恐怕SELECT的语句来援引CTE。固然CTE是三个批管理的一有些,那么说话从前用四个With最早然后以分行甘休。当你定义了三个多种CTE,即多少个CTE引用另八个CTE则须要被援用的CTE定义在援引的CTE以前。听上去也有一些凌乱,那咱们闲话少说看实例来证明呢。

下边是部分在CTE中能够被应用的选项:

  • O凯雷德DE奥迪Q3 BY (当使用top的时候能够运用)
  • INTO
  • OPTION (带有查询提醒)
  • FOR XML
  • FOR BROWSE

游标使用各类:
   1、定义游标
   2、展开游标
   3、使用游标
   4、关闭游标
   5、释放游标

with  ()  称为内部查询   与派生表同样,一旦外界查询完结后,CTE就机关释放了

递归CTE语句

本人领会递归就是调用本人的经过。每二个递归管理的迭代都回来一个结实的子集。这几个递归管理保持循环调用直至达到规范化限制才告一段落。最后的结果集其实正是CTE循环中每叁个调用超生的结果集的并集。

递归CTE,包罗了最少八个查询定义,三个是select语句,另一个询问被充当“锚成员”,而其余的查询定义被看成循环成员。锚成员查询定义不分包CTE而循环成员中包罗。其余,锚成员查询要求现身在CTE递归成员查询在此以前,且两岸再次来到的列一模一样。能够有八个锚成员查询,其中每二个都亟待与UNION ALL, UNION, INTEHighlanderSECT, 恐怕EXCEPT联合使用。当然也可能有多种的递归查询定义,每叁个递归查询定义一定与UNION ALL联合使用。UNION ALL 操作符被用来连接最终的锚查询与第三个递归查询。接下来我们用实际立在来研究一下CTE和递归CTE。

Transact-SQL:
declare 游标名 cursor [LOCAL | GLOBAL][FORWARD_ONLY | SCROLL][STATIC | KEYSET | DYNAMIC ] [READ_ONLY | SCROLL_LOCKS] 
  for selet语句   [for  update[of 列名[,列名]]
 注:LOCAL 局地游标     GLOBAL 全局游标
     FORWARD_ONLY 仅向前  SCROLL 滚动
     STATIC 静态  KEYSET 键集 DYNAMIC 动态
     READ_ONLY 只读 SCROLL_LOCKS 锁定游标当前行

CTE内部格局 正是地点代码所代表的秘诀  其实还也可能有一种外界格局

Example of a Simple CTE

如前所述,CTE 提供了一种能越来越好书写你的纷纷代码的格局,提升了代码可读性。如下边包车型大巴头晕目眩的代码

USE AdventureWorks2012;
GO
SELECT YearMonth, ProductID, SumLineTotal FROM
( SELECT CONVERT(CHAR(7),ModifiedDate,120) AS YearMonth , ProductID , SUM(LineTotal) AS SumLineTotal FROM Sales.SalesOrderDetail
GROUP BY ProductId, CONVERT(CHAR(7),ModifiedDate,120) )
MonthlyProductSales WHERE YearMonth = '2008-06';

 

代码是贰个select语句,有八个子查询在FROM前面包车型大巴子句中。子查询被作为一个派生表 MonthlyProductSales,查询表遵照基于ModifiedDate的月和年粒度实行集中,将LineTotal 金额加在一齐。在筛选出年和月份为“二〇〇九-06”**的结果后开展分组集中。

接下去大家用CTE来落到实处上述的代码。

USE AdventureWorks2012;
GO
-- CTE 定义
WITH MonthlyProductSales AS (
SELECT CONVERT(CHAR(7),ModifiedDate,120) AS YearMonth , ProductID , SUM(LineTotal) AS SumLineTotal FROM Sales.SalesOrderDetail GROUP BY ProductId, CONVERT(CHAR(7),ModifiedDate,120) )
-- 包含CTE的select语句
SELECT * FROM MonthlyProductSales WHERE YearMonth = '2008-06';

在那么些代码中,笔者将衍生表子查询放到了CTE命名称为MonthlyProductSales 的中间,然后代替了子查询,在本身的Select语句中调用CTE命名的表MonthlyProductSales,那样是还是不是突显愈发轻便掌握和爱惜了?

赢得游标的多寡
  FETCH [[NEXT | PRIOR | FIRST | LAST |
  ABSOLUTE{ n | @nvar | RELATIVE { n | @nvar}]
  From ] 游标名 [into 变量]
  注:
    NEXT  下一行  PRIOR  上一行  FIRST 第一行
    LAST  最终一行  ABSOLUTE n 第n行
    RELATIVE n 当前任务上马的第n行
    into 变量  把当下行的各字段值赋值给变量

WITH C(orderyear, custid) AS
(
  SELECT YEAR(orderdate), custid
  FROM Sales.Orders
)
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM C
GROUP BY orderyear;
GO

C(orderyear, custid)  可以理解为 select orderyear, custid from C   指定返回你想要的列  不过个人感觉没什么用!

它和派生表相同 也可以在CTE中查询使用参数

DECLARE @empid AS INT = 3;

WITH C AS
(
  SELECT YEAR(orderdate) AS orderyear, custid
  FROM Sales.Orders
  WHERE empid = @empid
)
SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
FROM C
GROUP BY orderyear;
GO

行使多种CTE的例子

 

一经你的代码越发复杂并且包括多少个子查询,你就得思量重写来简化维护和巩固易读性。重写的办法之一正是讲子查询重写成CTEs。为了更加好地体现,先看一下底下的非CTE复杂查询如下:

USE AdventureWorks2012;
GO
SELECT
SalesPersonID ,
SalesYear ,
TotalSales ,
SalesQuotaYear ,
SalesQuota FROM
( --第一个子查询
SELECT
SalesPersonID ,
SUM(TotalDue) AS TotalSales ,
YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
GROUP BY SalesPersonID, YEAR(OrderDate) ) AS Sales
JOIN ( -- 第二个子查询
SELECT
BusinessEntityID ,
SUM(SalesQuota)AS SalesQuota ,
YEAR(QuotaDate) AS SalesQuotaYear
FROM Sales.SalesPersonQuotaHistory
GROUP BY BusinessEntityID, YEAR(QuotaDate) )
AS Sales_Quota ON Sales_Quota.BusinessEntityID = Sales.SalesPersonID
AND Sales_Quota.SalesQuotaYear = Sales.SalesYear
ORDER BY SalesPersonID, SalesYear;

本身直接上代码啊,看看哪些通过CTE来简化这些代码。

USE AdventureWorks2012;
GO
WITH
-- 第一个被CTE重写的子查询
WITH Sales AS (
SELECT
SalesPersonID ,
SUM(TotalDue) AS TotalSales ,
YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
GROUP BY SalesPersonID, YEAR(OrderDate) ),
-- 第二个被CTE重写的子查询
Sales_Quota AS (
SELECT
BusinessEntityID ,
SUM(SalesQuota)AS SalesQuota ,
YEAR(QuotaDate) AS SalesQuotaYear
FROM Sales.SalesPersonQuotaHistory
GROUP BY BusinessEntityID, YEAR(QuotaDate) )
-- SELECT 使用多重CTEs
SELECT
SalesPersonID ,
SalesYear ,
TotalSales ,
SalesQuotaYear ,
SalesQuota
FROM Sales
JOIN Sales_Quota
ON Sales_Quota.BusinessEntityID = Sales.SalesPersonID
AND Sales_Quota.SalesQuotaYear = Sales.SalesYear
ORDER BY SalesPersonID, SalesYear;

 

着这段代码中,作者将多少个子查询转移到七个例外的CTEs中,第三个CTE用Sales来命名,定义了的第二身形查询,叫做SalesQuota在率先个CTE前面用逗号分隔与第二个。定义实现后,援用那多个别称来达成最后的select 语句,结果与事先复杂的代码结果一模一样。.

能够用三个纯净的WITH 子句定义三个多种CTEs,然后富含这么些CTEs在自身的最中的TSQL语句中,那使得笔者得以更便于的读、开辟和调整。使用多种CTEs对于复杂的TSQL逻辑来讲,让我们将代码放到更便于管理的细小片段里面分隔管理。

游标状态变量:
    @@fetch_status  游标状态
         0 成功  -1 失败  -2 丢失
    @@cursor_rows 游标中结果聚焦的行数
        n 行数 -1 游标是动态的  0 空集游标
操作游标的当下行:
   current of 游标名

概念多少个CTE

CTE引用CTE

为了促成CTE援引另贰个CTE大家必要满意上边三个标准:

  1. 被定义在同一个WITH自居中作为CTE被援用
  2. 被定义在被引述的CTE前面

代码如下:

USE AdventureWorks2013; GO WITH --第叁个被重写的子查询CTE Sales AS ( SELECT SalesPersonID , SUM(TotalDue) AS TotalSales , YEA福特Explorer(OrderDate) AS SalesYear FROM Sales.SalesOrderHeader WHERE SalesPersonID IS NOT NULL GROUP BY SalesPersonID, YEAEnclave(OrderDate) ), -- 第叁个子查询引用第三个CTETotalSales AS ( SELECT SUM(TotalSales)AS TotalSales , SalesYear FROM Sales GROUP BY SalesYear ) -- 调用第二个CTE SELECT * FROM TotalSales ORDER BY SalesYear;

 

以此代码中,小编定义了一个CTE命名字为Sales ,被第三个CTE引用,定义首个CTE叫做TotalSales,在这些CTE  中笔者聚集了TotalSales 列,通过整合SalesYear列。最终自身利用Select语句援引第一个CTE。

以下例子,在SQL SE传祺VE途观 三千 测量试验成功

WITH C1 AS
(
  SELECT YEAR(orderdate) AS orderyear, custid
  FROM Sales.Orders
),
C2 AS
(
  SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
  FROM C1
  GROUP BY orderyear
)
SELECT orderyear, numcusts
FROM C2
WHERE numcusts > 70;

CTE递归调用CTE实例

另叁个CTE的要紧职能就是调用自身,当用CTE调用自个儿的时候,就行程了CTE递归调用。贰个递归CTE有八个入眼部分,二个是锚成员,叁个是递归成员。锚成员开启递归成员,这里你能够把锚成员查询当作一个从未援用CTE的询问。而递归成员将会援用CTE。那一个锚成员确定了起来的记录集,然后递归成员来利用这几个伊始记录集。为了越来越好地领略递归CTE,我将开创三个实例数据经过采取递归CTE,

上边就是代码Listing 6:

 

USE tempdb; GO -- 先创建贰个客商表 CREATE TABLE dbo.Employee ( EmpID smallint NOT NULL, EmpName nvarchar(100) NOT NULL, Position nvarchar(50) NOT NULL, MgrId int NULL ); -- 插入数据INSERT INTO dbo.Employee VALUES (1, N'Joe Steel', N'President',NULL) ,(2, N'John Smith', N'VP Western Region Sales',1) ,(3, N'Sue Jones', N'VP Easter Region',1) ,(4, N'LynnHolland', N'Sales Person',2) ,(5, N'Linda 托马斯', N'Sales Person',3 ) ,(6, N'Kathy Johnson', N'Admin Assistant',1) ,(7, N'Rich Little', N'Sales Person',3) ,(8, N'大卫 Nelson', N'Sales Person', 2) ,(9, N'Mary杰克逊', N'Sales Person', 3);

Listing 6

在Listing 6笔者创立了二个职员和工人表,包涵了职工音讯,那一个表中插入了9个不等的职员和工人,MgrId 字段用来区别职员和工人的处理者的ID,这里有多少个字段为null的记录。此人并未有领导且是此处的最高档领导。来拜会自家将如何选拔递归CTE吧,在Listing7中:

USE tempdb;
GO
WITH ReportingStructure(MgrID, EmpID, EmpName, Position, OrgLevel) AS 
(
    --锚部分
    SELECT MgrID, EmpID, EmpName, Position, 0 AS OrgLevel
    FROM dbo.Employee 
    WHERE MgrID IS NULL
    UNION ALL
    -- 递归部分
    SELECT e.MgrID, e.EmpID, e.EmpName
         , e.Position, r.OrgLevel + 1
    FROM dbo.Employee AS e
    INNER JOIN ReportingStructure AS r
    ON e.MgrID = r.EmpID 
)
SELECT MgrID, EmpID, EmpName, Position, OrgLevel 
FROM ReportingStructure;

Listing 7

进行脚本结果:

MgrID EmpID  EmpName        Position                   OrgLevel
----- ------ -------------- -------------------------- -----------
NULL  1      Joe Steel      President                  0
1     2      John Smith     VP Western Region Sales    1
1     3      Sue Jones      VP Easter Region           1
1     6      Kathy Johnson  Admin Assistant            1
2     4      Lynn Holland   Sales Person               2
2     8      David Nelson   Sales Person               2
3     5      Linda Thomas   Sales Person               2
3     7      Rich Little    Sales Person               2
3     9      Mary Jackson   Sales Person               2

大家能觉察那些结果是负有职员和工人分级结构,注意OrgLevel 字段鲜明了分层等第结构,当您见到0的时候证实此人就是最大的管理者了,每三个职工过的直属长官都比本身的OrgLevel 大1。

use pubs
go

两个CTE用 , 隔开分离 通过with 内部存款和储蓄器 能够在外查询中往往引用

操纵递归

有的时候会产出无穷递归的CTE的或许,可是SQLServer有二个默许的最大递归值来制止现身Infiniti循环的CTE递归。默许是100,上面作者来比方表达:

USE tempdb; GO WITH InfiniteLoopCTE as ( -- Anchor Part SELECT EmpID, MgrID, Position FROM dbo.Employee WHERE MgrID = 1 UNION ALL -- Recursive Part SELECT InfiniteLoopCTE.EmpID , InfiniteLoopCTE.MgrID , InfiniteLoopCTE.Position FROM InfiniteLoopCTE JOIN dbo.Employee AS e ON e.EmpID = InfiniteLoopCTE.MgrID ) SELECT * FROM InfiniteLoopCTE;

Listing 8

这一部分代码引发了两个极致循环,因为递归部分将永世重回多行数据,那某个查询再次回到的结果是MrgID 为1的结果。而小编去运营这段代码后,只循环了95次,这正是出于最大递归次数的默感觉100。当然那个值也是足以设定的。如若大家绸缪超过一百回,150遍的话,如下所示:

USE tempdb; GO --Creates an infinite loop WITH InfiniteLoopCTE as ( -- 锚部分 SELECT EmpID, MgrID, Position FROM dbo.Employee WHERE MgrID = 1 UNION ALL -- 递归部分 SELECT InfiniteLoopCTE.EmpID , InfiniteLoopCTE.MgrID , InfiniteLoopCTE.Position FROM InfiniteLoopCTE JOIN dbo.Employee AS e ON e.EmpID = InfiniteLoopCTE.MgrID ) SELECT * FROM InfiniteLoopCTE OPTION (MAXRECURSION 150);

Listing 9

通过设定MAXRECUSION 的值为1四十八回完毕了递归1四十八遍的最大递归限制,这些脾性的最大值为32,767。

declare @auid char(12),@aulname varchar(20),@aufname varchar(20), @st char(2),@auinfo varchar(50)
declare auth_cur cursor for
select au_id, au_lname, au_fname, state
from authors

WITH YearlyCount AS
(
  SELECT YEAR(orderdate) AS orderyear,
    COUNT(DISTINCT custid) AS numcusts
  FROM Sales.Orders
  GROUP BY YEAR(orderdate)
)
SELECT Cur.orderyear, 
  Cur.numcusts AS curnumcusts, Prv.numcusts AS prvnumcusts,
  Cur.numcusts - Prv.numcusts AS growth
FROM YearlyCount AS Cur
  LEFT OUTER JOIN YearlyCount AS Prv
    ON Cur.orderyear = Prv.orderyear + 1;

曾几何时使用CTE

当然大家学习了什么使用CTE就要了解如哪天候来利用它,下边二种情形是行使CTE简化你的T-SQL语句的景色:

  1. 询问中要求递归
  2. 询问中有五个子查询,或许你有重复的平等的子查询在单一语句中。
  3. 询问时复杂壮大的

open auth_cur

能够需求在八个一样表结果做物理实例化  那样可以节省不胜枚举询问时间 只怕在有时表和表变量中固化内部查询结果

总结

CTE的功能为SQLServer 提供了有力的补偿,它让大家得以将复杂的代码切成比非常多轻便管理和读取的小的代码段,同一时候还允许我们应用它来树立递归代码。CTE提供了另一种艺术来兑现复杂的T-SQL逻辑,为以后我们的开拓提供了要命好的代码规范和易读性,

fetch next from auth_cur into @auid,@aulname,@aufname, @st
while (@@fetch_status=0)
  begin
    print '我编号: '+@auid
    print '小编姓名: '+@aulname+','+@aufname
    print '所在州: '+@st
    print '--------------------------'
    fetch next from auth_cur into @auid,@aulname,@aufname, @st
  end

递归CTE

close auth_cur
deallocate auth_cur

递归CTE最少由八个查询定义,最少三个询问作为定位点成员,八个查询作为递归成员。

Transact-SQL 游标主要用以存款和储蓄进程、触发器和Transact-SQL 脚本中,它们使结果集的剧情可用来别的Transact-SQL 语句。
/*另一个例子
来自:
原例子是用于注脚什么用WHILE取代游标及其好处,这里只摘出使用游标的例子
*/
  DECLARE @tbTargetPermissions    table(TargetPermissionId uniqueidentifier NOT NULL PRIMARY KEY)
图片 1    INSERT INTO @tbTargetPermissions 
图片 2        SELECT [TargetPermissionId] 
图片 3        FROM [ps_RolePermissions] 
图片 4        WHERE [TargetId] = @TargetId AND [RoleId] = @RoleId
图片 5    
图片 6    DECLARE @TargetPermissionId uniqueidentifier;
图片 7
图片 8    --定义游标
图片 9    DECLARE TargetPermissions_ByRoleId_Cursor CURSOR FOR 
图片 10        SELECT [TargetPermissionId] FROM @tbTargetPermissions 
图片 11
图片 12    --展开游标
图片 13    OPEN TargetPermissions_ByRoleId_Cursor
图片 14
图片 15    --读取游标第一条记下
图片 16    FETCH NEXT FROM    TargetPermissions_ByRoleId_Cursor INTO @TargetPermissionId
图片 17
图片 18    --检查@@FETCH_STATUS的值,以便实行巡回读取
图片 19    WHILE @@FETCH_STATUS = 0
图片 20    BEGIN
图片 21        EXEC ps_TargetPermissionEntity_Select @TargetPermissionId;
图片 22
图片 23        FETCH NEXT FROM    TargetPermissions_ByRoleId_Cursor INTO @TargetPermissionId;
图片 24    END
图片 25
图片 26    --关闭游标
图片 27    CLOSE TargetPermissions_ByRoleId_Cursor
图片 28    DEALLOCATE TargetPermissions_ByRoleId_Cursor

递归成员是一个援用CTE名称的查询 ,在率先次调用递归成员,上二个结实集是由上二遍递归成员调用重回的。 其实就和C# 方法写递归同样  再次回到上一个结果集 依次输出

<h3>
   心静似高山流水不动,心清若巫峰雾气不沾。
</h3>

   WITH    Emp
 AS ( SELECT  * FROM  dbo.dt_users
               WHERE  id=2
                UNION ALL  
                SELECT d.* FROM  Emp
                         INNER JOIN dbo.dt_users d ON d.agent_id = Emp.id
             )
    SELECT *
     FROM Emp 

在前头也写过 sql 语句的试行各种 其实到  FROM Emp   时 就进展了节点第二次递归  当大家递归到第三遍的时候 那些为施行的sql 语句实在是什么的吗

   WITH    Emp
 AS ( SELECT  * FROM  dbo.dt_users
               WHERE  id=2
                UNION ALL  
                SELECT  * FROM  dbo.dt_users
               WHERE  id=3
                UNION ALL  
                SELECT  * FROM  dbo.dt_users
               WHERE  id=4
                UNION ALL  
                SELECT d.* FROM  Emp
                         INNER JOIN dbo.dt_users d ON d.agent_id = Emp.id
             )
    SELECT *
     FROM Emp 

简易精晓能够把它看做两局地

SELECT  * FROM  dbo.dt_users
               WHERE  id=2

   SELECT d.* FROM  Emp
                         INNER JOIN dbo.dt_users d ON d.agent_id = Emp.id

上有的的结果集 会积存成最终呈现的结果 下一些的结果集  正是下贰次递归的 上部分结实集 依次拼接  正是以此递归最后的结果集 

下部分 在详解  认真看很有趣

  SELECT d.* FROM  Emp

SELECT d.* FROM   dbo.dt_users d

from Emp 源数据出自  d  在 on  d.agent_id = Emp.id 便是自连接 而 Emp.id 结果 来自何地呢  正是上部分结实集 如若是第三次运营结果集正是上某些运营的结果   记住下局部操作结果集都是如今的上一些结果集。

默认情况下递归是100次 也可在 外部查询 指定递归次数 MAXRECURSION N 0~32767 次范围 MAXRECURSION 0 并不是0次实际上是递归次数无限制

 

本文由澳门太阳娱乐集团官网发布于数据库,转载请注明出处:T-SQL用法三(游标和Fetch)

上一篇:mysql主从复制跳过错误 下一篇:没有了
猜你喜欢
热门排行
精彩图文