语法
存储过程语法如下:
CREATE PROCEDURE <存储过程名称>(参数列表)
存储过程体
删除存储过程:
DROP PROCEDURE <存储过程名称>
调用自定义函数语法:
SELECT <存储过程名称>(parameter_value,...)
语法实例
create procedure test_procedure()begindeclare i int;set i=0;while i<100000 doinsert into test_innodb values(i+1,rand_string(100));insert into test_myisam values(i+1,rand_string(100));commit;set i = i+1;end while;end;
效果
注：由于存储过程中是为两个表中增加10W条测试数据，导致执行时间过长。建议更改为1000，如果不修改可以增加游标批量添加数据。今后会有介绍
mysql> show create procedure test_procedure;+----------------+------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+| Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |+----------------+------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+| test_procedure | NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `test_procedure`()begindeclare i int;set i=0;while i<100000 doinsert into test_innodb values(i+1,rand_string(100));insert into test_myisam values(i+1,rand_string(100));commit;set i = i+1;end while;end | utf8 | utf8_general_ci | latin1_swedish_ci |+----------------+------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+1 row in setmysql> show create procedure test_procedure;+----------------+------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+| Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |+----------------+------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+| test_procedure | NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `test_procedure`()begindeclare i int;set i=0;while i<100000 doinsert into test_innodb values(i+1,rand_string(100));insert into test_myisam values(i+1,rand_string(100));commit;set i = i+1;end while;end | utf8 | utf8_general_ci | latin1_swedish_ci |+----------------+------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+1 row in setmysql> call test_procedure();
rand_string(100)为上一篇自定义函数
名词解释
| 大写 | 小写 | 词义 | 用法 |
| USE | use | 切换数据库 | use test |
| IF EXIST | if exist | 判断是否存在 | IF EXIST 名 |
| DELIMITER | delimiter | 定义结束符 | delimiter // |
| DEFINER | definer | 权限 | definer=root |
| PROCEDURE | procedure | 存储过程 | procedure procedure_name |
| DECLARE | declare | 声明 | DECLARE return_str varchar(10240) |
| WHILE..DO | while..do | while循环 | while i < n do |
| COMMIT | commit | 提交 | commit; |
| CALL | call | 执行存储过程 | call procedure_name(); |