MySQL进阶07_存储过程/存储函数

2.存储过程

概述

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。
存储过程思想上很简单，就是数据库SQL语言层面的代码封装与重用。
1
2

p1就是一系列的SQL语句

特点

封装，复用，
可以接收参数，也可以返回数据
减少网络交互，效率提升
1
2
3

创建和调用

--创建
create procedure 存储过程名称([参数列表])
BEGIN
	--SQL语句
END;


--调用
CALL 名称([参数])

--查看 (tian是数据库名)
select * from information_schema.ROUTINES where ROUTINE_SCHEMA='tian'; 
show create procedure 名称; --查看创建语句
--删除
drop procedure 存储过程名称;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

例子

create procedure p1()
BEGIN
	select count(*) from student;
END;

CALL p1();

1
2
3
4
5
6
7

注意:在命令行中，执行创建存储过程的SQL时，需要通过关键字delimiter指定SQL语句的结束符。

delimiter $$

create procedure p1()
BEGIN
select count(*) from student;
END$$

CALL p1();$$

delimiter ;

CALL p1();

系统变量

用户变量

用户定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用“@变量名”使用就可以。其作用域为当前连接。

--赋值
set @myname = 'itcast'
set @myage :=10
set @mygender := '男' ,@myhobby := 'java';

select @mycolor := 'red'
select count(*) into @mycount from tb_user;


--使用
select @myname ,@myage;
1
2
3
4
5
6
7
8
9
10
11

select @abc；

没有的话获取到的值就是NULL

局部变量

局部变量是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BEGIN …END块。

--赋值
create procedure p2()
begin 
	declare stu_count int default 0;
	--set stu_count := 10
	
	select count(*) into stu_count from student;
	select stu_count;
end;


call p2();
1
2
3
4
5
6
7
8
9
10
11
12

IF

语法

IF 条件一 THEN
  ....
ELSEIF 条件二 THEN
  ....
ELSE 
  ....
END IF;
1
2
3
4
5
6
7

例子

-- score >= 85 优秀
-- score >= 60 且 score < 85 及格
-- score < 60 不及格

create procedure p3()
begin
 	declare score int default 58;
 	declare result varchar(10);
 	if score >=85 then 
 		set result := '优秀';
 	elseif score >=60 then
 		set result := '及格';
 	else 
 		set result := '不及格';
 	end if;
 	select result;
end;

call p3()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

参数

例子

--根据传入(in)的参数score 并返回(out)
-- score >= 85 优秀
-- score >= 60 且 score < 85 及格
-- score < 60 不及格

create procedure p4(in score int,out result varchar)
begin
 	if score >=85 then 
 		set result := '优秀';
 	elseif score >=60 then
 		set result := '及格';
 	else 
 		set result := '不及格';
 	end if;
end;

call p4(68,@result)
select @result


--将传入(in)的2000分制的分数进行换算 ,然后返回分数(inout)
create procedure p5(inout score double)
begin
	set score := score * 0.5;
end;


set @score =70; //先随便定义一个
call p5(@score) //再传入
select @score; //查看   35

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

case

案例

--根据传入的月份，判定月份所属的季节（采用case结构）
--1-3月份 第一季度
--4-6月份 第二季度
--7-9月份 第三季度
--10-12月份 第四季度

create procedure p6(in month int)
begin 
	declare result varchar(10);
	case
		when month >=1 and month <=3 then
			set result := '第一季度';
		when month >=4 and month <=6 then
			set result := '第二季度';
		when month >=7 and month <=9 then
			set result := '第三季度';
		when month >=10 and month <=12 then
			set result := '第四季度';
		else
			set result := '非法参数';
	end case;
	select concat('您输入的月份为:',month,'，所属的季度为:',result)
end;

call p6(4);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

循环

• while

--计算从1累加到n的值，n为传入的参数

create procedure p7(in n int)
begin
	declare total int default 0;
	while  n>0 do
		set total := total +n;
		set n := n-1;
	end while;
	select total;
end;

call p7(100);
1
2
3
4
5
6
7
8
9
10
11
12
13

• repeat (联想java中 do … while)

--计算从1累加到n的值，n为传入的参数

create procedure p8(in n int)
begin
	declare total int default 0;
	repeat  
		set total := total +n;
		set n := n-1;
	until n <= 0
	end repeat;
	select total;
end;

call p8(100);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

• loop

--计算从1累加到n的值，n为传入的参数

create procedure p9(in n int)
begin
	declare total int default 0;
	sum:loop  
		if n <= 0 then
			leave sum;
		end if;
		set total := total +n;
		set n := n-1;
	end loop sum;
	select total;
end;

call p9(100);



--计算从1累加到n的偶数的值，n为传入的参数
create procedure p10(in n int)
begin
	declare total int default 0;
	sum:loop  
		if n <= 0 then
			leave sum;
		end if;
		if n%2 = 1 then
			set n := n-1;
			iterate sum;
		end if;
		set total := total +n;
		set n := n-1;
	end loop sum;
	select total;
end;

call p10(10);


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

游标

游标（CURSOR）是用来存储查询结果集的数据类型,在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH 和CLOSE，其语法分别如下。

例

--根据传入的参数uage，查询用户表tb_user ，所有用户年龄小于uage的用户姓名，专业
--将查询到的用户姓名，专业插入到新创建的一张新表(id,name,profession)

create procedure p11(in uage int)
begin
	declare uname varchar(100);
	declare upro varchar(100);
	declare u_cursor cursor for select name,profession from tb_user where age<uage;
	drop table if exists tb_user_pro;
	create table if not exists tb_user_pro(
    	id int primary key auto_increment,
        name varchar(100),
        profession varchar(100)
    );
    
    open u_cursor;
    while true do  --会有报错
    	fetch u_cursor into uname,upro;
    	insert into tb_user_pro values(null,uname,upro);
    end while;
    close u_cursor;
end;

call p11(20); --会有报错
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

解决上述报错问题 报错的状态码是02000

条件处理程序

--根据传入的参数uage，查询用户表tb_user ，所有用户年龄小于uage的用户姓名，专业
--将查询到的用户姓名，专业插入到新创建的一张新表(id,name,profession)

create procedure p11(in uage int)
begin
	declare uname varchar(100);
	declare upro varchar(100);
	
	declare exit handler for SQLSTATE '02000' close u_cursor;
    --declare exit handler for not found close u_cursor; 和上面一条意思一样
    
    
    declare u_cursor cursor for select name,profession from tb_user where age<uage;
	
	drop table if exists tb_user_pro;
	create table if not exists tb_user_pro(
    	id int primary key auto_increment,
        name varchar(100),
        profession varchar(100)
    );
    
    open u_cursor;
    while true do  
    	fetch u_cursor into uname,upro;
    	insert into tb_user_pro values(null,uname,upro);
    end while;
    close u_cursor;
end;

call p11(20); 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

3.存储函数

• 用的少 存储过程可以替换

存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的。具体语法如下:

例

--从1到n的累加
create function fun1(n int)
returns int deterministic
begin
	declare total int default 0;
	while n>0 do
		set total :=total+n;
		set n:=n-1;
	end while;
	return total;
end;

select fun1(100);
1
2
3
4
5
6
7
8
9
10
11
12
13