Java/SQL找出oracle数据库有空格的列

1.java方式

String table_sql = "select table_name from user_tables";//所有用户表 
List<String> table_list = getSession().createSQLQuery(table_sql).list();
for (int index = 0; index < table_list.size(); index++) {
   String tab = table_list.get(index);
   System.out.println("表名 "+tab);
   //表的所有列及数据类型
   String column_sql = "select column_name,data_type,data_length from user_tab_columns where table_name=upper('"+tab+"')";
   List<Object[]> column_list = getSession().createSQLQuery(column_sql).list();
   for (int j = 0; j < column_list.size(); j++) {
    String column = (String)column_list.get(j)[0];//列名
    //String dataType = (String)column_list.get(j)[1];//数据类型
    //查询是否有空格
    String checkColumn_sql = "select count(rownum) from "+tab+" where "+column+" like ' %' or "+column+" like '% ' and rownum<10 ";
    BigDecimal count = (BigDecimal)getSession().createSQLQuery(checkColumn_sql).uniqueResult();
    Integer num = count.intValue();
    if(num > 0){
     //System.out.println("列名 "+column+" 数据类型 "+dataType+" 含空格数量 "+num);
     System.out.println("update "+tab+" set "+column+" = trim("+column+") where "+column+" like ' %' or "+column+" like '% ';");
    }
 }
 System.out.println("======================================");

2.sql方式

declare
v_fid varchar2(1000);--用来存在表名的变量
cursor c_cur is select table_name from user_tables;--获得所有的用户表名（如果表多或数据量大可以加where条件）
cursor c_tab(c_cur varchar2) is select column_name from user_tab_columns where table_name=c_cur;--获得对应表的所有列名
v_column user_tab_columns.column_name%type;
v_sql varchar2(1000);
v_count varchar2(100);
begin   
  open c_cur; 
     loop 
       fetch c_cur into v_fid; 
       exit when c_cur%notfound; 
       dbms_output.put_line('========');
       dbms_output.put_line('表名:'||v_fid);
       
       open c_tab(v_fid);
         loop
           fetch c_tab into v_column;
           exit when c_tab%notfound;
             
             v_sql:='select count(rownum) from '||v_fid||' where '||v_column||' like '' %'' or '||v_column||' like ''% '' and rownum<10 ';
             execute immediate v_sql into v_count;

             if v_count > 0 then
              begin 
               dbms_output.put_line(v_column);--输出列名   
              end;
            end if;  
         end loop;
       close c_tab;
       
     end loop; 
   close c_cur; 
end;