Oracle刷建表语句

代码实例

select c.table_name,
       case when c.column_id=c.COLUMN_ID_MIN 
            then 'create external table '||u.USERNAME||'_'||c.table_name||' ( ' 
            else ',' 
       end 
       ||c.sqltxt||
       case when c.column_id=c.COLUMN_ID_MAX 
            then ') row format DELIMITED FIELDS terminated by ''\001'' stored as textfile location ''ZZZZZZZ/' || 
               c.table_name || 
               ''';' 
            else '' 
       end ,
       c.column_id,
       c.COLUMN_ID_MIN,
       c.COLUMN_ID_MAX
from (        
select                 table_name,
                       column_name || ' ' ||
                       case data_type
                       when 'NUMBER' then '    decimal(' || data_length || ',' || nvl(data_scale,0) || ') '
                       when 'FLOAT' then '    decimal(' || data_length || ',' || nvl(data_scale,0) || ') '
                       when 'VARCHAR2' then '    string '
                       when 'DATE' then '    string '
                       when 'INTEGER' then '    decimal(38,0)'
                       when 'CHAR' then '    string '
                       end sqlTxt
                       ,COLUMN_ID
                       ,min(COLUMN_ID)over(partition by table_name) as COLUMN_ID_MIN
                       ,max(COLUMN_ID)over(partition by table_name) as COLUMN_ID_MAX
                from   user_tab_columns 
                --where  table_name='EMP'
                --order  by COLUMN_ID asc     
                )c 
left join user_users u on 1=1               
order by c.table_name,c.COLUMN_ID  asc