plsql代码拆分电子邮件和moblie号码

Question

我需要代码来分割存储在单个列中的电子邮件和手机号码，并存储它两个收集变量发送短信和电子邮件。请注意，电子邮件和手机号码将被合并并存储在逗号分隔的单个列/变量中

Answer 1

已更新的代码显示邮件和手机号码。

DECLARE 
    l_str VARCHAR(150) := '[email protected], [email protected], [phone], [phone]'; 
    l_pattern_email VARCHAR(500) := '[a-zA-Z0-9._%-][email protected][a-zA-Z0-9._%-]+\.[a-zA-Z]{2,4}'; 
    l_pattern_phone VARCHAR(500) := '+[0-9]\([0-9]{3}\)([0-9]{3})-[0-9]{2}-[0-9]{2}'; 
    TYPE tp_emails_col_typ IS TABLE OF VARCHAR(100); 
    TYPE tp_phone_col_typ IS TABLE OF VARCHAR(100); 
    l_emails_col tp_emails_col_typ; 
    l_phone_col tp_phone_col_typ; 
BEGIN 
    SELECT REGEXP_SUBSTR(l_str, l_pattern_email, 1, LEVEL) BULK COLLECT INTO l_emails_col 
     FROM (
      SELECT l_str FROM dual 
    ) 
     CONNECT BY LEVEL <= REGEXP_COUNT(l_str, l_pattern_email); 

    IF l_emails_col.count > 0 THEN 
     FOR idx IN l_emails_col.first..l_emails_col.last LOOP 
      dbms_output.put_line(l_emails_col(idx)); 
     END LOOP; 
    END IF; 

    SELECT REGEXP_SUBSTR(l_str, l_pattern_phone, 1, LEVEL) BULK COLLECT INTO l_phone_col 
    FROM (
     SELECT l_str FROM dual 
    ) 
    CONNECT BY LEVEL <= REGEXP_COUNT(l_str, l_pattern_phone); 

    IF l_phone_col.count > 0 THEN 
    FOR idx IN l_phone_col.first..l_phone_col.last LOOP 
     dbms_output.put_line(l_phone_col(idx)); 
    END LOOP; 
    END IF; 

END;