游标cursor与loop循环的结合使用

Mysql存储过程中的loop循环：

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存储过程必须得带括号(),Oracle语法不需要-->
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语法，循环开始前需要声明个结束标志符以便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存储过程中的loop循环：

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()
;