oracle sqlserver 存储过程,存储过程--oracle,sqlserver示例

oracle版本

create or replace procedure test_procedure_002

as

childTempId varchar(200) ;

parentId varchar(200) ;

topParentId varchar(200) ;

CURSOR l_c1 is select id,menuparentset,menu_level,menuidstringset from oa_custmenu where menucodeset='contacts_menu';

CURSOR l_c2 is select id,menuparentset,menu_level,menuidstringset from oa_custmenu where menucodeset='workmanager_linkman';

CURSOR l_c3 is select id,menuparentset,menu_level,menuidstringset from oa_custmenu where menuparentset=( select id from oa_custmenu where menucodeset='workmanager_linkman' );

Begin

--　更新 workmanager_linkman

FOR i IN l_c2 LOOP

dbms_output.put_line(i.id||' '||i.menuparentset||' '||i.menu_level);

parentId := i.id ;

FOR j IN l_c1 LOOP

topParentId := j.id ;

dbms_output.put_line( '0-' || j.id || '-' || i.id );

childTempId := '0-' || j.id || '-' || i.id ;

update oa_custmenu set menu_level = childTempId , menuidstringset =childTempId where id=i.id ;

END LOOP;

END LOOP;

dbms_output.put_line('parentId-->'||parentId||';topParentId---->'||topParentId);

-- 更新workmanager_linkman的子目录

FOR i IN l_c3 LOOP

childTempId := '0-' || topParentId || '-' || parentId || '-' || i.id ;

dbms_output.put_line(childTempId);

update oa_custmenu set menu_level=childTempId ,menuidstringset=childTempId where id=i.id ;

END LOOP ;

End;

sqlserver版本

create proc test_procedure_002

as

declare @childTempId varchar(200) ;

declare @parentId varchar(200) ;

declare @topParentId varchar(200) ;

declare @idTemp varchar(200) ;

declare @menuparentsetTemp varchar(200) ;

declare @menu_levelTemp varchar(200) ;

declare @menuidstringsetTemp varchar(200) ;

Declare l_c1 CURSOR FOR select id,menuparentset,menu_level,menuidstringset from oa_custmenu where menucodeset='contacts_menu';

Declare l_c2 CURSOR FOR select id,menuparentset,menu_level,menuidstringset from oa_custmenu where menucodeset='workmanager_linkman';

Declare l_c3 CURSOR FOR select id,menuparentset,menu_level,menuidstringset from oa_custmenu where menuparentset=( select id from oa_custmenu where menucodeset='workmanager_linkman' );

Begin

--　更新 workmanager_linkman

open l_c1 ;

open l_c2 ;

open l_c3 ;

-- 遍历游标 1

fetch next from l_c2 into @idTemp,@menuparentsetTemp,@menu_levelTemp,@menuidstringsetTemp

-- while (@@fetch_status=0)

--begin

set @parentId = @idTemp ;

print '@parentId------>'+@parentId;

--fetch next from l_c1 into @idTemp,@menuparentsetTemp,@menu_levelTemp,@menuidstringsetTemp

--end

close l_c2 ;

DEALLOCATE l_c2 ;

-- 遍历游标 2

fetch next from l_c1 into @idTemp,@menuparentsetTemp,@menu_levelTemp,@menuidstringsetTemp

--while (@@fetch_status=0)

--begin

print '22222---->'+@parentId ;

set @topParentId = @idTemp ;

set @childTempId = '0-' + @topParentId + '-' + @parentId ;

update oa_custmenu set menu_level = @childTempId , menuidstringset =@childTempId where id=@parentId ;

--fetch next from l_c2 into @idTemp,@menuparentsetTemp,@menu_levelTemp,@menuidstringsetTemp ;

-- end

close l_c1 ;

DEALLOCATE l_c1 ;

-- 遍历游标 3

-- 更新workmanager_linkman的子目录

fetch next from l_c3 into @idTemp,@menuparentsetTemp,@menu_levelTemp,@menuidstringsetTemp

while(@@fetch_status=0)

begin

print '3333' ;

set @childTempId = '0-' + @topParentId + '-' + @parentId + '-' + @idTemp ;

print 'idTemp---->'+@idTemp

update oa_custmenu set menu_level=@childTempId ,menuidstringset=@childTempId where id=@idTemp ;

fetch next from l_c3 into @idTemp,@menuparentsetTemp,@menu_levelTemp,@menuidstringsetTemp

end

close l_c3 ;

DEALLOCATE l_c3 ;

End;