Loops: There may be a situation to execute a block of code several number of times. A
LOOP statement executes a sequence of statements multiple times
· Basic loop
·
WHILE loop
·
FOR loop (Reverse for loop)
· Cursor
FOR loop
Basic LOOP : Basic loop structure encloses sequence of statements in between the LOOP and END LOOP statements. With each iteration, the sequence of statements is executed and then control resumes at the top of the loop.
DECLARE
x NUMBER := 10;
BEGIN
LOOP
DBMS_OUTPUT.put_line (x);
x := x + 10;
IF x > 50
THEN
EXIT;
END IF;
END LOOP;
-- after exit, control resumes here
DBMS_OUTPUT.put_line ('After Exit x is: ' || x);
END;
WHILE LOOP: A WHILE LOOP statement in PL/SQL programming language repeatedly executes a target statement as long as a given condition is true.
DECLARE
a NUMBER (2) := 10;
BEGIN
WHILE a < 20
LOOP
DBMS_OUTPUT.put_line ('value of a: ' || a);
a := a + 1;
END LOOP;
END;
FOR LOOP : A FOR LOOP is a repetition control structure that allows you to efficiently write a loop that needs to execute a specific number of times
DECLARE
a NUMBER (2);
BEGIN
FOR a IN 10 .. 20
LOOP
DBMS_OUTPUT.put_line ('value of a: ' || a);
END LOOP;
END;
Reverse FOR LOOP Statement : By default, iteration proceeds from the initial value to the final value, generally upward from the lower bound to the higher bound. You can reverse this order by using the REVERSE keyword. In such case, iteration proceeds the other way. After each iteration, the loop counter is decremented
DECLARE
a NUMBER (2);
BEGIN
FOR a IN REVERSE 10 .. 20
LOOP
DBMS_OUTPUT.put_line ('value of a: ' || a);
END LOOP;
END;
Cursor FOR LOOP Statement: The cursor FOR LOOP statement implicitly declares its loop index as a record variable of the row type that a specified cursor returns and opens a cursor. With each iteration, the cursor FORLOOP statement fetches a row from the result set into the record. When there are no more rows to fetch, the cursor FOR LOOP statement closes the cursor. The cursor also closes if a statement inside the loop transfers control outside the loop or if PL/SQL raises an exception
BEGIN
FOR item
IN ( SELECT ename, job,
sal * 10 AS dream_salary
FROM scott.emp
WHERE ROWNUM <= 5
ORDER BY ename DESC, job ASC)
LOOP
DBMS_OUTPUT.PUT_LINE (
item.ename || ' dreams of making ' || item.dream_salary);
END LOOP;
END;