오라클 SQL(PL/SQL 제어문)

PL/SQL제어문

​

IF문

-특정 조건에 따라 처리를 하는 것

DECLARE
  vn_num1 NUMBER :=1;
  vn_num2 NUMBER :=2;
BEGIN
  IF vn_num1 >= vn_num2 THEN
     DBMS_OUTPUT.PUT_LINE(vn_num1 ||'이큰수');
  ELSE
     DBMS_OUTPUT.PUT_LINE(vn_num2 ||'이큰수');
  END IF;
END;

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);
    
    IF vn_salary BETWEEN 1 AND 3000 THEN
       DBMS_OUTPUT.PUT_LINE('낮음');
    ELSIF vn_salary BETWEEN 3001 AND 6000 THEN
       DBMS_OUTPUT.PUT_LINE('중간');
    ELSIF vn_salary BETWEEN 6001 AND 10000 THEN
       DBMS_OUTPUT.PUT_LINE('높음');
    ELSE
       DBMS_OUTPUT.PUT_LINE('최상위');
       
    END IF;
END;

case문

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);
  
  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 10000 THEN
           DBMS_OUTPUT.PUT_LINE('높음');
       ELSE
           DBMS_OUTPUT.PUT_LINE('최상위');
  END CASE;
END;

LOOP문

DECLARE
  vn_base_num NUMBER :=3;
  vn_cnt NUMBER :=1;
BEGIN
 LOOP
   DBMS_OUTPUT.PUT_LINE (vn_base_num || '*' || vn_cnt || '='
                          || vn_base_num * vn_cnt);
   vn_cnt :=vn_cnt +1;
   EXIT WHEN vn_cnt > 9;
 END LOOP;
END;

WHILE문

DECLARE
  vn_base_num NUMBER :=3;
  vn_cnt NUMBER :=1;
BEGIN
    WHILE vn_cnt <=9
    LOOP 
      DBMS_OUTPUT.PUT_LINE (vn_base_num || '*'||vn_cnt||'='
                            || vn_base_num*vn_cnt);
      vn_cnt := vn_cnt+1;
    END LOOP;
END;

DECLARE
  vn_base_num NUMBER :=3;
  vn_cnt NUMBER :=1;
BEGIN
    WHILE vn_cnt <=9
    LOOP 
      DBMS_OUTPUT.PUT_LINE (vn_base_num || '*'||vn_cnt||'='
                            || vn_base_num*vn_cnt);
      EXIT WHEN vn_cnt=5;
      vn_cnt := vn_cnt+1;
    END LOOP;
END;

FOR문

DECLARE
  vn_base_num NUMBER :=3;
BEGIN
  FOR i IN 1..9
  LOOP
    DBMS_OUTPUT.PUT_LINE(vn_base_num || '*' || i || '=' || vn_base_num * i);
  END LOOP;
END;

FOR문 REVERS

-FOR문을 반대로 실행

DECLARE
  vn_base_num NUMBER :=3;
BEGIN
  FOR i IN REVERSE 1..9
  LOOP
    DBMS_OUTPUT.PUT_LINE(vn_base_num || '*' || i || '=' || vn_base_num * i);
  END LOOP;
END;

-CONTINUE문

DECLARE
  vn_base_num NUMBER :=3;
BEGIN
  FOR i IN 1..9
  LOOP
    CONTINUE WHEN i=5;
    DBMS_OUTPUT.PUT_LINE(vn_base_num || '*' || i || '=' || vn_base_num * i);
  END LOOP;
END;

GOTO문

-라벨을 사용해서 원하는 위치로 GOTO함

-잘사용하지 않음

DECLARE
  vn_base_num NUMBER :=3;
BEGIN
  <<third>>
  FOR i IN 1..9
  LOOP
    DBMS_OUTPUT.PUT_LINE(vn_base_num || '*' || i || '=' || vn_base_num * i);
    IF i=3 THEN
        GOTO fourth;
    END IF;
  END LOOP;
  
  <<fourth>>
  vn_base_num :=4;
  FOR i IN 1..9
  LOOP
    DBMS_OUTPUT.PUT_LINE(vn_base_num || '*' || i || '=' || vn_base_num * i);
  END LOOP;
END;

NULL문

-ELSE절을 수행할때 아무것도 처리하지 않고 싶은 경우 NULL문을 사용한다.

-ELSE NULL;