全テーブルをチェックするPL/SQL

例えば「ある特定の日に更新されたデータを全て抜き出したい」という場合で使える(かもしれない)PL/SQLです。

自分の環境になりますがほとんどのテーブルに「更新年月日」という項目があります。ですからこの値を確認していけば特定の日に更新されたデータを抜き出す事は可能なわけですがいちいち全テーブルに対してSQLを発行するのは面倒なので作りました。

/*
ログインユーザが所有する全てのテーブルから
WK_FIELDの項目を持つテーブルを探し
WK_FIELDがWK_WORDであるデータ件数を取得する
*/
DECLARE
  WK_WORD VARCHAR2(500) := '20060810%';
  WK_FIELD VARCHAR2(500) := '更新年月日';

  SSQL VARCHAR2(500) :='';
  CNT NUMBER;

  -- カーソルに項目WK_FIELDを持つ全てのテーブルをセット
  CURSOR C1 IS
    SELECT TNAME FROM TAB T WHERE TABTYPE = 'TABLE'
    AND EXISTS (SELECT 'X'
      FROM SYS.ALL_TAB_COLS COL
      WHERE COLUMN_NAME = WK_FIELD
      AND T.TNAME = COL.TABLE_NAME  );


BEGIN
  FOR C1_REC IN C1 LOOP
    CNT := 0;
    SSQL := 'SELECT COUNT(*) FROM ' || C1_REC.TNAME || ' WHERE ' || WK_FIELD || ' LIKE ''' || WK_WORD || '''' ;
    --DBMS_OUTPUT.PUT_LINE(SSQL || 'INTO CNT');
    EXECUTE IMMEDIATE SSQL INTO CNT;
    IF CNT > 0 THEN
      DBMS_OUTPUT.PUT_LINE('件数:' || CNT  || '     テーブル:' || C1_REC.TNAME);
    END IF;
  END LOOP;
END;
/

例えが良くない気もしますがこの例では「更新年月日」という項目を持つテーブルを全てチェックし「更新年月日」が「2006年08月10日」であるテーブルと件数を出力しています。

結果は以下のように表示されます。

件数:7     テーブル:勘定科目マスタ
件数:5     テーブル:業務マスタ
PL/SQLが実行されました(297 msec.)

「最終更新ユーザー」が「legnum」であるデータが知りたい、という時でも使えます。以上、はてなのシンタックス・ハイライトを使った日記のテスト終わり。