Oracle批量导出Hive建表语句

1、Oracle批量导出Hive建表语句脚本

create or replace procedure p_tmp_gjl_ddl
is

/*create table dsjgs.tmp_gjl_table_list --手工导入需建表清单
(
o_id number,
owner varchar2(4000),
table_name varchar2(4000)
);

create table dsjgs.tmp_gjl_table_ddl --保存生成的ddl语句
(
o_id number,
owner varchar2(4000),
table_name varchar2(4000),
ddl_con varchar2(4000)
);

create table dsjgs.tmp_gjl --临时表保存字段相关信息
(
  column_name varchar2(4000),
  data_type   varchar2(4000),
  data_length number,
  data_scale number,
  comments    varchar2(4000),
  column_id   number
);*/

v_sql varchar2(4000);
v_owner varchar2(4000);
v_table_name varchar2(4000);
v_column_name varchar2(4000);
v_data_type varchar2(4000);
v_data_length varchar2(4000);
v_data_scale varchar2(4000);
v_comments varchar2(4000);
v_col_num number;
v_ddl varchar2(4000);
v_table_comments varchar2(4000);
begin
  for v_tab_list in (select o_id,owner,table_name from dsjgs.tmp_gjl_table_list 
    where o_id  not in (198,208,212,213,224,225,306,353,441,442,1290,1294,1295,
    1296,1298,1299,1336,1344,1345,1347,1348,1639,1648,1649,1659,1760,1761,2332,2390,2408,2432,2433,2571,2968) order by o_id) loop
    v_owner := v_tab_list.owner;
    v_table_name := v_tab_list.table_name;

    --获取表注释
    v_sql := 'select replace(replace(replace(comments,chr(9),''''),chr(10),''''),chr(13),'''') from all_tab_comments where owner='''||v_owner||''' and table_name='''||v_table_name||'''' ;
    execute immediate v_sql into v_table_comments;

    --将字段信息插入临时表
    execute immediate 'truncate table dsjgs.tmp_gjl';
    v_sql := 'insert into dsjgs.tmp_gjl
    select t1.column_name,t1.data_type,t1.data_length,t1.data_scale,t2.comments,t1.column_id
      from
      (select column_name,data_type,data_length,column_id,data_scale from all_tab_columns where owner='''||v_owner||''' and table_name='''||v_table_name||''' ) t1
      left join (select column_name,comments from all_col_comments where owner='''||v_owner||''' and table_name='''||v_table_name||''') t2
      on t1.column_name=t2.column_name
    ';
    execute immediate v_sql;
    commit;

    --获取表的字段数，决定循环次数
    v_sql := 'select max(column_id) from dsjgs.tmp_gjl';
    execute immediate v_sql into v_col_num;

    --拼接建表语句
    v_ddl := 'create table smzj_stg.stg_'||v_owner||'_'||v_table_name||'(';
    for i in 1..v_col_num loop
      --字段名称
      v_sql := 'select column_name from dsjgs.tmp_gjl where column_id='||i ;
      execute immediate v_sql into v_column_name;
      if v_column_name like '1-x%' then
        v_column_name := replace(v_column_name,'1-x','x1_x') ;--字段首字母不能为数字，不能包含-
      elsif v_column_name in ('MORE','DATE','TIMESTAMP','UPDATE','DESCRIBE','RANGE') then--关键字转换
        v_column_name := '`'||v_column_name||'`';
      else
        v_column_name := v_column_name;
      end if;
      --字段类型
      v_sql := 'select data_type from dsjgs.tmp_gjl where column_id='||i ;
      execute immediate v_sql into v_data_type;
      if v_data_type = 'DATE' then
        v_data_type := 'timestamp';
        elsif v_data_type like '%TIMESTAMP%' then
        v_data_type := 'timestamp';
        elsif v_data_type IN ('VARCHAR2','NVARCHAR2','CHAR','NCHAR') then
        v_data_type := 'varchar';
        elsif v_data_type IN ('CLOB','NCLOB','BLOB','NBLOB') then
        v_data_type := 'string';
        elsif v_data_type IN ('NUMBER','FLOAT','DOUBLE') then
        v_data_type := 'decimal';
      end if;
      --字段长度
      v_sql := 'select data_length from dsjgs.tmp_gjl where column_id='||i ;
      execute immediate v_sql into v_data_length;
      --字段精度
      v_sql := 'select data_scale from dsjgs.tmp_gjl where column_id='||i ;
      execute immediate v_sql into v_data_scale;
      if v_data_scale is null or length(v_data_scale)=0 then
        v_data_scale := 0;
      end if;
      --字段注释
      v_sql := 'select comments from dsjgs.tmp_gjl where column_id='||i ;
      execute immediate v_sql into v_comments;
      
      if v_data_type in ('timestamp','string') then--不拼接字段长度
        v_ddl := v_ddl||v_column_name||' '||v_data_type||' comment '''||v_comments||'''';
      elsif v_data_type in ('decimal') then--需拼接精度
        v_ddl := v_ddl||v_column_name||' '||v_data_type||'('||v_data_length||','||v_data_scale||') comment '''||v_comments||'''';
      else
        v_ddl := v_ddl||v_column_name||' '||v_data_type||'('||v_data_length||') comment '''||v_comments||'''';
      end if;
      
      -- 拼接,
      if i<>v_col_num then
        v_ddl := v_ddl||',';
      end if;
    --dbms_output.put_line(lower(v_ddl));
      
    end loop;
      v_ddl := v_ddl||') comment '''||v_table_comments||''''||' row format delimited fields terminated by ''|'' NULL defined as '''';';
      v_ddl := lower(v_ddl);
    --dbms_output.put_line(v_ddl);

    v_sql := 'delete from dsjgs.tmp_gjl_table_ddl where o_id='||v_tab_list.o_id;
    execute immediate v_sql;
    commit;

    v_sql := 'insert into dsjgs.tmp_gjl_table_ddl
    select '||v_tab_list.o_id||','''||v_tab_list.owner||''','''||v_tab_list.table_name||''','''||replace(v_ddl,'''','''''')||''' from dual';
    --dbms_output.put_line(v_sql);
    execute immediate v_sql;
    commit;

  end loop;
  
  exception 
    when others then
    dbms_output.put_line(sqlerrm);
end;