存储过程

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;

++++++++++++++++++++++++++++最新++++++++++++++++++++--------

CREATE OR REPLACE PROCEDURE hgx (v varchar)
as
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;
DBMS_OUTPUT.ENABLE (buffer_size=>null);
for loop_table in (select t1.TABLE_NAME
from user_tables t1
where t1.TABLE_NAME in ('T_MERCHANT_BASIC_INFO',
'T_BUSINESS_CIRCLE',
'T_MERCHANT_SHOP',
'T_PAY_ORDER',
'T_MERCHANT')) loop
sql_string :='';
p_table_owner := 'IPAY_CORE_TEST';
p_table_name := loop_table.table_name;
P_COL_NUM := 0;
i :=1;
p_table_comment :='';

sql_string := 'CREATE EXTERNAL TABLE ' || P_TABLE_NAME || '(';

dbms_output.put_line(sql_string);

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(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 || '",';
sql_string := p_col_name || ' ' || p_col_type || ' COMMENT ''' || p_col_comment || ''',';
dbms_output.put_line(sql_string);
else
-- sql_string := sql_string || '
-- ' || p_col_name || ' ' || p_col_type || ' COMMENT "' || p_col_comment || '"';
sql_string := p_col_name || ' ' || p_col_type || ' COMMENT ''' || p_col_comment || '''';
dbms_output.put_line(sql_string);
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'';';

sql_string := ')COMMENT ''' || p_table_comment || '''' ;
-- PARTITIONED BY(pt STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ''\001'';';
dbms_output.put_line(sql_string);
-- dbms_output.put_line('PARTITIONED BY (' || 'YEAR STRING, MONTH STRING, DAY STRING' || ')');
dbms_output.put_line('ROW FORMAT DELIMITED FIELDS TERMINATED BY ''\001''');
dbms_output.put_line('STORED AS TEXTFILE');
sql_string := 'LOCATION ' || '''/ODS/ODS_DB/' || p_table_name || ''';';
dbms_output.put_line(sql_string );

-- insert into tzr_tmp_table_create (table_name,sql_string)
-- values(p_table_name,sql_string);
--commit;
dbms_output.put_line('--------上面这张表是：' || p_table_comment || '--------------------------');
dbms_output.put_line('');
end loop;

end;