mysql存储过程 --游标的使用 取每行记录 (多字段)

delimiter $

create  PROCEDURE  phoneDeal()

BEGIN

     DECLARE   id  varchar (64);    -- id

     DECLARE   phone1   varchar (16);  -- phone

     DECLARE   password1   varchar (32);  -- 密码

     DECLARE   name1  varchar (64);    -- id

     -- 遍历数据结束标志

     DECLARE  done  INT  DEFAULT  FALSE ;

     -- 游标

     DECLARE  cur_account  CURSOR  FOR  select  phone, password , name  from  account_temp;

     -- 将结束标志绑定到游标

     DECLARE  CONTINUE  HANDLER  FOR  NOT  FOUND  SET  done =  TRUE ;

    

     -- 打开游标

     OPEN   cur_account;     

     -- 遍历

     read_loop: LOOP

             -- 取值 取多个字段

             FETCH   NEXT  from  cur_account  INTO  phone1,password1,name1;

             IF done  THEN

                 LEAVE read_loop;

              END  IF;

 

         -- 你自己想做的操作

         insert  into  account(id,phone, password , name ) value(UUID(),phone1,password1,CONCAT(name1, '的家长' ));

     END  LOOP;

 

 

     CLOSE  cur_account;

END  $

 

 

 

===================实例============================

BEGIN
#Routine body goes here...
DECLARE i INT DEFAULT 0;
DECLARE j INT DEFAULT 33;
DECLARE v_sourceid INT DEFAULT 0;

DECLARE schoolid bigint(20);
DECLARE schoolName VARCHAR(50);
-- endflag
DECLARE done INT DEFAULT FALSE;
-- declare cusor
DECLARE cur_schoolidt CURSOR FOR SELECT ID,SCHOOL_NAME from T_SCHOOL_DEFINED WHERE SCHOOL_NAME like '武汉%' and DELETED='N' LIMIT 170;
-- banding endflag to cusor
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
-- open cusor
OPEN cur_schoolidt;

-- 遍历
read_loop:LOOP
-- 取值
FETCH NEXT FROM cur_schoolidt INTO schoolid,schoolName;

IF done THEN
LEAVE read_loop;
END IF;

START TRANSACTION;
WHILE i<1000 DO
INSERT INTO T_MEMBER (LOGIN_NAME,NIKE_NAME,ORIGINAL_ID,MEMBER_SOURCE,SCHOOL_NAME,SEX,enrollment_year,GRADE,FIRST_NAME,UPDATOR,CREATE_DATE,UPDATE_DATE,STATUS,DELETED,SCHOOLID,MEM_CLASS,MEM_TYPE,IS_FEE,IS_CARD,joinclass_date,flag) VALUES
(CONCAT(CONCAT(j),"alad",i),CONCAT(CONCAT(j),"alad",i),1,0,schoolName,'M','2015','9',CONCAT(CONCAT(j),"alad",i),0,'2018-01-18 16:26:55','2018-01-18 16:26:55',99,'N',schoolid,1,1,0,0,'2018-01-18 16:26:55',0);

SELECT max(ID) INTO v_sourceid from T_MEMBER;
INSERT INTO T_MEMBER_IDENTITY (SOURCE_ID,SOURCE_SYSTEM,LOGIN_NAME,MOBILE_NO,PASSWORD,PASSWORD_STATE,LAST_LOGIN_TIME,CREATE_DATE,DELETED,MEM_TYPE) VALUES
(v_sourceid,1,CONCAT(CONCAT(j),"alad",i),'00000000000','96e79218965eb72c92a549dd5a330112',1,'2017-08-10 18:10:18','2017-08-07 17:06:53','N',1);
SET i=i+1;

END WHILE;
COMMIT;
SET i=0;
SET j=j+1;

END LOOP;

CLOSE cur_schoolidt;

END

转载于:https://www.cnblogs.com/AmilyWilly/p/8548275.html