How can we create MySQL stored procedure to calculate the factorial?

mysql> DELIMITER //
mysql> CREATE PROCEDURE get_factorial(IN N INT)
    -> BEGIN
    ->    SET @@GLOBAL.max_sp_recursion_depth = 255;
    ->    SET @@session.max_sp_recursion_depth = 255;
    ->
    ->    CALL factorial_recursive (N, @factorial);
    ->
    ->    SELECT @factorial;
    -> END //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER //
mysql> CREATE PROCEDURE factorial_recursive(IN N INT,OUT factorial INT)
    -> BEGIN
    ->    IF N = 1 THEN
    ->       SET factorial := 1;
    ->    ELSE
    ->       CALL factorial_recursive (N-1, factorial);
    ->       SET factorial := N * factorial;
    ->    END IF;
    -> END //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> Call get_factorial(10);
+--------------+
| @factorial   |
+--------------+
|      3628800 |
+--------------+
1 row in set (0.11 sec)
Query OK, 0 rows affected (0.12 sec)

mysql> Call get_factorial(5);
+-------------+
| @factorial  |
+-------------+
|         120 |
+-------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

Srinivas Gorla

Published on 21-Feb-2018 11:31:26

• Related Questions & Answers
• What is stored procedure and how can we create MySQL stored procedures?
• How can we invoke MySQL stored procedure?
• How can we alter a MySQL stored procedure?
• How can we drop a MySQL stored procedure?
• How can we perform START transactions inside MySQL stored procedure?
• How can we perform COMMIT transactions inside MySQL stored procedure?
• How can we write MySQL handler in a stored procedure?
• How can a MySQL stored procedure call another MySQL stored procedure inside it?
• How can I create MySQL stored procedure with IN parameter?
• How can I create MySQL stored procedure with OUT parameter?
• How can I create MySQL stored procedure with INOUT parameter?
• How can we perform ROLLBACK transactions inside a MySQL stored procedure?
• How can we handle a result set inside MySQL stored procedure?
• MySQL Stored Procedure to create a table?
• How can we see the source code of a particular MySQL stored procedure?