#### mysql/oracle/sqlserver增刪改查 #### 編寫存儲過程 CREATE PROCEDURE my_procedure2 () BEGIN DECLARE addcount INT (11) DEFAULT 0; DECLARE maxnum INT (11) DEFAULT 10; myLoop : LOOP IF addcount >= 20000000 THEN LEAVE myLoop; END IF; INSERT INTO cc ( sex, year_old, year_old_level, cell_phone_number, payment_money, store_code, pos_num, create_time, country, province, city ) SELECT t.sex, 2017 - YEAR (t.birthday) year_old, CASE WHEN (2017 - YEAR(t.birthday)) <= 15 THEN 1 WHEN (2017 - YEAR(t.birthday)) > 15 AND (2017 - YEAR(t.birthday)) <= 35 THEN 2 WHEN (2017 - YEAR(t.birthday)) > 35 AND (2017 - YEAR(t.birthday)) <= 50 THEN 3 ELSE 4 END year_old_level, u.cell_phone_number, u.payment_money, u.store_code, u.pos_num, u.create_time, t.country, a.province, a.city FROM t_user_store_consumption_info u LEFT JOIN weixini_user_info t ON u.cell_phone_number = t.mobile LEFT JOIN dd a ON a.store_code = u.store_code WHERE u.is_upload = 1 AND u.payment_money > 0 ORDER BY u.id LIMIT addcount, maxnum; COMMIT; SET addcount = addcount + maxnum; END LOOP myLoop; END;