【Oracle】オブジェクト情報抽出（プロシージャー、ファンクション）
オブジェクト一覧
SELECT OBJECT_NAME ,PROCEDURE_NAME ,OBJECT_TYPE ,AGGREGATE ,PIPELINED ,PARALLEL ,INTERFACE ,DETERMINISTIC ,AUTHID FROM USER_PROCEDURES --WHERE OBJECT_NAME IN ('TEST') ORDER BY OBJECT_NAME,PROCEDURE_NAME
オブジェクト引数一覧
SELECT PACKAGE_NAME ,OBJECT_NAME ,POSITION ,ARGUMENT_NAME ,DATA_TYPE ,DEFAULTED ,DEFAULT_VALUE ,DEFAULT_LENGTH ,IN_OUT ,DATA_LENGTH ,CHARACTER_SET_NAME ,TYPE_NAME ,TYPE_SUBNAME FROM USER_ARGUMENTS WHERE --OBJECT_NAME IN ('TEST') AND DATA_LEVEL <= 1 AND POSITION > 0 order by PACKAGE_NAME,object_name,overload,DATA_LEVEL,position
両方ともオブジェクトが多いときは、名前とかで絞り込むといい。
【Oracle】ユーザーテーブルの情報抽出
必要なオブジェクトだけ欲しい場合は、コメントのところで条件を指定する
SELECT T.TABLE_NAME AS "テーブル物理名", TC.COMMENTS AS "テーブル論理名", T.tablespace_name AS "テーブルスペース名", C.COLUMN_ID AS "カラムID", CASE WHEN PK.COLUMN_POSITION IS NOT NULL THEN PK.COLUMN_POSITION ELSE NULL END AS "PK", C.COLUMN_NAME AS "カラム物理名", CC.COMMENTS AS "カラム論理名", C.DATA_TYPE AS "データタイプ", CASE WHEN C.DATA_PRECISION IS NOT NULL THEN '(' || C.DATA_PRECISION || '.' || C.DATA_SCALE || ')' ELSE TO_CHAR(C.DATA_LENGTH) END AS "データ長", C.NULLABLE AS "NULL可", C.DATA_DEFAULT AS "デフォルト値" FROM USER_TABLES T INNER JOIN USER_TAB_COMMENTS TC ON ( T.TABLE_NAME = TC.TABLE_NAME ) INNER JOIN USER_TAB_COLUMNS C ON ( T.TABLE_NAME = C.TABLE_NAME ) INNER JOIN USER_COL_COMMENTS CC ON ( T.TABLE_NAME = CC.TABLE_NAME AND C.COLUMN_NAME = CC.COLUMN_NAME ) LEFT JOIN ( SELECT IND.INDEX_NAME, CONST.TABLE_NAME, IND.COLUMN_NAME, IND.COLUMN_POSITION FROM USER_IND_COLUMNS IND INNER JOIN USER_CONSTRAINTS CONST ON ( IND.INDEX_NAME = CONST.CONSTRAINT_NAME AND CONSTRAINT_TYPE = 'P' ) ) PK ON ( T.TABLE_NAME = PK.TABLE_NAME AND C.COLUMN_NAME = PK.COLUMN_NAME ) /*WHERE T.TABLE_NAME='AAAA'*/ ORDER BY T.TABLE_NAME, C.COLUMN_ID
【Oracle】オブジェクトのCREATE文を自動作成する
DB移行したいときとかに
使ってみるといいかもしれない。
ただ、ここで紹介する呼び出しはvbscriptでADODB使ってるので
その環境がない人は別の接続方法で試してほしい。
「SQL_DDL.vbs」として以下のソースコードをDB接続先情報を記述して保存する
Option Explicit ' ユーザ設定する項目 (DB 接続先情報) Const HOST = "ホストIPアドレス" Const PORT = "ポート番号" Const SERVICE = "サービス名" Const USER_ID = "ユーザー" Const PASSWORD = "パスワード" Dim respond, eLevel MAIN() ' コンソール画面のメイン処理 Sub EXECUTE_SQL(sql,dr,tm_flg) 'On Error Resume Next Dim con Set con = CreateObject("ADODB.Connection") Dim conStr conStr = "Provider=OraOLEDB.Oracle;" & _ "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=" & HOST & ")(PORT=" & PORT & ")))(CONNECT_DATA=(SERVICE_NAME=" & SERVICE & ")));" & _ "User Id=" & USER_ID & ";Password=" & PASSWORD & ";" con.CommandTimeout = 3000 con.ConnectionString = conStr con.Open Dim rs Set rs = con.Execute(sql) if Err.number = 0 then do until rs.eof=true if tm_flg="1" then 'タイムスタンプをファイルにつける WRITETXT dr & "\" & rs("TM_STAMP") & "_" & rs("OB_NAME") & ".sql",REPLACE(TRIM(cstr(rs("DDL"))),"""" & USER_ID & """" & ".","") else WRITETXT dr & "\" & rs("OB_NAME") & ".sql",REPLACE(TRIM(cstr(rs("DDL"))),"""" & USER_ID & """" & ".","") end if rs.MoveNext 'カーソルを進める loop else WScript.Echo( "エラーが発生しました！:" & Err.Description ) end if Set rs = Nothing con.Close Set con = Nothing End Sub Function READTXT(filename) READTXT = " SELECT DBMS_METADATA.GET_DDL(OBJECT_TYPE, OBJECT_NAME, '" & USER_ID & "') DDL," & _ "'" & USER_ID & "',OBJECT_TYPE || '_' || OBJECT_NAME AS OB_NAME,TO_CHAR(TO_DATE(TIMESTAMP,'yyyy-mm-dd:hh24:mi:ss'),'yyyymmdd_hh24miss') AS TM_STAMP " & _ " FROM USER_OBJECTS " & _ " WHERE " & _ " OBJECT_TYPE IN ('FUNCTION','PROCEDURE','TABLE','VIEW','INDEX','PACKAGE','TRIGGER') " & _ " ORDER BY OBJECT_TYPE,OBJECT_NAME " End Function SUB WRITETXT(filename,txt) Dim fso Dim outputFile Set fso = WScript.CreateObject("Scripting.FileSystemObject") ' 書き出しファイルの指定 (今回は新規作成する) Set outputFile = fso.OpenTextFile(filename, 2, True) outputFile.Write txt outputFile.Close Set outputFile = Nothing Set fso = Nothing END SUB Sub MAIN() Dim args, arg(99), v, cnt Dim str1, str2 Dim mode Dim CRLF REM 改行符号を設定します。 CRLF = chr(13) & chr(10) REM 引数の設定 Set args = WScript.Arguments cnt = 1 For Each v In args arg(cnt) = v cnt = cnt + 1 Next eLevel = 0: REM エラーが発生したときに値を設定します。 respond = "": REM 呼び出し元への戻り値を設定します。 REM -------------------------------- Dim sql 'msgbox args(0) sql = READTXT("") EXECUTE_SQL sql,args(0),args(1) REM -------------------------------- REM 結果の表示 WScript.Echo respond WScript.Quit (eLevel) REM --------------------ここまで End Sub
これをコマンドプロンプトで呼び出してみる。
最後の引数を1にするとファイル名がタイムスタンプ付きになる
@SET VBSCMD=SQL_DDL.vbs @echo 日付の設定 date /t set dt=%date% set d0=%dt:~0,1% set d1=%dt:~1,1% set d2=%dt:~2,1% set d3=%dt:~3,1% set d4=%dt:~5,1% set d5=%dt:~6,1% set d6=%dt:~8,1% set d7=%dt:~9,1% @echo 時間の設定 time /t @set tm=%time% @set t0=%tm:~0,1% @set t0=%t0: =0% @set t1=%tm:~1,1% @set t2=%tm:~3,1% @set t3=%tm:~4,1% @set t4=%tm:~6,1% @set t5=%tm:~7,1% @set t6=%tm:~9,1% @set t7=%tm:~10,1% @echo 時間文字列の出力 @set fname=%d0%%d1%%d2%%d3%%d4%%d5%%d6%%d7%%t0%%t1%%t2%%t3%%t4%%t5%%t6%%t7% @timeout /T 2 /NOBREAK MKDIR %fname% @timeout /T 2 /NOBREAK cscript //nologo %VBSCMD% %fname% 0 @timeout /T 2 /NOBREAK MKDIR %fname%TIME @timeout /T 2 /NOBREAK cscript //nologo %VBSCMD% %fname%TIME 1 @timeout /T 2 /NOBREAK PAUSE
SELECT DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME,'SQLTEST') DDL, 'SQLTEST', OBJECT_TYPE || '_' || OBJECT_NAME AS OB_NAME, TO_CHAR(TO_DATE(TIMESTAMP,'yyyy-mm-dd:hh24:mi:ss'),'yyyymmdd_hh24miss') AS TM_STAMP FROM USER_OBJECTS WHERE OBJECT_TYPE IN ( 'FUNCTION', 'PROCEDURE', 'TABLE', 'VIEW', 'INDEX', 'PACKAGE', 'TRIGGER' ) ORDER BY OBJECT_TYPE, OBJECT_NAME
出力時の文字化けとかあったら不安だ。
【Oracle】DBMS APIでCSVを出力してみる
DBMS APIを使って、CSVの出力するようなファンクションを作ってみた。
SQL文を引数にして、テキストが吐き出せるので
何かしら便利に使えそうだとは思っている。
とりあえず、適当な型を用意しよう。
CREATE OR REPLACE TYPE STD_VALUE4 AS OBJECT ( VALUE01 VARCHAR2(4000), VALUE02 VARCHAR2(4000), VALUE03 VARCHAR2(4000), VALUE04 VARCHAR2(4000) ); / CREATE OR REPLACE TYPE STD_VALUE4_TABLE IS TABLE OF STD_VALUE4;
まずはプロシージャーを作る。
カーソルをIN OUTで定義するのがミソのようだ。
CREATE OR REPLACE PROCEDURE SP_TEXT_OUT ( /*カーソル*/ REF_CUR IN OUT SYS_REFCURSOR, /*区切る文字 デフォルトは無し*/ DELIM_STR VARCHAR DEFAULT '', /*ヘッダーの有無 0:つけない（デフォルト) 1:つける*/ HEAD_FLG NUMBER DEFAULT 0, STD_ARRY OUT STD_VALUE4_TABLE, OUT_STS OUT VARCHAR2, OUT_MSG OUT VARCHAR2 ) IS /*レコード型*/ REC_VALUE STD_VALUE4; /*定義表*/ DESC_TBL DBMS_SQL.DESC_TAB; /*カーソルのカラム数*/ COL_COUNT NUMBER(3); /*カーソルID*/ CUR_NUM NUMBER(38); /*一時的に格納する文字列*/ TEMP_STR VARCHAR(255); TEMP_NUM NUMBER(38,10); TEMP_DATE DATE; /*レコード型に入れる文字列*/ T_STR VARCHAR(4000); /*行位置 ヘッダーは0になる*/ TEXT_CNT NUMBER(38) := 0; BEGIN /*初期化*/ OUT_STS := 'OK'; OUT_MSG := ''; T_STR := ''; TEMP_STR := ''; REC_VALUE := STD_VALUE4(' ',' ',' ',' '); STD_ARRY := STD_VALUE4_TABLE (); CUR_NUM := DBMS_SQL.TO_CURSOR_NUMBER(REF_CUR); /*カーソルで取得した時の定義情報を取得する*/ DBMS_SQL.DESCRIBE_COLUMNS(CUR_NUM,COL_COUNT,DESC_TBL); /*定義情報を展開する*/ FOR I IN 1..COL_COUNT LOOP /*VARCHAR2*/ IF ( DESC_TBL(I).COL_TYPE = 1 ) THEN DBMS_SQL.DEFINE_COLUMN(CUR_NUM,I,TEMP_STR,4000); /*NUMBER*/ ELSIF ( DESC_TBL(I).COL_TYPE = 2 ) THEN DBMS_SQL.DEFINE_COLUMN(CUR_NUM,I,TEMP_NUM); /*DATE*/ ELSIF ( DESC_TBL(I).COL_TYPE = 12 ) THEN DBMS_SQL.DEFINE_COLUMN(CUR_NUM,I,TEMP_DATE); /*その他*/ ELSE DBMS_SQL.DEFINE_COLUMN(CUR_NUM,I,TEMP_STR,4000); END IF; /*ヘッダー情報を無関係に取得しておく*/ TEMP_STR := DESC_TBL(I).COL_NAME; /*NULLだったら、項目は連結させない。*/ IF TRIM(TEMP_STR) IS NOT NULL THEN T_STR := T_STR || TEMP_STR; END IF; /*最後の要素以外はデルミタ文字をつける*/ IF I < COL_COUNT THEN /* NULLであればつけない*/ IF DELIM_STR IS NOT NULL THEN T_STR := T_STR || DELIM_STR; END IF; END IF; END LOOP; /*1の場合、要素を格納する*/ IF HEAD_FLG = 1 THEN /*要素追加*/ REC_VALUE.VALUE01 := TO_CHAR(TEXT_CNT); REC_VALUE.VALUE02 := T_STR; REC_VALUE.VALUE03 := LENGTH(T_STR); STD_ARRY.EXTEND; STD_ARRY(STD_ARRY.COUNT) := REC_VALUE; END IF; /*フェッチでループ*/ WHILE DBMS_SQL.FETCH_ROWS(CUR_NUM) > 0 LOOP /* 再初期化 */ T_STR := ''; TEMP_STR := ''; TEMP_NUM := 0; TEMP_DATE := NULL; REC_VALUE := STD_VALUE4(' ',' ',' ',' '); TEXT_CNT := TEXT_CNT + 1; FOR I IN 1..COL_COUNT LOOP /*定義情報を利用してカーソル内のフィールドの値を取得する*/ /*VARCHAR2*/ IF ( DESC_TBL(I).COL_TYPE = 1 ) THEN DBMS_SQL.COLUMN_VALUE(CUR_NUM,I,TEMP_STR); /*NUMBER 文字列に変換する*/ ELSIF ( DESC_TBL(I).COL_TYPE = 2 ) THEN DBMS_SQL.COLUMN_VALUE(CUR_NUM,I,TEMP_NUM); TEMP_STR := TO_CHAR(TEMP_NUM); /*DATE YYYY/MM/DDの形式にする*/ ELSIF ( DESC_TBL(I).COL_TYPE = 12 ) THEN DBMS_SQL.COLUMN_VALUE(CUR_NUM,I,TEMP_DATE); TEMP_STR := TO_CHAR(TEMP_DATE,'YYYY/MM/DD'); /*その他*/ ELSE DBMS_SQL.COLUMN_VALUE(CUR_NUM,I,TEMP_STR); END IF; /*TRIMかけてNULLだった場合、項目は連結させない。*/ IF TRIM(TEMP_STR) IS NOT NULL THEN T_STR := T_STR || TEMP_STR; END IF; /*最後の要素以外は区切り文字を付加する*/ IF I < COL_COUNT THEN /* 区切り文字はNULLだったら付けない。*/ IF DELIM_STR IS NOT NULL THEN T_STR := T_STR || DELIM_STR; END IF; END IF; END LOOP; /*要素追加*/ REC_VALUE.VALUE01 := TEXT_CNT; REC_VALUE.VALUE02 := T_STR; REC_VALUE.VALUE03 := LENGTH(T_STR); STD_ARRY.EXTEND; STD_ARRY(STD_ARRY.COUNT) := REC_VALUE; END LOOP; /*カーソルクローズ*/ DBMS_SQL.CLOSE_CURSOR(CUR_NUM); OUT_STS := 'OK'; OUT_MSG := ''; /*終了*/ RETURN; /*エラー*/ EXCEPTION WHEN OTHERS THEN /*念のためカーソルをオープンしてた場合は閉じる*/ IF REF_CUR%ISOPEN THEN DBMS_SQL.CLOSE_CURSOR(CUR_NUM); END IF; /*エラー内容追加*/ OUT_STS := 'NG'; OUT_MSG := SQLERRM; RETURN; END;
上記のプロシージャーを呼び出してパイプラインのファンクションにする。
CREATE OR REPLACE FUNCTION FUNC_TEXT_OUT ( /*SQL文*/ STRSQL VARCHAR2, /*区切る文字 デフォルトは無し*/ DELIM_STR VARCHAR2 DEFAULT '', /*ヘッダーの有無 0:つけない（デフォルト) 1:つける*/ HEAD_FLG NUMBER DEFAULT 0 ) RETURN STD_VALUE4_TABLE PIPELINED IS /*レコード型*/ REC_VALUE STD_VALUE4; /*戻り値のコレクション*/ STD_ARRY STD_VALUE4_TABLE; /*カーソル*/ REF_CUR SYS_REFCURSOR; /*レコード型に入れる文字列*/ W_STR VARCHAR2(4000); /*プロシージャー結果*/ OUT_STS VARCHAR2(4000); OUT_MSG VARCHAR2(4000); BEGIN /*初期化*/ REC_VALUE := STD_VALUE4(' ',' ',' ',' '); /*カーソルオープン*/ OPEN REF_CUR FOR STRSQL; SP_TEXT_OUT(REF_CUR,DELIM_STR,HEAD_FLG,STD_ARRY,OUT_STS,OUT_MSG); IF OUT_STS = 'OK' THEN FOR I IN 1..STD_ARRY.COUNT LOOP PIPE ROW ( STD_ARRY(I) ); END LOOP; ELSE /*エラー内容追加*/ REC_VALUE.VALUE04 := OUT_MSG; PIPE ROW ( REC_VALUE ); RETURN; END IF; /*終了*/ RETURN; /*エラー*/ EXCEPTION WHEN OTHERS THEN /*念のためカーソルをオープンしてた場合は閉じる*/ IF REF_CUR%ISOPEN THEN CLOSE REF_CUR; END IF; /*エラー内容追加*/ REC_VALUE.VALUE04 := SQLERRM; PIPE ROW ( REC_VALUE ); RETURN; END;
CSV出力よりも、もっと他の何かに役に立ちそうな気がするな。
【Oracle】DBMS APIでテーブル定義型の確認
SQL文字列を引数にして、そのSQL文字列のテーブル構成情報を出力する。
既存プログラムのSQLのフィールド解析とかで使えそう。
まずは型を適当に作成
CREATE OR REPLACE TYPE VALUE10 AS OBJECT ( VALUE01 VARCHAR2(500), VALUE02 VARCHAR2(500), VALUE03 VARCHAR2(500), VALUE04 VARCHAR2(500), VALUE05 VARCHAR2(500), VALUE06 VARCHAR2(500), VALUE07 VARCHAR2(500), VALUE08 VARCHAR2(500), VALUE09 VARCHAR2(500), VALUE10 VARCHAR2(500) ); / CREATE OR REPLACE TYPE VALUE10_TABLE IS TABLE OF VALUE10;
ファンクションを型にあわせて作成。
CREATE OR REPLACE FUNCTION FNC_SQL_CHK ( STRSQL VARCHAR2 ) RETURN VALUE10_TABLE PIPELINED IS /*定義表*/ DESC_TBL DBMS_SQL.DESC_TAB; /*カーソルのカラム数*/ COL_COUNT NUMBER(3); /*カーソルID*/ CUR_NUM NUMBER(38); /*カーソル*/ REF_CUR SYS_REFCURSOR; /*レコード型*/ W_RET_REC VALUE10; BEGIN /*カーソルオープン*/ OPEN REF_CUR FOR STRSQL; /*カーソルID取得*/ CUR_NUM := DBMS_SQL.TO_CURSOR_NUMBER(REF_CUR); /*カーソルで取得した時の定義情報を取得する*/ DBMS_SQL.DESCRIBE_COLUMNS(CUR_NUM,COL_COUNT,DESC_TBL); /*定義情報を展開する*/ FOR I IN 1..COL_COUNT LOOP /*初期化*/ W_RET_REC := VALUE10(' ',' ',' ',' ',' ',' ',' ',' ',' ',' '); /*情報代入*/ /*列名*/ W_RET_REC.VALUE01 := DESC_TBL(I).COL_NAME; /*タイプ（数字）*/ W_RET_REC.VALUE02 := TO_CHAR(DESC_TBL(I).COL_TYPE); /*タイプ（文字列）*/ W_RET_REC.VALUE03 := CASE DESC_TBL(I).COL_TYPE WHEN 1 THEN 'VARCHAR2' WHEN 2 THEN 'NUMBER' WHEN 12 THEN 'DATE' ELSE 'その他' END; /*最大長*/ W_RET_REC.VALUE04 := TO_CHAR(DESC_TBL(I).COL_MAX_LEN); /*精度*/ W_RET_REC.VALUE05 := TO_CHAR(DESC_TBL(I).COL_PRECISION); /*列名の長さ*/ W_RET_REC.VALUE06 := TO_CHAR(DESC_TBL(I).COL_NAME_LEN); /*NULL許可*/ W_RET_REC.VALUE07 := CASE DESC_TBL(I).col_null_ok WHEN TRUE THEN 'TRUE' ELSE 'FALSE' END; PIPE ROW ( W_RET_REC ); END LOOP; /*終了*/ RETURN; /*エラー*/ EXCEPTION WHEN OTHERS THEN /*念のためカーソルをオープンしてた場合は閉じる*/ IF REF_CUR%ISOPEN THEN CLOSE REF_CUR; END IF; /*エラー内容追加*/ /*初期化*/ W_RET_REC := VALUE10(' ',' ',' ',' ',' ',' ',' ',' ',' ',' '); W_RET_REC.VALUE10 := SQLERRM; PIPE ROW ( W_RET_REC ); RETURN; END;
列名の長さって必要なのかな？
なんか無駄に存在している気がする。
【Oracle】トリガーでログを記録する為のテーブルとトリガーのプロシージャーの作成
ダミーのようなテーブルをあらかじめ作成して
テーブルにデータが追加、更新、削除が行われたとき、
そこに変更ログを記録するようなことができると、
更新以前の状況の更新過程が分かるようになるので便利かもしれない。
そんなダミーテーブルの作成と、更新を記録するトリガーを作成する為の
プロシージャーを載せた。
ただ、このプロシージャーは、対象にするテーブルに主キーを設定しておかないと、作られないようにしてある。
適当にログテーブルを作って、人的にテーブルスペースを圧迫してほしくないので、
主キーのあるテーブルのみ有効ということにしておいた。
まずは、トリガーの自動作成プロシージャー。
CREATE OR REPLACE PROCEDURE CREATE_LOG_TRIGGER ( STR_TABLE IN VARCHAR2, OUT_STS OUT VARCHAR2, OUT_MSG OUT VARCHAR2 ) AS CURSOR REC_CUR IS SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME = STR_TABLE; TYPE REC_TYPE IS TABLE OF REC_CUR%ROWTYPE; CURSOR REC_CONS IS SELECT COLUMN_NAME FROM USER_CONS_COLUMNS WHERE TABLE_NAME = STR_TABLE AND CONSTRAINT_NAME IN ( SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = STR_TABLE AND CONSTRAINT_TYPE = 'P' ); TYPE REC_TYPE2 IS TABLE OF REC_CONS%ROWTYPE; CURSOR REC_COLUMNS IS SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME = STR_TABLE; TYPE REC_TYPE3 IS TABLE OF REC_COLUMNS%ROWTYPE; B_WRET_REC REC_TYPE; A_WRET_REC REC_TYPE2; C_WRET_REC REC_TYPE3; W_COUNT NUMBER(10); TRG_NAME VARCHAR2(100); LOG_TBL_NAME VARCHAR2(100); STRSQL CLOB; W_CRLF VARCHAR2(4) := CHR(13) || CHR(10); BEGIN OUT_STS := ' '; OUT_MSG := ' '; LOG_TBL_NAME := STR_TABLE || '_LOG'; TRG_NAME := STR_TABLE || '_LOG_TRG'; OPEN REC_COLUMNS; FETCH REC_COLUMNS BULK COLLECT INTO C_WRET_REC; CLOSE REC_COLUMNS; OPEN REC_CUR; FETCH REC_CUR BULK COLLECT INTO B_WRET_REC; CLOSE REC_CUR; OPEN REC_CONS; FETCH REC_CONS BULK COLLECT INTO A_WRET_REC LIMIT 100; CLOSE REC_CONS; /*主キーがない場合は作らない*/ IF A_WRET_REC.COUNT = 0 THEN OUT_STS := 'NONE'; OUT_MSG := '主キーなし'; RETURN; END IF; STRSQL := 'CREATE OR REPLACE TRIGGER ' || TRG_NAME || ' ' || W_CRLF || 'AFTER INSERT OR DELETE OR UPDATE ON ' || STR_TABLE || ' ' || W_CRLF || 'FOR EACH ROW' || W_CRLF || W_CRLF || 'DECLARE' || W_CRLF || W_CRLF || 'W_REC ' || LOG_TBL_NAME || '%ROWTYPE; ' || W_CRLF || 'W_REC_OLD ' || LOG_TBL_NAME || '%ROWTYPE; ' || W_CRLF || 'W_INS_FLG VARCHAR2(50); ' || W_CRLF || W_CRLF || '--' || STR_TABLE || '更新ログ出力トリガー:' || TRG_NAME || W_CRLF || '--※このトリガーは自動生成されています。' || W_CRLF || '-- トリガー名を絶対に変更しないこと！！' || W_CRLF || W_CRLF /*行トリガー作成*/ || '--行トリガー' || W_CRLF; STRSQL := STRSQL || 'BEGIN' || W_CRLF || W_CRLF || 'IF (INSERTING) THEN ' || W_CRLF || W_CRLF || 'W_INS_FLG := ''INSERT'';' || W_CRLF || W_CRLF || 'END IF; ' || W_CRLF || W_CRLF || 'IF (UPDATING) THEN ' || W_CRLF || W_CRLF || 'W_INS_FLG := ''UPDATE'';' || W_CRLF || W_CRLF || 'END IF; ' || W_CRLF || W_CRLF || 'IF (DELETING) THEN ' || W_CRLF || W_CRLF || 'W_INS_FLG := ''DELETE'';' || W_CRLF || W_CRLF || 'END IF; ' || W_CRLF || W_CRLF || W_CRLF; FOR I IN 1..C_WRET_REC.COUNT LOOP STRSQL := STRSQL || 'W_REC.' || C_WRET_REC(I).COLUMN_NAME || ' := ' || ':NEW.' || C_WRET_REC(I).COLUMN_NAME || ';' || W_CRLF; END LOOP; STRSQL := STRSQL || W_CRLF || W_CRLF; FOR I IN 1..C_WRET_REC.COUNT LOOP STRSQL := STRSQL || 'W_REC_OLD.' || C_WRET_REC(I).COLUMN_NAME || ' := ' || ':OLD.' || C_WRET_REC(I).COLUMN_NAME || ';' || W_CRLF; END LOOP; STRSQL := STRSQL || W_CRLF || W_CRLF || 'IF W_INS_FLG = ''DELETE'' THEN ' || W_CRLF || W_CRLF || 'W_REC_OLD.LOG_ID :=TO_CHAR(SYSTIMESTAMP,''yyyymmddhh24missff3'') || ''-'' || DBMS_RANDOM.STRING(''U'',15);' || W_CRLF || 'W_REC_OLD.INS_FLG :=W_INS_FLG;' || W_CRLF || 'W_REC_OLD.INS_DATE :=TO_CHAR(SYSDATE,''YYYYMMDD'');' || W_CRLF || 'W_REC_OLD.INS_TIME :=TO_CHAR(SYSTIMESTAMP,''YYYY/MM/DD HH24;MI:SS.ff3'');' || W_CRLF || 'INSERT INTO ' || LOG_TBL_NAME || ' VALUES W_REC_OLD;' || W_CRLF || W_CRLF || 'ELSE ' || W_CRLF || W_CRLF || 'W_REC.LOG_ID :=TO_CHAR(SYSTIMESTAMP,''yyyymmddhh24missff3'') || ''-'' || DBMS_RANDOM.STRING(''U'',15);' || W_CRLF || 'W_REC.INS_FLG :=W_INS_FLG;' || W_CRLF || 'W_REC.INS_DATE :=TO_CHAR(SYSDATE,''YYYYMMDD'');' || W_CRLF || 'W_REC.INS_TIME :=TO_CHAR(SYSTIMESTAMP,''YYYY/MM/DD HH24;MI:SS.ff3'');' || W_CRLF || 'INSERT INTO ' || LOG_TBL_NAME || ' VALUES W_REC;' || W_CRLF || W_CRLF; STRSQL := STRSQL || 'END IF; ' || W_CRLF || W_CRLF; /*トリガー末尾*/ STRSQL := STRSQL || W_CRLF || 'END ' || TRG_NAME || ';'; EXECUTE IMMEDIATE STRSQL; OUT_STS := 'OK'; END CREATE_LOG_TRIGGER;
これ単体で実行しても意味がないので、
上記のプロシージャーがコンパイルされた上で、以下を実行する。
以下は、ダミーテーブルと上記のCREATE_LOG_TRIGGERを
実行するプロシージャー。
もちろんユーザーにトリガーとテーブルを作る権限がないと動かない。
CREATE OR REPLACE PROCEDURE LOG_TABLE_CREATE ( STR_TABLE IN VARCHAR2, OP_FLG IN NUMBER /*0:作成 1:削除*/ , OUT_STS OUT VARCHAR2, OUT_MSG OUT VARCHAR2 ) AS CURSOR REC_CUR IS SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME NOT LIKE '%_LOG' AND TABLE_NAME LIKE CASE NVL(STR_TABLE,' ') WHEN ' ' THEN TABLE_NAME ELSE STR_TABLE END; TYPE REC_TYPE IS TABLE OF REC_CUR%ROWTYPE; /*プライマリーキーを探す*/ CURSOR REC_CONS ( TB_NAME VARCHAR2 ) IS SELECT COLUMN_NAME FROM USER_CONS_COLUMNS WHERE TABLE_NAME = TB_NAME AND CONSTRAINT_NAME IN ( SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = TB_NAME AND CONSTRAINT_TYPE = 'P' ); TYPE REC_TYPE3 IS TABLE OF REC_CONS%ROWTYPE; B_WRET_REC REC_TYPE; C_WRET_REC REC_TYPE3; W_COUNT NUMBER(10); LOG_TABLE_NAME VARCHAR2(100); LOG_PKEY_NAME VARCHAR2(100); TRG_NAME VARCHAR2(100); BEGIN OUT_STS := ' '; OUT_MSG := ' '; OPEN REC_CUR; LOOP FETCH REC_CUR BULK COLLECT INTO B_WRET_REC LIMIT 100; EXIT WHEN B_WRET_REC.COUNT = 0; FOR I IN 1..B_WRET_REC.COUNT LOOP LOG_TABLE_NAME := B_WRET_REC(I).TABLE_NAME || '_LOG'; TRG_NAME := B_WRET_REC(I).TABLE_NAME || '_LOG_TRG'; LOG_PKEY_NAME := ' '; W_COUNT := 0; /*テーブルが見つからなければ削除処理はしない*/ SELECT COUNT(*) INTO W_COUNT FROM USER_TABLES WHERE TABLE_NAME = LOG_TABLE_NAME; IF W_COUNT > 0 THEN /*トリガーの存在を確認*/ W_COUNT := 0; SELECT COUNT(TRIGGER_NAME) INTO W_COUNT FROM USER_TRIGGERS WHERE TRIGGER_NAME = TRG_NAME; /*存在しているのであればトリガーを削除する*/ IF W_COUNT > 0 THEN /*トリガー削除*/ EXECUTE IMMEDIATE 'DROP TRIGGER ' || TRG_NAME; END IF; /*プライマリーキーがあるか確認*/ BEGIN SELECT CONSTRAINT_NAME INTO LOG_PKEY_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = LOG_TABLE_NAME AND CONSTRAINT_TYPE = 'P'; EXCEPTION WHEN OTHERS THEN LOG_PKEY_NAME := ' '; END; IF LOG_PKEY_NAME <> ' ' THEN /*プライマリーキー削除*/ EXECUTE IMMEDIATE 'ALTER TABLE ' || LOG_TABLE_NAME || ' DROP CONSTRAINT ' || LOG_PKEY_NAME; END IF; /*テーブル削除*/ EXECUTE IMMEDIATE 'DROP TABLE ' || LOG_TABLE_NAME; END IF; IF OP_FLG = 0 THEN /*元テーブルの主キーがあるかチェック*/ C_WRET_REC := REC_TYPE3 (); OPEN REC_CONS(B_WRET_REC(I).TABLE_NAME); FETCH REC_CONS BULK COLLECT INTO C_WRET_REC; CLOSE REC_CONS; /*主キーがなければログテーブルの作成はしない*/ IF C_WRET_REC.COUNT > 0 THEN /*テーブル作成*/ EXECUTE IMMEDIATE 'CREATE TABLE ' || LOG_TABLE_NAME || ' AS SELECT * FROM ' || B_WRET_REC(I).TABLE_NAME || ' WHERE ROWNUM = 0'; /*カラム追加*/ EXECUTE IMMEDIATE 'ALTER TABLE ' || LOG_TABLE_NAME || ' ADD ( ' || 'LOG_ID VARCHAR2(50 byte) NOT NULL,' || 'INS_FLG VARCHAR2(50 byte) NOT NULL,' || 'INS_DATE VARCHAR2(50 byte) NOT NULL,' || 'INS_TIME VARCHAR2(50 byte) NOT NULL' || ') '; /*主キー追加 */ EXECUTE IMMEDIATE 'ALTER TABLE ' || LOG_TABLE_NAME || ' ADD ' || 'CONSTRAINT ' || LOG_TABLE_NAME || '_PKEY ' || 'PRIMARY KEY (LOG_ID) '; /*トリガー作成*/ CREATE_LOG_TRIGGER(B_WRET_REC(I).TABLE_NAME,OUT_STS,OUT_MSG); END IF; END IF; END LOOP; END LOOP; CLOSE REC_CUR; END LOG_TABLE_CREATE;
これをSQLDEVELOPERのデバッグサンプルのSQLを引用して実行してみる。
と、その前に作成対象のテーブルにプライマリキーを貼る。
ちなみに使ったサンプルのテーブルのスクリプトは以下の記事にあるので
使いたければどうぞ。
fubukin.hatenablog.com
さて、では以下を実行してみる。
DECLARE STR_TABLE VARCHAR2(200); OP_FLG NUMBER; OUT_STS VARCHAR2(200); OUT_MSG VARCHAR2(200); BEGIN STR_TABLE := 'TABLE1'; OP_FLG := 0; LOG_TABLE_CREATE( STR_TABLE => STR_TABLE, OP_FLG => OP_FLG, OUT_STS => OUT_STS, OUT_MSG => OUT_MSG ); /* Legacy output: DBMS_OUTPUT.PUT_LINE('OUT_STS = ' || OUT_STS); */ :OUT_STS := OUT_STS; /* Legacy output: DBMS_OUTPUT.PUT_LINE('OUT_MSG = ' || OUT_MSG); */ :OUT_MSG := OUT_MSG; --rollback; END;
左が更新したらログを出力するテーブル、右がログを出力するテーブル
そして、自動作成されたトリガーは以下になる。
CREATE OR REPLACE TRIGGER TABLE1_LOG_TRG AFTER INSERT OR DELETE OR UPDATE ON TABLE1 FOR EACH ROW DECLARE W_REC TABLE1_LOG%ROWTYPE; W_REC_OLD TABLE1_LOG%ROWTYPE; W_INS_FLG VARCHAR2(50); --TABLE1更新ログ出力トリガー:TABLE1_LOG_TRG --※このトリガーは自動生成されています。 -- トリガー名を絶対に変更しないこと！！ --行トリガー BEGIN IF ( INSERTING ) THEN W_INS_FLG := 'INSERT'; END IF; IF ( UPDATING ) THEN W_INS_FLG := 'UPDATE'; END IF; IF ( DELETING ) THEN W_INS_FLG := 'DELETE'; END IF; W_REC.A1 :=:NEW.A1; W_REC.A2 :=:NEW.A2; W_REC.A3 :=:NEW.A3; W_REC.A4 :=:NEW.A4; W_REC.A5 :=:NEW.A5; W_REC.A6 :=:NEW.A6; W_REC.A7 :=:NEW.A7; W_REC.A8 :=:NEW.A8; W_REC.A9 :=:NEW.A9; W_REC.A10 :=:NEW.A10; W_REC.A11 :=:NEW.A11; W_REC.A12 :=:NEW.A12; W_REC_OLD.A1 :=:OLD.A1; W_REC_OLD.A2 :=:OLD.A2; W_REC_OLD.A3 :=:OLD.A3; W_REC_OLD.A4 :=:OLD.A4; W_REC_OLD.A5 :=:OLD.A5; W_REC_OLD.A6 :=:OLD.A6; W_REC_OLD.A7 :=:OLD.A7; W_REC_OLD.A8 :=:OLD.A8; W_REC_OLD.A9 :=:OLD.A9; W_REC_OLD.A10 :=:OLD.A10; W_REC_OLD.A11 :=:OLD.A11; W_REC_OLD.A12 :=:OLD.A12; IF W_INS_FLG = 'DELETE' THEN W_REC_OLD.LOG_ID := TO_CHAR(SYSTIMESTAMP,'yyyymmddhh24missff3') || '-' || DBMS_RANDOM.STRING('U',15); W_REC_OLD.INS_FLG := W_INS_FLG; W_REC_OLD.INS_DATE := TO_CHAR(SYSDATE,'YYYYMMDD'); W_REC_OLD.INS_TIME := TO_CHAR(SYSTIMESTAMP,'YYYY/MM/DD HH24;MI:SS.ff3'); INSERT INTO TABLE1_LOG VALUES W_REC_OLD; ELSE W_REC.LOG_ID := TO_CHAR(SYSTIMESTAMP,'yyyymmddhh24missff3') || '-' || DBMS_RANDOM.STRING('U',15); W_REC.INS_FLG := W_INS_FLG; W_REC.INS_DATE := TO_CHAR(SYSDATE,'YYYYMMDD'); W_REC.INS_TIME := TO_CHAR(SYSTIMESTAMP,'YYYY/MM/DD HH24;MI:SS.ff3'); INSERT INTO TABLE1_LOG VALUES W_REC; END IF; END TABLE1_LOG_TRG;
自動生成は作ってみると後々、手動で作る手間も時間も省けるから楽だね。
