SQL Functions - Factorial

SQL Functions - factorial

Here is a recursive solution:

CREATE FUNCTION dbo.Factorial ( @iNumber int )
RETURNS INT
AS
BEGIN
DECLARE @i  int

    IF @iNumber <= 1
        SET @i = 1
    ELSE
        SET @i = @iNumber * dbo.Factorial( @iNumber - 1 )
RETURN (@i)
END

Creating plsql function for Factorial program

Make this code "return res_fact;" out of loop you code will run, But above code work for only 5.

PL/SQL CREATE PROCEDURE - factorial

1. You're failing to initialize the local variable factorial. Uninitialized variables are initially null and multiplying null by any value produces null.
2. I don't see why you'd want your loop to go in reverse order. It doesn't matter since multiplication is communitive but it it unlikely to make your code easier to read/ debug/ follow.
3. You don't want to subtract 1 from the value you are multiplying by on each iteration of the loop. When i = 1, for example, you're multiplying factorial by 0 which means that (assuming you initialize factorial), you'd always end up with 0. You want to multiply by i so there is no need for the local variable num.

If I make those fixes

CREATE OR REPLACE PROCEDURE factorial_number(
    n NUMBER) 
AS
    factorial NUMBER := 1;  -- Initialize
BEGIN
    FOR i IN 1..n LOOP -- Loop normally
      dbms_output.put_line( 'Beginning iteration ' || i || ' of loop. ' ||
                            'Factorial = ' || factorial ||
                            ' multiplying by ' || i );
      factorial := factorial * i; -- Multiply by i not i-1
    END LOOP;
    DBMS_OUTPUT.PUT_LINE (factorial);
EXCEPTION
    WHEN OTHERS
    THEN  DBMS_OUTPUT.PUT_LINE ('Error!');
END;

Then

BEGIN
    factorial_number(5);
END;

will print out 120 (5*4*3*2*1).

I'm also adding an additional dbms_output line to print out the current state of the variables on each iteration of the loop. That's a relatively old-school method of debugging. In the modern world, you'd walk through the code with a debugger where you can see the values of your local variables but introductory classes may not teach debugger usage initially.

factorial function error - Maximum stored procedure, function, trigger, or view nesting level exceeded

Recursive computations aren't where SQL Server (nor any other database) shines. The best solution is to move this out of the database and calculate it in your application. But if you absolutely must do this in the database, use an iterative method instead of recursive one, for example:

create function dbo.factorial(@num1 float(53))
returns float(53)
as
begin
    declare @factno bigint;

    ;With Nums As
    (
        select ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS RN
        FROM sys.objects
    )
    SELECT @factno = POWER(10.0, SUM(LOG10(RN)))
    FROM Nums
    WHERE RN <= @num1

    return @factno;
end

factorial of a number in pl/sql

Sum is reserved word in sql. Change variable name like

declare
  n number;
  temp number;
  sum_ number := 1;
begin
  n := &n;
  temp := n;
  while temp>0 loop
    sum_ := temp*sum_;
    temp := temp-1;
  end loop;
  dbms_output.put_line('Factorial of '||n||' is '||sum_);
end;
/

---

Related Topics