mysql 存储过程批量插入

最新推荐文章于 2025-08-18 19:53:22 发布

原创最新推荐文章于 2025-08-18 19:53:22 发布 · 151 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

mysql 专栏收录该内容

6 篇文章

订阅专栏

本文详细介绍了如何通过SQL脚本批量处理并存储房屋数据，包括房间ID、房间名称、楼层、面积等关键信息，并对状态、使用类型、租赁类型等属性进行映射和处理，确保数据的一致性和准确性。脚本采用CASE语句对状态进行条件判断，灵活转换为对应的使用类型代码，实现高效的数据入库操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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