MyOA数据库存储过程.docx
《MyOA数据库存储过程.docx》由会员分享,可在线阅读,更多相关《MyOA数据库存储过程.docx(17页珍藏版)》请在冰豆网上搜索。
![MyOA数据库存储过程.docx](https://file1.bdocx.com/fileroot1/2023-6/6/f167db57-4dc3-4ce7-9796-e1c2e36e3af2/f167db57-4dc3-4ce7-9796-e1c2e36e3af21.gif)
MyOA数据库存储过程
useMyOA
----------------------------------------------根据用户Id编号得到用户的所有可访问节点(用于框架左边页面挂树形结构)
CreateProcp_QueryTreeNodeByUserId
@useridint
as
SelectDistincta.ID,a.NodeName,a.Fid,a.Url,a.ImageUrl,a.DisplayOrderFromSysModelsa
InnerJoinSysRolesModlebOna.ID=b.ModleId
InnerJoinSysRolescOnb.RoleId=c.ID
InnerJoinSysUserRolesdOnc.ID=d.UserId
InnerJoinUserseOnd.UserId=e.ID
Wheree.ID=@userid
go
--execp_QueryTreeNodeByUserId2
-------------------------------------------------------------------------根据条件查询
createprocp_GetAllInfo
@UNamevarchar(50),
@Pwdvarchar(50)
as
select*fromUserswhereUserName=@UNameandUserPwd=@Pwd
go
--execp_GetAllInfo
--------------------------------------------------------------------------根据ID查询角色表
createprocp_GetAllRole
@idint
as
select*fromSysRoleswhereID=@id
go
--execp_GetAllRole
--------------------------------------------------------------------------------根据ID修改角色表
createprocp_UpdateRoleInfo
@Disint,
@RoleNamevarchar(50),
@Remarkvarchar(1024),
@idint
as
UpdateSysRolessetDisplayOrder=@Dis,RoleName=@RoleName,Remark=@RemarkwhereID=@id
go
--execp_UpdateRoleInfo
--------------------------------------------删除角色信息(操作两个表,1、将角色模块表中的所有此角色数据删除
-------------------------------------------2、将此角色信息删除)
CreateProcp_DeleteRole
@roleidint
AS
BEGIN
BEGINTRANSACTION
DeleteSysRolesModlewhereRoleId=@roleid
DeleteSysRoleswhereID=@roleid
COMMITTRANSACTION
END
--execp_DeleteRoledropprocp_DeleteRole
----------------------------------------------------------------------克隆角色的存储过程
createprocp_CloneRole
@roleIdint,
@orderint,
@roleNamevarchar(100)
as
declare@idint
--向角色表中插入数据
insertintoSysRoles(RoleName,DisplayOrder)values(@roleName,@order)
--查询刚插入数据的Id
select@id=@@identityfromSysRoles
--定义游标克隆角色
declarestuCursorcursorforselectModleIdfromSysRolesModlewhereRoleId=@roleId
---打开游标
openstuCursor
---定义变量
declare@pageIdint
---使用游标
fetchstuCursorinto@pageId
while@@fetch_status=0
begin
insertintoSysRolesModle(RoleId,ModleId)values(@id,@pageId)
--移动指针
fetchstuCursorinto@pageId
end
--关闭游标
closestuCursor
--删除游标
deallocatestuCursor
go
--测试
--execp_CloneRoleDropprocp_CloneRole
----------------------------------------------------------------------------------显示左边树形结构的存贮过程
createprocp_TreeList
@idint
as
selecttop(10)ID,ImageUrl,NodeName,DisplayOrderfromSysModelswhereFid=1
selecta.ID,NodeName,Fid,Url,ImageUrl,DisplayOrder,b.ModleIdfromSysModelsaleft
joinSysRolesModlebona.ID=b.ModleIdandb.RoleId=@id
--测试
--execp_TreeList1
go
-----------------------------------------------------------------------------批量设置中显示右边树形列表的存储过程
createprocp_RigthTree
as
selecttop(10)ID,ImageUrl,NodeName,DisplayOrderfromSysModelswhereFid=1
selectID,NodeName,Fid,Url,ImageUrl,DisplayOrderfromSysModels
go
---------------------------------------------参数:
1.要上移或下移排序DisplayOrder编号3.上移或下移(1:
上移0:
下移)
-------------------------------------------------修改站点次序的存储过程
Createprocp_RolesChangeOrder(@DisplayOrderint,@dirctionint)
AS
BEGIN
BEGINTRANSACTION
IF@dirction=0
BEGIN
UPDATESysRolesSETDisplayOrder=CASE
WHENDisplayOrder=@DisplayOrder
THEN@DisplayOrder+1
ELSE@DisplayOrder
END
WHERE(DisplayOrder=@DisplayOrderORDisplayOrder=@DisplayOrder+1)
END
ELSE
BEGIN
UPDATESysRolesSETDisplayOrder=CASE
WHENDisplayOrder=@DisplayOrder
THEN@DisplayOrder-1
ELSE@DisplayOrder
END
WHERE(DisplayOrder=@DisplayOrderORDisplayOrder=@DisplayOrder-1)
END
COMMITTRANSACTION
END
Go
---------------------------------------------------------------------------------编辑权限的存储过程
createprocp_EditRight
@roleIdint,
@strIdsvarchar(1000)
as
deleteSysRolesModlewhereRoleId=@roleId
declare@errorSumINT--用于累加错误
set@errorSum=0
declare@idvarchar(20)
declare@startint,@positionint
set@start=1--要截取的开始位置
set@position=CHARINDEX(',',@strIds)--要截取的结束位置即要截取的第一个id后边逗号的位置
begintransaction
WHILE@position>0
begin
set@id=substring(@strIds,@start,@position-@start)--字符串截取,得到要删除的ID号
--substring()截取时个参数的意思第一个要截取的字符串第二个要截取的位置(其下标从1开始)包括该位置
--第三个参数要截取的个数比如subString('abc'1,2)则截取到是bc.
insertintoSysRolesModlevalues(@roleId,@id)
SET@errorSum=@errorSum+@@ERROR--累计是否有错误
set@start=@position+1--重新计算,要截取的开始位置
set@position=CHARINDEX(',',@strIds,@start)--重新计算,要截取的结束位置
--第三个参数的意思是规定了查找","的开始位置
end
/*--根据是否有错误,确定事务是提交还是撤销---*/
if(@errorSum<>0)
begin
rollbacktransaction
print'批量添加失败,回滚事务'
end
else
begin
committransaction
print'批量添加成功,提交事务'
end
go
--------------------------------------------------------------------------------------新建角色的存储过程
createprocp_NewRole
@Disint,
@roleNamevarchar(100),
@remarkvarchar(400)
as
insertintoSysRolesvalues(@roleName,@remark,@Dis)
go
----------------------------------------------------------------------------------------批量设置权限的存储过程
createprocp_BatchRole
--标注是添加权限还是删除权限,0表添加,1表删除
@stateint,
--接收要删除或要添加的权限
@strIdsvarchar(1000),
--接收要添加或要删除的角色权限的Id
@strvarchar(500)
as
--权限变量
declare@errorSumINT--用于累加错误
set@errorSum=0
--角色编号变量
declare@roleidvarchar(20)
declare@rolestartint,@rolepositionint
set@rolestart=1--要截取的开始位置
set@roleposition=CHARINDEX(',',@str)
begintran
--外层循环控制角色个数
while@roleposition>0
begin
declare@idvarchar(20)
declare@startint,@positionint
set@start=1--要截取的开始位置
set@position=CHARINDEX(',',@strIds)--要截取的结束位置即要截取的第一个id后边逗号的位置
set@roleid=substring(@str,@rolestart,@roleposition-@rolestart)
--内层循环控制要添加或要删除的权限
while@position>0
begin
set@id=substring(@strIds,@start,@position-@start)
--添加权限
if(@state=0)
begin
deleteSysRolesModlewhereModleId=@idandRoleId=@roleid
insertintoSysRolesModle(RoleId,ModleId)values(@roleId,@id)
end
--删除权限
else
begin
deleteSysRolesModlewhereModleId=@idandRoleId=@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)--重新计算,要截取的结束位置
end
if(@errorSum<>0)
begin
rollbacktransaction
print'批量处理失败,回滚事务'
end
else
begin
committransaction
print'批量处理成功,提交事务'
end
GO
--测试
go
-----------------------------------------------------------单位管理的确定按钮
alterprocp_UpdateUnti
@namevarchar(50),
@teleint,
@faxint,
@postint,
@addressvarchar(50),
@webvarchar(200),
@emailvarchar(1024),
@bankNamevarchar(50),
@bank_numint,
@idint
as
updateUntisetUntiName=@name,Telepone=@tele,Fax=@fax,Post=@post,Address=@address,WebService=@web,Email=@email,Bank=@bankName,Bank_Num=@bank_numwhereID=@id
updateDepartsetDepartName=@namewhereFid=0
go
--execp_UpdateUnti'ee',NULL,NULL,NULL,'','','','',NULL,1
------------------------------------------------------------------------部门管理(新建部门)
createprocp_NewPart
@Disint,
@partNamevarchar(50),
@teleint,
@faxint,
@upDepartvarchar(50),
@GongNengvarchar(1024)
as
insertintoDepartvalues(@Dis,@partName,@tele,@fax,@upDepart,@GongNeng)
go
--execp_NewPart
----------------------------------------------------------------------------------根据ID得到要被修改的信息
createprocp_GetInfoByID
@idint
as
select*fromDepartwhereID=@id
go
--execp_GetInfoByID1
-----------------------------------------------------------------------------------------修改Depart表
createprocp_UpdateDepart
@idint,
@disint,
@partNamevarchar(50),
@teleint,
@Faxint,
@Upvarchar(50),
@GongNengvarchar(50)
as
updateDepartsetDisplayOrder=@dis,DepartName=@partName,Telephone=@tele,Fax=@Fax,UpDepart=@Up,GongNeng=@GongNengwhereID=@id
go
--execp_UpdateDepart
------------------------------------------------------------------------部门管理(新建下级部门)
createprocp_NewDownPart
@Disint,
@fidint,
@partNamevarchar(50),
@teleint,
@faxint,
@upDepartvarchar(50),
@GongNengvarchar(1024)
as
insertintoDepartvalues(@Dis,@fid,@partName,@tele,@fax,@upDepart,@GongNeng)
go
--execp_NewDownPartdropprocp_NewDownPart
-----------------------------------------------------------显示单位名称
createprocp_GetUntiName
as
selectUntiNamefromUnti
go
--execp_GetUntiName
---------------------------------------------------------------查询出所有的部门名称
alterprocp_GetAllPartName
as
select*fromDepart
go
--execp_GetAllPartName
---------------------------------------------------------------------查询出所有的用户
alterprocp_GetAllUser
as
select*fromUsers
go
--------------------------------------------------------------------------根据用户的ID查出用户的信息
alterprocp_GetAllUserInfo
@idint
as
select*fromUserswhereID=@id
go
----------------------------------------------------------------------------新建用户
createprocp_InserUsers
@uNamevarchar(50),
@pwdvarchar(50),
@realNamevarchar(50),
@roleNamevarchar(50),
@partNamevarchar(50),
@userDisint,
@fanWeivarchar(50),
@fangWenvarchar(50),
@workTypevarchar(50),
@guangBovarchar(20),
@emailint,
@ipAddressvarchar(1024),
@remarkvarchar(1024),
@partIDint
as
insertintoUsersvalues(@uName,@pwd,@realName,@roleName,@partName,@userDis,@fanWei,@fangWen,@workType,@guangBo,@email,@ipAddress,@remark,@partID)
go
-------------------------------------------------------------------------------------查询角色的名称
alterprocp_GetRoleName
as
SelectRoleNamefromSysRoles
go
-------------------