有两张分别是tb_customer以及tb_house
表tb_customer有如下字段:
entryName
building
unit
layer
roomNumber
同时这些字段是tb_house的联合主键,现在对这两个表的houseStatus字段进行更新:
DROP PROCEDURE IF EXISTS pro_updHouseStatus ;
CREATE PROCEDURE pro_updHouseStatus(
in i_houseStatus int,
in i_entryName varchar,
in i_building varchar,
in i_unit varchar,
in i_layer varchar,
in i_roomNumber varchar
)
BEGIN
DECLARE t_error INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;
START TRANSACTION;
update customer set houseStatus = i_houseStatus
where entryName = i_entryName and building = i_building and unit = i_unit
and layer = i_layer and roomNumber = i_roomNumber;
update house set houseStatus = i_houseStatus
where entryName = i_entryName and building = i_building and unit = i_unit
and layer = i_layer and roomNumber = i_roomNumber;
IF t_error = 1 THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
END;