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_num
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`root`@`%` 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
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
Database 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: superdb
Name: sp_add_sum_num
Type: PROCEDURE
Language: SQL
Definer: root@%
Modified: 2023-06-12 20:47:20
Created: 2023-06-12 20:47:20
Security_type: DEFINER
Comment:
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
Database 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_num
ROUTINE_CATALOG: def
ROUTINE_SCHEMA: superdb
ROUTINE_NAME: sp_add_sum_num
ROUTINE_TYPE: PROCEDURE
DATA_TYPE:
CHARACTER_MAXIMUM_LENGTH: NULL
CHARACTER_OCTET_LENGTH: NULL
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: NULL
COLLATION_NAME: NULL
DTD_IDENTIFIER: NULL
ROUTINE_BODY: SQL
ROUTINE_DEFINITION: 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
EXTERNAL_NAME: NULL
EXTERNAL_LANGUAGE: SQL
PARAMETER_STYLE: SQL
IS_DETERMINISTIC: NO
SQL_DATA_ACCESS: CONTAINS SQL
SQL_PATH: NULL
SECURITY_TYPE: DEFINER
CREATED: 2023-06-12 20:47:20
LAST_ALTERED: 2023-06-12 20:47:20
SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
ROUTINE_COMMENT:
DEFINER: root@%
CHARACTER_SET_CLIENT: utf8mb4
COLLATION_CONNECTION: utf8mb4_0900_ai_ci
DATABASE_COLLATION: utf8mb4_0900_ai_ci
1 row in set (0.01 sec)