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;