• ベストアンサー
  • 暇なときにでも

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

お世話になります。 Oracle+PLSQL初心者です。 特定のPLSQLを発行した際の、同一データが同じ表内にある場合の動作について混乱しています。 条件を満たす列に対して値+100の反応を期待しているのですが、思うような結果が返ってきません。 偶然に上記挙動が発生し、同一データの重複が問題かなと問題のなさそうな表をつくり直したら同現象は発生しなくなりました。 1. 同じ表に2度同じデータをインサート(同一データが2つある状態) 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

共感・応援の気持ちを伝えよう!

  • 回答数4
  • 閲覧数407
  • ありがとう数5

質問者が選んだベストアンサー

  • ベストアンサー
  • 回答No.3

サンプルのプログラムとテーブルのイメージが合っていないので、正確が回答を出せるか不明ですが、 カーソルの中にはTAROのデータが2件存在しています。 さらに、1度のUpdateで影響受けるデータも2件存在します。よってTAROのデータが200になるのだと思います。(1回目:0→100、2回目:100→200) 現在、FETCHしているデータにだけ更新するように処理を直すことでgooracleさんが想定している結果になると思います。 DEPTNO(?)で2件抽出されてしまうのであれば、rowidをSELECTするようにし、UPDATEする条件をempno(?)ではなくrowidを使った条件にすれば良いのではないでしょうか? ちなみにOPENされたカーソルのデータに更新がかかってもカーソルからデータがなくなることはないです。

共感・感謝の気持ちを伝えよう!

質問者からのお礼

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

関連するQ&A

  • 表の結合に関する質問

    EMP表 EMPNO ENAME JOBNO SAL DEPTNO ----- ------ ------ ----- ------ 1000 ADAMS 100 1000 10 1010 MILLER 200 1200 10 1020 SCOTT 100 800 30 DEPT表 DEPTNO DNAME EMPNO ----- ------ ------ 10 PERSONNEL 1000 20 ACCOUNT 30 DESIGN 上記表に対して、 [SQLコマンド] select e.empno , e.ename , dname , m.ename from emp e join dept d using(deptno) left outer join emp m on d.empno = m.empno; のSQL文を打つと、 EMPNO ENAME DNAME ENAME ----- ------ ------ ----- 1000 ADAMS PERSONNEL ADAMS 1010 MILLER PERSONNEL ADAMS 1020 SCOTT DESIGN と表示されますが、これが表示される過程がわからないです。 emp表とdep表をdeptno列で結合 from emp e join dept d using(deptno) この結合により EMPNO ENAME DNAME ・・・・・・ ----- ------ ------ ----- 1000 ADAMS PERSONNEL ・・・・・・ 1010 MILLER PERSONNEL ・・・・・・ 1020 SCOTT DESIGN ・・・・・・ となります。この部分は分かりますが、 次の、この表とemp表をempnoで結合 left outer join emp m on d.empno = m.empno; した際に、 EMPNO ENAME DNAME ENAME ----- ------ ------ ----- 1000 ADAMS PERSONNEL ADAMS 1010 MILLER PERSONNEL ADAMS 1020 SCOTT DESIGN となりますが、ENAME(4番目の列)で ADAMSと表示される過程が見えてきません。 この部分の過程について、ご教授の程お願い致します。

  • 翔泳社オラクルマスター教科書Silver

    翔泳社オラクルマスター教科書Silverを使ってシルバー取得の勉強をしていますが、どうもこの本の解答に納得のいかない点が多すぎで、このあいだから立て続けに質問してしまってごめんなさい。 何しろ知識が無さ過ぎて、本があっているのか、自分がおかしいのか、判断しかねるのです。 で、今日はこの問題です。 ---問題ここから--- 次の結合文の中で実行時にエラーになるものをひとつ選びなさい a.SELECT DEPT.DEPTNO,EMP.ENAME FROM DEPT D,EMP E WHERE DEPT.DEPTNO=EMP.DEPTNO; b.SELECT ENAME,SAL,GRADE FROM SALGTADE,EMP WHERE SAL>=LOSAL AND SAL<=HISAL; c.SELECT DEPT.DEPTNO,DMP.ENAME FROM DEPT,EMP WHERE DEPT.DEPTNO(+)=EMP.DEPTNO; D.SELECT D.DEPTNO,E.ENAME FROM DEPT D,EMP E WHERE D.DEPTNO=E.DEPTNO; ----問題ここまで---- で、この答えが選択肢aとなっているのですが、納得できません。 解説によれば、「FROM句で二つ以上の表を指定した場合、SELECT句やWHERE句で指定する列名は、どの表の列か判断できなければいけません。」 とあります。 つまり、正解は「どの表か明示的に表示していない」選択肢b だと思うのですが、やはり教科書があっていますか?? 私の考え方はどう間違っているのか、教えていただけたら嬉しいです。 どうかよろしくお願い致します☆

  • エラー「無効な識別子です」

    ORA-00904: "EMPNO": 無効な識別子です と表示されます。 解決方法知っている方、教えていただければ幸いです。 SELECT EMPNO, ENAME, D.DEPTNO, DNAME FROM DEPT D WHERE EMPNO = (SELECT EMPNO FROM EMP WHERE MGR IS NULL) SQL> DESCRIBE EMP; 名前 NULL? 型 ----------------------------------------- -------- ---------------------------- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NOT NULL NUMBER(2)

その他の回答 (3)

  • 回答No.4
  • MZ-80B
  • ベストアンサー率56% (46/81)

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

参考URL:
http://www.techonthenet.com/oracle/cursors/current_of.php

共感・感謝の気持ちを伝えよう!

質問者からのお礼

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

  • 回答No.2

同じデータが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はどこから出てきたのでしょうか? 1.はEMP表と考えたらいいでしょうか? あと、emp_recが宣言されてないようなのですが。

共感・感謝の気持ちを伝えよう!

質問者からのお礼

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

関連するQ&A

  • エラー2「無効な識別子です」

    ORA-00904: "D"."DEPT": 無効な識別子です。 先ほども似たような質問をしましたが原因がよく分からないなのでご存知の方教えていただければ幸いです。 SELECT D.DEPTNO, E.ENAME, E.SAL FROM EMP E, DEPT D WHERE E.SAL >ANY (SELECT MAX(E.SAL) FROM DEPT D, EMP E GROUP BY D.DEPT) SQL> DESCRIBE EMP; 名前 NULL? 型 ----------------------------------------- -------- ---------------------------- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NOT NULL NUMBER(2) SQL> DESCRIBE DEPT; 名前 NULL? 型 ----------------------------------------- -------- ---------------------------- DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13)

  • エラー「単一グループのグループ関数ではありません」

    エラーをどうやれば修正できるかわかりません。 回答のほどよろしくお願いいたします。 SELECT EMPNO, ENAME, DEPTNO, SAL FROM EMP HAVING AVG(SAL) >ANY (SELECT SAL FROM EMP WHERE HIREDATE LIKE '82%') SQL> DESCRIBE EMP; 名前 NULL? 型 ----------------------------------------- -------- ---------------------------- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NOT NULL NUMBER(2)

  • 自己結合について

    下記SQL文のSELECT句、m.empnoによって左項DEPTNOのMGR値がきちんと出力される理屈がよく分かりません。 回答のほどよろしくお願い致します。 select e.empno, m.empno from emp e join emp m on(e.mgr = m.empno) EMPNO EMPNO ---------- ---------- 7698 7839 7782 7839 7566 7839 7902 7566 7369 7902 7499 7698 7521 7698 7654 7698 7788 7566 7844 7698 7876 7788 7900 7698 7934 7782 13行が選択されました。 SQL> describe emp; 名前 NULL? 型 ------------------------------ EMPNO  NOT NULL NUMBER(4) ENAME      VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO   NOT NULL NUMBER(2)

  • カーソルループ内部でログを出力

    DECLARE CURSOR EMP_CUR IS SELECT SALARY FROM EMP WHERE DEPT_NO = 10 FOR UPDATE; BEGIN FOR EMP_REC IN EMP_CUR LOOP DBMS_OUTPUT.PUT_LINE( '変更前:' || EMP_REC.SALARY ); --SALARYが2500以下なら100を加える IF EMP_REC.SALARY < 2500 THEN UPDATE EMP SET SALARY = SALARY + 100 WHERE CURRENT OF EMP_CUR; DBMS_OUTPUT.PUT_LINE( '処理件数:' || SQL%ROWCOUNT ); END IF; DBMS_OUTPUT.PUT_LINE( '変更後:' || EMP_REC.SALARY ); END LOOP; END; / 上記のようなSQLで、 変更前のSALARYと変更後のSALARYを出力したいのですが、 変更前と変更後のEMP_REC.SALARYで、同じ値が出力されました。 処理の前後でSELECTする事は可能であると思いますが、 カーソル内でまとめて行いたいです。 カーソル内でIF文の前後の値を出力する事は可能でしょうか? また、もう1点質問があります。 それは、SALARYの値が、どちらも更新された値が出力される事についてです。 例:DEPT_IDが10で、SALARYが1000の従業員が居た場合。   変更前:1100   変更後:1100 EMP_REC.SALARYの値が、DECLAREで定義されたカーソルのデータならば、 1000が出力されると考えたのですが、 どちらも更新後の値が出力されるのは何故でしょうか? お手数をお掛け致しますが、よろしくお願い致します。

  • updateについて

    下記、SQLコマンドを実行するとMERTINの歩合給がSMITHのSALに格納されません。実行後、NULLが入ってしまいます。 ちゃんとemp表のMERTINのCOMMには値が入っているのになぜ実行結果がうまくいかないのでしょうか? 回答のほどよろしくお願い致します。 update w_emp set sal = (select comm from emp where ename = 'MERTIN') where sal < 1000 SQL> select * from w_emp; DEPTNO EMPNO ENAME JOB SAL 20 7902 FORD ANALYST 3000 20 7369 SMITH CLERK 10行が選択されました。 SQL> desc w_emp; 名前 NULL? 型 DEPTNO NOT NULL NUMBER(2) EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) SAL NUMBER(7,2) SQL> desc emp; 名前 NULL? 型 EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NOT NULL NUMBER(2)

  • SQLの問題で解答を見ても腑に落ちません

    問題集の答えが腑に落ちないのでどなたかわかりやすく説明いただけますでしょうか? EMP表 EMPNO ENAME  SAL   MGR  DEPTNO -------------------------------------- 1000  SCOTT  1000  NULL  10 1010  ADAMS  2000  1000  10 1030  TAYLOR  600   1000  30 1050  MILLER  800   1030  10 以下の副問い合わせを使ったSELECT文を実行した。検索されるデータ件数を選びなさい。 select empno,ename from emp where empno not in (select mgr from emp); A:0件 B:1件 C:2件 D:3件 解答はAの0件なのですが、なぜ0件なのでしょうか? 当方の解釈ですと、副問い合わせでmgrが(NULL,1000,1030)となり where empno not in (NULL,1000,1030) で 「empnoがNULLでも1000でも1030でもない件数」 となり、empnoが1010と1050の行が検索され、cの2件が正解なのでは? と思っているのですが・・・ 間違いをわかりやすく教えていただけますでしょうか? 宜しくお願いします。

  • SQL 副問い合わせ使い方について

    初心者です。 フォームに入力された、EMPNOとEMP_IDに一致するデータ(複数の場合あり)を取得するSQL文なのですが、参考にしようとしているコードがいまいち理解できなくて困っています。特にグループ化と結合のあたりがよくわかりません。どなたか、詳しく教えていただけないでしょうか?よろしくお願いします SELECT EMPINFO_A.EMPNO, EMPINFO_A.EMP_ID, EMPCD_CDA.CDA_NAME || EMPCD_CDB.CDB_NAME AS EMPCD_CDNM, NVL(EMPINFO_MAP_VIRTUAL.EMPINFO_COUNT, 0) AS EMPINFO_MAP_COUNT FROM EMPINFO_A, EMPCD_CDA, EMPCD_CDB (SELECT COUNT(*) AS EMPINFO_MAP_COUNT, EMPNO,EMP_ID FROM EMPINF0_MAP WHERE EMPNO = :P_EMPNO AND EMP_ID = :P_EMP_ID GROUP BY EMPNO, EMP_ID) EMPINFO_MAP_VIRTUAL WHERE EMPINFO_A._EMPNO = :P_EMPNO AND EMPINFO_A.EMP_ID = :P_EMP_ID AND EMPINFO_A.EMPNO = EMPINFO_MAP_VIRTUAL.EMPNO(+) AND EMPINFO_A.EMP_ID = EEMPINFO_MAP_VIRTUAL.EMP_ID(+)

  • SQLの書き方について

    すみません、SQL初心者なので変な質問かもしれませんが、、SQLの書き方がわからないので教えてください。以下のような二つのテーブルがあるとします。 (Table: EMP2014) EMPNO | EMPNAME | TEL 100 | TARO | 03-1111-2222 101 | HANAKO | 03-2222-3333 (Table: EMP2015) EMPNO | EMPNAME | TEL 200 | JIRO | 03-3333-4444 201 | KYOKO | 03-4444-5555 このテーブルからSQLを実行して、以下のような結果を得たいです。 EMPNO | EMPNAME | TEL 100 | TARO | 03-1111-2222 101 | HANAKO | 03-2222-3333 200 | JIRO | 03-3333-4444 201 | KYOKO | 03-4444-5555 JOINというのは二つのテーブルを左右に並べた形での結合だと思うんですけれど、僕がやりたいのは内容の似たテーブル(上記の場合全く同じカラム)を上下に単純にくっつけて一つの出力結果にしたいです。ビューなどをつくって仮想的に一つの表にできるものなのでしょうか? とても簡単なのかも知れませんが、、宜しくお願いします。

  • PL/SQLにてカーソル名を変数に

    PL/SQLにて、カーソル名を変数にしたいのですが、どうすればよいのでしょうか? 具体的には以下のとおりです。 DECLARE CURSOR Cur1 IS SELECT K1,K2 FROM EMP; BEGIN FOR Cur_Rec1 IN Cur1     Cur_Rec1.K1 DBMS_OUTPUT.PUT_LINE(Cur_Rec1.K1); ★ END LOOP; END; ★の部分の「Cur_Rec1.K1」を「Cur_Rec1.K2」として、置き換えて使用したいのです。(2回書きたくない) 初心者で、申し訳ないです。どうか、ご教授ください。

  • PL/SQLについての質問です。

    PL/SQLについての質問です。 カーソルデータ(emp_rec(i))を、TABLE変数(emp_ins(i))に代入して、代入されたTABLE変数を使用して、DML処理を行うことは可能ですか? エラーを解読し対処を行っても、エラーが発生してしまいます。 どうか、ご教授お願いします。 下記を実行すると、下記エラーが発生します。 -実行結果------------------------------------ DECLARE * 行1でエラーが発生しました。: ORA-06502: PL/SQL: 数値または値のエラーが発生しました ORA-06512: 行127 -エラー番号解読----------------------------------------- ●エラー名: PL/ SQL: 数値または値のエラーstring が発生しました ●原因: 算術、数値、文字列、変換または制約エラーが発生しました。たとえば、NULL 値をNOT NULL で宣言した変数に割り当てようとした場合、または100 以上の整数をNUMBER( 2) で宣言した変数に割り当てようとした場合にこのエラーが発生します ●処置:値が制約違反をしないように、データ、操作方法または宣言方法を変更してください。 -ソース------------------------------------ DECLARE --カーソル定義 CURSOR emp_cur IS SELECT a,b,c FROM emp TYPE emptabtype IS TABLE OF emp%ROWTYPE INDEX BY PLS_INTEGER; emp_rec emptabtype; /* 処理対象データ格納変数 */ -- insert作業用 emp_ins emptabtype; ins_count NUMBER := 1; BEGIN /* empカーソルオープン・フェッチ・クローズ */ OPEN emp_cur; FETCH emp_cur BULK COLLECT INTO emp_rec; CLOSE emp_cur; /* (挿入)処理対象のデータを、処理対象データ格納変数に格納する。 */ FOR i IN emp_rec.FIRST..emp_rec.LAST LOOP --挿入するレコードを格納 emp_ins(ins_count) := emp_rec(i); ins_count := ins_count + 1; END LOOP; /* DML処理 */ -- 挿入する FOR count_ins IN emp_ins.FIRST..emp_ins.LAST LOOP INSERT INTO temp VALUES(emp_ins(count_ins).a ,emp_ins(count_ins).b ,emp_ins(count_ins).c); END LOOP; COMMIT; END; /