ms sql 实例两则（a:游标-批量修改 b:cet-递归查询）

• 1：用游标实现批量修改数据

  原理：1：请出一个表中的数据放在游标的临时表中，

  2：fetch next from testcur info 来循环表中的每条数据

  3：对每条数据进行修改 或者 删除操作

  代码

  create table test
(
iId int identity(1,1) primary key,
tag nvarchar(10),
state1 nvarchar(10),
state2 nvarchar(10),
state3 nvarchar(10),
state4 nvarchar(10)
)
declare @tempid int
declare @temptag nvarchar(10)
declare testcur cursor for select iId, tag from test
open testcur
fetch next from testcur into @tempid, @temptag
while @@FETCH_STATUS=0 -- 0 = 语句执行成功 -1 = 语句失败或此句不在结果集中 -2 = 被提取的行不存在
begin
print @@FETCH_STATUS
fetch next from testcur into @tempid, @temptag
end
close testcur
deallocate testcur

  2:CET（递归查询）

  步骤： 1：建一个经典的递归表结构

  2：用 with as 来对表进行递归查询

  代码

  create table Tree
(
NodeId int ,
ParentId int,
NodeName nvarchar(50)
)
insert Tree(NodeId, ParentID, NodeName) values (0,-1,'全球')
insert Tree(NodeId, ParentID, NodeName) values (1,0,'美国')
insert Tree(NodeId, ParentID, NodeName) values (2,0,'中国')
insert Tree(NodeId, ParentID, NodeName) values (3,0,'德国')
insert Tree(NodeId, ParentID, NodeName) values (4,2,'四川省')
insert Tree(NodeId, ParentID, NodeName) values (5,2,'广东省')
insert Tree(NodeId, ParentID, NodeName) values (6,2,'山东省')
insert Tree(NodeId, ParentID, NodeName) values (7,4,'成都市')
insert Tree(NodeId, ParentID, NodeName) values (8,4,'泸州市')
insert Tree(NodeId, ParentID, NodeName) values (9,4,'乐山市')
insert Tree(NodeId, ParentID, NodeName) values (10,8,'纳溪区')
insert Tree(NodeId, ParentID, NodeName) values (11,8,'江阳区')
insert Tree(NodeId, ParentID, NodeName) values (12,8,'龙马潭')
insert Tree(NodeId, ParentID, NodeName) values (13,10,'护国镇')
insert Tree(NodeId, ParentID, NodeName) values (14,10,'合面镇')
insert Tree(NodeId, ParentID, NodeName) values (15,10,'丰乐镇')
insert Tree(NodeId, ParentID, NodeName) values (16,13,'大营村')
insert Tree(NodeId, ParentID, NodeName) values (17,13,'沙田村')
insert Tree(NodeId, ParentID, NodeName) values (18,13,'东巷口')
WITH OrderList
AS
(
select * from Tree
where NodeName='纳溪区'
union all
SELECT t.*
from Tree as t,OrderList as ol
where ol.ParentID = t.NodeId /*递归查询*/
)
select * from OrderList
OPTION (MAXRECURSION 10);

with temptree as
(
select * from tree where NodeName = '全球'
union all
select t.NodeId, t.ParentId, t.NodeName
from tree as t , temptree as tt
where t.ParentID = tt.NodeId
)
select tt.* from tree as t , temptree as tt
where t.NodeId = tt.parentID