Several examples of loops in oracle

GOTO Usage

DECLARE
  x NUMBER;
BEGIN
  x := 0;
  <<repeat_loop>> 
  --Cycle Point  
  x := x + 1;  
  DBMS_OUTPUT.PUT_LINE(X);  
  IF x < 9 THEN   --When x Value of less than9time,ongotoreach repeat_loop    
    GOTO repeat_loop;  
  END IF;
END;

FOR loop usage

DECLARE
  x number;-- Declare Variables
BEGIN
  x := 1;-- initial value
  FOR x IN REVERSE 1..10 LOOP
    -- REVERSE From large to small
    DBMS_OUTPUT.PUT_LINE('x = ' || x);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('end loop x= '|| x); -- x = 1
END;

WHILE Cycle Usage

DECLARE
  x number;-- Declare Variables
BEGIN
  x := 0;-- initial value
  WHILE x < 9 LOOP
    x := x + 1;
    DBMS_OUTPUT.PUT_LINE('x = ' || x);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('end loop x= '|| x); -- x = 1
END;

LOOP Cycle Usage

DECLARE
  x number;-- Declare Variables
BEGIN
  x := 0;-- initial value
  LOOP
    x := x + 1;
    EXIT WHEN x > 9;
    DBMS_OUTPUT.PUT_LINE('x = ' || x);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('end loop x= '|| x); -- x = 1
END;

Advanced Usage 1: Traversing cursors with LOOP loops

-- Advanced Usage 1: Use LOOP Loop traversal cursor
DECLARE
  CURSOR v_cursor IS
  SELECT * FROM DIM_EMPLOYEE;-- Define Cursor

  v_rowtype v_cursor%ROWTYPE;-- Define row variables

BEGIN
  OPEN v_cursor;-- Open Cursor

  LOOP 
    FETCH v_cursor INTO v_rowtype ;

    EXIT WHEN v_cursor%NOTFOUND;

    DBMS_OUTPUT.PUT_lINE(v_rowtype.emp_name);
  END LOOP;

  CLOSE v_cursor;-- Close Cursor
END;

Advanced Usage 2: Traversing cursors with FOR

-- Advanced Usage 2: Use FOR Traversing cursors
DECLARE
  CURSOR v_cursor IS
  SELECT * FROM DIM_EMPLOYE;-- Define Cursor

  --v_rowtype v_cursor%ROWTYPE;-- Define row variables in FOR This one in the loop can also be undefined. FOR Loops are defined by default

BEGIN
  FOR v_rowtype IN v_cursor LOOP
    DBMS_OUTPUT.PUT_lINE(v_rowtype.emp_name);
  END LOOP;
END;

Advanced Usage 3: Traversing cursors with WHILE

-- Advanced Usage 3: Use WHILE Traversing cursors
DECLARE
  CURSOR v_cursor IS
  SELECT * FROM DIM_EMPLOYEE;-- Define Cursor

  v_rowtype v_cursor%ROWTYPE;-- Define row variables

BEGIN
  OPEN v_cursor;-- Open Cursor  


  FETCH v_cursor INTO v_rowtype;-- Enable Cursor and Value

  WHILE (v_cursor%FOUND) LOOP-- Enable before entering

    DBMS_OUTPUT.PUT_lINE(v_rowtype.emp_name);-- Since it was enabled once, this sentence came first, using the cursor that was enabled at initialization

    FETCH v_cursor INTO v_rowtype;  -- Move cursor down and take value 

  END LOOP;

  CLOSE v_cursor;-- Close Cursor
END;