spamulapati

PL/SQL – Loops

A LOOP statement is an iterative control statement that allows you to executes a sequence of statements multiple times.

PL/SQL provides these loop statements.

  • Basic loop
  • FOR loop
  • WHILE loop
  • Cursor FOR loop

Loop Control Statements

To prevent an infinite loop, at least one statement must transfer control outside the loop.
The statements that can transfer control outside the loop are

  • CONTINUE
  • EXIT / EXIT WHEN
  • GOTO

CONTINUE

The CONTINUE statement exits the current iteration of a loop, either conditionally or unconditionally, and transfers control to the next iteration of either the current loop or an enclosing labeled loop.

Restrictions on CONTINUE Statement

  • A CONTINUE statement must be inside a LOOP statement.
  • A CONTINUE statement cannot cross a subprogram or method boundary.

EXIT

The EXIT statement exits the current iteration of a loop, either conditionally or unconditionally, and transfers control to the end of either the current loop or an enclosing labeled loop.

Restriction on EXIT Statement

  • An EXIT statement must be inside a LOOP statement.

GOTO

The GOTO statement transfers control to a labeled block or statement.

Restrictions on GOTO Statement

  • A GOTO statement cannot transfer control into an IF statement, CASE statement, LOOP statement, or sub-block.
  • A GOTO statement cannot transfer control from one IF statement clause to another, or from one CASE statement WHEN clause to another.
  • A GOTO statement cannot transfer control out of a subprogram.
  • A GOTO statement cannot transfer control into an exception handler.
  • A GOTO statement cannot transfer control from an exception handler back into the current block.

Basic loop

With each iteration of the basic LOOP statement, its statements run and control returns to the top of the loop. The LOOP statement ends when a statement inside the loop transfers control outside the loop or raises an exception.

Syntax :

LOOP
sequence_of_statements;
END LOOP;

Basic loop statement with no EXIT keyword will be an INFINITE-LOOP that will never stop. To prevent this, the EXIT condition should be given inside this execution block so that control exit from the loop.

Syntax :

LOOP
 sequence_of_statements;
 EXIT;
END LOOP;
LOOP
 sequence_of_statements;
 EXIT WHEN condition;
END LOOP;

Example :

SET SERVEROUTPUT ON;
DECLARE 
counter NUMBER := 0;
BEGIN
 LOOP
 counter := counter + 1;
 DBMS_OUTPUT.PUT_LINE('Counter : ' || counter);
 IF counter = 5 THEN
 EXIT;
 END IF;
 END LOOP;
END;
/

Counter : 1
Counter : 2
Counter : 3
Counter : 4
Counter : 5


PL/SQL procedure successfully completed.

SET SERVEROUTPUT ON;
DECLARE 
counter NUMBER := 0;
BEGIN
 LOOP
 counter := counter + 1;
 DBMS_OUTPUT.PUT_LINE('Counter : ' || counter);
 EXIT WHEN counter=3;
 END LOOP;
END;
/
Counter : 1
Counter : 2
Counter : 3


PL/SQL procedure successfully completed.

FOR loop

PL/SQL FOR LOOP is an iterative statement that allows you to execute a sequence of statements a fixed number of times. The FOR LOOP statement ends when its index reaches a specified value, or when a statement inside the loop transfers control outside the loop or raises an exception.

Syntax :

FOR loop_counter IN [REVERSE] lower_bound .. higher_bound
LOOP
 sequence_of_statements;
END LOOP;

Example :

DECLARE
 n_counter NUMBER := 5;
BEGIN
 FOR i IN 1 .. n_counter LOOP
 DBMS_OUTPUT.PUT_LINE('Iteration :' || i);
 END LOOP;
END;
/

Iteration :1
Iteration :2
Iteration :3
Iteration :4
Iteration :5

PL/SQL procedure successfully completed.

DECLARE
 n_counter NUMBER := 5;
BEGIN
 FOR i IN 1 .. n_counter LOOP
 IF i=3 THEN
 CONTINUE;
 END IF;
 DBMS_OUTPUT.PUT_LINE('Iteration :' || i);
 END LOOP;
END;
/
Iteration :1
Iteration :2
Iteration :4
Iteration :5

PL/SQL procedure successfully completed.

In above example, CONTINUE statement skipped the iteration when the condition is true and transfer the control to the next iteration of the current loop.

CONTINUE WHEN statement :

DECLARE
 n_counter NUMBER := 5;
BEGIN
 FOR i IN 1 .. n_counter LOOP
 CONTINUE WHEN i=3;
 
 DBMS_OUTPUT.PUT_LINE('Iteration :' || i);
 END LOOP;
END;
/

Reverse FOR LOOP Statements

DECLARE
 n_counter NUMBER := 5;
BEGIN
 FOR i IN REVERSE 1 .. n_counter LOOP
 CONTINUE WHEN i=3;
 
 DBMS_OUTPUT.PUT_LINE('Iteration :' || i);
 END LOOP;
END;
/
Iteration :5
Iteration :4
Iteration :2
Iteration :1

PL/SQL procedure successfully completed.

FOR LOOP with GOTO Statement.

DECLARE
 n_counter NUMBER := 5;
BEGIN
 FOR i IN 1 .. n_counter LOOP
 
 DBMS_OUTPUT.PUT_LINE('Iteration :' || i);
 IF i=3 THEN
 GOTO counter_reached;
 END IF;
 END LOOP;
 
 <<counter_reached>>
 
 DBMS_OUTPUT.PUT_LINE('Counter reached to 3'); 
END;
/

In above example, when the condition is true, GOTO statement transfer the control out side the loop to the label “counter_reached”.

GOTO Statement Goes to Labeled NULL Statement

DECLARE
 done BOOLEAN;
BEGIN
 FOR i IN 1..10 LOOP
 IF done THEN
 GOTO end_loop;
 END IF;
 <<end_loop>>
 NULL;
 END LOOP;
END;
/

GOTO Statement Cannot Transfer Control into IF Statement

DECLARE
 valid BOOLEAN := TRUE;
BEGIN
 GOTO update_row;
 
 IF valid THEN
 <<update_row>>
 NULL;
 END IF;
END;
/
Error report -
ORA-06550: line 4, column 3:
PLS-00375: illegal GOTO statement; this GOTO cannot branch to label 'UPDATE_ROW'
ORA-06550: line 6, column 12:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

NULL Statement

The NULL statement only passes control to the next statement. Some languages refer to such an instruction as a no-op (no operation).

WHILE loop

The WHILE LOOP statement runs one or more statements while a condition is TRUE. The WHILE LOOP statement ends when the condition becomes FALSE or NULL, or when a statement inside the loop transfers control outside the loop or raises an exception.

Syntax :

WHILE condition
LOOP
 sequence_of_statements;
END LOOP;

Example :

DECLARE
 n_counter NUMBER := 5;
 n_factorial NUMBER := 1;
 n_number NUMBER;
BEGIN
 n_number := n_counter;
 WHILE n_counter > 0
 LOOP
 n_factorial := n_factorial * n_counter;
 n_counter := n_counter - 1;
 END LOOP;
 
 DBMS_OUTPUT.PUT_LINE('factorial of ' || n_number ||
 ' is ' || n_factorial);
 
END;
/

factorial of 5 is 120


PL/SQL procedure successfully completed.

We can include EXIT, EXIT WHEN, CONTINUE and GOTO statements in WHILE LOOP as well as per requirement.

Cursor FOR LOOP

The cursor FOR LOOP statement implicitly declares its loop index as a record variable of the row type that a specified cursor returns, and then opens a cursor.

With each iteration, the cursor FOR LOOP 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.

Examples :

Implicit Cursor FOR LOOP Statement

BEGIN
 FOR item IN (
 SELECT last_name, job_id
 FROM employees
 WHERE job_id LIKE '%CLERK%'
 AND manager_id > 120
 ORDER BY last_name
 )
 LOOP
 DBMS_OUTPUT.PUT_LINE
 ('Name = ' || item.last_name || ', Job = ' || item.job_id);
 END LOOP;
END;
/

Explicit Cursor FOR LOOP Statement

DECLARE
 CURSOR c IS
 SELECT last_name, job_id FROM employees
 WHERE job_id LIKE '%CLERK%' AND manager_id > 120
 ORDER BY last_name;
BEGIN
 FOR item IN c
 LOOP
 DBMS_OUTPUT.PUT_LINE
 ('Name = ' || item.last_name || ', Job = ' || item.job_id);
 END LOOP;
END;
/

Passing Parameters to Explicit Cursor FOR LOOP Statement

DECLARE
 CURSOR c (job VARCHAR2, max_wage NUMBER) IS
 SELECT * FROM employees
 WHERE job_id = job
 AND salary > max_wage;
BEGIN
 FOR person IN c('ST_CLERK', 3000)
 LOOP
 
 DBMS_OUTPUT.PUT_LINE (
 'Name = ' || person.last_name || ', salary = ' ||
 person.salary || ', Job Id = ' || person.job_id
 );
 END LOOP;
END;
/

We will see more about cursors in detail in next chapters.

Nested Loops

The loop statements can also be nested. The outer and inner loop can be of different types. In nested loop, for every one iteration value of the outer loop, the inner loop will be executed fully.

Syntax :

LOOP -- outer
 statements;
 LOOP -- inner
 statements;
 END LOOP;
 statements;
END LOOP;

Example :

Labelling of Loops

In PL/SQL, the loops can be labeled. The label should be enclosed between “<<” and “>>”. The labeling of loops particularly in nested loop codes will give more readability.

The label can be given to GOTO statement to exit from that particular loop. Using label, the control can be made to directly exit the outer loop of the nested loops from anyplace inside the loops.

Syntax :

<<outer_loop>>
LOOP
 statements;
 <<inner_loop>>
 LOOP
 statements;
 END LOOP;
 statements;
END LOOP';

 

Posted in SQL PL/SQLLeave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Google photo

You are commenting using your Google account. ( Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s