'Cursor'에 해당되는 글 1건

  1. 2007.12.21 | [본문스크랩] PL/SQL (14) - 커서(cursor) FOR UPDATE 절

[본문스크랩] PL/SQL (14) - 커서(cursor) FOR UPDATE 절

오라클 | 2007. 12. 21. 15:31
Posted by 시반

###################################################################################################

#   source site : blog.naver.com/tangamjaelt

#   master : 강용운

#   email : tangamjaelt@korea.com

#   last release : 2007.08.29

###################################################################################################

 

 

FOR UPDATE 절

- 커서 질의에 FOR UPDATE 절을 추가해서 영향을 받는 행을 잠글 수 있다.

- FOR UPDATE 절은 SELECT 문 맨 마지막에 온다. (ORDER BY 보다 뒤)

- SELECT ... FOR UPDATE문은 갱신 또는 삭제될 행을 식별한 다음 결과 집합에 있는 각 행을 잠근다.

- 행의 기존 값을 기반으로 갱신을 수행하려는 경우, 갱신 전에 다른 사용자가 해당 행을 변경하는 것을 방지하는 효과가 있다.

- SELECT 문 맨 마지막에 온다. ORDER BY 보다 뒤에...

- FOR UPDATE OF column_name 을 사용하여 해당 column_name이 있는 테이블에서만 행을 잠근다.

- NOWAIT 절은 다른 세션에서 해당 행을 잠근 경우, 무한정 대기하지 않고 즉시 오라클 오류를 반환하기 때문에 제어가 곧바로 프로그램으로 되돌아가 다른 작업을 수행할 수 있다.

- NOWAIT 를 생략하면 행을 사용할 수 있을 때까지 무한정 대기한다.

 

 

cursor_test4.sql

 DECLARE
        v_product mobile_phone.product_name%TYPE;
        v_brand mobile_phone.brand%TYPE;

 

     CURSOR cursor_mp(p_brand VARCHAR2) IS
         SELECT brand, product_name FROM mobile_phone
                WHERE brand = p_brand

         FOR UPDATE OF brand;    (NOWAIT를 사용하지 않았으므로 접근시 무한정 대기)


 BEGIN
        OPEN cursor_mp('LG');
        LOOP
                FETCH cursor_mp INTO v_brand, v_product;
                EXIT WHEN cursor_mp %NOTFOUND;
                DBMS_OUTPUT.PUT_LINE(v_brand || ' => ' ||  v_product);
        END LOOP;
        DBMS_OUTPUT.PUT_LINE(v_brand || ' 총 ' || cursor_mp%ROWCOUNT || ' 건');
        CLOSE cursor_mp;

 END;
 /

 

- NOWAIT를 사용하지 않은 경우이다.

- 2개 세션을 열어놓고 테스트 해보자.

 

 

- session 1 에서 Update를 하고 아직 commit 되지 않은 상태에서, session 2 가 커서에 FOR UPDATE 문으로 이미 잠겨있는 행을 잠그려고 하기 때문에 이미 해당 row가 사용중이라는 메시지와 함게 NOWAIT 절이 요구된다는 메시지를 뿌려주고 있다.

 

- 반대로 session 2 가 먼저 FOR UPDATE 절이 있는 커서를 실행하고, session 1 이 Update를 시도하면 해당 row가 잠겨있기 때문에 계속 대기하게 된다.

 

 

- 이 때 session 2 에서 commit 이나 rollback을 해주면, 잠금이 풀리면서 session 1 의 Update 작업이 이루어진다.

 

 

 

 

cursor_test5.sql

 DECLARE
        v_product mobile_phone.product_name%TYPE;
        v_brand mobile_phone.brand%TYPE;

 

     CURSOR cursor_mp(p_brand VARCHAR2) IS
         SELECT brand, product_name FROM mobile_phone
                WHERE brand = p_brand

         FOR UPDATE OF brand NOWAIT;


 BEGIN
        OPEN cursor_mp('LG');
        LOOP
                FETCH cursor_mp INTO v_brand, v_product;
                EXIT WHEN cursor_mp %NOTFOUND;
                DBMS_OUTPUT.PUT_LINE(v_brand || ' => ' ||  v_product);
        END LOOP;
        DBMS_OUTPUT.PUT_LINE(v_brand || ' 총 ' || cursor_mp%ROWCOUNT || ' 건');
        CLOSE cursor_mp;

 END;
 /

 

- NOWAIT를 사용한 경우이다.

- NOWAIT 는 'WAIT 0' 과 같은 의미이다. 대기 하지 않는다.

- NOWAIT 절을 사용하면 루프에서 잠금을 수행하지 못해 반환되는 오류 코드를 테스트할 수 있다.

 

 

 

 

cursor_test6.sql

 DECLARE
        v_product mobile_phone.product_name%TYPE;
        v_brand mobile_phone.brand%TYPE;

 

     CURSOR cursor_mp(p_brand VARCHAR2) IS
         SELECT brand, product_name FROM mobile_phone
                WHERE brand = p_brand

         FOR UPDATE OF brand WAIT 10;


 BEGIN
        OPEN cursor_mp('LG');
        LOOP
                FETCH cursor_mp INTO v_brand, v_product;
                EXIT WHEN cursor_mp %NOTFOUND;
                DBMS_OUTPUT.PUT_LINE(v_brand || ' => ' ||  v_product);
        END LOOP;
        DBMS_OUTPUT.PUT_LINE(v_brand || ' 총 ' || cursor_mp%ROWCOUNT || ' 건');
        CLOSE cursor_mp;

 END;
 /

 

- WAIT 에 시간(초)을 지정하면 지정한 시간동안 재시도를 하고 Lock을 획득하지 못하면 에러를 출력한다.

 

 

- 10 초 후 아래와 같은 에러를 출력한다.

 

 

 

'오라클' 카테고리의 다른 글

[본문스크랩] PL/SQL (16) - 대용량 오브젝트 타입 - BFILE  (0) 2007.12.21
[본문스크랩] PL/SQL (15) - 대용량 오브젝트 타입 - CLOB, BLOB  (0) 2007.12.21
[본문스크랩] PL/SQL (13) - 커서(cursor)  (0) 2007.12.21
[본문스크랩] PL/SQL (12) - 데이터타입 (%ROWTYPE 과 %TYPE)  (0) 2007.12.21
[본문스크랩] PL/SQL (11) - 패키지 (Package)  (0) 2007.12.21
 
블로그 이미지

시반

시반(詩伴)이란 함께 시를 짓는 벗이란 뜻을 가지고 있습니다. 함께 나눌수 있는 그런 공간이길 바라며...

카테고리

분류 전체보기 (233)
개발 이야기 (73)
WEB2.0 (57)
DB2 (24)
MySQL (6)
오라클 (26)
기타 (44)
취미 (0)
잡담 (2)

티스토리툴바