UPDATE文について勉強中です。
あるテーブルから数万件のデータを抽出定し、抽出したレコードに更新を行うのですが、更新列の更新後の値から別の列の計算を行う必要があります。
例として、
- TBL_SALARYからEMP_IDが9以下のレコードを抽出する。
- HIRE_DATEが20051299以下の場合はPRICEを800、でない場合はPRICEを750にする。
- TIMEは仮に一律90に設定する。
- PRICE×TIMEでSALARYを更新する。
この場合に思いつく方法は3パターンあるのですが、出来る事ならAパターンのように計算式をSALARY列に書くのは手間・不具合を招きそうですし、Bパターンだと更新処理を2回走らせるのでコスト増と考えています。パターンCは書く量が多くなりますが、やりたい事は満たせています。
他に良い方法があれば教えて下さい。
よろしくお願いいたします。
CREATE TABLE TBL_SALARY ( EMP_ID NUMBER(3) ,EMP_NAME VARCHAR2(30) ,HIRE_DATE NUMBER(8) ,PRICE NUMBER(4) ,TIME NUMBER(3) ,SALARY NUMBER(6) ) / INSERT INTO TBL_SALARY VALUES (1, 'A', 20010101, 0, 0, 0); INSERT INTO TBL_SALARY VALUES (2, 'B', 20020101, 0, 0, 0); INSERT INTO TBL_SALARY VALUES (3, 'C', 20030101, 0, 0, 0); INSERT INTO TBL_SALARY VALUES (4, 'D', 20040101, 0, 0, 0); INSERT INTO TBL_SALARY VALUES (5, 'E', 20050101, 0, 0, 0); INSERT INTO TBL_SALARY VALUES (6, 'F', 20060101, 0, 0, 0); INSERT INTO TBL_SALARY VALUES (7, 'G', 20070101, 0, 0, 0); INSERT INTO TBL_SALARY VALUES (8, 'H', 20080101, 0, 0, 0); INSERT INTO TBL_SALARY VALUES (9, 'I', 20090101, 0, 0, 0); COMMIT; / -- <<パターンA>> UPDATE TBL_SALARY TBL SET PRICE = (CASE WHEN HIRE_DATE <= 20051299 THEN 800 ELSE 750 END) ,TIME = 90 ,SALARY= ((CASE WHEN HIRE_DATE <= 20051299 THEN 800 ELSE 750 END)*90) WHERE EMP_ID <= 9 ; -- <<パターンB>> -- 更新その1 UPDATE TBL_SALARY TBL SET PRICE = (CASE WHEN HIRE_DATE <= 20051299 THEN 800 ELSE 750 END) ,TIME = 90 WHERE EMP_ID <= 9 ; -- 更新その2 UPDATE TBL_SALARY TBL SET SALARY = PRICE * TIME WHERE EMP_ID <= 9 ; -- <<パターンC>> DECLARE CURSOR C_SALARY IS SELECT * FROM TBL_SALARY ; TYPE TY_SALARY IS TABLE OF TBL_SALARY%ROWTYPE ; RC_SALARY TY_SALARY; WK_PRICE TBL_SALARY.PRICE%TYPE; WK_TIME TBL_SALARY.TIME%TYPE; WK_SALARY TBL_SALARY.SALARY%TYPE; BEGIN OPEN C_SALARY; LOOP FETCH C_SALARY BULK COLLECT INTO RC_SALARY LIMIT 1000; EXIT WHEN RC_SALARY.COUNT = 0; FOR i IN 1..RC_SALARY.COUNT LOOP IF (RC_SALARY(i).HIRE_DATE <= 20051299) THEN WK_PRICE := 800; ELSE WK_PRICE := 750; END IF; WK_TIME := 90; WK_SALARY := WK_PRICE * WK_TIME; UPDATE TBL_SALARY TBL SET PRICE = WK_PRICE ,TIME = WK_TIME ,SALARY = WK_SALARY WHERE TBL.EMP_ID = RC_SALARY(i).EMP_ID ; END LOOP; END LOOP; CLOSE C_SALARY; DBMS_OUTPUT.PUT_LINE('COMMIT'); COMMIT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); ROLLBACK; END; /
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。