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 specified3、查看存儲過程和函數的狀態信息
(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 specified4、從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)
