1.创建数据库和表
CREATE DATABASE IF NOT EXISTS test;
use test;
create table t4 (
id int not null primary key auto_increment,
sname varchar(64) not null ,
age tinyint not null,
gender char(1)
)engine = innodb charset utf8mb4;
2.添加测试数据
DROP procedure IF EXISTS `test`.`p_where`;
DELIMITER $$
USE `test`$$
CREATE PROCEDURE `p_where`(in num int)
BEGIN
declare str1 varchar(64) default 'abcdefghijklmnpqrstuvwxyz';
declare str2 varchar(10) default 'MF';
declare v_name varchar(64);
declare v_age int;
declare v_gender char(1);
declare i int default 0;
while i<num do
select substr(str1,1+floor(rand()*20),6) into v_name;
select substr(str2,1+floor(rand()*2),1) into v_gender;
select 18+floor(rand()*12) into v_age;
insert into t4(sname,age,gender) values(v_name,v_age,v_gender);
set i=i+1;
end while ;
END$$
DELIMITER ;
call p_where(100);
select * from t4;
3.游标测试应用
CREATE PROCEDURE `p_cur`()
BEGIN
declare v_id int;
declare v_sname varchar(64);
declare v_age tinyint;
declare v_gender char(1);
declare i int default 1;
declare cur cursor for select id,sname,age,gender from t4 where id<5;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET i = 0;
open cur;
fetch cur into v_id,v_sname,v_age,v_gender;
while i=1
do
select v_id,v_sname,v_age,v_gender;
fetch cur into v_id,v_sname,v_age,v_gender;
end while;
close cur;
END
4.测试结果
mysql> call p_cur();
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 18
Current database: test
+------+---------+-------+----------+
| v_id | v_sname | v_age | v_gender |
+------+---------+-------+----------+
| 1 | defghi | 19 | M |
+------+---------+-------+----------+
1 row in set (0.07 sec)
+------+---------+-------+----------+
| v_id | v_sname | v_age | v_gender |
+------+---------+-------+----------+
| 2 | fghijk | 24 | F |
+------+---------+-------+----------+
1 row in set (0.07 sec)
+------+---------+-------+----------+
| v_id | v_sname | v_age | v_gender |
+------+---------+-------+----------+
| 3 | fghijk | 21 | F |
+------+---------+-------+----------+
1 row in set (0.07 sec)
+------+---------+-------+----------+
| v_id | v_sname | v_age | v_gender |
+------+---------+-------+----------+
| 4 | pqrstu | 26 | F |
+------+---------+-------+----------+
1 row in set (0.07 sec)
Query OK, 0 rows affected (0.07 sec)
