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

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

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

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

UPDATE文の基本構文

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

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

UPDATE テーブル名 SET 列名 = 値[,列名 = 値] [WHERE 条件];

UPDATE文のサンプル

PL/SQLでのUPDATE文の実行

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

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

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

SQL> CREATE OR REPLACE PROCEDURE PRO1
  2  IS
  3  BEGIN
  4        UPDATE tab1 SET emp_name = 'test' WHERE emp_id = '00001';
  5        COMMIT;
  6  END;
  7  /

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

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

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

SQL> select * from tab1;

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

SQL> EXECUTE PRO1

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

SQL> select * from tab1;

EMP_ID     EMP_NAME             DEPT              AGE
---------- -------------------- ---------- ----------
00001      test                 D0001              28
00002      Tanaka               D0002              25
00003      Kizaki               D0002              99

UPDATE件数の取得

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

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

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

SQL> CREATE OR REPLACE PROCEDURE PRO1
  2  IS
  3  BEGIN
  4      UPDATE tab1 SET age = 99;
  5      DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
  6      COMMIT;
  7  END;
  8  /

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

SQL> set serveroutput on
SQL> EXECUTE PRO1
3

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

SELECTした結果でUPDATEする方法

ORACLEデータベースでは、UPDATE句でINNER JOINが使えないため、SET句で副問合せを使ってSELECTした結果で更新します。

次の例ではtab1テーブルをtab2テーブルのSELECT結果を使って更新しています。

SQL> CREATE OR REPLACE PROCEDURE PRO1
  2  IS
  3  BEGIN
  4      UPDATE tab1 a
  5      SET a.EMP_NAME = (SELECT b.DEPT_NAME FROM tab2 b WHERE a.DEPT = b.DEPT);
  6      COMMIT;
  7  END;
  8  /

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

SQL> EXECUTE PRO1

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

SQL> select * from tab1;

EMP_ID     EMP_NAME             DEPT              AGE
---------- -------------------- ---------- ----------
00001      HR                   D0001              28
00002      SALES                D0002              25
00003      SALES                D0002              35
00004      DEV                  D0003              45
00005      DEV                  D0003              55
00006      DEV                  D0003              35

6行が選択されました。

INNER JOINを使った更新

PL/SQL(ORACLEデータベース)では、INNER JOINを使ってSELECTした結果を取得することが出来ません。

次のようなSQLは構文エラーとなり実行することが出来ません。

UPDATE tab1 a INNER JOIN tab2 b ON  a.DEPT = b.DEPT
SET a.EMP_NAME = b.DEPT_NAME;

複数テーブルを使ってUPDATEする方法

PL/SQLで複数テーブルを使ってUPDATEするには、WHERE句に条件を追加します。

次の例では、WHERE句に副問合せで別の表の結果を取得し、一致するレコードだけを更新しています。

SQL> select * from tab1;

EMP_ID     EMP_NAME             DEPT              AGE
---------- -------------------- ---------- ----------
00001      Suzuki               D0001              28
00002      Tanaka               D0002              25
00003      Kizaki               D0002              35
00004      Tanaka               D0003              45
00005      Kizaki               D0003              55
00006      Nakata               D0003              35

6行が選択されました。

SQL> CREATE OR REPLACE PROCEDURE PRO1
  2  IS
  3  BEGIN
  4      UPDATE tab1 a SET a.EMP_NAME = 'test'
  5      WHERE a.DEPT IN (SELECT b.DEPT FROM tab2 b WHERE b.DEPT_NAME = 'SALES');
  6      COMMIT;
  7  END;
  8  /

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

SQL> EXECUTE PRO1

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

SQL> select * from tab1;

EMP_ID     EMP_NAME             DEPT              AGE
---------- -------------------- ---------- ----------
00001      Suzuki               D0001              28
00002      test                 D0002              25
00003      test                 D0002              35
00004      Tanaka               D0003              45
00005      Kizaki               D0003              55
00006      Nakata               D0003              35

6行が選択されました。

UPDATE文でCASE式を使う方法

PL/SQLのUPDATE文でも、CASE式を使った条件分岐を実装することが出来ます。

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

次の例では、AGE列が30以上の値の場合に99に更新しています。30未満の場合はそのままの値で更新しています。

SQL> select * from tab1;

EMP_ID     EMP_NAME             DEPT              AGE
---------- -------------------- ---------- ----------
00001      Suzuki               D0001              28
00002      Tanaka               D0002              25
00003      Kizaki               D0002              35
00004      Tanaka               D0003              45
00005      Kizaki               D0003              55
00006      Nakata               D0003              35

6行が選択されました。

SQL> CREATE OR REPLACE PROCEDURE PRO1
  2  IS
  3  BEGIN
  4      UPDATE
  5          tab1 a
  6      SET
  7           a.AGE = CASE WHEN a.AGE >= 30 THEN 99 ELSE a.AGE END;
  8      COMMIT;
  9  END;
 10  /

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

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
00004      Tanaka               D0003              99
00005      Kizaki               D0003              99
00006      Nakata               D0003              99

6行が選択されました。

UPDATEとINSERTの同時実行

UPDATE文では、既に存在するレコードは更新、存在しないレコードはINSERTするといったことは出来ません。

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

モバイルバージョンを終了