存储过程

概念：存储过程就是存储在数据库当中的可以执行特定工作（查询和更新）的一组SQL代码的程序段。

与自定义函数的区别：自定义函数有且只有一个返回值，就像普通的函数一样，可以在表达式中嵌入调用。存储过程可以没有返回值，也可以有任意个输出参数，必须单独调用。

• 执行的本质都一样，只是函数有只能返回一个变量的限制，而存储过程可以返回多个；

• 函数是可以嵌入在sql中使用的，可以在select中调用，而存储过程不行；

• 函数限制比较多，比如不能使用临时表，只能用表变量。而存储过程的限制就相对比较少；

• 一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强；

• 对于存储过程来说可以返回参数，而函数只能返回值或者表对象；

存储过程的优点：

1. 存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次，所以使用存储过程可以提高数据库执行速度；

2. 当对数据库进行复杂操作时（比如对多个表进行Update、insert、Query、Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用；

3. 存储过程可以重复使用，可减少数据库开发人员的工作量；

4. 安全性高，可设定只有某此用户才具有对指定存储过程的使用权；

创建存储过程

create procedure 存储过程名称([in|out|inout] 参数 数据类型)
		begin
				MySQL 语句;
		end;

MySQl存储过程参数如果不显式指定in、out、inout 则默认为in ，习惯上，对于是in 的参数都不会显式指定。

• in 为默认类型，值必须在调用时指定，值不能返回

• out 值可以返回

• inout 值必须在调用时指定，值可以返回

存储过程调用：调用MySQL存储过程的时候，需要在过程名字后面加() ，即是没有一个参数，也需要() 。

call 存储过程名称([输入参数]);

Drop procedure if exists test; #先删除存储过程（如果存在）
Create procedure test # 存储过程名
(IN inparms INT, OUT outparams varchar(32)) # 输入参数, 输出参数
BEGIN # 语句块头
		DECLARE var CHAR(10); # 变量声明
		IF inparms = 1 THEN # IF条件开始
				SET var = 'hello'; # 赋值
		ELSE
				SET var = 'world';
		END IF; # IF结束
		INSERT INTO t1 VALUES (var); # SQL语句
		SELECT name FROM t1 LIMIT 1 INTO outparams;
END

call test(1, @out);

变量定义

变量是在程序运行过程中，值可以发生变化的量。

声明变量

declare 变量名[,...] 变量类型 [DEFAULT 默认值]

这个语句被用来声明局部变量。要给变量提供一个默认值，需要包含一个DEFAULT 语句。如果没有DEFAULT 子句，初始值为NULL 。

局部变量的作用范围在它被声明的BEGIN ... END 块内。

变量赋值

• set语句 SET 变量名=表达式 ...

• select … into语句：select col_name[,...] into var_name[,...] table_expr

这个select 语法把选定的列直接存储到变量，因此只有单一的行可以被取回。

执行部分

BEGIN ... END 语句

用于将一系列的SQL语句合并为一组语句，当需要同时执行两条以上的语句时，可以使用BEGIN ... END 语句包含在内形成一个语句块，作为一个整体来执行。通常该语句可以嵌套在其他语句中，如条件分支语句、循环语句中。

BEGIN
		语句1
		语句2
		… 
		语句n
END

BEGIN ... END 语句之内每个语句都必须用分号; 来结尾。

控制结构

条件控制语句if 语句

该语句用于设计条件分支流程，根据给定的条件，程序可以执行不同的操作和运算，从而使程序的功能更加完善，该语句的格式为：

if 条件表达式 then 语句块;
[elseif 条件表达式 then 语句块;]
[else 语句块; ]
end if;

case 语句

case 语句用于计算多个条件并为每个条件返回单个值，以简化SQL语句格式。case 语句不同于其他SQL语句，不能作为独立的语句来执行，而是需要作为其他语句的一部分来执行。

简单case 表达式：

case 表达式
when 条件1 then 结果表达式1
[when 条件2 then 结果表达式2
[…]]
[else 结果表达式n]
end case

搜索case 表达式：

case
when 布尔表达式１ then 结果表达式１
[when 布尔表达式2 then 结果表达式2
[…] ]
[else 结果表达式n]
end case

搜索case表达式和简单case表达式的功能类似，但能够实现的条件分支要比较简单，case表达式更为复杂。

while 语句

while 语句可以在满足条件的情况下重复执行在循环体内的语句。在循环体内部可以使用continue 、break 语句对循环进行控制。

while 条件表达式 do
语句块1
end while

当条件表达式的值为真时，循环将重复执行，直到条件表达式的值为假时，退出循环。

loop 语句

loop 允许某特定语句或语句群的重复执行，实现一个简单的循环构造。在循环内的语句一致重复直到循环被退出，退出通常伴随着一个leave 语句。

[begin_label:] LOOP
		语句块
END LOOP [end_label]

CREATE PROCEDURE p5 ()
BEGIN
		DECLARE v INT;
		SET v = 0;
		loop_label: LOOP
				INSERT INTO t VALUES (v);
				SET v = v + 1;
				IF v >= 5 THEN
						LEAVE loop_label;
				END IF;
		END LOOP;
END;

repeat 语句

类似do...while 语句，功能与while 差不多，差别是在执行一次后检查，而while 是在开始时检查。

repeat
		语句块
until 条件表达式 end repeat;

CREATE PROCEDURE p6 ()
BEGIN
		DECLARE v INT;
		SET v = 0;
		REPEAT
				INSERT INTO t VALUES (v);
				SET v = v + 1;
		UNTIL v >= 5 END REPEAT;
END; //

CREATE PROCEDURE p7 ()
BEGIN
		DECLARE v INT;
		SET v = 0;
		loop_label: LOOP
				IF v = 3 THEN
						SET v = v + 1;
						ITERATE loop_label;
				END IF;
				INSERT INTO t VALUES (v);
				SET v = v + 1;
				IF v >= 5 THEN
						LEAVE loop_label;
				END IF;
		END LOOP;
END;

ITERATE 结束当前循环，进入下一轮循环，相当于continue 。

案例1:创建一个存储过程，命名为getAllStudentInfo ，要求该存储过程返回学生基本信息

#定义存储过程
CREATE PROCEDURE getAllStudentInfo()
		SELECT * FROM student_info;

#调用存储过程
call getAllStudentInfo();

案例2:创建一个存储过程，命名为getAvgScore ，要求返回所有输入学生成绩的平均值。

#定义存储过程
CREATE PROCEDURE getAvgScore (sn varchar(10), out avgs numeric(5,2))
BEGIN
		SELECT avg(score) FROM score_info WHERE SNo=sn into avgs;
END

#调用存储过程
call getAvgScore('20200101',@a);
select @a;

案例3:创建一个存储过程，要求任意录入三个数，能输出最大数

#定义存储过程
CREATE PROCEDURE maxnum(a int,b int,c int)
BEGIN
		DECLARE max int;
		SET max=a;
		IF b>max THEN
				SET max=b;
		END IF;
		IF c>max THEN
				SET max=c;
		END IF;
		SELECT max;
END

#调用存储过程
call maxnum(10, 6, 8);

案例4:创建存储过程calsum ，实现任意两个整数的和。

#定义存储过程
create procedure calsum( a int, b int)
begin
		declare c int;
		set c=a+b;
		select c as sum;
end

#调用存储过程
call calsum(19, 32)