MySQL一个简单的存储过程demo

使用的工具是Navicat for MySQL。

首先创建一个学生表

mysql> create table student(s_name varchar(20) not null default '不详',sex varchar(4) not null default '不详',s_no int(5) auto_increment,age int(3) not null,height int(3) not null,primary key(s_no));
Query OK, 0 rows affected

mysql> insert into student (s_name,sex,age,height) values('小张','男',21,176);
Query OK, 1 row affected

mysql> insert into student (s_name,sex,age,height) values('小李','男',22,175);
Query OK, 1 row affected

mysql> insert into student (s_name,sex,age,height) values('小明','男',25,178);
Query OK, 1 row affected

mysql> insert into student (s_name,sex,age,height) values('小红','女',23,165);
Query OK, 1 row affected

mysql> insert into student (s_name,sex,age,height) values('小丽','女',19,160);
Query OK, 1 row affected

mysql> select * from student;
+--------+-----+------+-----+--------+
| s_name | sex | s_no | age | height |
+--------+-----+------+-----+--------+
| 小张   | 男  |    1 |  21 |    176 |
| 小李   | 男  |    2 |  22 |    175 |
| 小明   | 男  |    3 |  25 |    178 |
| 小红   | 女  |    4 |  23 |    165 |
| 小丽   | 女  |    5 |  19 |    160 |
+--------+-----+------+-----+--------+
5 rows in set

然后写一个存储过程，传入姓名s_name，返回学号s_no

delimiter $$
drop procedure if exists pro_gets_no;
create procedure pro_gets_no(in pname varchar(20),out pno int(5))
begin
    select s_no into pno from student where s_name=pname;
end $$
delimiter;

运行之后调用

set @pname='小红';
set @pno=0;
call pro_gets_no(@pname,@pno);
select * from student where s_no=@pno;

结果如下

再创建一个存储过程，将传入姓名的学生身高height修改为175，并且将之后的姓名改为身高姓名作为姓名输出

delimiter $$
drop procedure if exists pro_updateHeight;
create procedure pro_updateHeight(inout pname varchar(20))
begin 
    update student set height=175 where s_name=pname;
    select concat(height,s_name) into pname from student where s_name=pname;
end $$
delimiter;

运行之后调用

set @pname='小李';
call pro_updateHeight(@pname);
select @pname;

结果如下