hadoop学习工作总结（六）之生成hive的一个存储过程

                                                                                                                                            
declare
  -- Local variables here
  i integer;
  v_max_i number;
  v_where varchar2(4000);
  v_relation_column varchar2(30) := 'party_id';
  v_data_type varchar2(30);
 
begin

 for r in (SELECT  distinct t.tablename FROM xiao_cx_test t WHERE t.tablename  IN (
'FIN_PAC_HOW_CONTACT_INFOS',
'FIN_PAC_HOW_RELATION_INFOS',
'FIN_PAC_HOW_TRADE_INFOS',
'FIN_PAC_WHAT_CUSTOMER_VALUES',
'FIN_PAC_WHAT_PRODUCT_AMOUNTS',
'FIN_PAC_WHAT_PRODUCT_ROLES',
'FIN_PAC_WHAT_PRODUCT_TYPES',
'FIN_PAC_WHO_CUSTOMER_VALUES',
'FIN_PAC_WHO_IDENTIFIER_INFOS',
'TRA_PAC_HOW_RELATION_INFOS',
'TRA_PAC_WHAT_VEHICLE_INFO',
'TRA_PAC_WHO_IDENTIFIER_INFOS'
)) loop
         
 dbms_output.put_line(' SELECT ');
  dbms_output.put_line( '"'||r.tablename || '" ,');
 
 for j in (SELECT t.COLUMN_NAME, T.tablename,t.COLUMN_ID  FROM xiao_cx_test t WHERE T.tablename  IN (
'FIN_PAC_HOW_CONTACT_INFOS',
'FIN_PAC_HOW_RELATION_INFOS',
'FIN_PAC_HOW_TRADE_INFOS',
'FIN_PAC_WHAT_CUSTOMER_VALUES',
'FIN_PAC_WHAT_PRODUCT_AMOUNTS',
'FIN_PAC_WHAT_PRODUCT_ROLES',
'FIN_PAC_WHAT_PRODUCT_TYPES',
'FIN_PAC_WHO_CUSTOMER_VALUES',
'FIN_PAC_WHO_IDENTIFIER_INFOS',
'TRA_PAC_HOW_RELATION_INFOS',
'TRA_PAC_WHAT_VEHICLE_INFO',
'TRA_PAC_WHO_IDENTIFIER_INFOS'
)
and   t.tablename = r.tablename
order by t.tablename,to_number(t.COLUMN_ID)
)   loop
     
  select max(to_number(a.COLUMN_ID)) into  v_max_i
    from xiao_cx_test a  where a.tablename = r.tablename;

  if   j.COLUMN_ID <> v_max_i then
 
 
      dbms_output.put_line( 'sum( case when '|| j.COLUMN_NAME || ' is null then 0 when '|| j.COLUMN_NAME ||'=0 then 0 else 1 end )  as '|| j.COLUMN_NAME || ',');
  else
      dbms_output.put_line('sum( case when '|| j.COLUMN_NAME || ' is null then 0 when '|| j.COLUMN_NAME ||'=0 then 0 else 1 end ) as '|| j.COLUMN_NAME );
  end if;
 
  end loop;
  dbms_output.put_line('from  gbd_360_safe.'||r.tablename ||' where y="2014" and m="07" ;  ');
  dbms_output.put_line('');
  dbms_output.put_line('');
 
  end loop;
 
end;

 

 

 

drop table tzr_tmp_table_create;
create table tzr_tmp_table_create
(table_name           varchar2(50),
 sql_string           long);

declare
  sql_string       long;
  p_table_owner    varchar2(100);
  p_table_name     varchar2(100);
  P_COL_NUM        NUMBER;
  i                number;
  p_col_name       varchar2(50);
  p_col_type       varchar2(20);
  p_col_comment    varchar2(500);
  p_table_comment  varchar2(500);
 
begin
 delete from tzr_tmp_table_create;
  commit;
 
  for loop_table in (select t1.TABLE_NAME
                       from user_tables t1
                      where t1.TABLE_NAME in ('ODS_CIF2_LIFE_CUST',
'ODS_CIF2_LIFE_INSURED',
'ODS_CIF2_PERSON')) loop
  sql_string         :='';
  p_table_owner      := 'LCDMDATA';
  p_table_name       := loop_table.table_name;
  P_COL_NUM          := 0;
  i                  :=1;
  p_table_comment    :='';
 
  sql_string     := 'CREATE TABLE ' || P_TABLE_NAME || '(';
 
  SELECT COUNT(*)
  INTO P_COL_NUM
  FROM USER_TAB_COLUMNS T1
  WHERE T1.TABLE_NAME = p_table_name;
 
  while i <= p_col_num loop
   
        select t1.COLUMN_NAME,decode(t1.data_type,'NUMBER','DOUBLE','STRING'),replace(t2.COMMENTS,chr(10),'')
        into p_col_name,p_col_type,p_col_comment
        from user_tab_columns t1,user_col_comments t2
        where t1.TABLE_NAME = t2.TABLE_NAME
        and t1.COLUMN_NAME = t2.COLUMN_NAME
        and t1.TABLE_NAME = p_table_name
        and t1.COLUMN_ID = i;
       
        if i < p_col_num then
         
          sql_string :=  sql_string || '
  ' ||  p_col_name || ' ' || p_col_type || ' COMMENT "' || p_col_comment || '",';
        else
         
          sql_string :=  sql_string || '
  ' ||  p_col_name || ' ' || p_col_type || ' COMMENT "' || p_col_comment || '"';
        end if;
       
        i := i+1;
       
  end loop;
 
  select t.COMMENTS
  into p_table_comment
  from user_tab_comments t
  where t.TABLE_NAME = p_table_name;
 
   sql_string :=  sql_string || ')COMMENT "' || p_table_comment || '"' || '
PARTITIONED BY(pt STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ''\001'';';
 
  insert into tzr_tmp_table_create (table_name,sql_string)
  values(p_table_name,sql_string);
  commit;

end loop;
end;
/

select *
from tzr_tmp_table_create
order by table_name;