语法
存储过程语法如下:
CREATE PROCEDURE(参数列表)
存储过程体
删除存储过程:
DROPPROCEDURE
调用自定义函数语法:
SELECT(parameter_value,...)
语法实例
create procedure test_procedure()
begin
declare i int;
set i=0;
while i<100000 do
insert 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`()
begin
declare i int;
set i=0;
while i<100000 do
insert 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 set
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`()
begin
declare i int;
set i=0;
while i<100000 do
insert 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 set
mysql> call test_procedure();
rand_string(100)为上一篇自定义函数
名词解释
大写
小写
词义
用法
USE
use
切换数据库
use test
IF EXIST
if exist
判断是否存在
IF EXIST 名
DELIMITER
delimiter
定义结束符
delimiter //
DEFINER
definer
权限
definer=root@localhost
PROCEDURE
procedure
存储过程
procedure procedure_name
DECLARE
declare
声明
DECLARE return_str varchar(10240)
WHILE..DO
while..do
while循环
while i
COMMIT
commit
提交
commit;
CALL
call
执行存储过程
call procedure_name();