2022-12-01 데이터 베이스 오라클 SQL : 반복문

- 오라클의 반복문은 LOOP / WHILE / FOR 문이 제공됨
 - 주로 커서를 위한 명령으로 사용됨

 1. LOOP문
  - 반복문의 기본 구조 제공
  - 무한루프

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

 

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

 DECLARE
      L_CNT NUMBER:=0; -- 초기값 0
 BEGIN
   LOOP
      L_CNT:=L_CNT+1;   -- 증가시키는 명령
   EXIT WHEN L_CNT>=10; -- 10보다 크면 안된다. 혹시 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=PROD_ID
               AND SUBSTR(C.CART_NO,1,6) BETWEEN '202001' AND '202006'
             GROUP BY C.CART_MEMBER)A
     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;

-- OUTER JOIN 구매금액이 없어도 결과가 출력이 됨...  

 

---

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

(사용형식)
  WHILE 조건 LOOP
    반복수행할 명령문(들);
  END LOOP;

   . '조건'이 true 이면 반복을 수행하고, false이면 반복처리를 하지 않음
   . 반복횟수가 중요하지 않거나, 반복횟수를 알수 없는 경우
   . 반복종료의 조건을 알고 있는 경우

 

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

 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 
      -- I라는 변수는 오라클 시스템에서 알아서 타입명 맞춤 '최초값..최종값'
       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;

    . '커서명|커서용SELECT문' : 선언부에서 선언된 커서나 또는 커서를 구경하는 SELECT문을
      in_line 서브쿼리로 사용할 수 있음
    . '레코드(REC)'는 시스템에서 확보해 주며, 커서 내의 각행을 지칭하는 포인트 역활을 함
    . 커서의 각 열의 참조는 '레코드.열이름' 형태를 사용하여 수행
    . 커서를 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   -- REC : 포인터(참조형 변수)/지칭하는 위치
        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;
--     CLOSE CUR_MEM_ADD; -- 자동으로 FOR문을 벗어남. 생략가능
    END;

-- 커서와 가장 궁합이 잘맞는 반복문은 FOR문이다.

 

변수를 이용해서 하나씩 넣어주는 기능이 필요 없음.변수 대신에 커서에 저장되어있는 컬럼명 그대로 사용

 

구매금액은 커서에 의해서 결정되는게 아니고, 계산에 의해서 필요한 기억공간이기 때문에.

 

-- 커서 이름을 쓰는 대신에 인라인 서브쿼리 기술
 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;