Basic Loop In Oracle

Basic Loop Statement:-

The Loops means run the same statements with a series of differant values.


Structure of basic loop is
[label  ] loop
Stetements
end loop[label];

with each iteration of the loop statement run and control returns to the top of the loop.
To avoid an infinite loop ,we must use exit loop.exit statement exists the current iteration of a loop,eigher conditionally,or

unconditionally.

Example :-Write a pl sql block to print 1..10 numbers using basic loop.

DECLARE
  NUM NUMBER := 1;
BEGIN
  LOOP
    DBMS_OUTPUT.PUT_LINE(NUM);
    NUM := NUM+1;
    EXIT WHEN NUM>10;
  END LOOP;
END;


Example :- 2. List the Name,salary  of an employees from Employee_id 100 ...To 109 using basic loop.

DECLARE
  V_NAME EMPLOYEES.LAST_NAME%TYPE;
  V_SAL  EMPLOYEES.SALARY%TYPE;
BEGIN
  FOR V_ID IN 100..109 LOOP
      SELECT LAST_NAME,SALARY
      INTO   V_NAME,V_SAL
      FROM   EMPLOYEES
      WHERE  EMPLOYEE_ID=V_ID;
      DBMS_OUTPUT.PUT_LINE(V_NAME||' GETS '||V_SAL);
  END LOOP;
END;

Output:-
King GETS 24000
Kochhar GETS 17000
De Haan GETS 17000
Hunold GETS 9000
Ernst GETS 6000
Austin GETS 4800
Pataballa GETS 4800
Lorentz GETS 4200
Greenberg GETS 12000
Faviet GETS 9000

PL/SQL procedure successfully completed.

Example 3:-  List the Name,salary,bonus based on salary  of an employees from Employee_id 100 ...To 109 using basic loop.

DECLARE
  lNAME EMPLOYEES.LAST_NAME%TYPE;
  V_SAL  EMPLOYEES.SALARY%TYPE;
  V_BONUS V_SAL%TYPE;
BEGIN
  FOR EMPID IN 100..109 LOOP
           SELECT  LAST_NAME,SALARY
            INTO       lNAME,V_SAL
            FROM     EMPLOYEES
           WHERE   EMPLOYEE_ID=EMPID;
           IF V_SAL   >= 20000 THEN
                V_BONUS := V_SAL*.2;
           ELSIF V_SAL>=15000 THEN
               V_BONUS := V_SAL*.3;
           ELSIF V_SAL>= 10000 THEN
               V_BONUS := V_SAL *.4;
           ELSE
               V_BONUS := V_SAL*.5;
           END IF;
           DBMS_OUTPUT.PUT_LINE(lNAME||' GETS '||V_SAL);
           DBMS_OUTPUT.PUT_LINE('BONUS OF '||lNAME||' IS '||V_BONUS);
  END LOOP;
END;
/

For Loop:-

Pl sql for loop is an iterative statement that allows to execute
a series of statements a fixed number of times.
The foll is a syntax for loop statement.

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

SQL> declare
  2  n1 number;
  3  begin
  4  for  i in 1.. 10
  5  loop
  6  dbms_output.put_line(i);
  7  end loop;
  8  end;
  9  /

Output :-

1
2
3
4
5
6
7
8
9
10

in the

PL/SQL procedure successfully completed.

in the next example we will use the RESERVE keyword to print a list of integers in

descending order.

declare
n1 number;
begin
for  i in reverse 1.. 10
loop
dbms_output.put_line(i);
end loop;
end;
/
Output

SQL> /
10
9
8
7
6
5
4
3
2
1



Example 2.  write a PL/SQL block to display names and salary of employees.
.
 DECLARE
   V_NAME EMPLOYEES.LAST_NAME%TYPE;
   V_SAL  EMPLOYEES.SALARY%TYPE;
 BEGIN
   FOR V_ID IN 100..109 LOOP
       SELECT LAST_NAME,SALARY
       INTO   V_NAME,V_SAL
       FROM   EMPLOYEES
       WHERE  EMPLOYEE_ID=V_ID;
       DBMS_OUTPUT.PUT_LINE(V_NAME||' GETS '||V_SAL);
   END LOOP;
 END;

Output:-

King GETS 24000
Kochhar GETS 17000
De Haan GETS 17000
Hunold GETS 9000
Ernst GETS 6000
Austin GETS 4800
Pataballa GETS 4800
Lorentz GETS 4200
Greenberg GETS 12000
Faviet GETS 9000

PL/SQL procedure successfully completed.



While Loop:-





The while statement again and again executes a block of statements while a particular condition is true.
it's sysntax can be expressed as
While(expression)
{
statements
}
while statement evaluate expression which must return a boolean value.if expression is true while statement executes statements in the while block & it continues testing the expression until the expression evaluates to false.

Example:-Print 1..10 numbers using while loop.

DECLARE
NUM NUMBER := 1;
BEGIN
WHILE NUM <=10 LOOP
DBMS_OUTPUT.PUT_LINE(NUM);
NUM := NUM+1;
END LOOP;
END;

Output

1
2
3
4
5
6
7
8
9
10

PL/SQL procedure successfully completed.