INSERT文の自動生成

こじ

2年前

INSERT文を手作業で作ろうとすると、把握していないテーブルや、カラムが多いテーブルだと、INSERT文を作成するのに結構手間がかかると思います。

スキーマ名とテーブル名を指定すれば、自動でサンプル用のINSERT文を生成してくれるPLSQLを作ったので、使ってもらえると幸いです。

自動作成されたINSERT文のVALUESの値は自由に変更してもらえればと思います。

テーブル定義

TB_SAMPLEの定義は下記の通りです。

SQL> DESC TB_SAMPLE;
 名前                                      NULL?    型
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(10)
 NAME                                               VARCHAR2(600 CHAR)
 REGIST_DATE                                        DATE
 REGIST_TIMESTAMP                                   TIMESTAMP(6)
 BLOB_DATA                                          BLOB

INSERT文自動生成PLSQL

INSERT文を自動生成するPLSQLです。

宣言部のWK_OWNERにはスキーマ名を、WK_TABLE_NAMEにはテーブル名を指定して下さい。

カラム名と型によってINSERT文のVALUESの値を設定するようにしています。

SET SERVEROUTPUT ON
SET LINESIZE 32767
SET PAGESIZE 50000
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET TAB OFF
SET TIMING OFF
SET TRIMSPOOL ON

----------------------------------------
-- 宣言部
----------------------------------------
DECLARE

    -- 対象スキーマ名を指定して下さい
    WK_OWNER      VARCHAR2(30) := 'TEST';

    -- 対象テーブル名を指定して下さい
    WK_TABLE_NAME VARCHAR2(30) := 'TB_SAMPLE';

    -- カラム名の文字列連結用
    WK_COLUMNS   VARCHAR2(32767);
    -- VALUESの文字列連結用
    WK_VALUES    VARCHAR2(32767);
    -- DATA型の初期値
    WK_DATE      VARCHAR2(21) := TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS');
    -- TIMESTAMP型の初期値
    WK_TIMESTAMP VARCHAR2(28) := TO_CHAR(SYSTIMESTAMP, 'YYYY/MM/DD HH24:MI:SS.FF6');

    -- カーソルの宣言
    CURSOR CUR_SAMPLE(IN_OWNER IN VARCHAR2, IN_TABLE_NAME IN VARCHAR2) IS
    SELECT
        COLUMN_NAME
      , DATA_TYPE
    FROM
        ALL_TAB_COLUMNS
    WHERE
        OWNER      = IN_OWNER
    AND TABLE_NAME = IN_TABLE_NAME
    ORDER BY
        COLUMN_ID
    ;

    REC CUR_SAMPLE%ROWTYPE;

----------------------------------------
-- 実行部
----------------------------------------
BEGIN

    -- テーブル定義情報を取得
    OPEN CUR_SAMPLE(WK_OWNER, WK_TABLE_NAME);
    LOOP
        FETCH CUR_SAMPLE INTO REC;
        EXIT WHEN CUR_SAMPLE%NOTFOUND;

        -- INSERT文の列名を生成
        WK_COLUMNS := WK_COLUMNS || REC.COLUMN_NAME || ', ';
        
        -- VALUESの値をサンプル値で生成
        CASE
            -- 数値型
            WHEN REC.DATA_TYPE = 'NUMBER' THEN
                WK_VALUES := WK_VALUES || REC.COLUMN_NAME || ', ';
            -- DATE型
            WHEN REC.DATA_TYPE = 'DATE' THEN
                WK_VALUES := WK_VALUES || 'TO_DATE(''' || WK_DATE || ''', ''YYYY/MM/DD HH24:MI:SS''), ';
            -- TIMESTAMP型
            WHEN  REC.DATA_TYPE LIKE '%TIMESTAMP%' THEN
                WK_VALUES := WK_VALUES || 'TO_TIMESTAMP(''' || WK_TIMESTAMP || ''', ''YYYY/MM/DD HH24:MI:SS.FF6''), ';
            -- BLOB型
            WHEN  REC.DATA_TYPE = 'BLOB' THEN
                WK_VALUES := WK_VALUES || 'UTL_RAW.CAST_TO_RAW(''' || REC.COLUMN_NAME || '''), ';
            -- それ以外
            ELSE
                WK_VALUES := WK_VALUES || '''' || REC.COLUMN_NAME || ''', ';
        END CASE;
    END LOOP;
    CLOSE CUR_SAMPLE;

    DBMS_OUTPUT.PUT_LINE('INSERT INTO ' || WK_TABLE_NAME || ' (' || RTRIM(WK_COLUMNS, ', ') || ') VALUES (' || RTRIM(WK_VALUES, ', ') || ');');

----------------------------------------
-- 例外部
----------------------------------------
EXCEPTION
    WHEN OTHERS THEN
        -- 例外情報を出力
        DBMS_OUTPUT.PUT_LINE('エラーコード：'|| SQLCODE || ' ' || 'エラーメッセージ：' || SQLERRM(SQLCODE));
        CLOSE CUR_SAMPLE;
END;
/

自動生成したINSERT文

自動生成したINSERT文になります。VALUESの値は仮で設定しているので、自分で自由に編集して使って下さい。

INSERT INTO TB_SAMPLE (ID, NAME, REGIST_DATE, REGIST_TIMESTAMP, BLOB_DATA) VALUES (9, 'NAME', TO_DATE('2022/07/30 16:15:51', 'YYYY/MM/DD HH24:MI:SS'), TO_TIMESTAMP('2022/07/30 16:15:51.365000', 'YYYY/MM/DD HH24:MI:SS.FF6'), UTL_RAW.CAST_TO_RAW('BLOB_DATA'));