56,677
社区成员
发帖
与我相关
我的任务
分享
#A:id,name -- 假设A表id为自增型
#B:id,user_id,text -- 假设B表id为自增型
#temp:username,text -- 查出的临时数据表
drop procedure if exists procInsert;
DELIMITER $$
CREATE PROCEDURE procInsert()
BEGIN
DECLARE t_error INTEGER;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error = 1;
START TRANSACTION;
insert into A(name) select username from temp;
insert into B(user_id, text) select A.id,t.text from temp t inner join A on t.username = A.name;
IF t_error = 1 THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
END$$
DELIMITER ;
call procInsert();