首页 > 代码库 > MySQL关于存储过程的简单Demo

MySQL关于存储过程的简单Demo

使用的工具是Navicat for MySQL。

首先创建一个学生表

 1 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));
 2 Query OK, 0 rows affected
 3 
 4 mysql> insert into student (s_name,sex,age,height) values(小张,,21,176);
 5 Query OK, 1 row affected
 6 
 7 mysql> insert into student (s_name,sex,age,height) values(小李,,22,175);
 8 Query OK, 1 row affected
 9 
10 mysql> insert into student (s_name,sex,age,height) values(小明,,25,178);
11 Query OK, 1 row affected
12 
13 mysql> insert into student (s_name,sex,age,height) values(小红,,23,165);
14 Query OK, 1 row affected
15 
16 mysql> insert into student (s_name,sex,age,height) values(小丽,,19,160);
17 Query OK, 1 row affected
18 
19 mysql> select * from student;
20 +--------+-----+------+-----+--------+
21 | s_name | sex | s_no | age | height |
22 +--------+-----+------+-----+--------+
23 | 小张   ||    1 |  21 |    176 |
24 | 小李   ||    2 |  22 |    175 |
25 | 小明   ||    3 |  25 |    178 |
26 | 小红   ||    4 |  23 |    165 |
27 | 小丽   ||    5 |  19 |    160 |
28 +--------+-----+------+-----+--------+
29 5 rows in set

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

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

运行之后调用

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

结果如下

技术分享

 

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

1 delimiter $$
2 drop procedure if exists pro_updateHeight;
3 create procedure pro_updateHeight(inout pname varchar(20))
4 begin 
5     update student set height=175 where s_name=pname;
6     select concat(height,s_name) into pname from student where s_name=pname;
7 end $$
8 delimiter;

运行之后调用

1 set @pname=小李;
2 call pro_updateHeight(@pname);
3 select @pname;

结果如下

技术分享

 

MySQL关于存储过程的简单Demo