サイトアイコン PL/SQL日記

【PL/SQL】INSERT文の書き方:サンプル多数あり


ここではORACLE社が提供するPL/SQL言語でINSERT文の書き方について紹介しています。

PL/SQLの基本的な処理については↓で詳しく解説していますので参考にしてください。
>>PL/SQLの書き方

INSERT文の基本構文

INSERT文の基本的な構文・文法は次の通りです。

より詳しい解説は↓で紹介していますので参考にしてください。
>>【SQL】INSERT文の書き方:サンプル多数あり

INSERT INTO テーブル名(列名) VALUES (値);

INSERT文のサンプル

PL/SQLでのINSERT文の実行

PL/SQLでINSERT文を実行するときは、処理部(BEGIN~END)に実行したいINSERT文をそのまま記述します。

文末には、;(セミコロン)を記述します。

次の例では、処理部にINSERT文を1件実行しています。INSERT文を実行した後、コミットで確定しています。

SQL> CREATE OR REPLACE PROCEDURE PRO1
  2  IS
  3  BEGIN
  4        INSERT INTO tab1 VALUES('00001','Suzuki','D0001',28);
  5        COMMIT;
  6  END;
  7  /

プロシージャが作成されました。

実際にプロシージャをEXECUTE文で実行します。

実行結果として1件のレコードが登録されていることを確認できました。

SQL> EXECUTE PRO1;

PL/SQLプロシージャが正常に完了しました。

SQL> SELECT * FROM tab1;

EMP_ID     EMP_NAME             DEPT              AGE
---------- -------------------- ---------- ----------
00001      Suzuki               D0001              28

INSERT INTO-SELECT

PL/SQLではINSERT INTO~SELECT文を使うことで、別のある表のレコードを任意の表へ登録することが出来ます。

次の例では、tab1テーブルにtab1_bkのレコードを全件登録しています。列の型と個数、順序が完全に一致する場合は、列名の省略と「SELECT *」で登録することが出来ます。

SQL> CREATE OR REPLACE PROCEDURE PRO1
  2  IS
  3  BEGIN
  4        INSERT INTO tab1 SELECT * FROM tab1_bk;
  5        COMMIT;
  6  END;
  7  /

プロシージャが作成されました。

実行するとtab1_bkに登録されていたレコードを全てtab1に登録されました。

SQL> EXECUTE PRO1;

PL/SQLプロシージャが正常に完了しました。

SQL> SELECT * FROM tab1;

EMP_ID     EMP_NAME             DEPT              AGE
---------- -------------------- ---------- ----------
00001      Suzuki               D0001              28
00002      Tanaka               D0002              25
00003      Kizaki               D0002              35

次のように、列名を指定して、任意の値だけを登録することもできます。

INSERT INTO~SELECTの詳しい使い方は↓で解説していますので参考にしてください。
>>【SQL】INSERT-SELECTの使い方:サンプル多数あり

SQL> CREATE OR REPLACE PROCEDURE PRO1
  2  IS
  3  BEGIN
  4        INSERT INTO tab1(emp_id) SELECT emp_id FROM tab1_bk;
  5        COMMIT;
  6  END;
  7  /

プロシージャが作成されました。

SQL> EXECUTE PRO1;

PL/SQLプロシージャが正常に完了しました。

SQL> SELECT * FROM tab1;

EMP_ID     EMP_NAME             DEPT              AGE
---------- -------------------- ---------- ----------
00001
00002
00003

INSERT件数の取得

PL/SQLで実行した直前のINSERT文の処理結果件数は、ROWCOUNTという属性に格納されています。

このROWCOUNTは「SQL%ROWCOUNT」で取得出来ます。

次の例では、INSERT文で実行した件数をDBMS_OUTPUTパッケージで出力しています。

BEGIN
    INSERT INTO tab1 VALUES('00001','Suzuki','D0001',28);
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
    COMMIT;
END;
1
PL/SQLが実行されました(6 msec.)

複数テーブルへINSERTする方法

PL/SQLで複数のテーブルにINSERTする場合は、マルチテーブルインサートという機能を使います。

マルチテーブルインサートの基本構文は次の通りです。

INSERT ALL
 INTO テーブル名1(列名1...) VALUES(値1...)
 INTO テーブル名2(列名1...) VALUES(値2...)
SELECT * FROM dual;

次の例ではtab1とtab2の2つのテーブルに別々にレコードを1件ずつ登録しています。

SQL> CREATE OR REPLACE PROCEDURE PRO1
  2  IS
  3  BEGIN
  4        INSERT ALL
  5               INTO tab1 VALUES('00001','Suzuki','D0001',28)
  6               INTO tab2 VALUES('D0001','HR')
  7        SELECT * FROM dual;
  8        COMMIT;
  9  END;
 10  /

プロシージャが作成されました。

実行するとそれぞれのテーブルにレコードが登録されていることを確認できます。

SQL> EXECUTE PRO1

PL/SQLプロシージャが正常に完了しました。

SQL> SELECT * FROM tab1;

EMP_ID     EMP_NAME             DEPT              AGE
---------- -------------------- ---------- ----------
00001      Suzuki               D0001              28

SQL> SELECT * FROM tab2;

DEPT       DEPT_NAME
---------- --------------------
D0001      HR

複数レコードをINSERTする方法

複数テーブルへのINSERT機能である、マルチテーブルインサート機能を利用することで、1つのテーブルに複数のレコードを1文で登録することが出来ます。

次の例では、tab1テーブルに3件のレコードを1文で登録しています。

SQL> CREATE OR REPLACE PROCEDURE PRO1
  2  IS
  3  BEGIN
  4        INSERT ALL
  5               INTO tab1 VALUES('00001','Suzuki','D0001',28)
  6               INTO tab1 VALUES('00002','Tanaka','D0002',25)
  7               INTO tab1 VALUES('00003','Kizaki','D0002',35)
  8        SELECT * FROM dual;
  9        COMMIT;
 10  END;
 11  /

プロシージャが作成されました。

SQL> EXECUTE PRO1

PL/SQLプロシージャが正常に完了しました。

SQL> SELECT * FROM tab1;

EMP_ID     EMP_NAME             DEPT              AGE
---------- -------------------- ---------- ----------
00001      Suzuki               D0001              28
00002      Tanaka               D0002              25
00003      Kizaki               D0002              35

INSERT文に変数で値をセット

PL/SQLでINSERT文を実行する際、バインド変数を使ってVALUESの値をセットし、動的にSQLを実行することが出来ます。

動的SQLは、SQL文を構築してから、EXECUTE文で実行します。

SQLは文字列型で構築し、バインド変数は「:変数名」として記述しておきます。

実行時は次のように、USING IN以降にバインド変数にセットする変数名を列挙します。

EXECUTE IMMEDIATE SQL文 USING IN 変数1...;

次の例では、INSERT文に4つのバインド変数を設定し、SQLを実行しています。

SQL> CREATE OR REPLACE PROCEDURE PRO1
  2  IS
  3        emp_id VARCHAR2(5) := '00001';
  4        emp_name VARCHAR2(10) := 'Suzuki';
  5        dept VARCHAR2(5) := 'D0001';
  6        age NUMBER := 28;
  7        exeSql VARCHAR2(100);
  8  BEGIN
  9        exeSql := 'INSERT INTO tab1 VALUES(:emp_id,:emp_name,:dept,:age)';
 10        EXECUTE IMMEDIATE exeSql USING IN emp_id,emp_name,dept,age;
 11        COMMIT;
 12  END;
 13  /

プロシージャが作成されました。

実行すると次のように1件のレコードが登録されます。

SQL> EXECUTE PRO1

PL/SQLプロシージャが正常に完了しました。

SQL> SELECT * FROM tab1;

EMP_ID     EMP_NAME             DEPT              AGE
---------- -------------------- ---------- ----------
00001      Suzuki               D0001              28

INSERTとUPDATEの同時実行

INSERT文で、既に存在するレコードを更新することは出来ません。

INSERT文とUPDATE文の同時実行をするには、MERGE文で行います。

INSERT文の中でCASE式を使う方法

INSERT文ではCASE式を利用することが出来ます。

CASE式の書き方については↓で詳しく解説していますので参考にしてください。
>>【SQL】CASE式の書き方:サンプル多数あり

次の例ではtab1_bkテーブルのage列が30以上の場合は99を設定するようにしています。

SQL> CREATE OR REPLACE PROCEDURE PRO1
  2  IS
  3  BEGIN
  4        INSERT INTO tab1
  5        SELECT
  6               emp_id
  7               ,emp_name
  8               ,dept
  9               ,CASE WHEN age >= 30 THEN 99
 10                         ELSE age END
 11               CASE
 12        FROM
 13               tab1_bk;
 14        COMMIT;
 15  END;
 16  /

プロシージャが作成されました。

実行すると次のように結果が登録されます。

SQL> EXECUTE PRO1

PL/SQLプロシージャが正常に完了しました。

SQL> SELECT * FROM tab1;

EMP_ID     EMP_NAME             DEPT              AGE
---------- -------------------- ---------- ----------
00001      Suzuki               D0001              28
00002      Tanaka               D0002              25
00003      Kizaki               D0002              99
モバイルバージョンを終了