Oracle loops

<<Previous  Next>>

Explain uses of endless looping in PL/SQL with an example.

End less looping has no bounds and the program continues to operate endlessly until EXIT WHEN condition evaluates to TRUE. The example below will go on printing number because the EXIT WHEN condition never evaluates to true. This can be used to check the error handling of the program.

declare
          numb number := 1;
              begin
                   loop
                       dbms_output.put_line ('Number is '|| numb);
                     numb:= numb + 1;
                        exit when numb < 1; -- Never TRUE
                   end loop;
              end;

Oracle - Loops - Feb 23, 2010 at 11:00 PM by Rajmeet Ghai

Provide an example of While loop using PL/SQL.

While Loop in PL/SQL is used to execute some code until the condition becomes true:

DECLARE
    a NUMBER := 0;
           BEGIN
                  WHILE a <= 10 LOOP
                              a := a + 1;
    DBMS_OUTPUT.PUT_LINE('The value of a is ' || a);
                  END LOOP;
            END;

Oracle - Loops - Feb 23, 2010 at 11:00 PM by Rajmeet Ghai

Define Repeat-until loop in PL/SQL.

REPEAT UNTIL or DO WHILE loop executes the code at least once before checking for the condition.

Oracle - Loops - Feb 23, 2010 at 11:00 PM by Rajmeet Ghai

Provide an example of For loop using PL/SQL.

FOR loop in PL/SQL executes the code for a specific number of times.

Example:
Here, the for loop will print the number from 1(lower bound) to .. 10(upper bound)
BEGIN
             FOR num IN 1..10
    LOOP
             dbms_output.put_line(num);
    END LOOP;
END;

Also read

Oracle views

What is the content view and stacked view?, Explain the different types of canvas views, Explain the significance of OR REPLACE, FORCE and NOFORCE while creating view.............

Oracle table

Explain drop and truncate table command, Write the command to view the structure of the table, What are the limitation of alter command?, Explain Alter Table Command. What are the limitations of Alter Table command?............

Oracle Synonym

Table references can be complicated. So oracle allows you create synonym for a complicated reference. It renames a table reference..........

Oracle sequences

A field in oracle can be kept as auto incremented by using sequence. it can be used to create a number sequence...........

<<Previous  Next>>

Write your comment - Share Knowledge and Experience