资源描述:
《ms sql 实例两则(a-游标》由会员上传分享,免费在线阅读,更多相关内容在工程资料-天天文库。
1、mssql实例两则(a:游标 1:用游标实现批量修改数据 原理:1:请出一个表中的数据放在游标的临时表中, 2:fetchnextfromtestcur 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 nvarcha
2、r(10))declare tempid intdeclare temptag nvarchar(10)declare testcur cursor for select iId, tag from test open testcur fetch next from testcur into tempid, temptag testcur into tempid, temptag endclose testcurdeallocate testcur 2:CET(递归查询) 步骤:1:建一个经典的递归表结构 2:用e nvarchar(50
3、)) 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,
4、'四川省')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
5、(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, NodeNam
6、e) 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,'东巷口')AXRECURSION 10); ptree
7、 as( select * from tree e = '全球' union all select t.NodeId, t.ParentId, t.NodeName from tree as t , temptree as tt tree as t , temptree as tt where t.NodeId = tt.parentID>>>>这篇文章来自..,。