[Oracle] PL/SQL(2) IF, LOOP, FOR

 

PL/SQL

*제어문(조건문)

 

DECLARE
    v_no NUMBER := 7;
    v_score NUMBER := 80;

BEGIN
    --단수 IF~END IF
    IF v_no = 7 THEN 
        dbms_output.put_line('7입니다.');
    END IF;

   --IF ~ ELSE ~ END IF
    IF v_no = 5 THEN
        dbms_output.put_line('5입니다.');
   ELSE
        dbms_output.put_line('5가 아니다.');
END IF;

    --다중 IF ~ ELSIF ~END IF
    IF v_score > 90 THEN
         dbms_output.put_line('A학점');
    ELSIF v_score > 80 THEN
         dbms_output.put_line('B학점');
    ELSIF v_score > 70 THEN
         dbms_output.put_line('C학점');
    ELSE
         dbms_output.put_line('D학점');
END IF;

END;

  --단수 IF~END IF 결과

--IF ~ ELSE ~ END IF 결과

 --다중 IF ~ ELSIF ~END IF 결과

 

10~120 중에 임의의 부서번호를 받아서 해당 부서의 평균급여에 따라서 등급이 출력

DECLARE
    v_department_id NUMBER;
    v_salary NUMBER := 0;

BEGIN

v_department_id := ROUND(DBMS_RANDOM.VALUE(10, 120), -1);

SELECT AVG(salary) INTO v_salary
      FROM employees   
      group by department_id
      HAVING department_id = v_department_id;

IF v_salary BETWEEN 1 AND 3000 THEN
      dbms_output.put_line('낮음');
    ELSIF v_salary BETWEEN 3000 AND 6000 THEN
      dbms_output.put_line('보통');
    ELSE
      dbms_output.put_line('높음');
END IF;

END;

 

---

LOOP(반복문)

DECLARE
    i NUMBER := 0;

BEGIN
        LOOP
             i := i+1;
            --조건
            EXIT WHEN i > 10;
            dbms_output.put_line(i);
        END LOOP;

END;

---

WHILE 문

DECLARE
    i NUMBER := 0;

BEGIN
            WHILE i < 10 LOOP
                    i := i+1;
                     dbms_output.put_line(i);
            END LOOP;
END;

---

FOR 문

DECLARE
    i NUMBER := 0;

BEGIN
            FOR i IN 1..10 LOOP
                  dbms_output.put_line(i);
            END LOOP;
END;

FOR => 구구단 전체 출력

BEGIN
--구구단 출력
  FOR i IN 2..9 LOOP
    FOR j IN 1..9 LOOP
    dbms_output.put_line(i ||'*'|| j ||' = ' || i * j);
    END LOOP;
    dbms_output.put_line('');
  END LOOP;
END;