[ORACLE] 반복문 221201-03

😊반복문

 - 오라클의 반복문은 LOOP, WHILE, FOR문이 제공됨
 - 주로 커서를 위한 명령으로 사용됨
 1. LOOP문
  - 반복문의 기본 구조 제공
  - 무한루프 

(사용형식)
  LOOP
   반복처리명령문(들);
   [EXIT WHEN 조건;]
   [반복처리명령문(들);]
  END LOOP; 
   .'EXIT WHEN 조건' : 조건을 평가하여 결과가 참(true)이면 반복을 벗어남

✔사용예) 구구단을 5단을 출력하는 블록생성

DECLARE
    L_CNT NUMBER:=0;
  BEGIN
    LOOP
      L_CNT:=L_CNT+1;
      EXIT WHEN L_CNT >=10;
      DBMS_OUTPUT.PUT_LINE('5 * '||L_CNT||' = '||5*L_CNT);
     END LOOP; 
  END;

✔사용예) 회원의 주소지가 '충남'인 회원들의 2020년 상반기 구매액으로 조회하는 익명블록을
                작성하시오. 출력사항은 회원번호,회원명,주소,구매금액

(PL/SQL을 사용하지 않은 경우)
 SELECT M.MEM_ID AS 회원번호,
        M.MEM_NAME AS 회원명,
        M.MEM_ADD1||' '||M.MEM_ADD2 AS 주소,
        A.CSUM AS 구매금액
   FROM MEMBER M,
        (SELECT C.CART_MEMBER AS CID,
                SUM(C.CART_QTY*P.PROD_PRICE) AS CSUM
           FROM CART C, PROD P
          WHERE C.CART_PROD=P.PROD_ID
            AND SUBSTR(C.CART_NO,1,6) BETWEEN '202001' AND '202006'
          GROUP BY C.CART_MEMBER)A  -- 2020년 상반기 회원별 구매금액
  WHERE M.MEM_ID=A.CID
    AND M.MEM_ADD1 LIKE '충남%';
   
  (PL/SQL)  
  DECLARE -- 출력사항
    L_MID MEMBER.MEM_ID%TYPE;
    L_MNAME VARCHAR2(100);
    L_ADDR VARCHAR2(500);
    L_SUM NUMBER:=0;
    CURSOR CUR_MEM_ADD IS
      SELECT MEM_ID, MEM_NAME,MEM_ADD1||' '||MEM_ADD2
        FROM MEMBER
       WHERE MEM_ADD1 LIKE '충남%'; 
  BEGIN
    OPEN CUR_MEM_ADD;
    LOOP
      FETCH CUR_MEM_ADD INTO L_MID,L_MNAME,L_ADDR; --커서 집합에 들어있는 변수
      EXIT WHEN CUR_MEM_ADD%NOTFOUND;
      SELECT SUM(A.CART_QTY*B.PROD_PRICE) INTO L_SUM
        FROM CART A, PROD B
       WHERE A.CART_PROD=B.PROD_ID
        AND SUBSTR(A.CART_NO,1,6) BETWEEN '202001' AND '202006'
        AND A.CART_MEMBER=L_MID;
        
      DBMS_OUTPUT.PUT_LINE('회원번호 : '  ||L_MID);
      DBMS_OUTPUT.PUT_LINE('회원명 : '  ||L_MNAME);
      DBMS_OUTPUT.PUT_LINE('주  소  : '  ||L_ADDR);
      DBMS_OUTPUT.PUT_LINE('구매금액 : '  ||L_SUM);
      DBMS_OUTPUT.PUT_LINE('---------------------------------'); 
    END LOOP;
    CLOSE CUR_MEM_ADD;
  END;

2. WHILE문
  - JAVA의 WHILE문과 유사 기능

(사용형식)
  WHILE 조건 LOOP
    반복수행할 명령문(들);
  END LOOP;
  . '조건'이 true 이면 반복을 수행하고, false이면 반복처리를 하지 않음
  . 반복횟수가 중요하지 않거나, 반복횟수를 알 수 없는 경우
  . 반복종료의 조건을 알고 있는 경우

✔사용예)  구구단 5단을 출력하시오.

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

✔사용예) 회원의 주소지가 '충남'인 회원들의 2020년 상반기 구매액으로 조회하는 익명블록을
                작성하시오. 출력사항은 회원번호,회원명,주소,구매금액(WHILE 사용)

 DECLARE -- 출력사항
    L_MID MEMBER.MEM_ID%TYPE;
    L_MNAME VARCHAR2(100);
    L_ADDR VARCHAR2(500);
    L_SUM NUMBER:=0;
    CURSOR CUR_MEM_ADD IS
      SELECT MEM_ID, MEM_NAME,MEM_ADD1||' '||MEM_ADD2
        FROM MEMBER
       WHERE MEM_ADD1 LIKE '충남%'; 
  BEGIN
    OPEN CUR_MEM_ADD;
   FETCH CUR_MEM_ADD INTO L_MID,L_MNAME,L_ADDR; --커서 집합에 들어있는 변수 
   WHILE CUR_MEM_ADD%FOUND LOOP 
      
      SELECT SUM(A.CART_QTY*B.PROD_PRICE) INTO L_SUM
        FROM CART A, PROD B
       WHERE A.CART_PROD=B.PROD_ID
        AND SUBSTR(A.CART_NO,1,6) BETWEEN '202001' AND '202006'
        AND A.CART_MEMBER=L_MID;
        
      DBMS_OUTPUT.PUT_LINE('회원번호 : '  ||L_MID);
      DBMS_OUTPUT.PUT_LINE('회원명 : '  ||L_MNAME);
      DBMS_OUTPUT.PUT_LINE('주  소  : '  ||L_ADDR);
      DBMS_OUTPUT.PUT_LINE('구매금액 : '  ||L_SUM);
      DBMS_OUTPUT.PUT_LINE('---------------------------------'); 
      FETCH CUR_MEM_ADD INTO L_MID,L_MNAME,L_ADDR;
    END LOOP;
    CLOSE CUR_MEM_ADD;
  END;

3. FOR 문
 - JAVA의 FOR와 유사한 기능 제공
 - 반복횟수를 알고 있거나 반복횟수가 중요한 경우 사용

(일반 FOR문 사용형식) 
  FOR 인덱스 IN [REVERSE] 초기값...최종값 LOOP
    반복처리명령문(들);
  END LOOP;

. '인덱스' : 시스템에서 자동으로 확보하는 제어변수로 초기값에서 최종값까지 
    1씩 변하는 값을 보관
 . 'REVERSE' : 역순으로(최종값 -> 초기값) 인덱스값을 운용

 

✔사용예) 구구단의 5단을 출력

 DECLARE
  
  BEGIN
    FOR I IN 1..9 LOOP
      DBMS_OUTPUT.PUT_LINE('5 * '||I||' = '||5*I);
    END LOOP; 
    FOR I IN  REVERSE 1..9 LOOP
      DBMS_OUTPUT.PUT_LINE('5 * '||I||' = '||5*I);
    END LOOP;
  END;

커서용 FOR문 사용형식)

 FOR 레코드 IN 커서명|커서용SELECT문 LOOP --한 행을 지칭하는 포인터변수
     반복처리명령문(들);
  END LOOP;

. '커서명|커서용 SLELECT 문': 선언부에서 선언된 커서나 또는 커서를 구성하는 SELECT 문을
    in-line 서브쿼리로 사용할 수 있음
  . '레코드'는 시스템에서 확보해주며, 커서 내의 각행을 지칭하는 포인트 역활을 함
  . 커서의 각 열의 참조는 '레코드.열이름'형태를 사용하여 수행
  . 커서를 FOR문을 이용하면 OPEN,FETCH,CLOSE를 생략한다.

 

✔사용예)  회원의 주소지가 '충남'인 회원들의 2020년 상반기 구매액으로 조회하는 익명블록을
                 작성하시오. 출력사항은 회원번호,회원명,주소,구매금액(FOR 사용)  --커서와 가장 궁합이 잘 맞음

 DECLARE -- 구매금액
    L_SUM NUMBER:=0;
    CURSOR CUR_MEM_ADD IS
      SELECT MEM_ID,MEM_NAME,MEM_ADD1,MEM_ADD2
        FROM MEMBER
       WHERE MEM_ADD1 LIKE '충남%'; 
  BEGIN
   FOR REC IN CUR_MEM_ADD LOOP    
      SELECT SUM(A.CART_QTY*B.PROD_PRICE) INTO L_SUM
        FROM CART A, PROD B
       WHERE A.CART_PROD=B.PROD_ID
        AND SUBSTR(A.CART_NO,1,6) BETWEEN '202001' AND '202006'
        AND A.CART_MEMBER=REC.MEM_ID;
        
      DBMS_OUTPUT.PUT_LINE('회원번호 : '  ||REC.MEM_ID);
      DBMS_OUTPUT.PUT_LINE('회원명 : '  ||REC.MEM_NAME);
      DBMS_OUTPUT.PUT_LINE('주  소  : '  ||REC.MEM_ADD1||' '||REC.MEM_ADD2);
      DBMS_OUTPUT.PUT_LINE('구매금액 : '  ||L_SUM);
      DBMS_OUTPUT.PUT_LINE('---------------------------------'); 
    END LOOP;
  END;  
--------------------------------------------------------------------------  
  DECLARE -- 구매금액
    L_SUM NUMBER:=0;
  BEGIN
   FOR REC IN (SELECT MEM_ID,MEM_NAME,MEM_ADD1,MEM_ADD2
                 FROM MEMBER
                WHERE MEM_ADD1 LIKE '충남%') LOOP    
      SELECT SUM(A.CART_QTY*B.PROD_PRICE) INTO L_SUM
        FROM CART A, PROD B
       WHERE A.CART_PROD=B.PROD_ID
        AND SUBSTR(A.CART_NO,1,6) BETWEEN '202001' AND '202006'
        AND A.CART_MEMBER=REC.MEM_ID;
        
      DBMS_OUTPUT.PUT_LINE('회원번호 : '  ||REC.MEM_ID);
      DBMS_OUTPUT.PUT_LINE('회원명 : '  ||REC.MEM_NAME);
      DBMS_OUTPUT.PUT_LINE('주  소  : '  ||REC.MEM_ADD1||' '||REC.MEM_ADD2);
      DBMS_OUTPUT.PUT_LINE('구매금액 : '  ||L_SUM);
      DBMS_OUTPUT.PUT_LINE('---------------------------------'); 
    END LOOP;
  END;