存储过程--oracle,sqlserver示例_oracle调用sqlserver的存储过程-优快云博客

本文链接：https://blog.youkuaiyun.com/hdblocal/article/details/72652223

本文提供了一个Oracle和SQL Server中实现相似业务逻辑的存储过程示例，通过两个不同数据库系统的对比，展示了如何使用游标进行数据更新的具体操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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;