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
分享到:
评论