oracle版本@H_301_1@
- 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,menuidstringset from oa_custmenu where menucodeset='workmanager_linkman';
- CURSOR l_c3 is select id,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;
- 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,menuidstringset from oa_custmenu where menucodeset='contacts_menu';
- Declare l_c2 CURSOR FOR select id,menuidstringset from oa_custmenu where menucodeset='workmanager_linkman';
- Declare l_c3 CURSOR FOR select id,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,@menuidstringsetTemp
- --end
- close l_c2 ;
- DEALLOCATE l_c2 ;
- -- 遍历游标 2
- fetch next from l_c1 into @idTemp,@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,@menuidstringsetTemp ;
- -- end
- close l_c1 ;
- DEALLOCATE l_c1 ;
- -- 遍历游标 3
- -- 更新workmanager_linkman的子目录
- fetch next from l_c3 into @idTemp,@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,@menuidstringsetTemp
- end
- close l_c3 ;
- DEALLOCATE l_c3 ;
- End;