create or replace procedure P_CHECK_BLACK
is
/*用途：检查当前用户下是否有varchar2字段的末尾包含空格，
将结果插入到表contain_black中；
将这些字段中的空格删除
*/
type typ_tabnm is table of varchar2(100);
type typ_colnm is table of varchar2(100);
lis_tabnm typ_tabnm;--used to store table name
lis_colnm typ_colnm;--used to store column name
n_count number;--judge whether there are records demanding to be trim
begin
--对于常用的数据类型，我们只需要将varchar2的字段进行trim
select table_name,column_name
bulk collect into lis_tabnm,lis_colnm
from user_tab_cols where exists
(select 1 from table_log where table_log.table_name=user_tab_cols.table_name)
and user_tab_cols.data_type in ('VARCHAR2');
--execute immediate 'create table contain_black(tabnm varchar2(100),colnm varchar2(100))';
--将需要trim的字段表名、列名插入到表contain_black中
for i in lis_tabnm.first..lis_colnm.last loop
execute immediate 'insert into contain_black select '
||''''||lis_tabnm(i)||''''||','||''''||lis_colnm(i)||''''
||' from '||lis_tabnm(i)||' where '||lis_colnm(i)||'<>trim('||lis_colnm(i)||') and rownum=1';
end loop;
commit;
select count(1) into n_count from contain_black;
if n_count>0 then
select c.tabnm,c.colnm bulk collect into lis_tabnm,lis_colnm
from contain_black c;
for i in (select * from contain_black) loop
execute immediate
'update '||i.tabnm||' set '||i.colnm||'=trim('||i.colnm||')';
commit;
end loop;
/*for i in lis_tabnm.first..lis_colnm.last loop
execute immediate
'update '||lis_tabnm(i)||' set '||lis_colnm(i)||'=trim('||lis_colnm(i)||')';
commit;
end loop;*/
end if;
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26451536/viewspace-760070/，如需转载，请注明出处，否则将追究法律责任。