[본문스크랩] 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
 

[본문스크랩] PL/SQL (13) - 커서(cursor)

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

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

#   source site : blog.naver.com/tangamjaelt

#   master : 강용운

#   email : tangamjaelt@korea.com

#   last release : 2007.08.29

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

 

 

1. 커서란...

- 일반적으로 커서는 모니터에 해당 위치를 알려주고 그곳에 입력을 대기중이라고 깜빡거리는 것을 나타낸다.

- 메모장을 켜보면 깜빡거리는 것. 그게 커서이다.

- 같은 맥락으로 PL/SQL에서 커서는 메모리상에 SQL문이 실행되는 위치를 가리킨다.

- 커서를 통해, 메모리에 존재하는 SQL문 실행결과를 바로 접근하여 fetch 할 수 있다. 메모리영역이라 함은 private SQL영역으로 SQL문 실행 및 처리결과를 저장하는 곳이다.

- 이 때 커서는 현재 처리하고 있는 row를 가리키게 된다.

- 실행결과를 1개 row씩 처리하다가 마지막까지 처리가 끝나면 커서를 닫는다.

- 많은 row를 처리하기 위해 명시적 커서를 선언하고 제어한다.

 

 

 

2. 종류

   (1) 암시적커서 (Implicit Cursor) : 모든 DML, PL/SQL Select 문에 대해 선언된다.

        - 모든 SQL문에는 관련된 개별 커서가 존재한다.

        - SQL문을 실행하면 PL/SQL은 암시적 커서를 작성하여 자동관리 한다.

 

   (2) 명시적커서 (Explicit Cursor) : 프로그래머가 선언하고 이름을 지정한다.

        - query 결과를 첫번째 행부터 차례대로 처리할 수 있다.

        - 현재 처리중인 행을 추적한다.

        - 프로그래머가 PL/SQL 블록에 명시적 커서를 수동으로 제어할 수 있다.

        - 여러 행 질의에 의해 반환되는 행집합을 활성 집합이라하고 활성집합의 크기는 검색조건을 만족하는 행(row)수와 같다.

 

        < 명시적 커서 >

        

 

        (1) 커서를 연다.

        (2) 행(row)을 인출(fetch)한다.

        (3) 커서를 닫는다.

 

        - OPEN 문은 질의를 실행하여 결과 집합을 식별한 후 커서를 첫번째 행 앞에 위치시킨다.

        - FETCH 문은 현재 행(row)을 검색하고 지정한 조건(empty?)이 만족할 때까지 커서를 다음 행(row)로 이동시킨다.

        - CLOSE 문으로 마지막 행(row)까지 처리되었으면 커서를 닫는다.

 

 

3. 커서 속성

- %ROWCOUNT : 가장 최근에 인출한 행의 개수

- %FOUND : 가장 최근에 인출한 행이 있으면 TRUE

- %NOTFOUND : 가장 최근에 인출한 행이 없으면 TRUE

- %ISOPEN : 커서가 열려있으면 TRUE.

                    

- 커서가 열려있어야 FETCH가 가능하므로 이 속성을 사용해서 커서의 OPEN 상태를 확인한다.

- CLOSE 된 상태에서 FETCH를 하면 INVALID_CURSOR 예외가 발생한다.

 

   IF NOT cursor_name%ISOPEN THEN

          OPEN curosr_name;

   END IF;

  

4. 커서 선언

 

  CURSOR cursor_name IS

        SELECT문;

 

- 커서 선언에 INTO 절을 포함시키지 않는다. INTO절은 FETCH문에 포함된다.

- 질의에 ORDER BY를 사용하여 특정 순서로 행을 처리할 수 있다.

- CURSOR 질의에 있는 변수를 참조할 수 있으나 변수는 CURSOR문 앞에 선언해야 한다.

 

cursor_test.sql

  DECLARE

         v_mp mobile_phone.product_name%TYPE;

     CURSOR cursor_mp IS

         SELECT product_name FROM mobile_phone;

 

  BEGIN

     OPEN cursor_mp;

          LOOP

              FETCH cursor_mp INTO v_mp;

              EXIT WHEN cursor_mp%NOTFOUND;

                  DBMS_OUTPUT.PUT_LINE(v_mp);

          END LOOP;

          DBMS_OUTPUT.PUT_LINE('총 row수 : ' || cursor_mp%ROWCOUNT);

     CLOSE cursor_mp;

 

  END;

   /

 

- 명시적으로 OPEN, FETCH, CLOSE를 수행하였다.

 

SQL> SET SERVEROUTPUT ON;

SQL> @cursor_test

 

 

5. 커서 FOR LOOP 사용

 

- 커서 FOR 루프를 사용하면 암시적 커서가 자동으로 실행된다.

- 레코드가 암시적으로 선언된다.

- FOR 루프가 한번 반복될 때마다 행이 인출된다.

- 마지막 행이 처리되면 루프가 종료되고 커서가 자동으로 닫힌다.

- 암시적 커서 실행이 이루어지므로 OPEN, FETCH, CLOSE를 선언하지 않는다. (선언하면 이미 커서가 열렸다는 에러 발생)

 

cursor_test2.sql

  DECLARE

     CURSOR cursor_mp IS

         SELECT brand, product_name FROM mobile_phone;

     record_mp cursor_mp%ROWTYPE

 

  BEGIN

      FOR record_mp IN cursor_mp LOOP
          IF record_mp.brand = 'LG' THEN
              DBMS_OUTPUT.PUT_LINE(record_mp.brand || ' => ' || record_mp.product_name);
          END IF;
    END LOOP;

 END;

 /

 

- 이 예제에서는 브랜드가 LG인 상품을 출력하는 프로그램이다.

- 여기 예제에서는 %ROWTYPE을 사용하여 커서에 대한 record 타입 변수 record_mp 를 선언하였다.

- OPEN, FETCH, CLOSE 없이 바로 FOR문을 돌리면 된다.

 

 

 

 

6. 파라미터 사용 커서

- 커서에 파라미터를 정의하고 OPEN 시 파라미터를 전달할 수 있다.

- 실행할 때마다 이전에 사용했던 파라미터의 활성 집합을 닫고, 매번 새 파라미터를 이용해 커서를 OPEN한다.

- OPEN(parameter1, parameter2, ...) 형식으로 사용한다.

 

cursor_test3.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;
 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;

 

        OPEN cursor_mp('SAMSUNG');
        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;
 /

 

- OPEN cursor 를 통해 파라미터를 전달한다.

- LG, SAMSUNG 인 것들을 각각 cursor를 통해 출력하였다.

 

 

 

 

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

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

시반

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

카테고리

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

티스토리툴바