存储过程示例:
delimiter $
create procedure myCunChu()
begin
insert into age(id, age) values (1,'21'); --往age表中完成5条插入语句
insert into age(id, age) values (2,'24');
insert into age(id, age) values (3,'25');
insert into age(id, age) values (4,'26');
end $
调用存储过程
call muCunChu() $
4、参数模式为IN的存储过程示例
示:1:
delimiter $
create procedure myCunChuIn(in s_name varchar(20))
begin
select s.name, a.age from students s
inner join ages a
on s.age_id = g.id
where s.sname = s_name;
end $
调用存储过程:
call myCunChuIn('景旭') $
示例2:创建判断用户是否登录成功
delimiter $
create procedure myCunChuLogin(in username varchar(20), in password varchar(20))
begin
declare res int default 0; --定义一个字符串类型的变量,关键字为declare,变量名为res
select count(*) into res from users u --将查询结果赋值给变量res
where u.username = username
and u.password = password;
select if(res > 0, '登陆成功', '登录失败'); --打印结果
end $
调用存储过程:
call myCunChuLogin('景旭', '123') $
5、参数模式为OUT的存储过程示例
示例1:根据学生姓名,返回对应的年龄
delimiter $
create procedure myCunChuOut(in sname varchar(20), out age int)
begin
select a.age into age from students s
inner join ages a s.age_id = a.id
where s.sname = sname;
end $
调用:
call myCunChuOut('景旭', @age) $ --把值取出来放在变量里去
select @age $ --查看值
示例2:根据学生姓名,返回对应的年龄和学生编号
delimiter $
create procedure myCunChuOut2(in sname varchar(20), out age int, out sid int)
begin
select a.age, s.id into age, sid from students s
inner join ages a on s.age_id = a.id
where s.sname = sname;
end $
调用:
call myCunChuOut2('景旭', @age, @sid) $
select @age, @sid $
5、参数模式为INOUT的存储过程示例
案例:传入a和b两个数,然后让a和b都乘以2后返回
delimiter $
create procedure myCunChuInout(inout a int, inout b int)
begin
set a = a*2;
set b = b*2
end $
调用:
set @a = 10 $
set @b = 20$
call myCunChuInout(@a, @b) $
select @a, @b $