CREATE OR REPLACE procedure S2ERPDEMO.Proc_Base_RolePower(
SequenceBase in int,
SequenceShift in int,
RoleID in int,
Flag in int,
DealerID in int,
DealTime in VARCHAR2
)
AS
--映射游标
TYPE refCursor IS REF CURSOR;
userCursor refCursor;
menuuserCursor refCursor;
sourceCursor refCursor;
strSql varchar2(5000);
--临时整型变量
iUserID INTEGER;
iMenuUserID INTEGER;
iPurviewState INTEGER;
iNum INTEGER;
iCount INTEGER;
MenuUserID INTEGER;
MenuID INTEGER;
PurviewState INTEGER;
PowerFlag INTEGER;
iminp INTEGER;
imino INTEGER;
imaxm INTEGER;
PowerCode varchar2(500);
BEGIN
IF RoleID > 0 THEN
OPEN userCursor FOR
select "User_ID" from "Base_RoleUser","Base_UserInfo"
where "User_ID" = "RoleUser_UserID" and "RoleUser_RoleID" = RoleID and "RoleUser_State" = 1 and "User_State" =1 and "User_PowerType"!=3
group by "User_ID";
LOOP
FETCH userCursor INTO iUserID;
EXIT WHEN userCursor % NOTFOUND;
IF iUserID IS NOT NULL THEN
--删除原来的权限的指定岗位表和其他限制表
OPEN menuuserCursor FOR
select "MenuUser_ID","MenuUser_PurviewState" from "Base_MenuUser" where "MenuUser_UserID" = iUserID and "MenuUser_PowerCode" in (select "PurviewPowerCode_Content" from "Base_PurviewPowerCode" where 1=1);
LOOP
FETCH menuuserCursor INTO iMenuUserID,iPurviewState;
EXIT WHEN menuuserCursor % NOTFOUND;
IF iMenuUserID IS NOT NULL THEN
delete from "Base_MenuUserPosition" where "MenuUP_MenuUserID" = iMenuUserID AND iPurviewState=2;
delete from "Base_MenuUserExtCond" where "MUE_MenuUserID" = iMenuUserID AND iPurviewState=2;
END IF;
END LOOP;
CLOSE menuuserCursor;
iNum := 0;
IF Flag=1 THEN
select count("RoleUser_ID") into iNum from "Base_RoleUser","Base_UserInfo" where "User_ID" = "RoleUser_UserID" AND "User_ID"= iUserID and "RoleUser_State" = 1 and "User_State" =1;
END IF;
--如果Flag=0 或者 该人员存在多角色，则执行以下代码
IF Flag=0 OR iNum>=2 THEN
delete from "Base_MenuUser" where "MenuUser_UserID" = iUserID AND "MenuUser_PurviewState"=2 And "MenuUser_PowerCode" in (select "PurviewPowerCode_Content" from "Base_PurviewPowerCode" where 1=1);
--将角色权限分配给人员（不生成人员指定岗位记录）
--插入特殊资源限制,先判断是否有特殊资源指定
select count("ResMenuR_ID") into iCount from "Base_ResMenuRole" where "ResMenuR_MenuRoleID" in (select "MenuRole_ID" from "Base_MenuRole" where "MenuRole_RoleID" = RoleID);
if iCount > 0 then
strSql := 'select min("MenuRole_PositionFlag"),min("MenuRole_OperateState"), max("MenuRole_OperateID"),"MenuRole_MenuID","MenuRole_PurviewState","MenuRole_PowerFlag","MenuRole_PowerCode"
from "Base_MenuRole" where "MenuRole_RoleID" in (select "RoleUser_RoleID" from "Base_RoleUser","Base_UserInfo"
where "User_ID" = "RoleUser_UserID" and "User_ID" = ' || iUserID ||' and "RoleUser_State" = 1 and "User_State" =1
group by "RoleUser_RoleID") and "MenuRole_State" = 1 and "MenuRole_PowerCode" in (select "PurviewPowerCode_Content" from "Base_PurviewPowerCode" where 1=1)
group by "MenuRole_MenuID","MenuRole_PurviewState","MenuRole_PowerFlag","MenuRole_PowerCode"';
open sourceCursor for strSql;
Loop
FETCH sourceCursor INTO iminp,imino,imaxm,MenuID,PurviewState,PowerFlag,PowerCode;
EXIT WHEN sourceCursor % NOTFOUND;
MenuUserID := GetNextSeq('Base_MenuUser',SequenceBase,SequenceShift);
insert into "Base_MenuUser"("MenuUser_ID","MenuUser_PositionFlag","MenuUser_OperateState","MenuUser_OperateID","MenuUser_MenuID","MenuUser_PurviewState","MenuUser_PowerFlag","MenuUser_PowerCode","MenuUser_UserID","MenuUser_State","MenuUser_DealerID","MenuUser_DealTime")
values(MenuUserID,iminp,imino,imaxm,MenuID,PurviewState,PowerFlag,PowerCode,iUserID,1,DealerID,TO_TIMESTAMP(DealTime,'YYYY-MM-DD HH24:MI:SS'));
--插入特殊资源限制
insert into "Base_ResMenuUser" ("ResMenuUser_ID","ResMenuUser_MenuUserID","ResMenuUser_MenuID","ResMenuUser_OperateID","ResMenuUser_ResGroupCode","ResMenuUser_ResGroupName"
,"ResMenuUser_ResTable","ResMenuUser_ResField","ResMenuUser_ResFieldName","ResMenuUser_ResFieldValue")
select GetNextSeq('Base_ResMenuUser',SequenceBase,SequenceShift),MenuUserID,"ResMenuR_MenuID","ResMenuR_OperateID","ResMenuR_ResGroupCode","ResMenuR_ResGroupName"
,"ResMenuR_ResTable","ResMenuR_ResField","ResMenuR_ResFieldName","ResMenuR_ResFieldValue" from "Base_ResMenuRole" where "ResMenuR_MenuRoleID" in (select "MenuRole_ID" from "Base_MenuRole" where "MenuRole_RoleID" = RoleID and
"MenuRole_MenuID"=MenuID and "MenuRole_PurviewState"=PurviewState and "MenuRole_PowerFlag"=PowerFlag and "MenuRole_PowerCode"=PowerCode);
END LOOP;
CLOSE sourceCursor;
else
insert into "Base_MenuUser"("MenuUser_ID","MenuUser_PositionFlag","MenuUser_OperateState","MenuUser_OperateID","MenuUser_MenuID","MenuUser_PurviewState","MenuUser_PowerFlag","MenuUser_PowerCode","MenuUser_UserID","MenuUser_State","MenuUser_DealerID","MenuUser_DealTime")
select GetNextSeq('Base_MenuUser',SequenceBase,SequenceShift),minp,mino,maxm,"MenuRole_MenuID","MenuRole_PurviewState","MenuRole_PowerFlag","MenuRole_PowerCode" ,iUserID,1,DealerID,TO_TIMESTAMP(DealTime,'YYYY-MM-DD HH24:MI:SS')
from (
select min("MenuRole_PositionFlag") minp,min("MenuRole_OperateState") mino, max("MenuRole_OperateID") maxm,"MenuRole_MenuID","MenuRole_PurviewState","MenuRole_PowerFlag","MenuRole_PowerCode"
from "Base_MenuRole"
where "MenuRole_RoleID" in
(select "RoleUser_RoleID" from "Base_RoleUser","Base_UserInfo"
where "User_ID" = "RoleUser_UserID" and "User_ID" = iUserID and "RoleUser_State" = 1 and "User_State" =1
group by "RoleUser_RoleID")
and "MenuRole_State" = 1
and "MenuRole_PowerCode" in (select "PurviewPowerCode_Content" from "Base_PurviewPowerCode" where 1=1)
group by "MenuRole_MenuID","MenuRole_PurviewState","MenuRole_PowerFlag","MenuRole_PowerCode"
);
end if;
END IF;
--生成人员其他限制记录
insert into "Base_MenuUserExtCond"("MUE_ID","MUE_ExtCondID","MUE_MenuUserID")
select GetNextSeq('Base_MenuUserExtCond',SequenceBase,SequenceShift),"MRE_ExtCondID","MenuUser_ID"
from (
select "MRE_ExtCondID","MenuUser_ID" from "Base_MenuRole","Base_MenuRoleExtCond","Base_MenuUser"
where "MenuUser_PowerCode" = "MenuRole_PowerCode"
and "MenuUser_UserID" = iUserID
and "MenuUser_ID" not in (select "MUE_MenuUserID" from "Base_MenuUserExtCond")--该人不存在对应的岗位记录才添加
and "MenuUser_ID" not in (select "MenuUser_ID" from "Base_MenuUser" where "MenuUser_PurviewState"=1)--该人不存在该菜单操作的超级权限
and "MenuRole_ID" = "MRE_MenuRoleID"
and "MenuRole_State" = 1
and "MenuUser_State" = 1
and "MenuRole_RoleID" in
(select "RoleUser_RoleID" from "Base_RoleUser","Base_UserInfo"
where "User_ID" = "RoleUser_UserID" and "User_ID" = iUserID and "RoleUser_State" = 1 and "User_State" =1
group by "RoleUser_RoleID")
group by "MRE_ExtCondID","MenuUser_ID"
);
--删除iUserID重复的权限
IF iUserID IS NOT NULL THEN
Proc_Base_RolePower_DelDup(iUserID);
END IF;
END IF;
END LOOP;
CLOSE userCursor;
end if;
END;
/
@SequenceBase int,
@SequenceShift int,
@RoleID int,
@Flag int,
@DealerID int,
@DealTime NVARCHAR(20)
AS DECLARE @RETURN_CODE INT = 1
DECLARE @iUserID INT = 0
DECLARE @iMenuUserID INT = 0BEGIN TRY
--userCursor:
DECLARE userCursor CURSOR FOR
select User_ID from Base_RoleUser,Base_UserInfo
where User_ID = RoleUser_UserID and RoleUser_RoleID = RoleID and RoleUser_State = 1 and User_State =1 and User_PowerType!=3
group by User_ID
OPEN userCursor
FETCH NEXT FROM userCursor INTO @iUserID
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF iUserID IS NOT NULL
BEGIN
--operation of other cursors
END
FETCH NEXT FROM userCursor INTO @iUserID
END
CLOSE userCursor
DEALLOCATE userCursor
SET @RETURN_CODE = 0 --succeed
RETURN @RETURN_CODE
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SET @RETURN_CODE = 1 --failed
RETURN @RETURN_CODE
END CATCHGO
1.变量定义前加@
2.每行后面不用加;
3.IF 条件 （不用加THEN）
BEGIN （BEGIN和END中间写满足IF条件时执行的语句）
END
4.变量赋值：
SET @VARIABLE = 1;
5.把SELECT语句里的双引号去掉，没啥用。
6.CURSOR，要FETCH两次，你看我的例子里就知道了。
你先弄吧，我得下班了：）
--operation of other cursors
END
这里报end语法错误啊