首页 > 代码库 > 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
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。