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
标签：http 工具 var 技术 mysq name val img end
