问题及解答

(1) 编写函数 factorial(n), 计算 $n!$

DELIMITER //
CREATE FUNCTION factorial(n BIGINT)
RETURNS BIGINT
BEGIN
  DECLARE m BIGINT;
  SET m=1;
  WHILE n>1 
    DO 
    SET m=m*n;
    SET n=n-1;
  END WHILE;
  RETURN m;
END//

mysql> delimiter ;
mysql> SELECT factorial(20);
+---------------------+
| factorial(20)       |
+---------------------+
| 2432902008176640000 |
+---------------------+
1 row in set (0.00 sec)

适用范围

n=1,2,...,20

注意:  参数不能写成 (IN n BIGINT), 否则会返回错误:

ERROR 1064 (42000): Erreur de syntaxe près de 'IN n BIGINT)

验证

可以使用 Calculator 验证

>> 20!
in> 20!

out> 2432902008176640000

------------------------

(2) 编写过程 factorial(n,m),  这里计算 $m=n!$, 也即 n 是输入参数, m 是输出参数.

CREATE PROCEDURE factorial(IN n BIGINT, OUT m BIGINT)
BEGIN
  SET m=1;
  WHILE n>1 
    DO 
    SET m=m*n;
    SET n=n-1;
  END WHILE;
END//

mysql> delimiter ;
mysql> SET @m=1;
mysql> call factorial(9,@m);
Query OK, 0 rows affected (0.00 sec)

mysql> select @m;
+--------+
| @m     |
+--------+
| 362880 |
+--------+
1 row in set (0.00 sec)

可以使用 show procedure status like 'factorial'\G 查看所定义的过程.

mysql> show procedure status like '%factorial%'\G
*************************** 1. row ***************************
                  Db: company
                Name: factorial
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2021-12-19 18:49:46
             Created: 2021-12-19 18:49:46
       Security_type: DEFINER
             Comment:
character_set_client: gbk
collation_connection: gbk_chinese_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)