S-JIS[2009-03-28/2010-07-24] 変更履歴

UPDATE文

OracleSQLのUPDATE文のメモ。

  • Oracle Database SQL言語リファレンスのSQL文: UPDATE(Oracle10g)
  • Oracle Database SQL言語リファレンスのSQL文: UPDATE(Oracle11g)

デフォルト値を指定する例

「default」というキーワードでその項目のデフォルト値をセットできる。[2009-12-24]

update テーブル set 項目 = default;

insert文のdefaultキーワード


他テーブルから情報を取得してセットするUPDATE文

update テーブル a
set 項目A = (select 項目B from テーブル b where b.キー = a.キー)
;
update テーブル a
set (A1, A2) = (select B1, B2 from テーブル b where b.キー = a.キー)
;

参考: RNKさんのOracle SQL (データの更新 :UPDATE文の基礎)


他テーブルに存在している行を更新するUPDATE文

他のテーブルにデータが存在しているだけを更新するには、existsを使う。[2010-02-04]
他テーブルにマッチするデータが存在しているかどうかを条件とする)

update 更新対象テーブル u
set 〜
where
	exists (
		select * from 他テーブル s
		where
			s.key = u.key
			and S条件
	)
	and U条件
;

また、以下のSQLも等価。
(ただし、他テーブルとの結合方法によっては、このUPDATE文はエラーになる。「ORA-01732: このビューではデータ操作が無効です」)

update (select u.* from 更新対象テーブル u, 他テーブル s
	where s.key = u.key
	and S条件
       )
set 〜
where U条件
;
update (select u.* from 更新対象テーブル u, 他テーブル s
	where s.key = u.key
	and S条件
	and U条件
       )
set 〜
;

件数の小さいテーブルで試した感じでは、実行計画は、2番目と3番目は同じになるっぽい。1番目とはちょっと異なることがあるようだ。(最終的なコストはどちらも同じだったが)
件数の多いテーブルだと、どれも同じ実行計画になった。
また、同じSQLでも、更新対象テーブル他テーブルのどちらの件数がどれくらい多いかによって実行計画が変わる。


更新した値の取得(returning)

returning句を使うと、UPDATEした際の(更新後の)値をバインド変数に取り込むことが出来る。[2010-07-24]

update テーブル
set 項目=値,…
returning 項目名… into :バインド変数名…
;

UPDATE本体に指定しなかった項目もバインド変数に代入させることが出来る。

JDBCでreturningを使う方法


バインド変数はPL/SQLで使う他に、SQL*Plusでも定義することが出来る。

SQL> var v1 number

SQL> update EMP set SAL=SAL+1 where ENAME='SCOTT' returning EMPNO into :v1;
1行が更新されました。

SQL> print v1
        V1
----------
      7788
SQL> var v1 number
SQL> var v2 number

SQL> update EMP set SAL=SAL+1 where ENAME='SCOTT' returning EMPNO,SAL into :v1,:v2;
1行が更新されました。

SQL> print
        V1
----------
      7788

        V2
----------
     12931

更新対象が無かった場合(更新件数が0件の場合)は、バインド変数は変更されない。
(NULLになるわけでもない)


複数のレコード(行)が更新対象になった場合、バインド変数がnumber等の単純な型だと代入することが出来ない。

SQL> var v1 number

SQL> update EMP set SAL=SAL+1 returning EMPNO into :v1;
update EMP set SAL=SAL+1 returning EMPNO into :v1
*
行1でエラーが発生しました。:
ORA-24369:
1つ以上のバインド・ハンドルに、必要なコールバックが登録されていません。

PL/SQLでは、バインド変数の型(タイプ)にテーブルを指定してやると、値を取得することが出来る。

declare
	type NumberList is table of number(4);
	empnos	NumberList;
begin
	execute immediate 'update EMP set SAL=SAL+1 returning EMPNO into :1'
	returning bulk collect into empnos;
end;

参考: Oracle Database PL/SQLユーザーズ・ガイドおよびリファレンスの例7-5 RETURNING BULK COLLECT INTO句を使用した動的SQL

SQL*Plusで出来ない理由は、variableコマンドによる変数定義では独自の型(タイプ)が指定できないから。


SQLメモへ戻る / Oracle目次へ戻る / 技術メモへ戻る
メールの送信先:ひしだま