代码实例

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_MAXfrom (        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