电话号码处理,附错误类型(无效字符，plsql该sql中不允许使用函数)

--电话号码处理

PROCEDURE PRO_DM_MOBILE_SZKF(OUT_CODE OUT VARCHAR2(4000),OUT_MSG OUT VARCHAR2(4000)) IS

  S_SQL VARCHAR2(4000);

  T_SQL VARCHAR2(4000);

BEGIN

  UPDATE C_RESIDENTCUSTOMER A SET A.TEL1=TRIM(REGEXP_SUBSTR(A.TEL||','||A.MOBILEPHONE, '[^,]+', 1, 1)),

                                A.TEL2=TRIM(REGEXP_SUBSTR(A.TEL||','||A.MOBILEPHONE, '[^,]+', 1, 2)),

                                A.TEL3=TRIM(REGEXP_SUBSTR(A.TEL||','||A.MOBILEPHONE, '[^,]+', 1, 3)),

                                A.TEL4=TRIM(REGEXP_SUBSTR(A.TEL||','||A.MOBILEPHONE, '[^,]+', 1, 4)),

                                A.TEL5=TRIM(REGEXP_SUBSTR(A.TEL||','||A.MOBILEPHONE, '[^,]+', 1, 5)),

                                A.TEL6=TRIM(REGEXP_SUBSTR(A.TEL||','||A.MOBILEPHONE, '[^,]+', 1, 6)),

                                A.TEL7=TRIM(REGEXP_SUBSTR(A.TEL||','||A.MOBILEPHONE, '[^,]+', 1, 7)),

                                A.TEL8=TRIM(REGEXP_SUBSTR(A.TEL||','||A.MOBILEPHONE, '[^,]+', 1, 8)),

                                A.TEL9=TRIM(REGEXP_SUBSTR(A.TEL||','||A.MOBILEPHONE, '[^,]+', 1, 9)),

                                A.TEL10=TRIM(REGEXP_SUBSTR(A.TEL||','||A.MOBILEPHONE, '[^,]+', 1, 10)),

                                A.TEL11=TRIM(REGEXP_SUBSTR(A.TEL||','||A.MOBILEPHONE, '[^,]+', 1, 11)),

                                A.TEL12=TRIM(REGEXP_SUBSTR(A.TEL||','||A.MOBILEPHONE, '[^,]+', 1, 12)),

                                A.TEL13=TRIM(REGEXP_SUBSTR(A.TEL||','||A.MOBILEPHONE, '[^,]+', 1, 13)),

                                A.TEL14=TRIM(REGEXP_SUBSTR(A.TEL||','||A.MOBILEPHONE, '[^,]+', 1, 14)),

                                A.TEL15=TRIM(REGEXP_SUBSTR(A.TEL||','||A.MOBILEPHONE, '[^,]+', 1, 15));

COMMIT;

  --筛选移动电话

  UPDATE C_RESIDENTCUSTOMER SET 移动电话 = NULL;

  FOR I IN 1 .. 15 LOOP

    S_SQL := 'UPDATE C_RESIDENTCUSTOMER SET 移动电话=DECODE(IS_MOBILE(TEL' || I ||

             '),1,TEL' || I || ',NULL) WHERE 移动电话 IS NULL';

    /*

   DBMS_OUTPUT.PUT_LINE(S_SQL);

    */

    EXECUTE IMMEDIATE S_SQL;

  END LOOP;

  COMMIT;

  --去除和移动电话相同的tel字段,去除字段长度小于等于1的电话号码

  FOR I IN 1 .. 15 LOOP

    S_SQL := 'UPDATE C_RESIDENTCUSTOMER SETTEL' || I || '=NULL WHERE TEL' || I ||

             '=移动电话 AND TEL' || I || ' IS NOT NULL';

    /*

   DBMS_OUTPUT.PUT_LINE(S_SQL);

    */

    T_SQL:='UPDATE C_RESIDENTCUSTOMER SETTEL'||I||'=NULL WHERE LENGTH(TEL'||I||')

    <=1';

    EXECUTE IMMEDIATE S_SQL;

    EXECUTE IMMEDIATE T_SQL;

  END LOOP;

  COMMIT;

  --移动有内容行到优先字段

  FOR I IN 1 .. 14 LOOP

    S_SQL := 'UPDATE C_RESIDENTCUSTOMER SETTEL' || TO_CHAR(I) || '=TEL' ||

             TO_CHAR(I + 1) || ',TEL' || TO_CHAR(I + 1) ||

             '=NULL WHERE TEL' || TO_CHAR(I) || ' IS NULL AND TEL' ||

             TO_CHAR(I + 1) || ' IS NOT NULL';

    EXECUTE IMMEDIATE S_SQL;

  END LOOP;

  COMMIT;

  FOR I IN 14 .. 1 LOOP

    S_SQL := 'UPDATE C_RESIDENTCUSTOMER SETTEL' || TO_CHAR(I) || '=TEL' ||

             TO_CHAR(I + 1) || ',TEL' || TO_CHAR(I + 1) ||

             '=NULL WHERE TEL' || TO_CHAR(I) || ' IS NULL AND TEL' ||

             TO_CHAR(I + 1) || ' IS NOT NULL';

    EXECUTE IMMEDIATE S_SQL;

  END LOOP;

  COMMIT;

END PRO_DM_MOBILE_SZKF;

错误类型1，无效字符

DECLARE
  S_SQL VARCHAR2(4000);
BEGIN
  S_SQL := 'DELETE FROM t;';
  EXECUTE IMMEDIATE S_SQL;
END;

拼接的语句中不可带有分号

;号作为sql语句结束符只在sqlplus中有用，在plsql中必须去掉。

错误类型2，

错误：PLS-00231: 函数'IS_MOBILE' 不能在 SQL 中使用

行：887

文本：WHEN IS_MOBILE(TRIM(A.MOBILEPHONE)) =1 THEN

在包中使用函数需要在包头中有声明。