Question

Write a PL/SQL program to calculate factorial of a given number.

Answer 1

Of course. Here is a complete PL/SQL program to calculate the factorial of a number, including detailed explanations and examples of how to run it.

A factorial of a non-negative integer n, denoted by n!, is the product of all positive integers less than or equal to n. For example, 5! = 5 * 4 * 3 * 2 * 1 = 120. By definition, 0! = 1.

---

The PL/SQL Program

This anonymous PL/SQL block will:
1. Accept a number from the user.
2. Validate that the number is not negative.
3. Calculate the factorial using a loop.
4. Display the result.
5. Handle potential errors gracefully using an EXCEPTION block.

`sql
-- This command is needed in tools like SQL*Plus or SQL Developer to see the output
SET SERVEROUTPUT ON;

-- Start of the PL/SQL block
DECLARE

-- Variable to hold the number provided by the user.
-- The '&' creates a substitution variable that prompts for input.
v_number      NUMBER := &Enter_a_Non_Negative_Number;

-- Variable to store the calculated factorial.
-- Initialize to 1 because 0! = 1 and it's the multiplicative identity.
v_factorial   NUMBER := 1;

BEGIN

-- Step 1: Validate the input. Factorial is not defined for negative numbers.
IF v_number < 0 THEN
    -- If the number is negative, raise a predefined exception to be handled below.
    RAISE VALUE_ERROR;
END IF;

-- Step 2: Calculate the factorial using a loop.
-- The loop will run from 1 up to the given number.
-- If the input is 0, this loop will not execute, leaving v_factorial as 1 (which is correct).
IF v_number > 0 THEN
    FOR i IN 1..v_number LOOP
        v_factorial := v_factorial * i;
    END LOOP;
END IF;

-- Step 3: Display the result.
DBMS_OUTPUT.PUT_LINE('------------------------------------');
DBMS_OUTPUT.PUT_LINE('The factorial of ' || v_number || ' is: ' || v_factorial);
DBMS_OUTPUT.PUT_LINE('------------------------------------');

-- The EXCEPTION section handles errors that occur in the BEGIN block.
EXCEPTION

-- This handler catches the VALUE_ERROR we raised for negative numbers,
-- or if the factorial result becomes too large for the NUMBER data type.
WHEN VALUE_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('------------------------------------');
    DBMS_OUTPUT.PUT_LINE('ERROR: Please enter a non-negative integer.');
    DBMS_OUTPUT.PUT_LINE('Factorial is not defined for negative numbers.');
    DBMS_OUTPUT.PUT_LINE('------------------------------------');
    
-- A catch-all handler for any other unexpected errors.
WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);

END;
/
`

Explanation of the Code

1. DECLARE Section:
   v_number NUMBER := &Enter_a_Non_Negative_Number;: This declares a numeric variable v_number and uses & to prompt the user for input when the script is run.
   v_factorial NUMBER := 1;: This declares a numeric variable to hold the result. It is crucial to initialize it to 1, because 0! is 1, and for any other number, the multiplication will start with 1 * 1, 1 * 2, etc.

2. BEGIN Section (The Main Logic):
   IF v_number < 0 THEN ... RAISE VALUE_ERROR;: This is our input validation. If the user enters a negative number, we manually trigger the VALUE_ERROR exception, which immediately stops execution and passes control to the EXCEPTION block.
   FOR i IN 1..v_number LOOP ... END LOOP;: This is the core of the factorial calculation.

   *   The `FOR` loop iterates from `i = 1` up to the number provided.
   *   In each iteration, `v_factorial` is updated by multiplying it with the current value of `i`.
   *   Example for `v_number = 4`:
       *   `v_factorial` starts as `1`
       *   `i = 1`: `v_factorial` becomes `1 * 1 = 1`
       *   `i = 2`: `v_factorial` becomes `1 * 2 = 2`
       *   `i = 3`: `v_factorial` becomes `2 * 3 = 6`
       *   `i = 4`: `v_factorial` becomes `6 * 4 = 24`
   

   • DBMS_OUTPUT.PUT_LINE(...): If the calculation is successful, this line prints the final result in a user-friendly format.

3. EXCEPTION Section (Error Handling):
   WHEN VALUE_ERROR THEN: This block catches the VALUE_ERROR. It will execute if we RAISE it for a negative number, or if the result of the factorial becomes too large for a NUMBER variable to hold (numeric overflow).
   WHEN OTHERS THEN: This is a general "catch-all" handler for any other type of error that might occur, ensuring the program doesn't crash unexpectedly.

How to Run and Test

1. Copy the entire code block into your SQL client (like Oracle SQL Developer or SQL*Plus).
2. Execute the script.
3. Your tool will prompt you to enter a value.

Test Case 1: A standard positive number

• When prompted, enter: 5

• Expected Output:
  `

  Enter value for enter_a_non_negative_number: 5

  The factorial of 5 is: 120

  PL/SQL procedure successfully completed.
  `

Test Case 2: The edge case of zero

• When prompted, enter: 0

• Expected Output:
  `

  Enter value for enter_a_non_negative_number: 0

  The factorial of 0 is: 1

  PL/SQL procedure successfully completed.
  `

Test Case 3: An invalid negative number

• When prompted, enter: -3

• Expected Output:
  `

  Enter value for enter_a_non_negative_number: -3

  ERROR: Please enter a non-negative integer.

  Factorial is not defined for negative numbers.

  PL/SQL procedure successfully completed.
  `