mysql创建存储过程测试实验讲解
1.创建test3：create databse test3；
2.创建表t2：
use test3;
create table t2 (id int,name char(50),dtime timestamp);
[plain] view plain copy
mysql> select * from t2;
empty set (0.00 sec)
3.创建procedure来循环插入数据到新建的t2表中
[plain] view plain copy
mysql> delimiter $$
mysql> create procedure t2insert(in count int)
-> begin
-> declare i int default 0;
-> while i<count
-> do
-> insert into t2 values(i,concat('yoo',i),current_timestamp());
-> set i=i+1;
-> end while ;
-> end$$
query ok, 0 rows affected (0.06 sec)
4.查看存储过程的信息：
delimiter ;
show procedure status like '%insert%' \g;
show create procedure t2insert \g;
select * from information_schema.routines where routine_name = 't2insert' \g;
[plain] view plain copy
mysql> show procedure status like '%insert%' \g;
*************************** 1. row ***************************
db: test3
name: t2insert
type: procedure
definer: root@localhost
modified: 2018-06-10 22:13:09
created: 2018-06-10 22:13:09
security_type: definer
comment:
character_set_client: gbk
collation_connection: gbk_chinese_ci
database collation: utf8_general_ci
1 row in set (0.00 sec)
5.调用存储过程
[plain] view plain copy
mysql> call t2insert(10);
query ok, 1 row affected (0.65 sec)
mysql> select * from t2;
+------+------+---------------------+
| id | name | dtime |
+------+------+---------------------+
| 0 | yoo0 | 2018-06-10 22:18:25 |
| 1 | yoo1 | 2018-06-10 22:18:26 |
| 2 | yoo2 | 2018-06-10 22:18:26 |
| 3 | yoo3 | 2018-06-10 22:18:26 |
| 4 | yoo4 | 2018-06-10 22:18:26 |
| 5 | yoo5 | 2018-06-10 22:18:26 |
| 6 | yoo6 | 2018-06-10 22:18:26 |
| 7 | yoo7 | 2018-06-10 22:18:26 |
| 8 | yoo8 | 2018-06-10 22:18:26 |
| 9 | yoo9 | 2018-06-10 22:18:26 |
+------+------+---------------------+
10 rows in set (0.00 sec)
6.可以使用alter修改存储过程的简单特性但并不能修改过程体,所以要修改过程体的sql语句只能
drop删除后重新创建
alter procedure
drop procedure t2insert;