create or replace procedure PROC_test
is
--Description:删除字段中的指定字符(回车chr（13）、换行chr（10）)
--By LiChao
--Date:2016-03-01
colname
varchar(
20);
--列名
cnt
number;
--包含换行符的列的行数
v_sql
varchar(
2000);
--动态SQL变量
begin
--读取表中的列
for col
in (
select column_name
from user_tab_columns
where table_name
= 'TEMP') loop
colname :
= col.column_name;
--替换换行符chr(10)
v_sql :
= 'select count(1) from temp where instr(' || colname
||
',chr(10))>0 ';
EXECUTE IMMEDIATE V_SQL
into cnt;
if cnt
> 0 then
v_sql :
= 'update temp set ' || colname
|| '=trim(replace(' || colname
||
',chr(10),''''))' || 'where instr(' || colname
||
',chr(10))>0 ';
EXECUTE IMMEDIATE V_SQL;
commit;
end if;
--替换回车符chr(13)
v_sql :
= 'select count(1) from temp where instr(' || colname
||
',chr(13))>0 ';
EXECUTE IMMEDIATE V_SQL
into cnt;
if cnt
> 0 then
v_sql :
= 'update temp set ' || colname
|| '=trim(replace(' || colname
||
',chr(13),''''))' || 'where instr(' || colname
||
',chr(13))>0 ';
EXECUTE IMMEDIATE V_SQL;
commit;
end if;
--替换'|' chr(124) 为'*' chr（42）
v_sql :
= 'select count(1) from temp where instr(' || colname
||
',chr(124))>0 ';
EXECUTE IMMEDIATE V_SQL
into cnt;
if cnt
> 0 then
v_sql :
= 'update temp set ' || colname
|| '=replace(' || colname
||
',chr(124),chr(42))' || 'where instr(' || colname
||
',chr(124))>0 ';
EXECUTE IMMEDIATE V_SQL;
commit;
end if;
end loop;
end PROC_test;
/
转载请注明原文地址: https://www.6miu.com/read-1408.html
