«Back to Home

Oracle Jump Start
Topics

How To Use The Loop Statement In Oracle

Description

There are some types of loop statement in Oracle, which are-
  • Simple - loop
  • While - loop
  • For – loop
  • Cursor – For – loop 
Simple loop

A simple loop statement executes a sequence of statements multiple times. The loop and End loop keywords enclose the statement.

Syntax
  1. Loop  
  2. statement 1;  
  3. statement 2;  
  4. statement 3;  
  5. ……  
  6. End loop;   
Example
  1. set serveroutput on;  
  2. Declare  
  3. v_counter number : = 0;  
  4. v_result number;  
  5. Begin  
  6. loop  
  7. v_counter : = v_counter + 1;  
  8. v_result : = 15 * v_counter;  
  9. DBMS_output.put_line(‘15’ || ‘*’ ||v_counter||’=’||v_result);  
  10. if v_counter >=10 then  
  11. Exit;  
  12. End if;  
  13. End loop;  
  14. End;  
1

WHILE LOOP

It executes block of the statements several times. It is best used, when number of iteration to be performed are unknown.

Syntax
  1. While condition Loop  
  2. statement 1;  
  3. statement 2;  
  4. statement 3;  
  5. ……  
  6. End loop;   
Example

In the example, given below, print multiplication table of 15, using while loop.
  1. Set ServerOutPut ON;  
  2. Declare  
  3. v_counter number :=1;  
  4. v_result number;  
  5. Begin  
  6. While  
  7. v_counter <=10 loop  
  8. v_result :=15 * v_counter;  
  9. DBMS_output.put_line(‘15’||’ * ’||v_counter||’ = ’||v_result);  
  10. v_counter := v_counter+1  
  11. End loop;  
  12. DBMS_output.put_line(‘outside the loop’);  
  13. End;  
2

While loop works with Boolean variable as a Test condition.

Example
  1. Set serveroutput ON;  
  2. Declare  
  3. v_Test nBoolean := TRUE;  
  4. v_counter number := 0;  
  5. Begin  
  6. while v_Test loop  
  7. v_counter :=v_counter +1;  
  8. DBMS_output.put_line(v_counter);  
  9. //Loop termination code//  
  10. if v_counter = 10  
  11. then  
  12. v_Test := false  
  13. End if;  
  14. End loop;  
  15. DBMS_output.put_line(‘outside the loop’);  
  16. End;  
3

FOR LOOP

For loop allows you to execute the block of statements repeatedly for fixed number of times.

Syntax
  1. For Loop_counter IN [Reverse]  
  2. Lower_Limit . . Upper_Limit Loop  
  3. Statement 1;  
  4. statement 2;  
  5. statement 3;  
  6. …  
  7. END Loop;  
Example
  1. Set Serveroutput ON;  
  2. Begin  
  3. For v_counter IN 1 . . 10 Loop  
  4. DBMS_OUTPUT.PUT_LINE(v_counter);  
  5. END Loop;  
  6. End;  
4

For Loop in Reverse Order
  1. Set serveroutput ON;  
  2. Begin  
  3. for v_counter in reverse 1 .. 10 loop  
  4. DBMS_output.put_line(v_counter);  
  5. End Loop;  
  6. End;  
5

Cursor For Loop

A cursor for loop is used, when you want to fetch the process and all the records in a cursor.

Syntax
  1. For Loop_Index IN Cursor_name  
  2. Loop  
  3. Statements  
  4. End Loop;  
Example
  1. SET SERVEROUTPUT ON;  
  2. DECLARE  
  3. CURSOR employee_cur IS SELECT emp_id, salary FROM employee;  
  4. BEGIN  
  5. FOR employee IN employee_cur  
  6. LOOP  
  7. DBMS_OUTPUT.PUT_LINE('EMP_Id'||' = '||' salary');  
  8. END LOOP;  
  9. END;  
6

Summary

Thus, we learnt, there are some types of loop in Oracle such as simple loop, while-loop, for-loop, cursor-for-loop. We learnt, how to use these loop statements in Oracle with the examples.