存储过程--oracle,sqlserver示例

前端之家收集整理的这篇文章主要介绍了存储过程--oracle,sqlserver示例前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

oracle版本@H_301_1@

  1. create or replace procedure test_procedure_002
  2. as
  3. childTempId varchar(200) ;
  4. parentId varchar(200) ;
  5. topParentId varchar(200) ;
  6. CURSOR l_c1 is select id,menuparentset,menu_level,menuidstringset from oa_custmenu where menucodeset='contacts_menu';
  7. CURSOR l_c2 is select id,menuidstringset from oa_custmenu where menucodeset='workmanager_linkman';
  8. CURSOR l_c3 is select id,menuidstringset from oa_custmenu where menuparentset=( select id from oa_custmenu where menucodeset='workmanager_linkman' );
  9. Begin
  10. -- 更新 workmanager_linkman
  11. FOR i IN l_c2 LOOP
  12. dbms_output.put_line(i.id||' '||i.menuparentset||' '||i.menu_level);
  13. parentId := i.id ;
  14. FOR j IN l_c1 LOOP
  15. topParentId := j.id ;
  16. dbms_output.put_line( '0-' || j.id || '-' || i.id );
  17. childTempId := '0-' || j.id || '-' || i.id ;
  18. update oa_custmenu set menu_level = childTempId,menuidstringset =childTempId where id=i.id ;
  19. END LOOP;
  20. END LOOP;
  21. dbms_output.put_line('parentId-->'||parentId||';topParentId---->'||topParentId);
  22. -- 更新workmanager_linkman的子目录
  23. FOR i IN l_c3 LOOP
  24. childTempId := '0-' || topParentId || '-' || parentId || '-' || i.id ;
  25. dbms_output.put_line(childTempId);
  26. update oa_custmenu set menu_level=childTempId,menuidstringset=childTempId where id=i.id ;
  27. END LOOP ;
  28. End;




sqlserver版本@H_301_1@

  1. create proc test_procedure_002
  2. as
  3. declare @childTempId varchar(200) ;
  4. declare @parentId varchar(200) ;
  5. declare @topParentId varchar(200) ;
  6. declare @idTemp varchar(200) ;
  7. declare @menuparentsetTemp varchar(200) ;
  8. declare @menu_levelTemp varchar(200) ;
  9. declare @menuidstringsetTemp varchar(200) ;
  10. Declare l_c1 CURSOR FOR select id,menuidstringset from oa_custmenu where menucodeset='contacts_menu';
  11. Declare l_c2 CURSOR FOR select id,menuidstringset from oa_custmenu where menucodeset='workmanager_linkman';
  12. Declare l_c3 CURSOR FOR select id,menuidstringset from oa_custmenu where menuparentset=( select id from oa_custmenu where menucodeset='workmanager_linkman' );
  13. Begin
  14. -- 更新 workmanager_linkman
  15. open l_c1 ;
  16. open l_c2 ;
  17. open l_c3 ;
  18. -- 遍历游标 1
  19. fetch next from l_c2 into @idTemp,@menuparentsetTemp,@menu_levelTemp,@menuidstringsetTemp
  20. -- while (@@fetch_status=0)
  21. --begin
  22. set @parentId = @idTemp ;
  23. print '@parentId------>'+@parentId;
  24. --fetch next from l_c1 into @idTemp,@menuidstringsetTemp
  25. --end
  26. close l_c2 ;
  27. DEALLOCATE l_c2 ;
  28. -- 遍历游标 2
  29. fetch next from l_c1 into @idTemp,@menuidstringsetTemp
  30. --while (@@fetch_status=0)
  31. --begin
  32. print '22222---->'+@parentId ;
  33. set @topParentId = @idTemp ;
  34. set @childTempId = '0-' + @topParentId + '-' + @parentId ;
  35. update oa_custmenu set menu_level = @childTempId,menuidstringset =@childTempId where id=@parentId ;
  36. --fetch next from l_c2 into @idTemp,@menuidstringsetTemp ;
  37. -- end
  38. close l_c1 ;
  39. DEALLOCATE l_c1 ;
  40. -- 遍历游标 3
  41. -- 更新workmanager_linkman的子目录
  42. fetch next from l_c3 into @idTemp,@menuidstringsetTemp
  43. while(@@fetch_status=0)
  44. begin
  45. print '3333' ;
  46. set @childTempId = '0-' + @topParentId + '-' + @parentId + '-' + @idTemp ;
  47. print 'idTemp---->'+@idTemp
  48. update oa_custmenu set menu_level=@childTempId,menuidstringset=@childTempId where id=@idTemp ;
  49. fetch next from l_c3 into @idTemp,@menuidstringsetTemp
  50. end
  51. close l_c3 ;
  52. DEALLOCATE l_c3 ;
  53. End;

猜你在找的Oracle相关文章