[script]P_CHECK_BLACK.sql 檢查當前使用者下是否有varchar2欄位的末尾包含空格

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;

end P_CHECK_BLACK;
/