以前没有接触过游标,没有写过存储过程,不过这次需要一个存储过程,就亲自动手写了一个。不管写的怎么样,功能还是实现了,自己做个记录,也给不会的朋友一个参考
Oracle版:
CREATE OR REPLACE PROCEDURE usp_tool_ModifyReportMenuid (
p_menuid NVARCHAR2 := '',
p_reportmenuid NVARCHAR2 := '')
AS
v_menuid NVARCHAR2 (40) := '';
v_reportmenuid NVARCHAR2 (40) := '';
v_maxmenuid NUMBER := 0;
v_tempmenuid NUMBER := 0;
v_insertmenuid NVARCHAR2 (40) := '';
v_onlevel NUMBER := 0;
v_moduleid NVARCHAR2 (40) := '';
v_child_menuid NVARCHAR2 (40) := '';
v_truemenuid NVARCHAR2 (40) := '';
v_flag NUMBER := 1;
CURSOR cursor_1
IS
SELECT menuid
FROM secfunmodule
WHERE pmenuid = p_menuid;
CURSOR cursor_2
IS
SELECT menuid
FROM secfunmodule
WHERE pmenuid = p_reportmenuid;
BEGIN
v_menuid := p_menuid;
v_reportmenuid := p_reportmenuid;
OPEN cursor_1; --打开游标
BEGIN
LOOP
FETCH cursor_1
INTO v_insertmenuid;
EXIT WHEN cursor_1%NOTFOUND;
BEGIN
v_tempmenuid :=
TO_NUMBER (
'1'
|| SUBSTR (v_insertmenuid,
LENGTH (v_insertmenuid) - 2,
3));
IF v_maxmenuid < v_tempmenuid
THEN
BEGIN
v_maxmenuid := v_tempmenuid;
END;
END IF;
END;
END LOOP;
END;
IF cursor_1%ISOPEN
THEN
CLOSE cursor_1;
END IF;
v_insertmenuid := v_menuid || TO_NCHAR (SUBSTR ((v_maxmenuid+10), 2, 3));
select onlevel,moduleid into v_onlevel,v_moduleid from secfunmodule where menuid = v_menuid;
UPDATE secfunmodule
SET pmenuid = v_menuid,
menuid = v_insertmenuid,
rightid = v_insertmenuid,
itemcansee = 1,
onlevel = v_onlevel + 1,
label = v_insertmenuid,
moduleid = v_moduleid
WHERE menuid = v_reportmenuid;
OPEN cursor_2;
BEGIN
LOOP
FETCH cursor_2 INTO v_child_menuid;
EXIT WHEN cursor_2%NOTFOUND;
BEGIN
v_truemenuid :=
v_insertmenuid || SUBSTR (TO_CHAR (1000 + v_flag), 2, 3);
UPDATE secfunmodule
SET pmenuid = v_insertmenuid,
menuid = v_truemenuid,
rightid = v_truemenuid,
itemcansee = 1,
onlevel = v_onlevel + 2,
label = v_truemenuid,
moduleid = v_truemenuid
WHERE menuid = v_child_menuid;
v_flag := v_flag + 1;
END;
END LOOP;
END;
IF cursor_2%ISOPEN
THEN
CLOSE cursor_2;
END IF;
commit;
END;
SQL-Server版:
IF ( OBJECT_ID('usp_tool_ModifyReportMenuid', 'P') IS NOT NULL )
DROP PROC usp_tool_ModifyReportMenuid
go
CREATE PROC usp_tool_ModifyReportMenuid
(
@menuid VARCHAR(40) , --报表要加入的功能模块的Menuid
@reportMenuid VARCHAR(40) --报表的menuid
)
AS
IF @menuid IS NOT NULL
AND @menuid != ''
AND @reportMenuid IS NOT NULL
AND @reportMenuid != ''
BEGIN
DECLARE @maxMenuid INT ; --功能menuid的后三位最大值
DECLARE @tempMenuid INT ;
SET @maxMenuid = 0 ;
DECLARE @InsertMenuid VARCHAR(40) --功能menuid
DECLARE cursor_1 CURSOR LOCAL FORWARD_ONLY FOR --定义一个游标,遍历更新子功能
SELECT menuid
FROM secfunmodule WHERE PMENUID=@menuid ;
BEGIN
OPEN cursor_1 ;
L1:
FETCH NEXT FROM cursor_1 INTO @InsertMenuid ;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @tempMenuid = CONVERT(INT, ( '1'
+ SUBSTRING(@InsertMenuid,
LEN(@InsertMenuid)
- 2, 3) ))
IF ( @maxMenuid < @tempMenuid )
BEGIN
SET @maxMenuid = @tempMenuid ;
END
GOTO L1 ;
END ;
END
CLOSE cursor_1 ;
DEALLOCATE cursor_1 ;
SET @InsertMenuid = @menuid
+ CONVERT(VARCHAR(10), ( SUBSTRING(CONVERT(VARCHAR(10), ( @maxMenuid
+ 10 )), 2, 3) )) --要新加的报表的menuid
PRINT 'menuid:' ;
PRINT @InsertMenuid ;
DECLARE @onlevel INT , --功能层级
@moduleid VARCHAR(40)
SELECT @onlevel = ONLEVEL ,
@moduleid = moduleid
FROM secfunmodule
WHERE MENUID = @menuid ;
UPDATE secfunmodule
SET PMENUID = @menuid ,
menuid = @InsertMenuid ,
rightid = @InsertMenuid ,
itemcansee = 1 ,
onlevel = @onlevel + 1 ,
Label = @InsertMenuid ,
Moduleid = @moduleid
WHERE menuid = @reportMenuid ;
DECLARE cursor_2 CURSOR LOCAL FORWARD_ONLY FOR --定义一个游标,遍历更新子功能
SELECT menuid
FROM secfunmodule WHERE PMENUID=@reportMenuid ;
BEGIN
OPEN cursor_2 ;
DECLARE @child_menuid VARCHAR(40) ; --子功能的menuid
DECLARE @flag INT ; --定义标志位,用来计算menuid
SET @flag = 1 ;
DECLARE @TrueMenuid VARCHAR(40) ;
L2:
FETCH NEXT FROM cursor_2 INTO @child_menuid ;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TrueMenuid = @InsertMenuid
+ SUBSTRING(CONVERT(VARCHAR(10), ( 1000 + @flag )),
2, 3) ;
UPDATE secfunmodule
SET PMENUID = @InsertMenuid ,
menuid = @TrueMenuid ,
rightid = @TrueMenuid ,
itemcansee = 1 ,
onlevel = @onlevel + 2 ,
Label = @TrueMenuid ,
Moduleid = @moduleid
WHERE menuid = @child_menuid ;
SET @flag = @flag + 1 ;
GOTO L2 ;
END ;
END ;
CLOSE cursor_2 ;
DEALLOCATE cursor_2 ;
END ;
ELSE
BEGIN
PRINT '请输入正确的参数' ;
END ;
go