Day 60. [PL/SQL] CASE, WHILE, FOR, LOOP

CASE 문

 

SET SERVEROUTPUT ON;

DECLARE
    VN_SALARY NUMBER := 0;
    VN_DEPARTMENT_ID NUMBER := 0;
    
BEGIN
    VN_DEPARTMENT_ID := ROUND(DBMS_RANDOM.VALUE (10, 120), -1);
    
    SELECT SALARY
    INTO VN_SALARY
    FROM EMPLOYEES
    WHERE DEPARTMENT_ID = VN_DEPARTMENT_ID AND ROWNUM = 1;
    
    DBMS_OUTPUT.PUT_LINE(VN_SALARY);
    DBMS_OUTPUT.PUT_LINE(VN_DEPARTMENT_ID);
    
    CASE
        WHEN VN_SALARY BETWEEN 1 AND 3000 THEN
            DBMS_OUTPUT.PUT_LINE('낮음');
        WHEN VN_SALARY BETWEEN 3001 AND 6000 THEN
            DBMS_OUTPUT.PUT_LINE('중간');
        WHEN VN_SALARY BETWEEN 6001 AND 9000 THEN
            DBMS_OUTPUT.PUT_LINE('높음');
        ELSE
            DBMS_OUTPUT.PUT_LINE('최상위');
    END CASE;
END;
/

---

LOOP

 

DECLARE
    I NUMBER := 3;
    J NUMBER := 1;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE( I || ' * ' || J || ' = ' || I*J);
        J := J + 1;
        EXIT WHEN J = 10;
    END LOOP;
END;
/

-- Loop를 이용해 구구단 출력
DECLARE
    I NUMBER := 2;
    J NUMBER := 1;
BEGIN
    LOOP
        LOOP
            DBMS_OUTPUT.PUT_LINE( I || ' * ' || J || ' = ' || I*J);
            J := J + 1;
            EXIT WHEN J = 10;
        END LOOP;
        I := I + 1;
        J := 1;
        EXIT WHEN I = 10;
    END LOOP;
END;
/

DECLARE
    I NUMBER := 0;
    A NUMBER;
    J NUMBER;
BEGIN
    LOOP
        A := TRUNC(I/9) + 2;
        J := TO_NUMBER(MOD(I,9), '9') + 1;
        
        DBMS_OUTPUT.PUT_LINE(A || ' * ' || J || ' = ' || A*J);
        
        I := I + 1;
        EXIT WHEN I = 72;  
    END LOOP;
END;
/

---

WHILE

 

-- 1부터 100까지의 홀수의 합계 출력하는 익명 블럭을 작성하시오.

DECLARE
    I NUMBER := 1;
    NUM NUMBER := 0;
BEGIN
    WHILE I <= 100
    LOOP
        IF MOD(I, 2) = 1 THEN
            NUM := NUM + I;
        END IF;
        DBMS_OUTPUT.PUT_LINE(I || '          NUM : ' || NUM);    
        I := I + 1;
    END LOOP;
END;
/

---

FOR

 

-- FOR 루프를 사용해서 1에서 20의 합계
DECLARE
    I NUMBER := 1;
    NUM NUMBER := 0;
BEGIN
-- FOR I IN REVERSE 20..1 은 하나씩 줄이기
    FOR I IN 1..20
    LOOP
        NUM := NUM + I;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('NUM : ' || NUM);    
END;
/