mysql 存储过程批量插入

CREATE PROCEDURE `Proc_ExtRmData`()
BEGIN

  -- 需要定义接收游标数据的变量

  DECLARE v_BiotopeID Varchar(36);
  DECLARE v_BuildingID Varchar(36);
  DECLARE v_UnitID Varchar(36);
  DECLARE v_UnitNo int(1);
  DECLARE v_HouseID Varchar(36);
  DECLARE v_RoomID Varchar(40);
  DECLARE v_RoomName Varchar(36);
  DECLARE v_FloorNo int(1);
  DECLARE v_FloorArea int(2);
  DECLARE v_InsideArea int(2);
  DECLARE v_ChargeArea int(2);
  DECLARE v_HouseType Varchar(16);
  DECLARE v_Direction Varchar(8);
  DECLARE v_HouseholdCount int(1);
  DECLARE v_Balance FLOAT(8,2);
  DECLARE v_FeeTypeID Varchar(36);
  DECLARE v_FeeType Varchar(36);
  DECLARE v_HouseKeeperID Varchar(36);
  DECLARE v_PaFlag int(1);
  DECLARE v_State Varchar(8);
  DECLARE v_UseType Varchar(8);
  DECLARE v_RentType Varchar(8);
  DECLARE v_LiveFlag int(1);
  DECLARE v_UseTypeCode int(1);
  DECLARE v_RentTypeCode int(1);

  -- 遍历数据结束标志
  DECLARE done INT DEFAULT FALSE;

  -- 游标
  DECLARE cur CURSOR FOR
  SELECT 
    b.BiotopeID,b.BuildingID,u.BuildingUnitID,u.UnitNo,r.RoomID,r.RoomNo,r.Layer,
    r.AreaBuild,r.AreaUse,r.AreaFee,r.HouseType,r.TowardTo,r.PeopleNumber,
    r.Money,r.FeeTypeID,r.FeeType,r.WorkerID,r.IsCheckManager,r.State,r.UseType,r.RentType
  From Etl_Biotope as a, Etl_Building as b, Etl_Unit as u, tblRoom as r
  where (a.BioID='LE34F235') and (a.BiotopeID=b.BiotopeID) and (b.BuildingID=u.BuildingID) and (u.UnitID=r.Unit);

  -- 将结束标志绑定到游标
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  -- 打开游标
  OPEN cur;
 
  -- 开始循环
  read_loop: LOOP
    -- 提取游标里的数据，这里只有一个，多个的话也一样;
    FETCH cur INTO v_BiotopeID,v_BuildingID,v_UnitID,v_UnitNo,v_RoomID,v_RoomName,v_FloorNo,
    v_FloorArea,v_InsideArea,v_ChargeArea,v_HouseType,v_Direction,v_HouseholdCount,
    v_Balance,v_FeeTypeID,v_FeeType,v_HouseKeeperID,v_PaFlag,v_State,v_UseType,v_RentType;
 
    -- 声明结束的时候
    IF done THEN
      LEAVE read_loop;
    END IF;
    -- 这里做你想做的循环的事件
    set v_HouseID = lower(substr(v_RoomID,2,36));

    CASE  v_State 
       WHEN '登记' THEN set v_LiveFlag = 0;
       WHEN '未住' THEN set v_LiveFlag = 1;
       WHEN '已住' THEN set v_LiveFlag = 2;
       WHEN '已租' THEN set v_LiveFlag = 3;
       ELSE set v_LiveFlag = 1; 
    END CASE; 

    IF (v_LiveFlag = 0) or (v_LiveFlag = 1) or (v_UseType='临建')
    THEN set v_UseTypeCode=0;
    ELSEIF (v_UseType='住宅')
    THEN set v_UseTypeCode=1;
    ELSE set v_UseTypeCode=2;
    END IF;

    INSERT INTO Etl_House
    (BiotopeID,BuildingID,UnitID,UnitNo,HouseID,RoomID,RoomName,FloorNo,
    FloorArea,InsideArea,ChargeArea,HouseType,Direction,HouseholdCount,
    Balance,FeeTypeID,FeeType,HouseKeeperID,PaFlag,LiveFlag,UseType)
    VALUES (v_BiotopeID,v_BuildingID,v_UnitID,v_UnitNo,v_HouseID,v_RoomID,v_RoomName,v_FloorNo,
    v_FloorArea,v_InsideArea,v_ChargeArea,v_HouseType,v_Direction,v_HouseholdCount,
    v_Balance,v_FeeTypeID,v_FeeType,v_HouseKeeperID,v_PaFlag,v_LiveFlag,v_UseTypeCode);

  END LOOP;
  -- 关闭游标
  CLOSE cur;

END