この記事ではINSERT文を自動生成する方法について解説します。
INSERT文を自動生成する方法
試験などでデータをINSERTしたいときにINSERT文を作成することがあると思います。
いちいちカラムを調べたり、データ型にあった値を入力する必要があったりと手作業で作るのはなかなか面倒です。
それが大量の表があり、すべての表のINSERT文を作成するとなると骨が折れます。
今回はスキーマに存在するすべての表に対して、自動でINSERT文を生成するPL/SQLを作成しましたのでご紹介します。
PL/SQL
こちらの記事を参考に、スキーマ単位ですべての表のINSERT文を生成するように改良しました。
https://kozirefer.com/plsql-auto-insert/
PL/SQLは以下のようになります。
SET SERVEROUTPUT ON
SET LINESIZE 20000
SET PAGESIZE 50000
DECLARE
WK_OWNER VARCHAR2(30) := '&owner_name';
WK_COLUMNS VARCHAR2(32767);
WK_VALUES VARCHAR2(32767);
CURSOR CUR_COLUMN(IN_OWNER IN VARCHAR2, IN_TABLE_NAME IN VARCHAR2) IS
SELECT
COLUMN_NAME, DATA_TYPE, DATA_LENGTH
FROM ALL_TAB_COLUMNS
WHERE OWNER = IN_OWNER AND TABLE_NAME = IN_TABLE_NAME
ORDER BY COLUMN_ID;
CURSOR CUR_TABLE(IN_OWNER IN VARCHAR2) IS
SELECT TABLE_NAME FROM ALL_TABLES
WHERE OWNER = IN_OWNER
ORDER BY TABLE_NAME;
REC1 CUR_COLUMN%ROWTYPE;
REC2 CUR_TABLE%ROWTYPE;
BEGIN
OPEN CUR_TABLE(WK_OWNER);
LOOP
FETCH CUR_TABLE INTO REC2;
EXIT WHEN CUR_TABLE%NOTFOUND;
WK_COLUMNS:='';
WK_VALUES:='';
OPEN CUR_COLUMN(WK_OWNER, REC2.TABLE_NAME);
LOOP
FETCH CUR_COLUMN INTO REC1;
EXIT WHEN CUR_COLUMN%NOTFOUND;
-- INSERT文の列名を生成
WK_COLUMNS := WK_COLUMNS || REC1.COLUMN_NAME || ', ';
-- VALUESの値を定義
CASE
-- 数値型
WHEN REC1.DATA_TYPE = 'NUMBER' THEN
WK_VALUES := WK_VALUES || 0 || ', ';
-- CHAR(14)型
WHEN REC1.DATA_TYPE = 'CHAR' and REC1.DATA_LENGTH=14 THEN
WK_VALUES := WK_VALUES || '''' || '20231221000000' || ''', ';
-- CHAR(8)型
WHEN REC1.DATA_TYPE = 'CHAR' and REC1.DATA_LENGTH=8 THEN
WK_VALUES := WK_VALUES || '''' || '20231221' || ''', ';
-- DATE型
WHEN REC1.DATA_TYPE = 'DATE' THEN
WK_VALUES := WK_VALUES || '''' || '20231221' || ''', ';
-- TIMESTAMP型
WHEN REC1.DATA_TYPE LIKE '%TIMESTAMP%' THEN
WK_VALUES := WK_VALUES || '''' || '20231221000000' || ''', ';
-- BLOB型
WHEN REC1.DATA_TYPE = 'BLOB' THEN
WK_VALUES := WK_VALUES || '''' || '' || ''', ';
-- それ以外
ELSE
WK_VALUES := WK_VALUES || '''' || 'A' || ''', ';
END CASE;
END LOOP;
CLOSE CUR_COLUMN;
DBMS_OUTPUT.PUT_LINE
('INSERT INTO ' || REC2.TABLE_NAME || ' VALUES (' || RTRIM(WK_VALUES, ', ') || ');');
END LOOP;
CLOSE CUR_TABLE;
END;
/使い方
まずはINSERT文を生成するうえで挿入するデータを「-- VALUESの値を定義」以降の式に入力します。
データ型とデータサイズの組み合わせで自由に値を設定することができます。
SQLを実行すると「owner_name」を聞かれるのでINSERT文を作成したいスキーマ名を入力します。
すると自動でINSERT文が生成されます。
実行例
TESTスキーマに対してPL/SQLを実行します。
SQL> @create_insert
owner_nameに値を入力してください: TEST
旧 2: WK_OWNER VARCHAR2(30) := '&owner_name';
新 2: WK_OWNER VARCHAR2(30) := 'TEST';
INSERT INTO CARD VALUES (0, 'A', 'A');
INSERT INTO DEPT VALUES (0, 'A', 'A');
INSERT INTO EMP VALUES (0, 'A', 'A', 0, '20231221', 0, 0, 0);
INSERT INTO EMP1 VALUES (0, 'A', 'A');
INSERT INTO EMP2 VALUES (0, 'A', 'A');
INSERT INTO ID VALUES ('A', 'A');
INSERT INTO SALES VALUES (0, 'A', '20231221');
PL/SQLプロシージャが正常に完了しました。INSERT文が自動で生成されました。