ここでは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 28INSERT 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 35INSERT文に変数で値をセット
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 28INSERTと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