[Oracle SQL] 커서, 반복명령

고동이의 IT·2021년 10월 28일
0

Oracle SQL

목록 보기
29/31

> # 3. 커서

  • 오라클 명령에 영향받은 행들의 집합
  • SELECT 문인 경우 결과 집합 --뷰

1) 묵시적 커서 --커서를 생성한게 아니라 sql을 해석해서 실행됨

  • 이름이 없는 커서
  • 자동생성되며 OPEN과 동시에 CLOSE되기 때문에 커서 내의 데이터 접근이 허용되지 않음
  • 커서속성
    속성         의미

    SQL%ISOPEN 커서가 OPEN 되었으면 TRUE (항상 FALSE)
    SQL%FOUND SQL명령의 결과로 하나 이상 행이 반환되었으면 TRUE
    SQL%NOTFOUND SQL명령의 결과로 하나의 행도 반환되지 않았으면 TRUE
    SQL%ROWCOUNT SQL명령으로 영향받은 행의 개수

    2)명시적 커서

    선언문에 의하여 생성되는 커서
    커서의 사용은 선언 => OPEN=>FETCH=>CLOSE 단계를 거쳐야 함
    (단, FOR문에서 사용시에는 예외)

(1) 커서선언
- DECLARE 선언부에서 선언
(선언형식)
CURSOR 커서명[(데이터타입 변수명,...)] IS
SELECT 문;
- 매개변수는 OPEN문에서 값을 배정함

(2) 커서 OPEN

- 커서사용을 위해 반드시 필요
- 모든 커서는 사용전에 OPEN 되고 사용 후 CLOSE 되어야 함
- 실행영역에서 기술
(사용형식)
OPEN[(expr[,expr,...])];
- expr: 커서 선언문에 전달할 값을 지정

(3) 커서 FETCH
- 커서내의 데이터를 읽어오는 역할 수행(행단위)
- 실행영역 내의 반복문 안에 위치
- 반복문 밖에서 기술
(사용형식)
FETCH 커서명 INTO 변수명[,변수명,...]

(4) 커서 CLOSE
- OPEN되어 사용이 종료된 커서는 반드시 CLOSE되어야함
- CLOSE 되지 않은 커서는 재 OPEN 될 수 없음
(사용형식)
CLOSE 커서명;

4. 반복명령

  • 개발언어의 반복문과 동일 기능 제공
  • LOOP, WHILE, FOR 문이 제공됨

1) LOOP 문 --자바의 DO문
- 조건이 없는 단순한 무한 루프기능을 제공
- EXIT 문을 이용하여 반복을 벗어날 수 있음
- 모든 반복명령의 기본 구조로 사용
(사용형식)
LOOP
반복시킬 명령문(들);
[EXIT WHEN 조건;]
END LOOP;
- 'EXIT WHEN 조건' : 조건이 참(true)면 LOOP 문을 벗어남

사용예) 구구단의 7단을 LOOP문으로 구성하시오

 DECLARE 
      V_RES NUMBER:=0;
      V_CNT NUMBER:=1;
      
    BEGIN
      LOOP
        EXIT WHEN V_CNT >9;
        V_RES:=7*V_CNT;
        DBMS_OUTPUT.PUT_LINE(7||'*'||V_CNT||'='||V_RES);
        V_CNT:=V_CNT+1;
        END LOOP;
        
   END;
   

커서 사용예) 2005년 6월 매입상품별 판매현황을 조회하시오

 1) 2005년 6월 매입상품 조회 => 커서
        SELECT DISTINCT BUY_PROD 
          FROM BUYPROD
         WHERE BUY_DATE BETWEEN TO_DATE('20050601') AND TO_DATE('20050630');
 2) 익명블록
   DECLARE
     V_PID PROD.PROD_ID%TYPE; --상품코드
     V_PNAME PROD.PROD_NAME%TYPE; --상품명
     V_CNT NUMBER:=0; --판매수량합계
     V_SUM NUMBER:=0; --판매금액합계
     
  CURSOR CUR_BUYPROD IS
     SELECT DISTINCT BUY_PROD 
      FROM BUYPROD
     WHERE BUY_DATE BETWEEN TO_DATE('20050601') AND TO_DATE('20050630');
     
  BEGIN 
    OPEN CUR_BUYPROD;
    LOOP 
      FETCH CUR_BUYPROD INTO V_PID;
      EXIT  WHEN CUR_BUYPROD%NOTFOUND;
      SELECT SUM(CART_QTY), SUM(CART_QTY*PROD_PRICE)
        INTO V_CNT, V_SUM
        FROM CART, PROD
       WHERE CART_NO LIKE '200506%'
         AND CART_PROD=V_PID
         AND CART_PROD=PROD_ID;
      SELECT PROD_NAME INTO V_PNAME
        FROM PROD
       WHERE PROD_ID=V_PID;
       
       DBMS_OUTPUT.PUT_LINE('상품코드 : ' ||V_PID);
       DBMS_OUTPUT.PUT_LINE('상품명 : ' ||V_PNAME);
       DBMS_OUTPUT.PUT_LINE('판매수량 : ' ||V_CNT);
       DBMS_OUTPUT.PUT_LINE('판매금액 : ' ||V_SUM);
       DBMS_OUTPUT.PUT_LINE('------------------');
       
       END LOOP;     
   END;
   

사용예) 거주지 도시를 키보드로 입력받아 그 거주지에 살고 있는 회원의 정보를 출력하시오
출력할 회원정보는 회원번호, 회원명, 주소이다.

   ACCEPT P_CITY PROMPT '시도 입력: '
   DECLARE
    CURSOR CUR_MEM(P_SIDO MEMBER.MEM_ADD1%TYPE ) IS -- OPEN 에서 받은거
      SELECT MEM_ID, MEM_NAME
        FROM MEMBER
       WHERE SUBSTR(MEM_ADD1,1,2)=SUBSTR(P_SIDO,1,2);
       V_MID MEMBER.MEM_ID%TYPE;
       V_MNAME MEMBER.MEM_NAME%TYPE;
       V_ADDR VARCHAR2(200);
       V_RESULT VARCHAR2(500);
   BEGIN
     OPEN CUR_MEM('&P_CITY');
     LOOP
       FETCH CUR_MEM INTO V_MID, V_MNAME;
       EXIT WHEN CUR_MEM%NOTFOUND;
       SELECT MEM_ADD1||' '||MEM_ADD2 INTO V_ADDR
         FROM MEMBER
        WHERE MEM_ID=V_MID;
       V_RESULT:=V_MID||' ' || V_MNAME||' ' ||V_ADDR;
       DBMS_OUTPUT.PUT_LINE(V_RESULT);
       END LOOP;
       CLOSE CUR_MEM;
       
   END;
   

2)WHILE 문

  • 개발언어의 WHILE문과 같은 기능 제공

  • 반복 수행전 조건을 체크하여 반복 수행여부 결정

    (사용형식)
    WHILE 조건 LOOP
    반복처리문(들);
    :
    END LOOP;

    • 조건이 만족하면(TRUE) 반복 수행

사용예) 구구단의 7단을 출력

    DECLARE
       V_CNT NUMBER:=1;
     BEGIN
       WHILE V_CNT<=9 LOOP
         DBMS_OUTPUT.PUT_LINE(7||'*'||V_CNT||'='||7*V_CNT);
         V_CNT:=V_CNT+1;
       END LOOP;
     END;

사용예) 2005년 금액을 기준으로 가장 많은 상품을 구매한 회원 5명의 회원명, 주소를 출력하는 익명블록을 작성하시오

  (가장 많은 상품을 구매한 회원 5명=>커서)
    SELECT A.CART_MEMBER
      FROM (SELECT CART_MEMBER, 
                    SUM(CART_QTY*PROD_PRICE)
              FROM CART, PROD
             WHERE CART_PROD=PROD_ID
               AND CART_NO LIKE '2005%'
             GROUP BY CART_MEMBER
             ORDER BY 2 DESC)A
      WHERE ROWNUM<=5;
      
      (익명블록-WHILE)
      DECLARE
      CURSOR CUR_CART01 IS
      SELECT A.CART_MEMBER
      FROM (SELECT CART_MEMBER, 
                    SUM(CART_QTY*PROD_PRICE)
              FROM CART, PROD
             WHERE CART_PROD=PROD_ID
               AND CART_NO LIKE '2005%'
             GROUP BY CART_MEMBER
             ORDER BY 2 DESC)A
      WHERE ROWNUM<=5;
      V_MID MEMBER.MEM_ID%TYPE;
      V_MNAME MEMBER.MEM_NAME%TYPE;
      V_ADDR VARCHAR2(200);
      
      BEGIN
        OPEN CUR_CART01;
        FETCH CUR_CART01 INTO V_MID;
        WHILE CUR_CART01%FOUND LOOP --커서에 데이터가 있으면 돔
          SELECT MEM_NAME, MEM_ADD1||' '||MEM_ADD2
            INTO V_MNAME, V_ADDR
            FROM MEMBER
           WHERE MEM_ID = V_MID;
           DBMS_OUTPUT.PUT_LINE(V_MID||' '||V_MNAME||' '||V_ADDR);
           FETCH CUR_CART01 INTO V_MID;
        END LOOP;
        CLOSE CUR_CART01;
      END;
      

3) FOR 문

  • 반복횟수를 정확히 알고 있거나 반복횟수가 중요한 경우 사용
    (일반적 FOR 문 사용형식)
    FOR 제어변수 IN [REVERSE] 초기값..최종값 LOOP
    반복처리문(들);
    :
    END LOOP;

사용예) 구구단의 7단을 출력

      DECLARE
      BEGIN
        FOR I IN 1..9 LOOP --I는 1~9까지 증가하면서 반복
          DBMS_OUTPUT.PUT_LINE(7||'*'||I||'='||7*I);
          END LOOP;
      END;

(역순)

 DECLARE
      BEGIN
        FOR I IN REVERSE 1..9 LOOP --I는 9~1까지 증가하면서 반복
          DBMS_OUTPUT.PUT_LINE(7||'*'||I||'='||7*I);
          END LOOP;
      END;

(커서를 위한 FOR 문 사용형식)
FOR 레코드명 IN 커서명|커서선언문 LOOP
반복처리문(들);
:
END LOOP;
- '레코드명' : 시스템에서 제공되며 커서의 각 행을 지칭한다
ex)커서의 컬럼이 a1, a2이고 레코드명이 REC인경우 커서컬럼 참조는 REC.a1 REC.a2 와 같이 사용=>변수사용이 불필요
- 커서명|커서선언문: 커서를 IN-LINE 서브쿼리 형식으로 직접 정의할 수 있음
- FOR문에 사용되는 커서는 OPEN, FETCH, CLOSE 문이 불필요

      DECLARE
      CURSOR CUR_CART01 IS
      SELECT A.CART_MEMBER AS CID
      FROM (SELECT CART_MEMBER, 
                    SUM(CART_QTY*PROD_PRICE)
              FROM CART, PROD
             WHERE CART_PROD=PROD_ID
               AND CART_NO LIKE '2005%'
             GROUP BY CART_MEMBER
             ORDER BY 2 DESC)A
      WHERE ROWNUM<=5;
      V_MNAME MEMBER.MEM_NAME%TYPE;
      V_ADDR VARCHAR2(200);
      
      BEGIN
        FOR REC IN CUR_CART01 LOOP 
          SELECT MEM_NAME, MEM_ADD1||' '||MEM_ADD2
            INTO V_MNAME, V_ADDR
            FROM MEMBER
           WHERE MEM_ID =REC.CID;
           DBMS_OUTPUT.PUT_LINE(REC.CID||' '||V_MNAME||' '||V_ADDR);
        END LOOP;
      END;
      
      
      
      
         DECLARE
      V_MNAME MEMBER.MEM_NAME%TYPE;
      V_ADDR VARCHAR2(200);
      
      BEGIN
        FOR REC IN (SELECT A.CART_MEMBER AS CID
                      FROM (SELECT CART_MEMBER, 
                                   SUM(CART_QTY*PROD_PRICE)
                               FROM CART, PROD
                              WHERE CART_PROD=PROD_ID
                                AND CART_NO LIKE '2005%'
                             GROUP BY CART_MEMBER
                             ORDER BY 2 DESC)A
                     WHERE ROWNUM<=5)           
          LOOP 
          SELECT MEM_NAME, MEM_ADD1||' '||MEM_ADD2
            INTO V_MNAME, V_ADDR
            FROM MEMBER
           WHERE MEM_ID =REC.CID;
           DBMS_OUTPUT.PUT_LINE(REC.CID||' '||V_MNAME||' '||V_ADDR);
        END LOOP;
      END;
profile
삐약..뺙뺙

0개의 댓글