본문 바로가기
개발/database

[oracle] 오라클 plsql loop sample

by 가시죠 2021. 1. 17.
반응형

oracle plsql loop sample

DECLARE
  V_CNT INTEGER;
  CURSOR C1 IS
    SELECT LEVEL AS LVL, LEVEL * LEVEL AS LVL2
      FROM DUAL
    CONNECT BY LEVEL < 21;
  L2 C1%ROWTYPE;
BEGIN
  DBMS_OUTPUT.ENABLE;

  DBMS_OUTPUT.PUT_LINE('LOOP1');
  V_CNT := 0;
  DBMS_OUTPUT.PUT_LINE('V_CNT : ' || V_CNT);
  LOOP
    V_CNT := V_CNT + 1;
    DBMS_OUTPUT.PUT_LINE('V_CNT : ' || V_CNT);
    IF V_CNT = 5 THEN
      EXIT;
    END IF;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('LOOP2');
  V_CNT := 0;
  DBMS_OUTPUT.PUT_LINE('V_CNT : ' || V_CNT);
  LOOP
    V_CNT := V_CNT + 1;
    DBMS_OUTPUT.PUT_LINE('V_CNT : ' || V_CNT);
    EXIT WHEN V_CNT = 5;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('LOOP3');
  V_CNT := 0;
  DBMS_OUTPUT.PUT_LINE('V_CNT : ' || V_CNT);
  LOOP
    V_CNT := V_CNT + 1;
    DBMS_OUTPUT.PUT_LINE('V_CNT1 : ' || V_CNT);
    IF V_CNT < 5 THEN
      CONTINUE;
    END IF;
    DBMS_OUTPUT.PUT_LINE('V_CNT2 : ' || V_CNT);
    EXIT WHEN V_CNT = 5;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('LOOP4');
  V_CNT := 0;
  DBMS_OUTPUT.PUT_LINE('V_CNT : ' || V_CNT);
  LOOP
    V_CNT := V_CNT + 1;
    DBMS_OUTPUT.PUT_LINE('V_CNT1 : ' || V_CNT);
    CONTINUE WHEN V_CNT < 5;
    DBMS_OUTPUT.PUT_LINE('V_CNT2 : ' || V_CNT);
    EXIT WHEN V_CNT = 5;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('LOOP5');
  V_CNT := 5;
  DBMS_OUTPUT.PUT_LINE('V_CNT : ' || V_CNT);
  FOR i IN 1 .. V_CNT LOOP
    DBMS_OUTPUT.PUT_LINE('I : ' || i);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('LOOP6');
  V_CNT := 5;
  DBMS_OUTPUT.PUT_LINE('V_CNT : ' || V_CNT);
  FOR i IN REVERSE 1 .. V_CNT LOOP
    DBMS_OUTPUT.PUT_LINE('i : ' || i);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('CURSOR LOOP1');
  FOR L1 IN C1 LOOP
    DBMS_OUTPUT.PUT_LINE('LVL :' || L1.LVL);
    DBMS_OUTPUT.PUT_LINE('LVL2 :' || L1.LVL2);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('CURSOR LOOP2');
  OPEN C1;

  LOOP
  
    FETCH C1
      INTO L2;
  
    DBMS_OUTPUT.PUT_LINE('LVL :' || L2.LVL);
  
    DBMS_OUTPUT.PUT_LINE('LVL2 :' || L2.LVL2);
  
    EXIT WHEN C1%NOTFOUND;
  
  END LOOP;

  CLOSE C1;

END;

== == == == == == == == == == == == == DBMS_OUTPUT == == == == == == == == == == == == == == == ==
LOOP1
V_CNT :0
V_CNT :1
V_CNT :2
V_CNT :3
V_CNT :4
V_CNT :5

LOOP2
V_CNT :0
V_CNT :1
V_CNT :2
V_CNT :3
V_CNT :4
V_CNT :5

LOOP3
V_CNT :0
V_CNT1 :1
V_CNT1 :2
V_CNT1 :3
V_CNT1 :4
V_CNT1 :5
V_CNT2 :5

LOOP4
V_CNT :0
V_CNT1 :1
V_CNT1 :2
V_CNT1 :3
V_CNT1 :4
V_CNT1 :5
V_CNT2 :5

LOOP5
V_CNT :5
I :1
I :2
I :3
I :4
I :5

LOOP6
V_CNT :5
I :5
I :4
I :3
I :2
I :1

CURSOR LOOP1
LVL :1
LVL2 :1
LVL :2
LVL2 :4
LVL :3
LVL2 :9
LVL :4
LVL2 :16
LVL :5
LVL2 :25
.
.
.
LVL :19
LVL2 :361
LVL :20
LVL2 :400

CURSOR LOOP2
LVL :1
LVL2 :1
LVL :2
LVL2 :4
LVL :3
LVL2 :9
LVL :4
LVL2 :16
LVL :5
LVL2 :25
.
.
.
LVL :20
LVL2 :400
LVL :20
LVL2 :400


출처: https://ransu.tistory.com/ [잡다한 블로그]

반응형

댓글