Mar27th

DB定義変更

Top / DB定義変更

DB定義変更SQL

・新規TBL作成

SET SERVEROUTPUT ON SIZE 200000
declare
 mysql     varchar2(500);
 c_nam     varchar2(50);
 c_id      varchar2(50);
 c_typ     varchar2(50);
 c_leng    varchar2(50);
 c_null    varchar2(50);
 c_com     varchar2(50);
 c_count   number;
 index_space    varchar2(50);
 error_message  varchar2(2000);
 war_count number;
 err_count number;
begin
--新規テーブル追加
   dbms_output.put_line('DDLVer2-1-18 新規テーブル追加スタート');
   --年間給付額計算書記録TBL テーブル追加
   begin
       dbms_output.put_line(' 年間給付額計算書記録TBL テーブル追加 開始');
 
       --テーブル追加
       begin
           
           select count(*) into c_count from USER_TAB_COLUMNS a
                                                    left join 
                                                         (select * from user_col_comments where table_name = 'TBL_NKN_KF_Y_CLC_SYO_R') b
                                                    on a.column_name = b.column_name
                                                    where a.table_name = 'TBL_NKN_KF_Y_CLC_SYO_R';
           
           select tablespace_name into index_space from  user_ts_quotas
                                                   where tablespace_name <> (select default_tablespace from  user_users);            
           
           case c_count 
                when 0 then
                   --テーブル作成
                   EXECUTE IMMEDIATE
                   'CREATE TABLE TBL_NKN_KF_Y_CLC_SYO_R(NKN_KF_Y_CLC_SYO_SEQ NUMBER(10) NOT NULL,
                                                        NK_SD_SYZK_KJIN_MS_SEQ NUMBER(10) NOT NULL,
                                                        KF_YXX CHAR(4) NOT NULL,
                                                        SKSI_TNO NUMBER(3) NOT NULL,
                                                        SKSI_CYCL_C CHAR(1) NOT NULL,
                                                        NKN_KF_SOU_Y NUMBER(15),
                                                        SYEK_SOTO_Y NUMBER(15),
                                                        TRKS_YMD DATE,
                                                        TRK_USR_ID VARCHAR2(20),
                                                        TRK_YMD DATE,
                                                        LST_UPD_USR_ID VARCHAR2(20),
                                                        LST_UPD_YMD DATE,
                                                        SYS_UPD_USR_ID VARCHAR2(20),
                                                        SYS_UPD_TIM DATE,
                                                        CONSTRAINT PK_NKN_KF_Y_CLC_SYO_R PRIMARY KEY  (NKN_KF_Y_CLC_SYO_SEQ) USING INDEX TABLESPACE ' || index_space || '
                                                       )';
                   --コメント付与
                   EXECUTE IMMEDIATE 'COMMENT ON TABLE TBL_NKN_KF_Y_CLC_SYO_R IS ''年間給付額計算書記録 TBL''';
                   EXECUTE IMMEDIATE 'COMMENT ON COLUMN  TBL_NKN_KF_Y_CLC_SYO_R.NKN_KF_Y_CLC_SYO_SEQ IS ''年間給付額計算書SEQ''';
                   EXECUTE IMMEDIATE 'COMMENT ON COLUMN  TBL_NKN_KF_Y_CLC_SYO_R.NK_SD_SYZK_KJIN_MS_SEQ IS ''年金制度所属個人明細SEQ''';
                   EXECUTE IMMEDIATE 'COMMENT ON COLUMN TBL_NKN_KF_Y_CLC_SYO_R.KF_YXX IS ''給付年''';
                   EXECUTE IMMEDIATE 'COMMENT ON COLUMN TBL_NKN_KF_Y_CLC_SYO_R.SKSI_TNO IS ''作成通番''';
                   EXECUTE IMMEDIATE 'COMMENT ON COLUMN TBL_NKN_KF_Y_CLC_SYO_R.SKSI_CYCL_C IS ''作成サイクルコード''';
                   EXECUTE IMMEDIATE 'COMMENT ON COLUMN TBL_NKN_KF_Y_CLC_SYO_R.NKN_KF_SOU_Y IS ''年間給付総額''';
                   EXECUTE IMMEDIATE 'COMMENT ON COLUMN TBL_NKN_KF_Y_CLC_SYO_R.SYEK_SOTO_Y IS ''収益相当額''';
                   EXECUTE IMMEDIATE 'COMMENT ON COLUMN TBL_NKN_KF_Y_CLC_SYO_R.TRKS_YMD IS ''取消年月日''';
                   EXECUTE IMMEDIATE 'COMMENT ON COLUMN TBL_NKN_KF_Y_CLC_SYO_R.TRK_USR_ID IS ''登録ユーザID''';
                   EXECUTE IMMEDIATE 'COMMENT ON COLUMN TBL_NKN_KF_Y_CLC_SYO_R.TRK_YMD IS ''登録年月日''';
                   EXECUTE IMMEDIATE 'COMMENT ON COLUMN TBL_NKN_KF_Y_CLC_SYO_R.LST_UPD_USR_ID  IS ''最終更新ユーザID''';
                   EXECUTE IMMEDIATE 'COMMENT ON COLUMN TBL_NKN_KF_Y_CLC_SYO_R.LST_UPD_YMD IS ''最終更新年月日''';
                   EXECUTE IMMEDIATE 'COMMENT ON COLUMN TBL_NKN_KF_Y_CLC_SYO_R.SYS_UPD_USR_ID  IS ''システム更新ユーザID''';
                   EXECUTE IMMEDIATE 'COMMENT ON COLUMN TBL_NKN_KF_Y_CLC_SYO_R.SYS_UPD_TIM IS ''システム更新日時''';
                   --トリガ再作成
                   EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER TR_NKN_KF_Y_CLC_SYO_R BEFORE  INSERT OR UPDATE ON TBL_NKN_KF_Y_CLC_SYO_R FOR EACH ROW 
                                     BEGIN 
                                        :NEW.SYS_UPD_USR_ID := USER; 
                                        :NEW.SYS_UPD_TIM    := CURRENT_TIMESTAMP; 
                                     END;';
                   EXECUTE IMMEDIATE 'ALTER TRIGGER TR_NKN_KF_Y_CLC_SYO_R ENABLE';
                   
                   ----Fキー作成1
                   EXECUTE IMMEDIATE 'ALTER TABLE TBL_NKN_KF_Y_CLC_SYO_R
                                      ADD CONSTRAINT FK_NKN_KF_Y_CLC_SYO_R FOREIGN KEY  (NK_SD_SYZK_KJIN_MS_SEQ)
                                      REFERENCES TBL_NK_SD_SYZK_KJIN_I_MS (NK_SD_SYZK_KJIN_MS_SEQ)';
                   --シーケンス作成
                   EXECUTE IMMEDIATE 'CREATE SEQUENCE NKN_KF_Y_CLC_SYO_SEQ INCREMENT BY 1 START  WITH 1';
               else                 
                   dbms_output.put_line(' 年間給付額計算書記録TBL はすでに下記の構成で作成されています');
                   dbms_output.put_line('   column_name	data_type	data_length	nullable	column_id	comments');
                   for cur in (select a.column_name,a.data_type,a.data_length,a.nullable,a.column_id,b.comments 
                                    from USER_TAB_COLUMNS a
                                    left join 
                                         (select * from user_col_comments where table_name = 'TBL_NKN_KF_Y_CLC_SYO_R') b
                                    on a.column_name = b.column_name
                                    where a.table_name = 'TBL_NKN_KF_Y_CLC_SYO_R') 
           
                   loop 
                   begin
                          dbms_output.put_line('   ' || cur.column_name || '	' || cur.data_type ||  '	' ||  cur.data_length || '	' || cur.nullable || '	' || cur.column_id || '	' || cur.comments );
                       exception
                             when others then null; 
                   END;
                   end loop;
     
           end case;
       end;
     
       dbms_output.put_line(' 年間給付額計算書記録TBL テーブル追加 終了');
       
   exception
       when others then
            dbms_output.put_line(' 【ERROR】年間給付額計算書記録TBL新規作成にてエラーが発生しました');
            error_message := sqlerrm;
            dbms_output.put_line('            エラーメッセージ:' || error_message);
            err_count := 1;
 
   end;
   
   dbms_output.put_line('DDLVer2-1-18 新規テーブル追加エンド');
end;
/

・TBL項目追加

SET SERVEROUTPUT ON SIZE 200000
begin
--バックアップ取得
 dbms_output.put_line('定義更新前バックアップ 開始');
 EXECUTE IMMEDIATE 'create table UPDDL_2_1_17_BK2 as select * from TBL_NK_SLD_KITI_KY_I_MS'; 
 dbms_output.put_line('  年金スライド改定規約情報明細TBL ⇒ UPDDL_2_1_17_BK2');
 dbms_output.put_line('定義更新前バックアップ 終了');
end;
/
declare
 mysql     varchar2(500);
 c_nam     varchar2(50);
 c_id      varchar2(50);
 c_typ     varchar2(50);
 c_leng    varchar2(50);
 c_null    varchar2(50);
 c_com     varchar2(50);
 c_count   number;
 index_space    varchar2(50);
 error_message  varchar2(2000);
 war_count number;
 err_count number;
begin
   war_count := 0;
   err_count := 0;
   dbms_output.put_line('DDLVer2-1-17 UP用SQLスタート');
   
   --年金スライド改定規約情報明細TBL 定義変更
   begin
       dbms_output.put_line(' 年金スライド改定規約情報明細TBL 定義変更 開始');
 
       --カラム追加
       begin
           dbms_output.put_line('          カラム追加 スライド改定率名');
           
           select a.column_name,a.data_type,a.data_length,a.nullable,a.column_id,b.comments 
           into c_nam,c_typ,c_leng,c_null,c_id,c_com from USER_TAB_COLUMNS a
                                                    left join 
                                                         (select * from user_col_comments where table_name = 'TBL_NK_SLD_KITI_KY_I_MS') b
                                                    on a.column_name = b.column_name
                                                    where a.table_name = 'TBL_NK_SLD_KITI_KY_I_MS'
                                                    and a.column_name in ('SLD_KITI_PR_NAM');
     
           if c_id <> 11 then
               dbms_output.put_line('          カラム追加 【警告】スライド改定率名が存在しますがカラム位置が違います');
               war_count := 1;
           elsif c_typ <> 'VARCHAR2' then
               dbms_output.put_line('          カラム追加 【警告】スライド改定率名が存在しますがデータ型が違います');
               war_count := 1;
           elsif c_leng <> 60 then
               dbms_output.put_line('          カラム追加 【警告】スライド改定率名が存在しますが桁数が違います');
               war_count := 1;
           elsif c_null <> 'Y' then
               dbms_output.put_line('          カラム追加 【警告】スライド改定率名が存在しますがnot null制約が違います');
               war_count := 1;
           elsif c_com <> 'スライド改定率名' then
               dbms_output.put_line('          カラム追加 【警告】スライド改定率名が存在しますがコメントが違います');
               war_count := 1;
           else 
               dbms_output.put_line('          カラム追加 定義更新済みなので実施しません');
           end if;
       
       exception
           when no_data_found then
                 dbms_output.put_line('          カラム追加 定義変更を実施します');
                 
                 select tablespace_name into index_space from  user_ts_quotas
                                                   where tablespace_name <> (select default_tablespace from user_users); 
                                                   
                 --テーブル削除
                 mysql := 'DROP TABLE TBL_NK_SLD_KITI_KY_I_MS CASCADE CONSTRAINTS PURGE';
                 EXECUTE IMMEDIATE mysql;
           
                 --テーブル再作成定義変更
                 EXECUTE IMMEDIATE
                 'CREATE TABLE TBL_NK_SLD_KITI_KY_I_MS(NK_SLD_KITI_SEQ NUMBER(10) NOT NULL,
                                                       KF_K CHAR(2) NOT NULL,
                                                       NK_KD_SNTK_D CHAR(2) NOT NULL,
                                                       SLD_KITI_JKEN_C CHAR(1),
                                                       SLD_KITI_S_TI NUMBER(3),
                                                       SLD_KITI_YK_TI NUMBER(3),
                                                       SLD_KITI_SYK_KGTS NUMBER(4),
                                                       SLD_KITI_Y_SNSY_WAY_C CHAR(1),
                                                       SLD_KITI_Y NUMBER(15),
                                                       SLD_KITI_PR NUMBER(9,7),
                                                       SLD_KITI_PR_NAM VARCHAR2(60),
                                                       TRKS_YMD DATE,
                                                       TRK_USR_ID VARCHAR2(20),
                                                       TRK_YMD DATE,
                                                       LST_UPD_USR_ID VARCHAR2(20),
                                                       LST_UPD_YMD DATE,
                                                       SYS_UPD_USR_ID VARCHAR2(20),
                                                       SYS_UPD_TIM DATE,
                                                       CONSTRAINT PK_NK_SLD_KITI_KY_I_MS PRIMARY KEY (NK_SLD_KITI_SEQ, KF_K, NK_KD_SNTK_D) USING INDEX TABLESPACE ' || index_space || '
                                                       )';
          
                 --コメント付与
                 EXECUTE IMMEDIATE 'COMMENT ON TABLE TBL_NK_SLD_KITI_KY_I_MS IS ''年金スライド改定規約情報明細TBL''';
                 EXECUTE IMMEDIATE 'COMMENT ON COLUMN TBL_NK_SLD_KITI_KY_I_MS.NK_SLD_KITI_SEQ IS ''年金スライド改定SEQ''';
                 EXECUTE IMMEDIATE 'COMMENT ON COLUMN TBL_NK_SLD_KITI_KY_I_MS.KF_K IS ''給付階''';
                 EXECUTE IMMEDIATE 'COMMENT ON COLUMN TBL_NK_SLD_KITI_KY_I_MS.NK_KD_SNTK_D IS ''年金種類選択区分''';
                 EXECUTE IMMEDIATE 'COMMENT ON COLUMN TBL_NK_SLD_KITI_KY_I_MS.SLD_KITI_JKEN_C IS ''スライド改定条件コード''';
                 EXECUTE IMMEDIATE 'COMMENT ON COLUMN TBL_NK_SLD_KITI_KY_I_MS.SLD_KITI_S_TI IS ''スライド改定開始値''';
                 EXECUTE IMMEDIATE 'COMMENT ON COLUMN TBL_NK_SLD_KITI_KY_I_MS.SLD_KITI_YK_TI IS ''スライド改定有効値''';
                 EXECUTE IMMEDIATE 'COMMENT ON COLUMN TBL_NK_SLD_KITI_KY_I_MS.SLD_KITI_SYK_KGTS IS ''スライド改定周期月数''';
                 EXECUTE IMMEDIATE 'COMMENT ON COLUMN TBL_NK_SLD_KITI_KY_I_MS.SLD_KITI_Y_SNSY_WAY_C IS ''スライド改定額算出方法コード''';
                 EXECUTE IMMEDIATE 'COMMENT ON COLUMN TBL_NK_SLD_KITI_KY_I_MS.SLD_KITI_Y IS ''スライド改定額''';
                 EXECUTE IMMEDIATE 'COMMENT ON COLUMN TBL_NK_SLD_KITI_KY_I_MS.SLD_KITI_PR IS ''スライド改定率''';
                 EXECUTE IMMEDIATE 'COMMENT ON COLUMN TBL_NK_SLD_KITI_KY_I_MS.SLD_KITI_PR_NAM IS ''スライド改定率名''';
                 EXECUTE IMMEDIATE 'COMMENT ON COLUMN TBL_NK_SLD_KITI_KY_I_MS.TRKS_YMD IS ''取消年月日''';
                 EXECUTE IMMEDIATE 'COMMENT ON COLUMN TBL_NK_SLD_KITI_KY_I_MS.TRK_USR_ID IS ''登録ユーザID''';
                 EXECUTE IMMEDIATE 'COMMENT ON COLUMN TBL_NK_SLD_KITI_KY_I_MS.TRK_YMD IS ''登録年月日''';
                 EXECUTE IMMEDIATE 'COMMENT ON COLUMN TBL_NK_SLD_KITI_KY_I_MS.LST_UPD_USR_ID IS ''最終更新ユーザID''';
                 EXECUTE IMMEDIATE 'COMMENT ON COLUMN TBL_NK_SLD_KITI_KY_I_MS.LST_UPD_YMD IS ''最終更新年月日''';
                 EXECUTE IMMEDIATE 'COMMENT ON COLUMN TBL_NK_SLD_KITI_KY_I_MS.SYS_UPD_USR_ID IS ''システム更新ユーザID''';
                 EXECUTE IMMEDIATE 'COMMENT ON COLUMN TBL_NK_SLD_KITI_KY_I_MS.SYS_UPD_TIM IS ''システム更新日時''';
                 --トリガ再作成
                 EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER TR_NK_SLD_KITI_KY_I_MS BEFORE INSERT OR UPDATE ON TBL_NK_SLD_KITI_KY_I_MS FOR EACH ROW 
                                   BEGIN 
                                      :NEW.SYS_UPD_USR_ID := USER; 
                                      :NEW.SYS_UPD_TIM    := CURRENT_TIMESTAMP; 
                                   END;';
                 EXECUTE IMMEDIATE 'ALTER TRIGGER TR_NK_SLD_KITI_KY_I_MS ENABLE';
                 --データ戻し
                 EXECUTE IMMEDIATE 'insert into TBL_NK_SLD_KITI_KY_I_MS 
                                    select NK_SLD_KITI_SEQ,KF_K,NK_KD_SNTK_D,SLD_KITI_JKEN_C,SLD_KITI_S_TI,SLD_KITI_YK_TI,SLD_KITI_SYK_KGTS,SLD_KITI_Y_SNSY_WAY_C,SLD_KITI_Y,SLD_KITI_PR,
                                           '''',
                                           TRKS_YMD,TRK_USR_ID,TRK_YMD,LST_UPD_USR_ID,LST_UPD_YMD,
                                           SYS_UPD_USR_ID,SYS_UPD_TIM
                                    from UPDDL_2_1_17_BK2';
                 commit;                   
                 ----Fキー作成1
                 EXECUTE IMMEDIATE 'ALTER TABLE TBL_NK_SLD_KITI_KY_I_MS
                                    ADD CONSTRAINT FK_NK_SLD_KITI_KY_I_MS FOREIGN KEY (NK_SLD_KITI_SEQ)
                                    REFERENCES TBL_NK_SLD_KITI_KY_I (NK_SLD_KITI_SEQ)';
       end;
       
       dbms_output.put_line(' 年金スライド改定規約情報明細TBL 定義変更 終了');
       
   exception
       when others then
            dbms_output.put_line(' 【ERROR】年金スライド改定規約情報明細TBLの定義変更にてエラーが発生しました');
            error_message := sqlerrm;
            dbms_output.put_line('            エラーメッセージ:' || error_message);
            err_count := 1;
 
   end;
   
   
   --最終確認
   dbms_output.put_line('');
   dbms_output.put_line('  定義変更後のオブジェクト確認');
   --外部キー有効化
   begin  
       dbms_output.put_line('    外部キー有効化 開始');
       for cur in (select table_name,constraint_name from user_constraints 
                                                     where constraint_type = 'R'
                  )   loop
          begin 
               mysql := 'alter table ' || cur.table_name || ' enable constraints ' || cur.constraint_name;
               EXECUTE IMMEDIATE mysql;
        
          exception
              when others then 
                  dbms_output.put_line('     【ERROR】: ' || cur.constraint_name || 'が有効化できません');
                  err_count := 1;
           end;      
        end loop; 
        dbms_output.put_line('    外部キー有効化 終了');
   end;
   --コンパイル
   begin
       dbms_output.put_line('    コンパイル 開始');
       for cur in (select OBJECT_NAME,OBJECT_TYPE 
                   from obj 
                   where status = 'INVALID' ) 
       loop 
           begin
              if cur.OBJECT_TYPE = 'PACKAGE BODY' then 
                 EXECUTE IMMEDIATE 'alter ' || cur.OBJECT_TYPE || ' "' || cur.OBJECT_NAME || '" compile body'; 
              else 
                 EXECUTE IMMEDIATE 'alter ' || cur.OBJECT_TYPE || ' "' || cur.OBJECT_NAME || '" compile'; 
              end if; 
       
           exception
              when others then null; 
           END;
       end loop;
   
       for cur2 in (select OBJECT_NAME,OBJECT_TYPE 
                    from obj 
                    where status = 'INVALID' ) 
       loop 
           begin
              dbms_output.put_line('    【ERROR】:' || cur2.OBJECT_TYPE || ' ' || cur2.OBJECT_NAME || 'が有効化できません');
              err_count := 1;
           exception
              when others then null; 
           END;
       end loop;
 
       dbms_output.put_line('    コンパイル  終了');
   end;
   
   dbms_output.put_line('  定義変更後のオブジェクト確認終了');
   --バックアップテーブル削除
   begin
       dbms_output.put_line('');
       dbms_output.put_line(' バックアップテーブル削除');
       
       if err_count = 1 then
          dbms_output.put_line('  定義変更処理でエラーが発生しているためバックアップテーブルを削除できません。');
          dbms_output.put_line('  エラー内容を確認し対応後に手動でバックアップテーブルを削除してください。');
       elsif war_count = 1 then
          dbms_output.put_line('  定義変更処理で警告が発生しています。');
          dbms_output.put_line('  バックアップテーブルは削除しますが警告発生個所を確認し適宜修正してください。');
          dbms_output.put_line('  バックアップデータが必要な場合はDBAに問い合わせてください。');
          
          EXECUTE IMMEDIATE 'drop table UPDDL_2_1_17_BK2 cascade constraint';
                     
       else
          dbms_output.put_line('  バックアップテーブルを削除しました。');
          
          EXECUTE IMMEDIATE 'drop table UPDDL_2_1_17_BK2 cascade constraint purge';
          
       end if;
   
   end;
   dbms_output.put_line('DDLVer2-1-17 UP用SQLエンド');
   
end;
/

DB定義比較SQL

SET SERVEROUTPUT ON SIZE 20000
set VERIFY off
declare
 mysql     varchar2(500);
 o_nam     varchar2(50) := '&比較元スキーマ';
 r_nam     varchar2(50) := '&比較先スキーマ';
 o_count   number;
begin
 --定義比較
 dbms_output.put_line('定義比較開始');
 dbms_output.put_line(' 比較元スキーマ : ' || o_nam);
 dbms_output.put_line(' 比較先スキーマ : ' || r_nam);
 --テーブル比較
 dbms_output.put_line('■テーブル比較');
 --テーブル比較増分
 begin
     dbms_output.put_line('比較先スキーマのみに存在するテーブル確認');
     select count(*) into o_count from (select table_name from dba_tables
                                                          where owner = r_nam
                                        minus
                                        select table_name from dba_tables
                                                          where owner = o_nam);
     
     case o_count
         when 0 then
             dbms_output.put_line('  OK:比較先スキーマのみに存在するテーブルはありません');
         else
             dbms_output.put_line('  NG:以下のテーブルが比較先スキーマに存在しています');
             for car1 in (select table_name from dba_tables
                                            where owner = r_nam
                          minus
                          select table_name from dba_tables
                                            where owner = o_nam
                         )
             loop
                 dbms_output.put_line('     ' || car1.table_name);
             end loop;
        end case;     
 end;
 --テーブル比較差分
 begin
     dbms_output.put_line('比較先スキーマに存在しないテーブル');
     select count(*) into o_count from (select table_name from dba_tables
                                                          where owner = o_nam
                                        minus
                                        select table_name from dba_tables
                                                          where owner = r_nam);
     
     case o_count
         when 0 then
             dbms_output.put_line('  OK:比較先スキーマに存在しないテーブルはありません');
         else
             dbms_output.put_line('  NG:以下のテーブルが比較先スキーマに足りていません');
             for car1 in (select table_name from dba_tables
                                            where owner = o_nam
                          minus
                          select table_name from dba_tables
                                            where owner = r_nam
                         )
             loop
                 dbms_output.put_line('     ' || car1.table_name);
             end loop;
        end case;     
 end; 
 
 --カラム比較
 dbms_output.put_line('■カラム比較');
 --カラム比較増分
 begin
     dbms_output.put_line('比較先スキーマにのみ存在するカラム確認');
     select count(*) into o_count from (select table_name,column_name from dba_tab_columns
                                                                      where owner = r_nam
                                        minus
                                        select table_name,column_name from dba_tab_columns
                                                                      where owner = o_nam
                                        )
                                  where table_name in (select table_name from dba_tables
                                                                         where owner = r_nam
                                                                         and table_name not in (select table_name from dba_tables
                                                                                                                  where owner = r_nam
                                                                                                minus
                                                                                                select table_name from dba_tables
                                                                                                                  where owner = o_nam
                                                                                               )
                                                      );
     
     case o_count
         when 0 then
             dbms_output.put_line('  OK:比較先スキーマのみに存在するカラムはありません');
         else
             dbms_output.put_line('  NG:以下のカラムが比較先スキーマに存在しています');
             dbms_output.put_line('  テーブル名    カラム名');
             for car1 in (select table_name,column_name from (select table_name,column_name from  dba_tab_columns
                                                                                            where owner = r_nam
                                                              minus
                                                              select table_name,column_name from dba_tab_columns
                                                                                            where owner = o_nam
                                                              )
                                                        where table_name in (select table_name from dba_tables
                                                                                               where owner = r_nam
                                                                                               and table_name not in (select table_name  from dba_tables
                                                                                                                                        where owner = r_nam
                                                                                                                      minus
                                                                                                                      select table_name from dba_tables
                                                                                                                                        where owner = o_nam
                                                                                                                      )
                                                                            )
                         )
             loop
                   dbms_output.put_line('  ' || car1.table_name || '       ' || car1.column_name);
             end loop;
        end case;     
 end; 
   --カラム比較差分
 begin
     dbms_output.put_line('比較先スキーマに存在しないカラム確認');
     select count(*) into o_count from (select table_name,column_name from dba_tab_columns
                                                                      where owner = o_nam
                                        minus
                                        select table_name,column_name from dba_tab_columns
                                                                      where owner = r_nam
                                        )
                                  where table_name in (select table_name from dba_tables
                                                                         where owner = o_nam
                                                                         and table_name not in (select table_name from dba_tables
                                                                                                                  where owner = o_nam
                                                                                                minus
                                                                                                select table_name from dba_tables
                                                                                                                  where owner = r_nam
                                                                                               )
                                                      );
     
     case o_count
         when 0 then
             dbms_output.put_line('  OK:比較先スキーマに存在しないカラムはありません');
         else
             dbms_output.put_line('  NG:以下のカラムが比較先スキーマに足りていません');
             dbms_output.put_line('  テーブル名    カラム名');
             for car1 in (select table_name,column_name from (select table_name,column_name from dba_tab_columns
                                                                                            where owner = o_nam
                                                              minus
                                                              select table_name,column_name from dba_tab_columns
                                                                                            where owner = r_nam
                                                              )
                                                        where table_name in (select table_name from dba_tables
                                                                                               where owner = o_nam
                                                                                               and table_name not in (select table_name from dba_tables
                                                                                                                                        where owner = o_nam
                                                                                                                      minus
                                                                                                                      select table_name from dba_tables
                                                                                                                                        where owner = r_nam
                                                                                                                      )
                                                                            )
                         )
             loop
                   dbms_output.put_line('  ' || car1.table_name || '       ' || car1.column_name);
             end loop;
        end case;     
 end;
 --カラム定義差分
 begin
     dbms_output.put_line('カラム定義の差分確認');
     select count(*) into o_count from (select table_name,column_name,data_type,data_type_mod,data_type_owner,
                                               data_length,data_precision,data_scale,nullable,default_length from dba_tab_columns
                                                                                                             where owner = r_nam
                                        minus
                                        select table_name,column_name,data_type,data_type_mod,data_type_owner,
                                               data_length,data_precision,data_scale,nullable,default_length from dba_tab_columns
                                                                                                             where owner = o_nam
                                        )
                                  where table_name in (select table_name from dba_tables
                                                                         where owner = r_nam
                                                                         and column_name not in (select column_name from (select table_name,column_name from dba_tab_columns
                                                                                                                                                        where owner = r_nam
                                                                                                                          minus
                                                                                                                          select table_name,column_name from dba_tab_columns
                                                                                                                                      where owner = o_nam
                                                                                                                          )
                                                                                                                    where table_name in (select table_name from dba_tables
                                                                                                                                                           where owner = r_nam
                                                                                                                                        )
                                                                                                )
                                                      );
     
     case o_count
         when 0 then
             dbms_output.put_line('  OK:カラム定義に差分はありません');
         else
             dbms_output.put_line('  NG:以下のカラム定義に差分があります');
             dbms_output.put_line('     テーブル名 カラム名 データ型 修飾子 データ型所有者 長さ 精度 小数点 NULL');
             for car1 in (select table_name,column_name,data_type,data_type_mod,data_type_owner,
                                 data_length,data_precision,data_scale,nullable,default_length
                          from (select table_name,column_name,data_type,data_type_mod,data_type_owner,
                                       data_length,data_precision,data_scale,nullable,default_length from dba_tab_columns
                                                                                                     where owner = r_nam
                                 minus
                                select table_name,column_name,data_type,data_type_mod,data_type_owner,
                                       data_length,data_precision,data_scale,nullable,default_length from dba_tab_columns
                                                                                                      where owner = o_nam
                                )
                           where table_name in (select table_name from dba_tables
                                                                  where owner = r_nam
                                                                   and column_name not in (select column_name from (select table_name,column_name from dba_tab_columns
                                                                                                                                                 where owner = r_nam
                                                                                                                   minus
                                                                                                                   select table_name,column_name from dba_tab_columns
                                                                                                                                                 where owner = o_nam
                                                                                                                   )
                                                                                                              where table_name in (select table_name from dba_tables
                                                                                                                                                     where owner = r_nam
                                                                                                                                   )
                                                                                           )
                                                 )
                          )
             loop
                   dbms_output.put_line(r_nam || ' : ' || car1.table_name || ',' || car1.column_name || ',' || car1.data_type || ',' || car1.data_type_mod || ',' || car1.data_type_owner || ',' || car1.data_length || ',' || car1.data_precision || ',' || car1.data_scale || ',' || car1.nullable);
                   for car2 in (select table_name,column_name,data_type,data_type_mod,
                                       data_type_owner,data_length,data_precision,data_scale,nullable from dba_tab_columns
                                                                                                      where owner = o_nam
                                                                                                      and table_name = car1.table_name
                                                                                                      and column_name = car1.column_name
                                )
                   loop
                       dbms_output.put_line(o_nam || ' : ' || car2.table_name || ',' || car2.column_name || ',' || car2.data_type || ',' || car2.data_type_mod || ',' || car2.data_type_owner || ',' || car2.data_length || ',' || car2.data_precision || ',' || car2.data_scale || ',' || car2.nullable);
                   end loop;
             end loop;
        end case;     
 end; 
 --VIEW定義確認
 dbms_output.put_line('■VIEW比較');
 begin
     dbms_output.put_line('VIEW定義の差異確認');
     select count(*) into o_count from (select table_name from (select table_name,column_name from dba_tab_columns
                                                                                              where owner = r_nam
                                                                minus
                                                                select table_name,column_name from dba_tab_columns
                                                                                              where owner = o_nam
                                                                )
                                                          where table_name in (select view_name from dba_views
                                                                                                where owner = r_nam
                                                                               )
                                        union
                                        select table_name from (select table_name,column_name from dba_tab_columns
                                                                                              where owner = o_nam
                                                                minus
                                                                select table_name,column_name from dba_tab_columns
                                                                                              where owner = r_nam
                                                                )
                                                          where table_name in (select view_name from dba_views
                                                                                                where owner = o_nam
                                                                               )
                                        );
     
     case o_count
         when 0 then
             dbms_output.put_line('  OK:VIEW定義に差異はありません');
         else
             dbms_output.put_line('  NG:以下のVIEW定義に差異があります');
             for car1 in (select table_name from (select table_name,column_name from dba_tab_columns
                                                                                where owner = r_nam
                                                  minus
                                                  select table_name,column_name from dba_tab_columns
                                                                                where owner = o_nam
                                                  )
                                            where table_name in (select view_name from dba_views
                                                                                  where owner = r_nam
                                                                 )
                          union
                          select table_name from (select table_name,column_name from dba_tab_columns
                                                                                 where owner = o_nam
                                                  minus
                                                  select table_name,column_name from dba_tab_columns
                                                                                where owner = r_nam
                                                  )
                                            where table_name in (select view_name from dba_views
                                                                                  where owner = o_nam
                                                                 )
                          )
             loop
                   dbms_output.put_line( '   ' || car1.table_name );
             end loop;
        end case;     
 end; 
 --外部キー確認
 dbms_output.put_line('■外部キー比較');
 --外部キー比較増分
 begin
     dbms_output.put_line('比較先スキーマにのみ存在する外部キー確認');
     select count(*) into o_count from (select constraint_name,constraint_type,table_name from dba_constraints
                                                                                          where owner = r_nam
                                                                                          and constraint_type = 'R'
                                        minus
                                        select constraint_name,constraint_type,table_name from dba_constraints
                                                                                          where owner = o_nam
                                                                                          and constraint_type = 'R'
                                        );
     case o_count
         when 0 then
             dbms_output.put_line('  OK:比較先スキーマにのみ存在する外部キーはありません');
         else
             dbms_output.put_line('  NG:以下の外部キーが比較先スキーマに存在しています');
             for car1 in (select constraint_name from (select constraint_name,constraint_type,table_name from dba_constraints
                                                                                                         where owner = r_nam
                                                                                                         and constraint_type = 'R'
                                                       minus
                                                       select constraint_name,constraint_type,table_name from dba_constraints
                                                                                                         where owner = o_nam
                                                                                                         and constraint_type = 'R'
                                                       )
                         )
             loop
                 dbms_output.put_line( '   ' || car1.constraint_name );
             end loop;
     end case;
 end;  
 --外部キー比較差分
 begin
     dbms_output.put_line('比較先スキーマに存在しない外部キー確認');
     select count(*) into o_count from (select constraint_name,constraint_type,table_name from dba_constraints
                                                                                          where owner = r_nam
                                                                                          and constraint_type = 'R'
                                        minus
                                        select constraint_name,constraint_type,table_name from dba_constraints
                                                                                          where owner = o_nam
                                                                                          and constraint_type = 'R'
                                        );
     case o_count
         when 0 then
             dbms_output.put_line('  OK:比較先スキーマに存在しない外部キーはありません');
         else
             dbms_output.put_line('  NG:以下の外部キーが比較先スキーマに足りていません');
             for car1 in (select constraint_name from (select constraint_name,constraint_type,table_name from dba_constraints
                                                                                                         where owner = o_nam
                                                                                                         and constraint_type = 'R'
                                                       minus
                                                       select constraint_name,constraint_type,table_name from dba_constraints
                                                                                                         where owner = r_nam
                                                                                                         and constraint_type = 'R'
                                                       )
                         )
             loop
                 dbms_output.put_line( '   ' || car1.constraint_name );
             end loop;
     end case;
 end;  
 --外部キー有効化確認
 begin
     dbms_output.put_line('比較先スキーマで有効化されていない外部キー確認');
     select count(*) into o_count from (select constraint_name,constraint_type,table_name,status from dba_constraints
                                                                                                 where owner = r_nam
                                                                                                 and constraint_type = 'R'
                                                                                                 and status = 'DISABLED'
                                        );
     case o_count
         when 0 then
             dbms_output.put_line('  OK:全ての外部キーが有効化されています');
         else
             dbms_output.put_line('  NG:以下の外部キーが有効化されていません');
             for car1 in (select constraint_name from (select constraint_name,constraint_type,table_name,status from dba_constraints
                                                                                                                where owner = r_nam
                                                                                                                and constraint_type = 'R'
                                                                                                                and status = 'DISABLED'
                                                       )
                         )
             loop
                 dbms_output.put_line( '   ' || car1.constraint_name );
             end loop;
     end case;
 end;
 --SEQ確認
 dbms_output.put_line('■SEQUENCE比較');
 --SEQ比較増分
 begin
     dbms_output.put_line('比較先スキーマにのみ存在するSEQ確認');
     select count(*) into o_count from (select sequence_name from dba_sequences
                                                             where sequence_owner = r_nam
                                        minus
                                        select sequence_name from dba_sequences
                                                             where sequence_owner = o_nam
                                        );
     case o_count
         when 0 then
             dbms_output.put_line('  OK:比較先スキーマにのみ存在するSEQはありません');
         else
             dbms_output.put_line('  NG:以下のSEQが比較先スキーマに存在しています');
             for car1 in (select sequence_name from dba_sequences
                                               where sequence_owner = r_nam
                          minus
                          select sequence_name from dba_sequences
                                               where sequence_owner = o_nam
                          )
             loop
                 dbms_output.put_line( '   ' || car1.sequence_name );
             end loop;
     end case;
 end;    
 --SEQ比較差分
 begin
     dbms_output.put_line('比較先スキーマに存在しないSEQ確認');
     select count(*) into o_count from (select sequence_name from dba_sequences
                                                             where sequence_owner = o_nam
                                        minus
                                        select sequence_name from dba_sequences
                                                             where sequence_owner = r_nam
                                        );
     case o_count
         when 0 then
             dbms_output.put_line('  OK:比較先スキーマに存在しないSEQはありません');
         else
             dbms_output.put_line('  NG:以下のSEQが比較先スキーマに足りていません');
             for car1 in (select sequence_name from dba_sequences
                                               where sequence_owner = o_nam
                          minus
                          select sequence_name from dba_sequences
                                               where sequence_owner = r_nam
                          )
             loop
                 dbms_output.put_line( '   ' || car1.sequence_name );
             end loop;
     end case;
 end;
 --INDEX確認
 dbms_output.put_line('■INDEX比較');
 --INDEX比較増分
 begin
     dbms_output.put_line('比較先スキーマにのみ存在するINDEX確認');
     select count(*) into o_count from (select index_name from dba_indexes
                                                          where owner = r_nam
                                                          and uniqueness = 'NONUNIQUE'
                                        minus   
                                        select index_name from dba_indexes
                                                          where owner = o_nam
                                                          and uniqueness = 'NONUNIQUE'
                                        );
     case o_count
         when 0 then
             dbms_output.put_line('  OK:比較先スキーマにのみ存在するINDEXはありません');
         else
             dbms_output.put_line('  NG:以下のINDEXが比較先スキーマに存在しています');
             for car1 in (select index_name from dba_indexes
                                            where owner = r_nam
                                            and uniqueness = 'NONUNIQUE'
                          minus   
                          select index_name from dba_indexes
                                            where owner = o_nam
                                            and uniqueness = 'NONUNIQUE'
                          )
             loop
                 dbms_output.put_line( '   ' || car1.index_name );
             end loop;
     end case;
 end;
 --INDEX比較差分
 begin
     dbms_output.put_line('比較先スキーマに存在しないINDEX確認');
     select count(*) into o_count from (select index_name from dba_indexes
                                                          where owner = o_nam
                                                          and uniqueness = 'NONUNIQUE'
                                        minus   
                                        select index_name from dba_indexes
                                                          where owner = r_nam
                                                          and uniqueness = 'NONUNIQUE'
                                        );
     case o_count
         when 0 then
             dbms_output.put_line('  OK:比較先スキーマにのみ存在するINDEXはありません');
         else
             dbms_output.put_line('  NG:以下のINDEXが比較先スキーマに足りていません');
             for car1 in (select index_name from dba_indexes
                                            where owner = o_nam
                                            and uniqueness = 'NONUNIQUE'
                          minus   
                          select index_name from dba_indexes
                                            where owner = r_nam
                                            and uniqueness = 'NONUNIQUE'
                          )
             loop
                 dbms_output.put_line( '   ' || car1.index_name );
             end loop;
     end case;
 end;
 --TRIGGER確認
 dbms_output.put_line('■TRIGGER比較');
 --TRIGGER比較増分
 begin
     dbms_output.put_line('比較先スキーマにのみ存在するTRIGGER確認');
     select count(*) into o_count from (select trigger_name from dba_triggers
                                                            where owner = r_nam
                                        minus
                                        select trigger_name from dba_triggers
                                                            where owner = o_nam
                                        );
     case o_count
         when 0 then
             dbms_output.put_line('  OK:比較先スキーマにのみ存在するTRIGGERはありません');
         else
             dbms_output.put_line('  NG:以下のTRIGGERが比較先スキーマに存在しています');
             for car1 in (select trigger_name from dba_triggers
                                              where owner = r_nam
                          minus
                          select trigger_name from dba_triggers
                                              where owner = o_nam
                          )
             loop
                 dbms_output.put_line( '   ' || car1.trigger_name );
             end loop;
     end case;
 end;
 --TRIGGER比較差分
 begin
     dbms_output.put_line('比較先スキーマに存在しないTRIGGER確認');
     select count(*) into o_count from (select trigger_name from dba_triggers
                                                            where owner = o_nam
                                        minus
                                        select trigger_name from dba_triggers
                                                            where owner = r_nam
                                        );
     case o_count
         when 0 then
             dbms_output.put_line('  OK:比較先スキーマに存在しないTRIGGERはありません');
         else
             dbms_output.put_line('  NG:以下のTRIGGERが比較先スキーマに足りていません');
             for car1 in (select trigger_name from dba_triggers
                                              where owner = o_nam
                          minus
                          select trigger_name from dba_triggers
                                              where owner = r_nam
                          )
             loop
                 dbms_output.put_line( '   ' || car1.trigger_name );
             end loop;
     end case;
 end;
 --FUNCTION確認
 dbms_output.put_line('■FUNCTION比較');
 --FUNCTION比較増分
 begin
     dbms_output.put_line('比較先スキーマにのみ存在するFUNCTION確認');
     select count(*) into o_count from (select object_name from dba_objects
                                                           where owner = r_nam
                                                           and object_type = 'FUNCTION'
                                        minus
                                        select object_name from dba_objects
                                                           where owner = o_nam
                                                           and object_type = 'FUNCTION'
                                        );
     case o_count
         when 0 then
             dbms_output.put_line('  OK:比較先スキーマにのみ存在するFUNCTIONはありません');
         else
             dbms_output.put_line('  NG:以下のFUNCTIONが比較先スキーマに存在しています');
             for car1 in (select object_name from dba_objects
                                             where owner = r_nam
                                             and object_type = 'FUNCTION'
                          minus
                          select object_name from dba_objects
                                             where owner = o_nam
                                             and object_type = 'FUNCTION'
                          )
             loop
                 dbms_output.put_line( '   ' || car1.object_name );
             end loop;
     end case;
 end;
 --FUNCTION比較差分
 begin
     dbms_output.put_line('比較先スキーマに存在しないFUNCTION確認');
     select count(*) into o_count from (select object_name from dba_objects
                                                           where owner = o_nam
                                                           and object_type = 'FUNCTION'
                                        minus
                                        select object_name from dba_objects
                                                           where owner = r_nam
                                                           and object_type = 'FUNCTION'
                                        );
     case o_count
         when 0 then
             dbms_output.put_line('  OK:比較先スキーマに存在しないFUNCTIONはありません');
         else
             dbms_output.put_line('  NG:以下のFUNCTIONが比較先スキーマに足りていません');
             for car1 in (select object_name from dba_objects
                                             where owner = o_nam
                                             and object_type = 'FUNCTION'
                          minus
                          select object_name from dba_objects
                                             where owner = r_nam
                                             and object_type = 'FUNCTION'
                          )
             loop
                 dbms_output.put_line( '   ' || car1.object_name );
             end loop;
     end case;
 end;
end;
/