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

来源：互联网发布：caxa画图软件编辑：程序博客网时间：2024/06/14 04:08

--电话号码处理

PROCEDURE PRO_DM_MOBILE_SZKF(OUT_CODEOUT VARCHAR2(4000),OUT_MSGOUT 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 .. 15LOOP

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 .. 15LOOP

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 .. 14LOOP

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 .. 1LOOP

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

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

0 0