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;