|
DELIMITER // DROP PROCEDURE IF EXISTS update_hit// CREATE PROCEDURE update_hit() BEGIN /* 定義變量一 */ DECLARE _done int default 0; DECLARE a1 int; DECLARE a4 datetime; DECLARE a2 VARCHAR(200) character set utf8; DECLARE a3 Bigint(32); DECLARE description VARCHAR(200) character set utf8; /* 定義光標(biāo) */ DECLARE _Cur CURSOR FOR SELECT product_id,trophy_name,user_id,hit_time FROM hit_trophy_tab ; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _done = 1;#錯(cuò)誤定義,，標(biāo)記循環(huán)結(jié)束 /* 打開(kāi)光標(biāo) */ OPEN _Cur; /* 循環(huán)執(zhí)行 */ REPEAT FETCH _Cur INTO a1, a2, a3, a4; IF NOT _done THEN set description=REPLACE("參與了門(mén)店抽獎(jiǎng)活動(dòng)，并獲得獎(jiǎng)品【%s】",'%s',a2); INSERT INTO merchant_event(`merchant_id`,`user_mac`,`subtype_id`,`description`,`added_at`) VALUES(a1,a3,4,description,unix_timestamp(a4)); END IF; UNTIL _done END REPEAT; #當(dāng)_done=1時(shí)退出被循 /*關(guān)閉光標(biāo)*/ CLOSE _Cur; END// |
|
|
來(lái)自： 個(gè)人資料8912 > 《未命名》
