SQL之存储过程

一、介绍

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

特点

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

二、基本语法

创建

CREATE PROCEDURE 存储过程名称(参数列表])
BEGIN
-- SQL语句
END ;

调用

CALL名称([参数D;

查看

SFLECT*FROMINFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='xxx';
--查询指定数据库的存储过程及状态信息
SHOW CREATE PROCEDURE 存储过程名称;--查询某个存储过程的定义

删除

DROP PROCEDURE[ IF EXISTS]存储过程名称;

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

案例

三、变量

系统变量

系统变量是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量(GLOBAL)、会话变量(SESSION)。
查看系统变量

SHOW[ SESSION |GLOBAL ] VARIABLES ;--查看所有系统变量
SHOW [ SESSION | GLOBAL ] VARIABLES LIKE'..... ;
 --可以通过LIKE模糊匹配方式查找变量
SELECT @@[SESSION |GLOBAL] 系统变量名;--查看指定变量的值

设置系统变量

SET [SESSION | GLOBAL]系统变量名=值;
SET @@[SESSION|GLOBAL]系统变量名=值;

自定义变量

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

赋值

SET @var_name = expr [,@var_name = expr ]... ;
SET @var_name :=expr [, @var_name := expr] ... ;
SELECT @var_name := expr [, @var_name := expr] ... ;
SELECT字段名INTO @var_name FROM表名;

使用

SELECT @var_name ;

局部变量

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

声明

DECLARE 变量名变量类型[DEFAULT ...];

变量类型就是数据库字段类型:INT、BIGINT、CHAR、VARCHAR、DATE、TIME等。

赋值

SET变量名=值;
SET变量名:=值;
SELECT字段名INTO变量名FROM表名...;

案例

create procedure p2()
begin
declare stu_count int default 0;
select count(*) into stu_count from student;select stu_count;
end;
-- 调用
call p2();

四、条件、循环判断

if语法:

IF条件1 THEN
ELSEIF 条件2 THEN   --可选
….…-
ELSE     --可选
--.…
END IF;

案例

create PROCEDURE p2()
begin
  DECLARE score int DEFAULT 58;
	DECLARE res varchar(10);
	
	if score >= 85 then
	     set res := '优秀';
	elseif score >= 60 then
       set res := '及格';
	else
       set res := '不及格';
  end if;
	select res;
end; 


call p2();

参数

用法

CREATE PROCEDURE存储过程名称([IN/OUT/INOUT参数名参数类型])
BEGIN
-- SQL语句
END ;

案例
– 根据传入(in)参数score，判定当前分数对应的分数等级，并返回(out)。

create PROCEDURE p3(in score int,out res VARCHAR(10))
begin
	if score >= 85 then
	     set res := '优秀';
	elseif score >= 60 then
       set res := '及格';
	else
       set res := '不及格';
  end if;
end; 

call p3(68,@res);

select @res

-- 将传入的200分制的分数,进行换算,换算成百分制，然后返回分数---> inout

create PROCEDURE p4(inout score DOUBLE)
begin
	set score := score * 0.5;
end; 

set @score = 178;
call p4(@score);

select @score

case

语法一

CASE case_value
	WHEN when_value1 THEN staternent_list1
	[ WHEN when_value2 THEN statement_list 2]...
	[ ELSE statement_list ]
END CASE;

语法二

CASE
	WHEN search_condition1 THEN statement_list1
	[WHEN search_condition2 THEN statement_list2] ...
	[ELSE statement_list]
END CASE;

案例

create procedure p5(in a int, out res varchar(20))
begin
	case 
		when a >= 1 and a <= 3 then set res := '第一季度';
		when a >= 4 and a <= 6 then set res := '第二季度';
		when a >= 7 and a <= 9 then set res := '第三季度';
		when a >= 10 and a <= 12 then set res := '第四季度';
		else set res := '非法参数';
	end case;
end;

call p5(13,@res);
select @res;

while

while循环是有条件的循环控制语句。满足条件后，再执行循环体中的SQL语句。具体语法为:

#先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑
WHILE 条件 DO
	SQL逻辑...
END WHILE;

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

create procedure p6(in i int)
begin
	DECLARE res int DEFAULT(0);
	while i > 0 do
		set res := res + i;
		set i := i -1;
	end while;
	select res;
end;

call p6(10 );

repeat

repeat是有条件的循环控制语句,当满足条件的时候退出循环。具体语法为:

#先执行一次逻辑，然后判定逻辑是否满足，如果满足，则退出。如果不满足，则继续下
一次循环
REPEAT
	SQL逻辑...
	UNTIL条件
END REPEAT;

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

create procedure p7(in i int)
begin
	DECLARE res int DEFAULT(0);
	repeat 
		set res := res + i;
		set i := i -1;
	until i <= 0
	end repeat;
	select res;
end;

drop PROCEDURE p7

call p7(10);

loop

LOOP实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。LOOP可以配合一下两个语句使用:.

LEAVE:配合循环使用，退出循环。
ITERATE:必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。

[begin_label:] LOOP
SQL逻辑...
END LOOP[end_ label];

LEAVE label;--退出指定标记的循环体
ITERATE label;--直接进入下一次循环

案例
1.计算从1累加到n的值，n为传入的参数值。
2.计算从7到n之间的偶数累加的值，n为传入的参数值。

create procedure p8(in i int)
begin
	DECLARE res int DEFAULT(0);
	sum:loop 
		if i<=0 then
		  leave sum;
		end if;
		set res := res + i;
		set i := i -1;
	end loop sum;
	select res;
end;

call p8(10)

create procedure p9(in i int)
begin
	DECLARE res int DEFAULT(0);
	sum:loop 
		if i<=0 then
		  leave sum;
		end if;
		if i%2 = 1 then 
			set i := i -1;
			iterate sum;
		end if;
		set res := res + i;
		set i := i -1;
	end loop sum;
	select res;
end;

call p9(10)

五、游标

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

声明游标

DECLARE 游标名称CURSOR FOR查询语句;

打开游标

OPEN 游标名称;

获取游标记录

FETCH 游标名称INTO变量[,变量];

关闭游标

CLOSE 游标名称;

案例
根据传入的参数uage，来查询用户表tb_user中，所有的用户年龄小于等于uage的用户姓名(name)和专业(profession)，并将用户的姓名和专业插入到所创建的一张新表(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;
	declare exit handler for SQLSTATE'02000' close u_cursor;

	
	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;

条件处理程序

条件处理程序(Handler）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。具体语法为:

DECLARE handler_action HANDLER FOR condition_value [, condition_value]... statement ;
handler_action
	CONTINUE:继续执行当前程序
	EXIT:终止执行当前程序
condition_value
	SQLSTATE sqlstate_value:状态码，如02000
	SQLWARNING:所有以01开头的SQLSTATE代码的简写
	NOT FOUND:所有以02开头的SQLSTATE代码的简写
	SQLEXCEPTION:所有没有被SQLWARNING 或 NOT FOUND捕获的SQLSTATE
	代码的简写