Mysql存儲(chǔ)過(guò)程中的loop循環(huán)：

drop table if exists hpbaselayout;
create table hpbaselayout(
         id int not null,
    layoutcode varchar(1000) not null,
    layoutimage varchar(1000) not null
)
;
drop table if exists pagelayout;
create table pagelayout(
         id int not null,
    layoutcode varchar(1000) not null,
    layoutimage varchar(1000) not null
)
;

DROP procedure IF EXISTS p_update_hplayoutcode;
DELIMITER //  
<!--Mysql存儲(chǔ)過(guò)程必須得帶括號(hào)(),Oracle語(yǔ)法不需要-->
create  procedure p_update_hplayoutcode()
begin  
DECLARE s_id int;
DECLARE s_layoutcode varchar(1000);
DECLARE s_layoutimage varchar(1000);
DECLARE v_sql varchar(1000);  
declare b int default 0 ;  
DECLARE t_cur CURSOR for select id,layoutcode,layoutimage from hpbaselayout;
set @num=(select count(1) from hpbaselayout);
OPEN t_cur;
<!--Mysql loop語(yǔ)法，循環(huán)開(kāi)始前需要聲明個(gè)結(jié)束標(biāo)志符以便leave-->
   loop_label:loop
    fetch  t_cur into s_id,s_layoutcode,s_layoutimage;
    update pagelayout set layoutcode=s_layoutcode,layoutimage=s_layoutimage where  id = s_id; 
    set b=b+1;
    IF b=@num THEN
      leave loop_label;
    end if;
  end loop;
  close t_cur;
  commit;
end;
// 
DELIMITER ;

call p_update_hplayoutcode()
;

Oracle存儲(chǔ)過(guò)程中的loop循環(huán)：

create or replace procedure p_update_hplayoutcode
as 
type  ref_cursor  is ref cursor;
t_cur ref_cursor;
s_id hpbaselayout.id%type;
s_layoutcode hpbaselayout.layoutcode%type;
s_layoutimage hpbaselayout.layoutimage%type;
v_sql varchar2(1000);  
begin  
  v_sql := 'select id,layoutcode,layoutimage from hpbaselayout';
  open t_cur for v_sql;
  loop
    fetch  t_cur into s_id,s_layoutcode,s_layoutimage;
    update pagelayout set layoutcode=s_layoutcode,layoutimage=s_layoutimage where  id = s_id; 
    exit when  t_cur %notfound;
  end loop;
  close t_cur;
  commit;
end;
;
call p_update_hplayoutcode()
;