oracle mysql 存储过程

一、Oracle

declare
  i number := 0;
   begin
   for rec in (SELECT ID
   FROM demo_tab d
   WHERE d.id = 2
  ) loop
 update demo2_tab set demo_column = 'xxx'  where id = rec.id;
  i := i + 1;
  if (mod(i, 1000) = 0) then
  commit;
 dbms_lock.sleep(1);
  end if;
  end loop;
  commit;
  end;

二、mysl

CREATE PROCEDURE  proc_demo()

BEGIN
            DECLARE i  INT DEFAULT 0;
            DECLARE done INT DEFAULT 0;
            DECLARE v_demo_code VARCHAR(100);
     DECLARE cur1 CURSOR FOR SELECT demo_code from demo_tab ;  
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; 

  （注意：当游标查询的数据量非常大时，需要如下方式加大缓存。）

    set read_buffer_size=104857600;
  set sort_buffer_size=104857600;
  set read_rnd_buffer_size=209715200;


     OPEN cur1;  
     emp_loop: LOOP  
         FETCH cur1 INTO  v_demo_code;  
                                SET autocommit=0;
                               update demo2_tab set xx_column = 'xxxx'  where demo_code = v_demo_code;
                               set i = i + 1;
                               IF mod(i,5000) = 0
                                        then commit;
                                 set i = 0;
                               END IF;
         IF done=1 THEN  
                                  commit;
             LEAVE emp_loop;  
         END IF;  
     END LOOP emp_loop;  
     commit;
     CLOSE cur1;  
END