For loop Oracle

FOR LOOP is the most used loop structure in Oracle.  FOR LOOP executes specified number of times which defined in the definition. So generally no need of any specific exit command for FOR LOOPs. FOR LOOP is suitable when the number of iterations are known.

Examples:-

1)  Printing 1 to 10 in ascending order

SQL> set serveroutput on
SQL> begin
  2  for i in 1..10 loop
  3    dbms_output.put_line(‘value of i = ‘ || i);
  4  end loop;
  5  end;
  6  /
value of i = 1
value of i = 2
value of i = 3
value of i = 4
value of i = 5
value of i = 6
value of i = 7
value of i = 8
value of i = 9
value of i = 10

PL/SQL procedure successfully completed.

2)  Printing 1 to 10 in descending order – Using reverse keyword

SQL> begin
  2  for i in reverse 1..10  loop
  3    dbms_output.put_line(‘value of i = ‘ || i);
  4  end loop;
  5  end;
  6  /
value of i = 10
value of i = 9
value of i = 8
value of i = 7
value of i = 6
value of i = 5
value of i = 4
value of i = 3
value of i = 2
value of i = 1

PL/SQL procedure successfully completed.

Note :- In the above example variable ‘i’ is called index. Index variable cannot be modified programmatically ( or cannot be used as assignment target). see below

SQL> begin
2  for i in 1..10  loop
3    dbms_output.put_line(‘value of i = ‘ || i);
4    i := 5;
5  end loop;
6  end;
7  /
i := 5;
*
ERROR at line 4:
ORA-06550: line 4, column 3:
PLS-00363: expression ‘I’ cannot be used as an assignment target
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored

Read also – While loop , goto statement

Advertisements

Author: sqlandplsql

To learn more about the Oracle, SQL, PL SQL, Performance Tuning, Database Modeling, Ubuntu, MySQL etc .Suggestions, comments, feedbacks and referrals are highly appreciated.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s