Several examples of loops in oracle
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;
BEGIN
x := 1;
FOR x IN REVERSE 1..10 LOOP
DBMS_OUTPUT.PUT_LINE('x = ' || x);
END LOOP;
DBMS_OUTPUT.PUT_LINE('end loop x= '|| x);
END;
WHILE Cycle Usage
DECLARE
x number;
BEGIN
x := 0;
WHILE x < 9 LOOP
x := x + 1;
DBMS_OUTPUT.PUT_LINE('x = ' || x);
END LOOP;
DBMS_OUTPUT.PUT_LINE('end loop x= '|| x);
END;
LOOP Cycle Usage
DECLARE
x number;
BEGIN
x := 0;
LOOP
x := x + 1;
EXIT WHEN x > 9;
DBMS_OUTPUT.PUT_LINE('x = ' || x);
END LOOP;
DBMS_OUTPUT.PUT_LINE('end loop x= '|| x);
END;
Advanced Usage 1: Traversing cursors with LOOP loops
DECLARE
CURSOR v_cursor IS
SELECT * FROM DIM_EMPLOYEE;
v_rowtype v_cursor%ROWTYPE;
BEGIN
OPEN v_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;
END;
Advanced Usage 2: Traversing cursors with FOR
DECLARE
CURSOR v_cursor IS
SELECT * FROM DIM_EMPLOYE;
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
DECLARE
CURSOR v_cursor IS
SELECT * FROM DIM_EMPLOYEE;
v_rowtype v_cursor%ROWTYPE;
BEGIN
OPEN v_cursor;
FETCH v_cursor INTO v_rowtype;
WHILE (v_cursor%FOUND) LOOP
DBMS_OUTPUT.PUT_lINE(v_rowtype.emp_name);
FETCH v_cursor INTO v_rowtype;
END LOOP;
CLOSE v_cursor;
END;
Tags:
Oracle
less
Posted on Fri, 22 May 2020 12:41:29 -0400 by e11even