テーブルをリプレイスするスクリプトを生成するスクリプト
var tablename varchar2(32)
var yyyymmdd varchar2(8)
var scriptdir varchar2(512)
execute :tablename := '&1';
execute :yyyymmdd := '&2';
execute :scriptdir := '&3';
select statement from(
select 0 as no, '-- ■■■ テーブル名( ' || :TABLENAME || ' )' as statement from dual
union all select 0.1 as no, 'SELECT TO_CHAR(SYSDATE,''YYYY/MM/DD HH24:MI:SS'') FROM DUAL' from dual
union all select 0.9 as no, '/' from dual
union all select 1 as no, 'CREATE TABLE ' || :TABLENAME||'_'||:YYYYMMDD || ' AS SELECT * FROM ' || :TABLENAME from dual
union all select 1.9 as no, '/' from dual
union all select 2 as no, 'DROP TABLE ' || :TABLENAME || ' CASCADE CONSTRAINTS' from dual
union all select 2.9 as no, '/' from dual
union all select 3 as no, '@"'||:SCRIPTDIR||'\'||:TABLENAME||'.SQL"' from dual
union all select 3.9 as no, '/' from dual
union all select 4 as no, 'INSERT INTO ' || :TABLENAME || '(' from dual
union all select 4 + column_id / 1000 as no, decode(column_id,1,' ',',') || column_name from user_tab_columns where table_name = :TABLENAME
union all select 4.2 as no, ')' from dual
union all select 4.3 as no, 'SELECT * FROM ' || :TABLENAME||'_'||:YYYYMMDD from dual
union all select 4.9 as no, '/' from dual
union all select 5 as no, 'COMMIT' from dual
union all select 5.9 as no, '/' from dual
union all select 90 as no, 'SELECT ''件数チェック'',OLD.COUNT,NEW.COUNT' from dual
union all select 90.1 as no, ',''■■■''||DECODE(OLD.COUNT, NEW.COUNT, ''OK'', ''※NG※'') FROM' from dual
union all select 90.2 as no, ' (SELECT COUNT(*) COUNT FROM ' || :TABLENAME||'_'||:YYYYMMDD||') OLD' from dual
union all select 90.3 as no, ',(SELECT COUNT(*) COUNT FROM ' || :TABLENAME || ' ) NEW' from dual
union all select 90.9 as no, '/' from dual
union all select 99 as no, '-- ワークテーブル削除' from dual
union all select 99.1 as no, '-- DROP TABLE ' || :TABLENAME||'_'||:YYYYMMDD from dual
union all select 99.9 as no, '' from dual
union all select 99.9 as no, '' from dual
order by no)
/
