sql存储过程中循环批量插入

    前几天有一个需求很头痛，部门是有上下级关系的，在给部门的经理赋予角色和权限的时候，通常我们都会认为假如经理A的部门是1，那么我给了他部门1 的管理权限，那么1的下级部门101,102,103 “自然而然的”都应该给他管理。

    这个自然而然可不是想当然的那么自然，尤其系统没有设置批量添加功能的时候，靠人工一个个的去添加下级部门真的是不现实。更过分的是，用户要求整个公司的管理人员都要自动的拥有其所在部门的所有下级部门的某个权限，emmmm，烦死了，领导那么多，做事的小弟却只有一个，一个个手动添加到何时？？？

    由于整个系统都是依赖sql存储过程，别问我为什么不写java啊net啊c#啊什么的，这个系统是公司购买的，全部都是存储过程，几千上万个吧，几千行的存储过程能把人看瞎咯！没办法只能随行就市写起来咯。

    思路也很清楚，首先要把这些管理人员和所在部门找到，这是其一，其二是要把这些管理人员的下级部门找到，这两个都找到以后，要顺序循环管理人员，然后把他的部门和权限关联起来。说起来容易做起来难，尤其我的存储过程还没达到精通的程度，也是反复琢磨，终于给搞出来了。

以下就是具体代码，仅供参考：

首先准备部门和下级部门数据，这个就比较简单了，一个简单的递归查询，写成存储过程以便调用。

CREATE PROCEDURE [dbo].[rsp_selectTreeofsubDep]  --查询下级部门
    -- Add the parameters for the stored procedure here
@depid int    --父部门id
AS
BEGIN
    with cte as (
select d.depID ,d.AdminID from Department d
where DepID = @depid
union all
select d.DepID ,d.AdminID from Department d
inner join cte o on o.DepID = d.AdminID
)
select DepID  from cte 
END

GO

 

　　然后就是主存储过程，用来循环批量插入数据的：

CREATE Procedure [dbo].[rsp_insertMultitiesofDep]      --代理部门批量插入      

AS
DECLARE @i int,  @RoleID int,@Depid int ----循环角色ID

 DECLARE @UserDepidTemp TABLE    --部门负责人代号和其所在部门
(
UserID varchar(10),
Depid int
)
 DECLARE @subDepidTemp TABLE    --部门负责人的下级部门
(                  
Depid int
)
 DECLARE @UserDepidRoleTemp TABLE   --部门负责人角色和其所在部门
(
RoleID int,
Depid int,
FlagID TINYINT
)
BEGIN
--整理部门负责人代号和所在部门 ，将部门负责人的用户名和部门编号放到临时表
insert into @UserDepidTemp 
select UserID,Depid from employee 
where UserID in (select distinct e.UserID from employee e
left join Department d on  e.DepID = d.DepID
join employee p on e.NID = p.MANAGER )

--整理部门负责人的角色ID和部门,将角色表的部门经理的角色和用户表的部门经理的部门放到临时表,标志位全部置0
insert into @UserDepidRoleTemp(RoleID,Depid,FlagID)
select RoleID ,depid ,0 from USER_ROLE u left join @UserDepidTemp b on u.UserID = b.UserID
where depid is not null
 

---开始循环角色，用户取自上面的角色代号和部门代号的临时表
SET @i=1
WHILE( @i>=1)
BEGIN
SELECT @RoleID=''
SELECT TOP 1 @RoleID = RoleID FROM @UserDepidRoleTemp WHERE FlagID=0     --顺序读取表中的第一个角色用户，即当前角色用户
SET @i=@@ROWCOUNT                                                        --受影响行数为1

select @Depid=''
select top 1 @Depid = Depid from @UserDepidRoleTemp WHERE RoleID=@RoleID    --获取当前用户角色所在部门作为父部门，以便下一行查询下级部门列表
insert into @subDepidTemp   EXEC rsp_selectTreeofsubDep @Depid              --获取下级部门结果集
insert into ROLE_DEPT (ROLEID,DepID) SELECT @Roleid,Depid from @subDepidTemp      --将当前循环表中的用户角色和查询到的下级部门放入角色部门表
IF @i<=0 GOTO Return_Lab                                                       --当i小于0时跳出循环
IF @@error=0
delete from @subDepidTemp    --千万记得要删除下级部门临时表，这个表放的是当前角色用户的下级部门
UPDATE @UserDepidRoleTemp SET FlagID=1 WHERE ID = @ID    --将用户角色和所在部门的标志位置1，下次循环将跳过该数据
Return_Lab:
END
END




GO

嗯，如上，就上面这个存储过程，现在来看也不复杂，可是我还是写了好久，好几个地方数据都拿错了，导致测试表的结果总不对。

当然现在这种批量事务一般都用其他服务端语言解决了，仍然用纯sql实现的方案比较少了，我也是赶鸭子上架硬着头皮写，总算写出来了也算完成了一件事。

    换新工作后每天就是sql，各种眼花缭乱的sql整到没脾气，存储过程虽然也能写，但是更深层次的事务管理和锁啊同步啊互斥啥的都没理解，能用就行，还需慢慢修行吧！也不知道纯sql能找到工作否。