ตอนที่ 9 : การสร้าง Loop ข้อมูลบน Store Procedure (Oracle : Stored Procedure)

LOOP
   {...statements...}
END LOOP;

CREATE OR REPLACE PROCEDURE MY_STORED_PROCEDURE
AS
BEGIN
    DECLARE
    nRow NUMBER(2) := 0;
    BEGIN
      LOOP
         DBMS_OUTPUT.PUT_LINE('Row : ' || nRow);
         nRow := nRow + 1;
         EXIT WHEN nRow >= 10;
      END LOOP;
    END;
END;

EXEC MY_STORED_PROCEDURE();

FOR loop_counter IN [REVERSE] lowest_number..highest_number
LOOP
   {...statements...}
END LOOP;

CREATE OR REPLACE PROCEDURE MY_STORED_PROCEDURE
AS
BEGIN
     FOR i IN 1..10 
     LOOP
      DBMS_OUTPUT.PUT_LINE('Row : ' ||i);
     END LOOP;
END;

EXEC MY_STORED_PROCEDURE();

WHILE condition
LOOP
   {...statements...}
END LOOP;

CREATE OR REPLACE PROCEDURE MY_STORED_PROCEDURE
AS
BEGIN
    DECLARE
    nRow NUMBER(2) := 0;
    BEGIN   
      WHILE nRow <= 10
      LOOP
           DBMS_OUTPUT.PUT_LINE('Row : ' ||nRow);
           nRow := nRow + 1;
      END LOOP;
    END;
END;

EXEC MY_STORED_PROCEDURE();

FOR record_index in cursor_name
LOOP
   {...statements...}
END LOOP;

CREATE OR REPLACE PROCEDURE MY_STORED_PROCEDURE
AS
BEGIN
  FOR CusInfo IN (SELECT * FROM CUSTOMER)
  LOOP
      DBMS_OUTPUT.PUT_LINE(CusInfo.CUSTOMER_ID || ' ' || CusInfo.NAME || ' ' 
        || CusInfo.EMAIL || ' ' || CusInfo.COUNTRY_CODE || ' ' || CusInfo.BUDGET || ' ' || CusInfo.USED);
  END LOOP;
END;

EXEC MY_STORED_PROCEDURE();

CREATE OR REPLACE PROCEDURE MY_STORED_PROCEDURE
AS
  CURSOR curCustomer IS 
    SELECT CUSTOMER_ID, NAME, EMAIL  FROM CUSTOMER;
BEGIN
  FOR CusInfo IN curCustomer
  LOOP
      DBMS_OUTPUT.PUT_LINE(CusInfo.CUSTOMER_ID || ' ' || CusInfo.NAME || ' ' || CusInfo.EMAIL );
  END LOOP;
END;

EXEC MY_STORED_PROCEDURE();