oracle存储过程中使用游标做循环封装数据集

Dear 丶 2022-06-05 00:48 191阅读 0赞

需求是 双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;