–本脚本根据当前年，如果存在202012格式结尾的月表，会自动生产202101到202112格式结尾的年月表建表语句
–需要先创建root_table表结构
create table root_table(
owner varchar2(30),
table_name varchar2(30),
table_name_formart varchar2(30),
status varchar2(2))
declare
v_sql varchar2(2000);
err_num VARCHAR2(15);
err_msg VARCHAR2(100);
script_table clob;
script_comment clob;
script_index clob;
script_grant clob;
script_trigger clob;
script_synonyms clob;
script_synonym1 clob;
script_synonym2 clob;
v_ddl clob;
p_this_year varchar2(12) := ‘2020’;
p_next_year varchar2(12);
BEGIN
DBMS_OUTPUT.ENABLE(buffer_size => null);
dbms_metadata.set_transform_param(dbms_metadata.session_transform,
‘SQLTERMINATOR’,
true);
dbms_metadata.set_transform_param(dbms_metadata.session_transform,
‘STORAGE’,
false);
dbms_metadata.set_transform_param(dbms_metadata.session_transform,
‘TABLESPACE’,
true);
–开始构建commoth表
–生成年月，以备后续以12月份生成年月表
BEGIN
BEGIN
v_sql := ‘create table commonth as
SELECT TO_CHAR(ADD_MONTHS(DATE ‘’’ || p_this_year ||
‘-01-01’’, ROWNUM - 1), ‘‘YYYYMM’’) MONTHS
FROM DUAL
CONNECT BY ROWNUM <= MONTHS_BETWEEN(DATE ‘’’ || p_this_year ||
‘-12-01’’, DATE ‘’’ || p_this_year || ‘-01-01’’) + 1’;
– dbms_output.put_line(v_sql);
EXECUTE IMMEDIATE v_sql;
END;
EXCEPTION
WHEN OTHERS THEN
err_num := SQLCODE;
err_msg := SUBSTR(SQLERRM, 1, 100);
dbms_output.put_line(’–’ || err_num || ‘1—’ || err_msg);
END;
–结束构建commoth表
–开始构建root_table表
–生产母板表，以备后续以根据母板表生产后续年的月表
BEGIN
SELECT to_char((ADD_MONTHS(sysdate, +12)), ‘YYYY’)
into p_next_year
FROM dual;
BEGIN
v_sql := ‘truncate table root_table’;
EXECUTE IMMEDIATE v_sql;
v_sql := ‘insert into root_table select /*+ parallel(32) */
owner,
table_name,
replace(table_name, reverse(substr(reverse(table_name), 1, 6)), ‘‘YYYYMM’’) table_name_formart,’‘0’’ status
from dba_tables
where regexp_like(table_name, ‘’((202012)$)’’)’;
– dbms_output.put_line(v_sql);
EXECUTE IMMEDIATE v_sql;
commit;
END;
EXCEPTION
WHEN OTHERS THEN
err_num := SQLCODE;
err_msg := SUBSTR(SQLERRM, 1, 100);
dbms_output.put_line(’–’ || err_num || ‘2—’ || err_msg);
END;
–结束构建root_table表
–开始获取建表语句
BEGIN
BEGIN
FOR i_table in (select owner, table_name, table_name_formart
from root_table
where status = 0) loop
begin
script_table := replace(dbms_metadata.get_ddl(object_type => 'TABLE',
schema => i_table.owner,
name => i_table.table_name),
'"',
null);
--dbms_output.put_line(script_table);
EXCEPTION
WHEN OTHERS THEN
continue;
end;
--comment
begin
script_comment := replace(dbms_metadata.get_dependent_ddl(object_type => 'COMMENT',
base_object_schema => i_table.owner,
base_object_name => i_table.table_name),
'"',
null);
-- dbms_output.put_line(script_comment);
EXCEPTION
WHEN OTHERS THEN
if (ora_is_servererror(31608)) then
dbms_output.put_line('--no comment');
else
err_num := SQLCODE;
err_msg := SUBSTR(SQLERRM, 1, 100);
dbms_output.put_line('--' || err_num || '2---' || err_msg);
end if;
end;
--index
begin
script_index := replace(dbms_metadata.get_dependent_ddl(object_type => 'INDEX',
base_object_schema => i_table.owner,
base_object_name => i_table.table_name),
'"',
null);
-- dbms_output.put_line(script_index);
EXCEPTION
WHEN OTHERS THEN
if (ora_is_servererror(31608)) then
dbms_output.put_line('--no index');
else
err_num := SQLCODE;
err_msg := SUBSTR(SQLERRM, 1, 100);
dbms_output.put_line('--' || err_num || '2---' || err_msg);
end if;
end;
--grant
begin
script_grant := replace(dbms_metadata.get_dependent_ddl(object_type => 'OBJECT_GRANT',
base_object_schema => i_table.owner,
base_object_name => i_table.table_name),
'"',
null);
-- dbms_output.put_line(script_grant);
EXCEPTION
WHEN OTHERS THEN
if (ora_is_servererror(31608)) then
dbms_output.put_line('--no grant');
else
err_num := SQLCODE;
err_msg := SUBSTR(SQLERRM, 1, 100);
dbms_output.put_line('--' || err_num || '2---' || err_msg);
end if;
end;
--trigger
begin
script_trigger := replace(dbms_metadata.get_dependent_ddl(object_type => 'TRIGGER',
base_object_schema => i_table.owner,
base_object_name => i_table.table_name),
'"',
null);
-- dbms_output.put_line(script_trigger);
EXCEPTION
WHEN OTHERS THEN
if (ora_is_servererror(31608)) then
dbms_output.put_line('--no grant');
else
err_num := SQLCODE;
err_msg := SUBSTR(SQLERRM, 1, 100);
dbms_output.put_line('--' || err_num || '2---' || err_msg);
end if;
end;
begin
SELECT 'CREATE PUBLIC SYNONYM ' || T.synonym_name || ' FOR ' ||
T.table_owner || '.' || T.table_name || ';'
into script_synonym1
fROM DBA_SYNONYMS T
WHERE T.table_owner = i_table.owner
AND T.table_name = i_table.table_name
AND T.owner = 'PUBLIC';
SELECT 'CREATE SYNONYM ' || T.owner || '.' || T.synonym_name ||
' FOR ' || T.table_owner || '.' || T.table_name || ';'
into script_synonym2
fROM DBA_SYNONYMS T
WHERE T.table_owner = i_table.owner
AND T.table_name = i_table.table_name
AND T.owner <> 'PUBLIC';
-- dbms_output.put_line(script_synonym1);
-- dbms_output.put_line(script_synonym2);
EXCEPTION
WHEN OTHERS THEN
if (ora_is_servererror(31608)) then
dbms_output.put_line('--no grant');
else
err_num := SQLCODE;
err_msg := SUBSTR(SQLERRM, 1, 100);
dbms_output.put_line('--' || err_num || '2---' || err_msg);
end if;
end;
v_ddl := script_table ||chr(13)|| script_comment ||chr(13)|| script_index ||chr(13)||
script_grant ||chr(13)|| script_trigger ||chr(10)|| script_synonym1 ||chr(10)||
script_synonym2;
for i_moth in (select months from commonth) loop
dbms_output.put_line(replace(replace(v_ddl, p_this_year||'12', i_moth.months),p_this_year,p_next_year));
--dbms_output.put_line('aa');
end loop;
end loop;
END;
EXCEPTION
WHEN OTHERS THEN
err_num := SQLCODE;
err_msg := SUBSTR(SQLERRM, 1, 100);
dbms_output.put_line(err_num || ‘3—’ || err_msg);
END;
–结束获取建表语句
END;
/
