MySQL存储过程实现累加运算 1+2+…+n 等于多少？

本文主要是介绍MySQL存储过程实现累加运算 1+2+…+n 等于多少？，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

MySQL创建存储过程，实现累加运算，计算 1+2+…+n 等于多少。具体的代码如下

1、实现计算1+2+3+…+n的和

DELIMITER // 
CREATE PROCEDURE sp_add_sum_num(IN n INT) 
BEGIN DECLARE i INT; DECLARE sum INT; SET i = 1; SET sum = 0;WHILE i <= n DO SET sum = sum + i; SET i = i +1; END WHILE; SELECT sum; 
END // 
DELIMITER ;-- 调用存储过程
(root@192.168.80.85)[superdb]> call sp_add_sum_num(100);
+------+
| sum  |
+------+
| 5050 |
+------+
1 row in set (0.03 sec)Query OK, 0 rows affected (0.03 sec)(root@192.168.80.85)[superdb]> call sp_add_sum_num(10);
+------+
| sum  |
+------+
|   55 |
+------+
1 row in set (0.00 sec)Query OK, 0 rows affected (0.01 sec)

2、查看存储过程和函数的创建信息

使用SHOW CREATE语句查看存储过程和函数的创建信息
语法结构
SHOW CREATE {PROCEDURE | FUNCTION} 存储过程名或函数名

(root@192.168.80.85)[superdb]> SHOW CREATE PROCEDURE sp_add_sum_num \G;
*************************** 1. row ***************************Procedure: sp_add_sum_numsql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTIONCreate Procedure: CREATE DEFINER=`root`@`%` PROCEDURE `sp_add_sum_num`(IN n INT)
BEGINDECLARE i INT;DECLARE sum INT;SET i = 1;SET sum = 0;WHILE i <= n DOSET sum = sum + i;SET i = i +1;END WHILE;SELECT sum;
END
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ciDatabase Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)ERROR:
No query specified

3、查看存储过程和函数的状态信息

(root@192.168.80.85)[superdb]> SHOW PROCEDURE STATUS like 'sp_add_sum_num';
+---------+----------------+-----------+----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db      | Name           | Type      | Language | Definer | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+---------+----------------+-----------+----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| superdb | sp_add_sum_num | PROCEDURE | SQL      | root@%  | 2023-06-12 20:47:20 | 2023-06-12 20:47:20 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+---------+----------------+-----------+----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.09 sec)(root@192.168.80.85)[superdb]> SHOW PROCEDURE STATUS like 'sp_add_sum_num' \G;
*************************** 1. row ***************************Db: superdbName: sp_add_sum_numType: PROCEDURELanguage: SQLDefiner: root@%Modified: 2023-06-12 20:47:20Created: 2023-06-12 20:47:20Security_type: DEFINERComment:
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ciDatabase Collation: utf8mb4_0900_ai_ci
1 row in set (0.01 sec)ERROR:
No query specified

4、从infomation_schema.Routines表中查看存储过程和函数的信息

(root@192.168.80.85)[superdb]> SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='sp_add_sum_num' AND ROUTINE_TYPE = 'PROCEDURE' \G;                                                                                          
*************************** 1. row ***************************SPECIFIC_NAME: sp_add_sum_numROUTINE_CATALOG: defROUTINE_SCHEMA: superdbROUTINE_NAME: sp_add_sum_numROUTINE_TYPE: PROCEDUREDATA_TYPE:
CHARACTER_MAXIMUM_LENGTH: NULLCHARACTER_OCTET_LENGTH: NULLNUMERIC_PRECISION: NULLNUMERIC_SCALE: NULLDATETIME_PRECISION: NULLCHARACTER_SET_NAME: NULLCOLLATION_NAME: NULLDTD_IDENTIFIER: NULLROUTINE_BODY: SQLROUTINE_DEFINITION: BEGINDECLARE i INT;DECLARE sum INT;SET i = 1;SET sum = 0;WHILE i <= n DOSET sum = sum + i;SET i = i +1;END WHILE;SELECT sum;
ENDEXTERNAL_NAME: NULLEXTERNAL_LANGUAGE: SQLPARAMETER_STYLE: SQLIS_DETERMINISTIC: NOSQL_DATA_ACCESS: CONTAINS SQLSQL_PATH: NULLSECURITY_TYPE: DEFINERCREATED: 2023-06-12 20:47:20LAST_ALTERED: 2023-06-12 20:47:20SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTIONROUTINE_COMMENT:DEFINER: root@%CHARACTER_SET_CLIENT: utf8mb4COLLATION_CONNECTION: utf8mb4_0900_ai_ciDATABASE_COLLATION: utf8mb4_0900_ai_ci
1 row in set (0.01 sec)