MyOA数据库存储过程.docx

1、MyOA数据库存储过程use MyOA-根据用户Id编号得到用户的所有可访问节点（用于框架左边页面挂树形结构）Create Proc p_QueryTreeNodeByUserId userid int as Select Distinct a.ID,a.NodeName,a.Fid,a.Url,a.ImageUrl,a.DisplayOrder From SysModels a Inner Join SysRolesModle b On a.ID = b.ModleId Inner Join SysRoles c On b.RoleId = c.ID Inner Join SysUserRo

2、les d On c.ID = d.UserId Inner Join Users e On d.UserId = e.ID Where e.ID = useridgo- exec p_QueryTreeNodeByUserId 2-根据条件查询create proc p_GetAllInfo UName varchar(50), Pwd varchar(50)as select * from Users where UserName=UName and UserPwd=Pwdgo- exec p_GetAllInfo-根据ID查询角色表create proc p_GetAllRole id

3、intas select * from SysRoles where ID=idgo- exec p_GetAllRole-根据ID修改角色表create proc p_UpdateRoleInfo Dis int, RoleName varchar(50), Remark varchar(1024), id intas Update SysRoles set DisplayOrder=Dis ,RoleName=RoleName ,Remark=Remark where ID =idgo- exec p_UpdateRoleInfo-删除角色信息(操作两个表，1、将角色模块表中的所有此角色数

4、据删除- 2、将此角色信息删除)Create Proc p_DeleteRole roleid intASBEGIN BEGIN TRANSACTION Delete SysRolesModle where RoleId = roleid Delete SysRoles where ID = roleid COMMIT TRANSACTIONEND- exec p_DeleteRole drop proc p_DeleteRole-克隆角色的存储过程create proc p_CloneRoleroleId int,order int,roleName varchar(100)asdeclar

5、e id int-向角色表中插入数据insert into SysRoles(RoleName,DisplayOrder)values(roleName,order)-查询刚插入数据的Idselect id=identity from SysRoles-定义游标克隆角色declare stuCursor cursor for select ModleId from SysRolesModle where RoleId=roleId-打开游标open stuCursor-定义变量declare pageId int-使用游标fetch stuCursor into pageIdwhile fet

6、ch_status=0begininsert into SysRolesModle(RoleId,ModleId) values(id,pageId)-移动指针fetch stuCursor into pageIdend-关闭游标close stuCursor-删除游标deallocate stuCursorgo-测试-exec p_CloneRole Drop proc p_CloneRole-显示左边树形结构的存贮过程create proc p_TreeListid intasselect top (10) ID,ImageUrl,NodeName,DisplayOrder from Sy

7、sModels where Fid=1select a.ID,NodeName,Fid,Url,ImageUrl,DisplayOrder,b.ModleId from SysModels a leftjoin SysRolesModle b on a.ID=b.ModleId and b.RoleId=id-测试-exec p_TreeList 1go-批量设置中显示右边树形列表的存储过程create proc p_RigthTreeasselect top (10) ID,ImageUrl,NodeName,DisplayOrder from SysModels where Fid=1se

8、lect ID,NodeName,Fid,Url,ImageUrl,DisplayOrder from SysModels go-参数：1.要上移或下移排序DisplayOrder编号 3.上移或下移 (1:上移 0:下移)-修改站点次序的存储过程Create proc p_RolesChangeOrder(DisplayOrder int,dirction int)ASBEGIN BEGIN TRANSACTION IF dirction =0 BEGIN UPDATE SysRoles SET DisplayOrder=CASE WHEN DisplayOrder=DisplayOrder

9、 THEN DisplayOrder+1 ELSE DisplayOrder END WHERE (DisplayOrder=DisplayOrder OR DisplayOrder=DisplayOrder+1) END ELSE BEGIN UPDATE SysRoles SET DisplayOrder=CASE WHEN DisplayOrder=DisplayOrder THEN DisplayOrder-1 ELSE DisplayOrder END WHERE (DisplayOrder=DisplayOrder OR DisplayOrder=DisplayOrder-1) E

10、ND COMMIT TRANSACTIONEND Go-编辑权限的存储过程create proc p_EditRightroleId int,strIds varchar(1000)asdelete SysRolesModle where RoleId=roleIddeclare errorSum INT -用于累加错误 set errorSum = 0 declare id varchar(20) declare start int, position int set start=1 -要截取的开始位置 set position=CHARINDEX(,strIds) -要截取的结束位置即要截

11、取的第一个id后边逗号的位置 begin transaction WHILE position0 begin set id = substring(strIds, start, position-start) -字符串截取，得到要删除的ID号-substring()截取时个参数的意思第一个要截取的字符串第二个要截取的位置(其下标从1开始)包括该位置-第三个参数要截取的个数比如subString(abc1,2)则截取到是bc. insert into SysRolesModle values(roleId,id) SET errorSum=errorSum+ERROR -累计是否有错误 set

12、start=position+1 -重新计算，要截取的开始位置 set position=CHARINDEX(, strIds, start) -重新计算，要截取的结束位置-第三个参数的意思是规定了查找,的开始位置 end /*-根据是否有错误，确定事务是提交还是撤销-*/ if(errorSum 0) begin rollback transaction print 批量添加失败，回滚事务 end else begin commit transaction print 批量添加成功，提交事务 endgo-新建角色的存储过程create proc p_NewRoleDis int,roleNa

13、me varchar(100),remark varchar(400)asinsert into SysRoles values(roleName,remark,Dis)go-批量设置权限的存储过程create proc p_BatchRole-标注是添加权限还是删除权限,0表添加,1表删除state int,-接收要删除或要添加的权限strIds varchar(1000),-接收要添加或要删除的角色权限的Idstr varchar(500)as-权限变量declare errorSum INT -用于累加错误 set errorSum = 0-角色编号变量declare roleid va

14、rchar(20) declare rolestart int, roleposition int set rolestart=1 -要截取的开始位置 set roleposition=CHARINDEX(,str) begin tran-外层循环控制角色个数 while roleposition0 begin declare id varchar(20) declare start int, position int set start=1 -要截取的开始位置 set position=CHARINDEX(,strIds) -要截取的结束位置即要截取的第一个id后边逗号的位置 set rol

15、eid = substring(str, rolestart,roleposition-rolestart) -内层循环控制要添加或要删除的权限 while position0 begin set id = substring(strIds, start,position-start) -添加权限 if(state=0) begin delete SysRolesModle where ModleId=id and RoleId=roleid insert into SysRolesModle (RoleId,ModleId) values(roleId,id) end -删除权限 else

16、begin delete SysRolesModle where ModleId=id and RoleId=roleid end SET errorSum=errorSum+ERROR -累计是否有错误 set start=position+1 -重新计算，要截取的开始位置 set position=CHARINDEX(, strIds, start) -重新计算，要截取的结束位置 end set rolestart=roleposition+1 -重新计算，要截取的开始位置 set roleposition=CHARINDEX(, str, rolestart) -重新计算，要截取的结束位

17、置 endif(errorSum 0) begin rollback transaction print 批量处理失败，回滚事务 end else begin commit transaction print 批量处理成功，提交事务 end GO-测试go-单位管理的确定按钮alter proc p_UpdateUnti name varchar(50), tele int, fax int, post int, address varchar(50), web varchar(200), email varchar(1024), bankName varchar(50), bank_num

18、int, id intas update Unti set UntiName=name,Telepone=tele,Fax=fax,Post=post,Address=address,WebService=web,Email=email,Bank=bankName,Bank_Num=bank_num where ID=id update Depart set DepartName=name where Fid = 0go - exec p_UpdateUnti ee,NULL,NULL,NULL,NULL,1 -部门管理（新建部门）create proc p_NewPart Dis int,

19、partName varchar(50), tele int, fax int, upDepart varchar(50), GongNeng varchar(1024)as insert into Depart values(Dis,partName,tele,fax,upDepart,GongNeng)go- exec p_NewPart -根据ID得到要被修改的信息create proc p_GetInfoByID id intas select * from Depart where ID=id go- exec p_GetInfoByID 1-修改Depart表create proc

20、 p_UpdateDepart id int, dis int, partName varchar(50), tele int, Fax int, Up varchar(50), GongNeng varchar(50)as update Depart set DisplayOrder=dis,DepartName=partName,Telephone=tele,Fax=Fax,UpDepart=Up,GongNeng=GongNeng where ID=idgo- exec p_UpdateDepart-部门管理（新建下级部门）create proc p_NewDownPart Dis in

21、t, fid int, partName varchar(50), tele int, fax int, upDepart varchar(50), GongNeng varchar(1024)as insert into Depart values(Dis,fid,partName,tele,fax,upDepart,GongNeng)go- exec p_NewDownPart drop proc p_NewDownPart -显示单位名称create proc p_GetUntiNameas select UntiName from Unti go- exec p_GetUntiName

22、-查询出所有的部门名称alter proc p_GetAllPartNameas select * from Depart go- exec p_GetAllPartName-查询出所有的用户alter proc p_GetAllUseras select * from Users go-根据用户的ID查出用户的信息alter proc p_GetAllUserInfo id intas select * from Users where ID = idgo-新建用户create proc p_InserUsers uName varchar(50), pwd varchar(50), rea

23、lName varchar(50), roleName varchar(50), partName varchar(50), userDis int, fanWei varchar(50), fangWen varchar(50), workType varchar(50), guangBo varchar(20), email int, ipAddress varchar(1024), remark varchar(1024), partID intas insert into Users values(uName,pwd,realName,roleName,partName,userDis,fanWei,fangWen,workType,guangBo,email,ipAddress,remark,partID) go-查询角色的名称alter proc p_GetRoleNameas Select RoleName from SysRolesgo-