需求是 雙11活動結束後根據活動id 給參與的使用者發京東E卡券,每個使用者無論參與多少次 隻發一個。
這時就存在一個查到很多個使用者符合條件 要一一發短信的結果集了。
create or replace procedure "PRO_SEND_SMS_ECARD" IS
no_result exception;
-- 定義遊标
cursor c_acc
is
SELECT DISTINCT c.phone FROM ( -- 得到參與過活動的使用者手機号
SELECT * FROM sky_user_order100
UNION ALL
SELECT * FROM sky_user_order101
UNION ALL
SELECT * FROM sky_user_order102
UNION ALL
SELECT * FROM sky_user_order103
UNION ALL
SELECT * FROM sky_user_order104
UNION ALL
SELECT * FROM sky_user_order105
UNION ALL
SELECT * FROM sky_user_order106
UNION ALL
SELECT * FROM sky_user_order107
UNION ALL
SELECT * FROM sky_user_order108
UNION ALL
SELECT * FROM sky_user_order109) a,sky_shopping b,sky_user c
WHERE a.shoping_id = b.shopping_id
AND a.user_id = c.user_id AND b.online_activity_id = '20160704' -- 存儲過程中的遊标無法接受外來參數 ??
AND c.phone NOT IN (SELECT phone FROM sky_temp_11ac)
AND a.flow_status IN(5,1,2,3) AND a.buy_time IS NOT NULL;
v_acc VARCHAR(30);
v_E_id VARCHAR(255);
msg VARCHAR2(255);
BEGIN
open c_acc;
FETCH c_acc into v_acc;
while c_acc %found loop
INSERT INTO sky_temp_11ac VALUES(v_acc); -- 把發送過短信的手機号添加入臨時表,不再發送
commit; -- 每次 insert update delete 後 要commit一下
SELECT ID INTO v_E_id FROM sky_jdecard WHERE status = 0 AND ROWNUM =1; --得到e卡中沒有被發出的第一個id
msg := '恭喜您'|| v_acc || '獲得卡号為' || v_E_id || '的京東E卡一張'; -- 拼裝消息
insert into sky_sms_message -- 寫入短信表,有一個job會自動掃描并發送短信
(message_id, sms_content, create_time, status, user_id, sms_type, mobile, is_mo)
select S_SKY_SMSMESAGE.NEXTVAL,msg,
sysdate, 0,a.user_id, 0, v_acc, 0 FROM dual,sky_user a WHERE a.phone = v_acc;
COMMIT;
update sky_jdecard set status = 1 where ID = v_E_id; -- 将被發放出去的京東e卡置為已用
commit;
FETCH c_acc into v_acc;
end loop;
close c_acc;
END PRO_SEND_SMS_ECARD;