201105

2. WHILE문

- 애플리케이션 개발언어의 WHILE문과 같은 기능 제공

*사용형식

WHILE 조건
    LOOP
      반복처리문;
END LOOP;

- '조건'이 만족할 때 반복명령을 처리

EX

구구단의 7단을 춢력하시오

DECLARE
  V_BASE NUMBER:=7;
  V_CNT NUMBER:=0;
  V_RES VARCHAR2(50);
BEGIN
  WHILE V_CNT<9 LOOP
    V_CNT:=V_CNT+1;
    V_RES:=V_BASE||'*'||V_CNT||'='||V_BASE*V_CNT;
    DBMS_OUTPUT.PUT_LINE(V_RES);
  END LOOP;
  
  EXCEPTION WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('예외발생:'||SQLERRM);
END;

EXCEPTION WHEN OTHERS << 자바의 EXCEPTION클래스로 보내는것과 같다

자바의 PRINTSTACKTRACE < 오류발생을 추적해서 알아낼수있는것 이와비슷한게 오라클에서는 SQLERRM을 쓴다

EX

회원번호를 입력받아 회원명과 직업, 마일리지를 출력하는 커서를 작성하시오

WHILE문을 사용할 것

ACCEPT A_ID PROMPT '회원번호 : '
DECLARE 
  V_NAME    MEMBER.MEM_NAME%TYPE;
  V_JOB     MEMBER.MEM_JOB%TYPE;
  V_MILE    MEMBER.MEM_MILEAGE%TYPE;
  CURSOR CUR_MEM02(P_ID MEMBER.MEM_ID%TYPE) IS 
    SELECT  MEM_NAME, MEM_JOB, MEM_MILEAGE
    FROM    MEMBER
    WHERE   MEM_ID=P_ID;
BEGIN
  OPEN CUR_MEM02('&A_ID');
   FETCH CUR_MEM02 INTO V_NAME, V_JOB, V_MILE;
  WHILE CUR_MEM02%FOUND LOOP
   
    DBMS_OUTPUT.PUT_LINE('회원명:'||V_NAME);
    DBMS_OUTPUT.PUT_LINE('직업:'||V_JOB);
    DBMS_OUTPUT.PUT_LINE('마일리지:'||V_MILE);
    DBMS_OUTPUT.PUT_LINE('~*~*~*~*~*~');
    FETCH CUR_MEM02 INTO V_NAME, V_JOB, V_MILE;
     
END LOOP;

EXCEPTION WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('예외발생:'||SQLERRM);
    CLOSE CUR_MEM02;
END;

FETCH의 위치 유념해서 보기

 

커서를 왜쓰느냐

DECLARE
    V_NAME MEMBER.MEM_NAME%TYPE;
BEGIN
    SELECT MEM_NAME INTO V_NAME
    FROM MEMBER
    WHERE MEM_MILEAGE >= 3000;
    DBMS_OUTPUT.PUT_LINE(V_NAME);
END;

마일리지가 3000이 넘는사람은 여러사람있는데 V_NAME에 저장될수잇는 사람은 1명임

SELECT MEM_NAME에는 3000넘는 여러사람의 이름이 나옴 근데 그걸 V_NAME한자리에 저장시키려니까

이걸 해결하려면? 여러명 출력시켜놓고 나중에한사람씩 패치해서 뽑아내야함 >> 커서가 필요함

커서와 제일 궁합이 잘맞는 건 FOR문

커서를 쓰면 반드시 반복문이 따라와야한다. 행단위로 읽어오는걸 FETCH라고 한다.

실행영역에서 오픈되어질때 커서가 실행되어짐

오픈했다고 해서 되는게아니고 들어와서 있는지없는지 확인해야함 그게 FETCH

 

 

EX

회원테이블에서 마일리지가 3000이상인 회원들이 2005년에 구입한 실적을 조회하시오

구입실적목록은 회원번호, 회원명, 구입금액이다

DECLARE
  V_NAME MEMBER.MEM_NAME%TYPE;
  V_ID MEMBER.MEM_ID%TYPE;
  V_AMT NUMBER := 0;
  CURSOR CUR_MEM03 IS
    SELECT MEM_ID, MEM_NAME
      FROM MEMBER
     WHERE MEM_MILEAGE >= 3000;
BEGIN
  OPEN CUR_MEM03;
 FETCH CUR_MEM03 INTO V_ID, V_NAME;
 WHILE CUR_MEM03%FOUND LOOP
   SELECT SUM(CART_QTY*PROD_PRICE) INTO V_AMT
     FROM CART, PROD
    WHERE CART_PROD=PROD_ID
      AND CART_NO LIKE '2005%'
      AND CART_MEMBER=V_ID;
   DBMS_OUTPUT.PUT_LINE('회원번호 : '||V_ID);
   DBMS_OUTPUT.PUT_LINE('회원명 : '||V_NAME);
   DBMS_OUTPUT.PUT_LINE('구입금액 : '||V_AMT);
   DBMS_OUTPUT.PUT_LINE('==================');
   FETCH CUR_MEM03 INTO V_ID, V_NAME;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('회원수 : '||CUR_MEM03%ROWCOUNT);
   CLOSE CUR_MEM03;
END;
  

LOOP 문은 먼저 체크안하고 밑에서 체크하니까 자바의 두와일(무조건 한번실행을 전제)과 같은거임

와일문은 와일문 밖에서 페치한번 하니까 아님

 

3. FOR문

- 다른 언어의 FOR문과 같은 기능 제공

* (사용형식 - 일반적 FOR문)

FOR 변수 IN [REVERSE] 초기값..최종값
LOOP
  반복명령(들);
END LOOP;

알아서 최종값~최종값까지 1씩 증가됨

리버스를 쓰면 최종값~초기값까지 1씩 감소

변수는 시스템에서 알아서 선언해줌

- '변수'는 시스템에서 선언됨
- 'REVERSE'사용시 

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

사용형식 - 커서사용 for

FOR 레코드명 IN 커서명((매개변수 타입,...)]
LOOP
    커서처리문;
END LOOP;

 FOR문을 사용하는 커서는 OPEN과 FETCH,CLOSE문이 불필요(자동으로 처리됨)
    '커서명[(매개변수 타입,...)]'대신 커서 선언문이 올 수 있음(INLINE 커서)

EX

매개변수를 통하여 부서번호를 입력받아 그 부서에 속한 사원명과 급여를 출력하는 커서를 FOR문을 이용하여 작성하시오

DECLARE

CURSOR CUR_EMP03(P_DEPT EMPLOYEES.DEPARTMENT_ID%TYPE) IS
SELECT EMP_NAME, SALARY
FROM EMPLOYEES 
WHERE DEPARTMENT_ID=P_DEPT;

BEGIN
    FOR REC_EMP IN CUR_EMP03(50) LOOP
    DBMS_OUTPUT.PUT_LINE('사원명:'||REC_EMP,EMP_NAME);
    DBMS_OUTPUT.PUT_LINE('급여:'||REC_EMP,SALARY);
    DBMS_OUTPUT.PUT_LINE('----------------------------');
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('사원수:'||CUR_EMP03%ROWCOUNT||'명');
END;
  

안됨..고치기

 

INLINE 커서 사용해서 풀어보기

 

EX

회원테이블에서 마일리지가 3000이상인 회원들이 2005년에 구입한 실적을 조회하시오. 구입실적목록은 회원번호, 회원명, 구입금액이다. (FOR문사용)

DECLARE
V_AMT NUMBER:=0;
V_RES VARCHAR2(100);
CURSOR CUR_MEM04 IS
SELECT MEM_ID, MEM_NAME
FROM MEMBER
WHERE MEM_MILEAGE >=3000;
BEGIN
FOR REC_CART IN CUR_MEM04 LOOP
SELECT SUM(CART_QTY*PROD_PRICE) INTO V_AMT
FROM CART, PROD
WHERE CART_PROD=PROD_ID
AND CART_NO LIKE '2005%'
AND CART_MEMBER=REC_CART.MEM_ID;
V_RES:=REC_CART.MEM_ID||','||REC_CART.MEM_NAME||','||V_AMT;
DBMS_OUTPUT.PUT_LINE(V_RES);
DBMS_OUTPUT.PUT_LINE('--------------');
END LOOP;
END;