一个使用正则表达式的例子

CREATE OR REPLACE PROCEDURE proc_mmss_writer_ser
(
 i_desttermid IN VARCHAR2, --目的号码或者号码串，用数据库中英文逗号分隔，如:'15812356894,13456789658,13536925874'
 i_content    IN VARCHAR2, --发送内容
 i_sign       IN VARCHAR2, --
 i_pn         IN NUMBER --发送端口号10657050009998
) IS
 /*
    TODO: wner="zhqh" created="2010/12/9"
    text=" 串号帮助信息发送，写到sms_send3中"
    */
 v_pn               VARCHAR2(40) := '10657050009998';
 v_sms_send_id      NUMBER(10);
 v_desttermid       VARCHAR2(120);
 v_needreply        NUMBER(1) := 1;
 v_msgformat        NUMBER(2) := 15;
 v_feetype          CHAR(2) := '01';
 v_feeusertype      CHAR(1) := '3';
 v_t_ainserviceid   CHAR(2) := 'BZ';
 v_free_service_id  CHAR(5) := '-XXMF';
 v_err_code         NUMBER;
 v_err_txt          VARCHAR2(300);
 v_sernum           INTEGER := 10; --每次串号的号码数 ，要修改每次串号的号码数，只要将这个变量进行修改就可以了。
 v_destmobile       VARCHAR2(11);
 v_flag             INTEGER := 0;
 v_activity_send_id NUMBER(11); --写activity表的时候用到的id，串号的时候。
 v_content_num      INTEGER := 0;
 v_percontent_num   INTEGER := 300; --每条短信长度
BEGIN
 --将号码分成v_sernum个一组，循环处理
 --将内容按照指定的字数拆分成多条分别发送
 FOR v_content_num IN 1 .. ceil(length(i_content) / v_percontent_num)
 LOOP
  --将号码按照指定的个数拆成多次串号发送
  FOR v_desttermid IN (SELECT wm_concat(rm) destermid,
         length(regexp_replace(',' ||
                wm_concat(rm),
                '[^,]+')) send_num
          FROM (WITH a AS (SELECT ',' || i_desttermid rm
              FROM dual)
          SELECT regexp_substr(rm,
                '[^,]+',
                1,
                rownum) rm,
              trunc((rownum - 1) / v_sernum) rn
            FROM a
          CONNECT BY rownum <=
               (length(regexp_replace(rm,
                    '[^,]+'))))
           GROUP BY rn
        )
  LOOP
   SELECT s_sms_send.nextval INTO v_sms_send_id FROM dual;
   INSERT INTO sms_send3
    (id, msg_id, port, serviceid, needreply, msgformat, feetype, feecode, attime, srctermid, desctermid, msg_content, feeusertype, receive_flag, succ_flag, msgmode, feeterminalid, send_num)
   VALUES
    (v_sms_send_id, 0, v_pn, v_free_service_id, v_needreply, v_msgformat, v_feetype, 0, SYSDATE, v_pn, v_desttermid.destermid, substr(i_content,
       (v_content_num - 1) *
       v_percontent_num + 1,
       v_percontent_num), v_feeusertype, 0, 0, 0, substr(v_desttermid.destermid,
       1,
       11), v_desttermid.send_num);
   v_flag := 0; --用来判断
   --dbms_output.put_line('1：' || v_desttermid.destermid || '-' ||v_sms_send_id);
   --插入sms_sent_activity和sms_sent_content表需要一条一条的插入，将串号的号码分开多次插入
   FOR v_destmobile IN (SELECT rm
           FROM ((WITH a AS (SELECT ',' ||
               v_desttermid.destermid rm
                FROM dual)
           SELECT regexp_substr(rm,
                 '[^,]+',
                 1,
                 rownum) rm
             FROM a
           CONNECT BY rownum <=
                (length(regexp_replace(rm,
                     '[^,]+')))))
         )
   LOOP
    IF (v_flag = 0)
    THEN
     v_activity_send_id := v_sms_send_id;
     v_flag             := 1;
    ELSE
     SELECT s_sms_send.nextval
       INTO v_activity_send_id
       FROM dual;
    END IF;
    --dbms_output.put_line('2：' || v_destmobile.rm || '-' || v_activity_send_id);
    INSERT INTO sms_sent_activity
     (id, msg_id, ainserviceid, serviceid, attime, srctermid, desctermid, feeterminalid, status, src_mobile, src_userid, src_orgid, unit_id, src_usertype)
    VALUES
     (v_activity_send_id, 0, v_t_ainserviceid, v_free_service_id, SYSDATE, v_pn, v_destmobile.rm, v_destmobile.rm, 3, 0, 0, 0, 0, 0);
    INSERT INTO sms_sent_content
     (id, msg_id, msgcontent, attime)
    VALUES
     (v_activity_send_id, 0, substr(i_content,
        (v_content_num - 1) *
        v_percontent_num + 1,
        v_percontent_num), SYSDATE);
    MERGE INTO mms.mmss_internal_test_mobile@mms_website a
    USING (SELECT v_destmobile.rm mobile, SYSDATE senttime
       FROM dual) b
    ON (a.mobile = b.mobile)
    WHEN MATCHED THEN
     UPDATE SET a.last_sent_time = b.senttime
    WHEN NOT MATCHED THEN
     INSERT VALUES (b.mobile, b.senttime);
   END LOOP;
  END LOOP;
 END LOOP;
 COMMIT;
EXCEPTION
 WHEN OTHERS THEN
  ROLLBACK; --记录存储过程错误日志并提交
  v_err_code := SQLCODE;
  v_err_txt  := substr('EDU.PROC_MMSS_WRITER_SER:' ||
        substr(SQLERRM, 1, 200) ||
        dbms_utility.format_error_backtrace,
        1,
        300);
  --dbms_output.put_line(v_err_txt);
  INSERT INTO edu.proc_err_logs
   (code, message, info)
  VALUES
   (v_err_code, v_err_txt, 'EXCEPTION');
  --添加短信通知
  COMMIT;
END;
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25296295/viewspace-691230/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25296295/viewspace-691230/