1. Home
  2. Docs
  3. Oracle 19c PL/SQL
  4. 2 Control Statements
  5. FOR Loop

FOR Loop

/*
  Write a program to print numbers 1-10 in a single line

  Output:
  1 2 3 4 5 6 7 8 9 10
*/

SET SERVEROUTPUT ON;

DECLARE
  V_Result VARCHAR2(100);
BEGIN
  DBMS_OUTPUT.PUT_LINE('Illustration of FOR Loop :');
  FOR i IN 1 .. 10
  LOOP
    V_Result :=V_Result||' '||i;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(V_Result);
END;
/
/*
  Write a program to print numbers in triangular fashion

  Output:
    1
    1  2
    1  2  3
    1  2  3  4
    1  2  3  4  5
*/

DECLARE
  V_StartNum NUMBER := 1;
  V_EndNum   NUMBER := 5;
  V_Result   VARCHAR2(50);
BEGIN
  FOR I IN V_StartNum..V_EndNum
    LOOP
      V_Result :=V_Result||'  '||I;
      DBMS_OUTPUT.PUT_LINE(V_Result);
    END LOOP;
END;
/
--Factorial of a given number

DECLARE
  V_Start  NUMBER := 1;
  V_Number NUMBER := &inputNum;
  V_Fact   NUMBER := 1;
BEGIN
  FOR i IN V_Start..V_Number
    LOOP
      V_Fact := V_Fact*i;
    END LOOP;
  DBMS_OUTPUT.PUT_LINE('Factorial of '||V_Number||' is : '||V_Fact);
END;
/

Sometimes writing a code without thinking all the aspect might bring code bugs. For example above code works well for a positive integer, but does not work for zero or negative numbers. So, you must add validations for such scenarios to avoid future code bugs.

DECLARE
  V_Start NUMBER :=1;
  V_Number NUMBER :='&GNumber';
  V_Fact NUMBER :=1;
BEGIN
  IF V_Number =0 THEN
    DBMS_OUTPUT.PUT_LINE('Factorial of 0 is : 1');
  ELSIF V_Number <0 THEN
    DBMS_OUTPUT.PUT_LINE('Please enter a positive integer for factorial.');
  ELSE
    FOR I IN V_Start..V_Number
      LOOP
        V_Fact :=V_Fact*I;
      END LOOP;
    DBMS_OUTPUT.PUT_LINE('Factorial of '||V_Number||' is : '||V_Fact);
  END IF;
END;
/

Always check your code for logical errors before executing it. NULL is an important characteristic for any variable. A code must be throughly tested for NULLs. If not NULLs might break your code flow. Watch…

SQL> /
Enter value for gnumber: NULL
old   3:   V_Number NUMBER :='&GNumber';
new   3:   V_Number NUMBER :='NULL';
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error 
ORA-06512: at line 3
DECLARE
  V_Start NUMBER :=1;
  V_Number NUMBER :=&GNumber;
  V_Fact NUMBER :=1;
BEGIN
  IF V_Number IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('Fatal Error ! NULL encountered unable to proceed.');
  ELSIF V_Number =0 THEN
    DBMS_OUTPUT.PUT_LINE('Factorial of 0 is : 1');
  ELSIF V_Number <0 THEN
    FOR I IN V_Number..(V_Start-2)
      LOOP
        V_Fact :=V_Fact*I;
      END LOOP;
    DBMS_OUTPUT.PUT_LINE('Factorial of '||V_Number||' is : '||V_Fact);
  ELSE
    FOR I IN V_Start..V_Number
      LOOP
        V_Fact :=V_Fact*I;
      END LOOP;
    DBMS_OUTPUT.PUT_LINE('Factorial of '||V_Number||' is : '||V_Fact);
  END IF;
END;
/
Was this article helpful to you? Yes No

How can we help?