MySQL教程彻底学懂存储过程

OStack程序员社区-中国程序员成长平台 › 门户 › 编程›数据库

原作者: [db:作者] 来自: [db:来源] 收藏邀请

1、存储过程的相关概念

什么是存储过程：
类似于java中的方法，python中的函数。

使用存储过程的好处：
1、提高代码的重用性；
2、简化操作；
3、减少了编译次数，并且减少了和数据库服务器的连接次数，提高了效率。注意：平时每执行一句sql语句，就会连接mysql服务器一次；

存储过程的含义：
一组预先编译好的SQL语句的集合。

2、存储过程的用法

1）存储过程的创建语法

create procedure 存储过程名(参数列表)
begin
     存储过程体(一组合法有效的sql语句)
end

2）对创建语法的理解

① 参数列表：参数列表包含3部分

参数模式   参数名   参数类型

例如：in  stuname  varchar(20)

② 参数模式分类

in：该参数可以作为输入，也就是该参数，需要调用方传入值。

out：该参数可以作为输出，也就是该参数，可以作为返回值。

inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值。

③ 如果存储过程体仅仅只有一句话，begin/end可以省略。

Ⅰ 存储过程体中的每条sql语句的结尾，要求必须加分号。
Ⅱ 存储过程的结尾，可以使用delimiter重新设置，否则都用";"会造成混淆。
语法：delimiter 结束标记
例如：delimiter $ -- 表示以$作为结尾符号。

④ 存储过程的调用语法

call 存储过程名(实参列表);

3.空参的存储过程

"存储过程的整个执行过程，最好在CMD窗口中执行"
-- 创建一个存储过程
delimiter $
create procedure myp()
begin
    	insert into admin(username,`password`)
    	values ("tom","1111"),("jerry","2222"),
    	("jalen","3333"),("rose","4444"),("tonny","5555");
end $
-- 调用存储过程
call myp()$
-- 查看结果。
select * from admin$

结果如下：

4.带in模式的存储过程

1）案例：创建存储过程实现，根据部门编号，查询对应的部门名称。

-- 创建一个存储过程
delimiter $
create procedure myp2(in num int)
begin
    select e.ename,d.dname
    from emp e 
    left join dept d on e.deptno=d.deptno
    where e.deptno=num;
end $
-- 调用存储过程
call myp2(10)$

结果如下：

2）案例：创建一个存储过程实现，用户是否登陆成功。

操作如下：

-- 创建一个存储过程
delimiter $
create procedure myp3(in username varchar(10),password varchar(10))
begin 
    declare result int;
    select count(*) into result
    from admin ad
    where ad.username=username
    and ad.password=password;
    select if(count(*) > 0,"登陆成功","登陆失败") 登陆状态;
end $
-- 调用存储过程
call myp3('john','8888');

结果如下：

5.带out模式的存储过程

1）案例1：创建一个存储过程，根据女神姓名，返回对应的男神姓名

-- 创建一个存储过程
delimiter $
create procedure myp4(in beautyName varchar(20),out boyName varchar(20))
begin 
    select b.boyName into boyName
    from beauty left join boys b
    on beauty.boyfriend_id=b.id
    where beauty.name=beautyName;
end $
-- 调用
# 重新定义一个变量@boyname接收返回值boyName。
call myp4("赵敏",@boyname)$
select @boyname$

call myp4("柳岩",@boyname)$
select @boyname$

结果如下：

2）案例2：创建一个存储过程，根据女神名，返回对应的男神名和男神魅力值

-- 创建一个存储过程
delimiter $
create procedure myp5(in beautyName varchar(20),out boyName varchar(20),out userCP int)
begin 
    select b.boyName,b.userCP into boyName,userCP
    from beauty left join boys b
    on beauty.boyfriend_id=b.id
    where beauty.name=beautyName;
end $
-- 调用
# 重新定义一个变量@boyname接收返回值boyName。
call myp5("赵敏",@boyname,@usercp)$
select @boyname,@usercp$

call myp5("柳岩",@boyname,@usercp)$
select @boyname,@usercp$

结果如下：

6.带inout模式的存储过程

1）案例1：传入a和b两个值，最终a和b都翻倍并返回。

-- 创建一个存储过程
delimiter $
create procedure myp6(inout a int ,inout b int)
begin 
    -- 局部变量不用加@符号。
    set a=a*2;
		set b=b*2;
end $
-- 调用
-- 特别注意调用这一块儿。
set @m=10$
set @n=20$
call myp6(@m,@n)$
select @m,@n$

结果如下：

7.存储过程的案例解析

1）创建存储过程或函效，实现传入用户名和密码，插入到admin表中。

2）创建储过程或函数，实现传入女神编号，返回女神名称和女神电话。

3）创建存储存储过程或函数，实现传入两个女神生日，返回大小。

1）创建存储过程或函效，实现传入用户名和密码，插入到admin表中。

-- 创建一个存储过程
delimiter $
create procedure pro1(in username varchar(20),in userpwd varchar(20))
begin 
    insert into admin(username,`password`)
    values (username,userpwd);
end $

-- 调用存储过程
call pro1("鲁智深","123abc")$
select * from admin$

结果如下：

2）创建储过程或函数，实现传入女神编号，返回女神名称和女神电话。

-- 创建一个存储过程
delimiter $
create procedure pro2(in id int,out beautyName varchar(20),out beautyPhone varchar(20))
begin
    select beauty.name,beauty.phone into beautyName,beautyPhone
    from beauty
    where beauty.id=id;
end $

-- 调用存储过程
call pro2(2,@beautyname,@beautyphone)$
select @beautyname,@beautyphone$

call pro2(3,@beautyname,@beautyphone)$
select @beautyname,@beautyphone$

结果如下：

3）创建存储存储过程或函数，实现传入两个女神生日，返回大小。

-- 创建一个存储过程
delimiter $
create procedure pro3(in borndate1 datetime,in borndate2 datetime,out result int)
begin
    select datediff(borndate1,borndate2) into result;
end $

-- 调用存储过程
call pro3("1993-8-12",now(),@result)$
select @result$

结果如下：

8.存储过程的删除

drop procedure 存储过程名；

9.查看某个存储过程的信息

10.存储过程案例考核

1）创建存储过程或函数实现传入女神名称，返回：女神AND男神，格式的字符串
如传入：小昭
返回：小昭AND张无忌
2）创建存储过程或函数，根据传入的条目数和起始索引，查询beauty表的记录。

1）创建存储过程或函数，实现传入女神名称，返回：女神AND男神，格式的字符串。

delimiter $
create procedure test1(in beautyname varchar(20))
begin
    select concat(beauty.name,"AND",boys.boyName)
    from beauty left join boys
    on beauty.boyfriend_id=boys.id
    where beauty.name=beautyname;
end $

call test1("柳岩")$
call test1("赵敏")$

结果如下：

2）创建存储过程或函数，根据传入的条目数和起始索引，查询beauty表的记录。

delimiter $
create procedure test2(in startIndex int,in length int)
begin
    select *
    from beauty
    limit startIndex,length;
end $
-- 每页显示3条记录
-- 显示第2页
call test2(3,3)$
-- 显示第3页
call test2(6,3)$

结果如下：

以上就是MySQL系列彻底学懂存储过程的详细内容，更多关于MySQL存储过程的资料请关注极客世界其它相关文章！