hive建Oracle镜像表,Oracle 自动生成hive建表语句

1 create or replace procedure p_ddpt_sql_init(p_table_name varchar2, --输入表名,必须大写

2 p_error_no out int, --错误编号

3 p_error_info out varchar2 --错误信息

4 )5

6 --调度平台 建表语句生成

7

8 as

9 nb_begindate date;10 nb_enddate date;11 v_count number;12

13 v_update_sql varchar(2000);14 v_temp_sql varchar(2000);15 v_detail_sql varchar(2000);16 v_out_sql varchar(2000);17

18 v_owner varchar(2000);19 v_table_name varchar(2000);20 v_tab_comments varchar(2000);21 v_column_name varchar(2000);22 v_data_type varchar(2000);23 v_col_comments varchar(2000);24 begin

25

26 p_error_no := 0;27 p_error_info := '';28

29 nb_begindate :=sysdate;30

31 v_out_sql := 'use dm_mms;'||chr(13)||

32 'drop table'||p_table_name||';'||chr(13)||

33 'create table'||p_table_name||'('||chr(13);34

35 execute immediate 'truncate table t_p_table_name_temp';36

37 insert intot_p_table_name_temp38 (owner, table_name, tab_comments, column_name, data_type, col_comments)39 selecta.owner,40 a.table_name,41 c.comments tab_comments,42 a.column_name,43 a.data_type,44 b.comments col_comments45 fromall_tab_columns a, all_col_comments b, all_tab_comments c46 where a.table_name =b.table_name47 and a.owner =b.owner48 and a.column_name =b.column_name49 and a.table_name =c.table_name50 and a.owner =c.owner51 and c.table_type = 'TABLE'

52 and a.table_name =p_table_name53 order bya.table_name;54 commit;55

56 update t_p_table_name_temp set data_type = 'string' where data_type = 'VARCHAR2';57 update t_p_table_name_temp set data_type = 'double' where data_type = 'NUMBER';58 commit;59

60 update t_p_table_name_temp set data_type = 'string' where column_name = 'YGBH';61 update t_p_table_name_temp set data_type = 'string' where column_name = 'DWBH';62 update t_p_table_name_temp set data_type = 'string' where column_name = 'FGS';63 update t_p_table_name_temp set data_type = 'string' where column_name = 'KHYF';64 update t_p_table_name_temp set data_type = 'string' where column_name = 'SFYL';65 update t_p_table_name_temp set data_type = 'string' where column_name = 'RSSJ';66 update t_p_table_name_temp set data_type = 'string' where column_name = 'YLSC';67 update t_p_table_name_temp set data_type = 'string' where column_name = 'YGZT';68 update t_p_table_name_temp set data_type = 'string' where column_name = 'SYYLYF';69 update t_p_table_name_temp set data_type = 'string' where column_name = 'YGLX';70 update t_p_table_name_temp set data_type = 'string' where column_name = 'KHBH';71 update t_p_table_name_temp set data_type = 'string' where column_name = 'CSMRZ';72 update t_p_table_name_temp set data_type = 'string' where column_name = 'CSLX';73 update t_p_table_name_temp set data_type = 'string' where column_name like '%YF';74 update t_p_table_name_temp set data_type = 'string' where column_name like '%RQ';75 update t_p_table_name_temp set data_type = 'string' where column_name like '%BH';76 commit;77

78 --v_update_sql := 'update'|| p_table_name || ' set yj=replace(yj,''chr(13)'',chr(13)) where table_name = '''||p_table_name ||'''';

79 dbms_output.put_line(v_update_sql);80

81 for c_row in (SELECT * FROMt_p_table_name_temp)82 loop83

84 v_owner :=c_row.owner;85 v_table_name :=c_row.table_name;86 v_tab_comments :=c_row.tab_comments;87 v_column_name :=c_row.column_name;88 v_data_type :=c_row.data_type;89 v_col_comments :=c_row.col_comments;90

91 if v_temp_sql is null then

92 v_temp_sql := v_column_name ||' '||v_data_type || 'comment'||'"'||v_col_comments||'"';93 v_detail_sql :=v_column_name;94 else

95 v_temp_sql := v_temp_sql ||','||chr(13) || v_column_name ||' '||v_data_type || 'comment'||'"'||v_col_comments||'"';96 v_detail_sql := v_detail_sql||',' || chr(13) ||v_column_name;97 end if;98

99 endloop;100

101 --select distinct tab_comments into v_tab_comments from t_p_table_name_temp;

102

103 v_out_sql := v_out_sql || v_temp_sql ||')'||chr(13) ||'comment'||'"'|| v_tab_comments||'"'||chr(13);104 v_detail_sql := 'select' || chr(13) || v_detail_sql || 'from'|| v_owner ||'.' ||v_table_name;105

106 v_out_sql:=v_out_sql||'partitioned by(busi_date string)'||';';107

108

109

110 nb_enddate :=sysdate;111

112 delete t_sql where table_name =p_table_name;113 insert intot_sql114 (scsj, sqlyj, table_name, mxyj)115 values

116 (nb_enddate, v_out_sql, p_table_name, v_detail_sql);117 commit;118

119 p_error_no := 1;120 p_error_info := '调度平台建表语句生成成功！';121 --p_insert_log_info('', nb_begindate, nb_enddate, '调度平台SQL语句生成', 'p_ddpt_sql_init', p_error_no, p_error_info);

122 exception123 when others then

124 p_error_no := -1;125 p_error_info := '调度平台建表语句生成失败！' ||sqlerrm;126 --p_insert_log_info('', nb_begindate, nb_enddate, '奖励38 调度平台SQL语句生成', 'p_ddpt_sql_init', p_error_no, p_error_info);

127 rollback;128 end p_ddpt_sql_init;