同一表に同一データがある場合のPLSQLの挙動

質問

お世話になります。
Oracle+PLSQL初心者です。

特定のPLSQLを発行した際の、同一データが同じ表内にある場合の動作について混乱しています。

条件を満たす列に対して値+100の反応を期待しているのですが、思うような結果が返ってきません。
偶然に上記挙動が発生し、同一データの重複が問題かなと問題のなさそうな表をつくり直したら同現象は発生しなくなりました。

1. 同じ表に２度同じデータをインサート（同一データが２つある状態）

ID NAME SAL DEPT
1 TARO 0 10
2 HANAKO 100 20
1 TARO 0 10
2 HANAKO 100 20

2. 特定条件下（sal<1000, deptno=10）でsalが+100になるPLSQLを発行、COMMIT

DECLARE
CURSOR emp_cur IS SELECT sal, empno FROM emp
WHERE deptno = 10;
BEGIN
FOR emp_rec IN emp_cur LOOP
IF emp_rec.sal < 1000 THEN
UPDATE emp SET sal = sal + 100
WHERE empno = emp_rec.EMPNO;
END IF;
END LOOP;
END;
/
COMMIT;

3. SELECTでSALを確認すると200増えている（2倍）

ID NAME SAL DEPT
1 TARO 200 10
2 HANAKO 100 20
1 TARO 200 10
2 HANAKO 100 20

個人的には下記のような値になると思っていました。
言葉で説明するのが難しいのですが、心当たりのある方がいらっしゃったらお答え願います。
よろしくお願い致します。

ID NAME SAL DEPT
1 TARO 100 10
2 HANAKO 100 20
1 TARO 100 10
2 HANAKO 100 20

回答No.3

ossan_hiro · ベストアンサー

サンプルのプログラムとテーブルのイメージが合っていないので、正確が回答を出せるか不明ですが、
カーソルの中にはTAROのデータが２件存在しています。
さらに、１度のUpdateで影響受けるデータも2件存在します。よってTAROのデータが200になるのだと思います。(１回目：0→100、2回目:100→200)

現在、FETCHしているデータにだけ更新するように処理を直すことでgooracleさんが想定している結果になると思います。
DEPTNO(?)で2件抽出されてしまうのであれば、rowidをSELECTするようにし、UPDATEする条件をempno(?)ではなくrowidを使った条件にすれば良いのではないでしょうか？
ちなみにOPENされたカーソルのデータに更新がかかってもカーソルからデータがなくなることはないです。

お礼

PED02744さんのご回答を参考に、お蔭様で理解できました。
加えてossan_hiroさんに補足していただくまで、「データ更新によってカーソルからデータがなくなることはない」というのは認識不足でした。
もう少し、動作を理解しながら試していこうと思います。
ご丁寧にありがとうございました。

回答No.4

MZ-80B

回答は出ていますが
PL/SQLでは WHERE CURRENT OF カーソル名
という構文もあるので参考にしてみてください。
ROWIDを指定するのと同じ効果があります。

参考URL：

http://www.techonthenet.com/oracle/cursors/current_of.php

お礼

URL参考にさせていただきました。

手元の参考書では次の項で学ぶ構成になっていました。
WHERE CURRENT OF カーソル名の方法、ROWIDを指定する方法、両方で比べて試してみようと思います。

ありがとうございました。

回答No.2

PED02744

同じデータが2件ある状態で、最初のカーソルの実行結果はどうなるか、考えてみましょう。
emp_curは DEPTNO=10の物を取得するのですから、2件ヒットします。
さてここで、2件をFORでループさせてます。
ループ内でSALが1000よりも小さいものに対して+100を実行しているので、
UPDATEは2回走ります。
1回目のUPDATEで0→100に変わります。
2回目のUPDATEで100→200に変わります。
何の問題もないとおもいます。
※DEPTNO/EMPNOがどちらもDEPTを意味しているとした場合です。
　あなたのサンプルのカラム名がおかしいので、正確な事はいえないのですが。

ANO1.さんへ。
　オラクルの場合、FORのすぐ後ろの変数は自動変数なので、宣言不要なんですよ。

お礼

emp_curによって2件のTAROが取得されるところまでは理解していたのですが、UPDATEが2回走るというのがイメージできていませんでした。ご指摘頂いた箇所を熟読してようやく理解でました。
サンプルのカラム名についても申し訳ありません。
LOOP文もそうですが、肝心な動作内容が全く見えていないことに気づきました。
ご丁寧にありがとうございました。

回答No.1

noname#19431

EMPNOはどこから出てきたのでしょうか？
１．はEMP表と考えたらいいでしょうか？
あと、emp_recが宣言されてないようなのですが。

お礼

誤解を招くサンプルで混乱させてしまい、申し訳ありませんでした。
言語自体が初めてでしたので宣言必要な場合もあるのだと初めて知りました。今後の参考にさせていただきます。ありがとうございました。