mysql——存储过程

一、基本语法:
1、创建

create procedure 存储过程名称([参数列表])
begin
---sql语句(一条或多条);
end;

例:

create procedure p1()
begin
select count(*) from student;
end;

2、调用

call 名称([参数]);

例:

call p1();

3、查看
(1)

 select * from information_schema.routines where routine_schema="数据库名"; ----查询指定数据库的存储过程及状态信息 

例:

select * from information_schema.ROUTINES where routine_schema="student";

(2)

show create procedure 存储过程名称; ------查询某个存储过程的定义

例:

show create procedure p1;

4、删除

drop procedure [if exists] 存储过程名称;

例:

drop procedure if exists p1;

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

delimiter $$
create procedure p1()
begin
select count(*) from student;
end$$

call p1();
$$

二、存储过程变量
1、系统变量(默认session级别)
系统变量是MySQL服务器提供，不是用户定义的,属于服务器层面。分为全局变量(GLOBAL)、 会话变量 (SESSION) 。

➢查看系统变量

SHOW [ SESSION|GLOBAL ] VARIABLES ; # 查看所有系统变量

例:

show session variables;

 

SHOW [SESSION |GLOBAL] VARIABLES LIKE....; # 可以通过LIKE模糊匹配方式查找变量

例:

show session/gloable variables like "auto%";

 

 

SELECT @@[SESSION|GLOBAL] 系统变量名; # 查看指定变量的值

例:

select @@autocommit;
select @@session.autocommit;
select @@gloable.autocommit;

➢设置系统变量

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

例:

set session autocommit=0;

 

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

注意:
◇如果没有指定SESSION/GLOBAL,默认是SESSION会话变量。
◇mysql服务重新启动之后，所设置的全局参数会失效,要想不失效，可以在/etc/my.cnf中配置。

2、用户自定义变量
用户定义变量是用户根据需要自己定义的变量,用户变量不用提前声明，在用的时候直接用“@变量名”使用就可以。其作用域为当前连接。(系统用两个@，用户自定义用一个@)
(var_name变量名，expr变量值)
➢赋值

SET @var_name = expr [ @var_name = expr]...;
SET @var.name := expr [, @var.name := exp]...; (推荐)

例:

set @myname="student";
set @myage:=10;
set @mygender:="男",@myhobby:="java";

 

SELECT @var_name = expr [ @var_name := exp]...;
SELECT 字段名 INTO @var.name FROM 表名;

例:

select @mycolor:="red";
select count(*) into @mycount from student;

➢使用

SELECT @var_name;

例:

select @myname,@myage,@mygender,@myhobby;

注意:
用户定义的变量无需对其进行声明或初始化，只不过获取到的值为NULL。

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

◆声明

DECLARE 变量名 变量类型[DEFAULT ... ] ;
变量类型就是数据库字段类型:INT、BIGINT、CHAR、VARCHAR、DATE、TIME等。
◆赋值

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

例：

creae procedure p2()
begin
declare stu_count int default 0; # 声明 default可以省略
set stu_count := 1000; [ 或者select count(*) into stu_count from student;] # 赋值
select stu_count; # 查询赋值之后的结果
end;

# 调用
call p2();

三、if 判断
if语法:

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

例：
定义存储过程，完成如下需求：
根据定义的分数score变量，判定当前分数对应的分数等级。
1. score >=85分，等级为优秀。
2. score >= 60分且score<85分，等级为及格。
3. score <60分，等级为不及格。

# 无参
create procedure p3()
begin
declare score int 58; # 传递的参数，判断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();

四、参数

类型 含义 备注
IN 该类参数作为输入，也就是需要调用时传入值 默认
OUT 该类参数作为输出，也就是该参数可以作为返回值
INOUT 既可以作为输入参数，也可以作为输出参数
用法:

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

例：
定义存储过程，完成如下需求：
1.根据传入参数score，判定当前分数对应的分数等级，并返回。
score >=85分，等级为优秀。
score >= 60分且score <85分，等级为及格。score <60分，等级为不及格。

# 有参
create procedure p4(in score int, out result varchar(10))
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;

2.将传入的200分制的分数,进行换算,换算成百分制，然后返回。

create procedure p5(inout score double)
begin
set score := score * 0.5;
end;
set @score =198;
call p5(@score);
select @score;

五、case

case语法一：

CASE case_value
WHEN when_value1 THEN statement_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;

例：
定义存储过程，完成如下需求：
根据传入的月份，判定月份所属的季节（要求采用case结构)。
1.1-3月份，为第一季度
2.4-6月份，为第二季度
3.7-9月份，为第三季度
4.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(5);

 

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

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

例：
定义存储过程，完成如下需求：
计算从1累加到n的值，n为传入的参数值。
分析：
定义局部变量，记录累加之后的值；每循环一次，就会对n进行减1，如果n减到0，则退出循环

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(10);

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

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

例：
定义存储过程，完成如下需求：
计算从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(10);

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

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

[begin_label:] LOOP
SQL逻辑...
END LOOP [end _label];
LEAVE label; --退出指定标记的循环体
ITERATE label; --直接进入下一次循环

例：
定义存储过程，完成如下需求：
（1）计算从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(10);

​
（2）计算从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;
interate sum;
end if;
set total := total + n;
set n := n - 1;
end loop sum;
select total;
end;

call p10(10);

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

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

➢打开游标

OPEN 游标名称;

➢获取游标记录

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

➢关闭游标

CLOSE 游标名称;

例：
定义存储过程，完成如下需求：
根据传入的参数uage，来查询用户表tb_user中，所有的用户年龄小于等于uage的用户姓名(name)和专业（profession)，并将用户的姓名和专业插入到所创建的一张新表(id,name,profession)中。
逻辑：
（1）声明游标，存储查询结果集
（2）准备：创建表结构
（3）开启游标
（4）获取游标中的记录
（5）插入数据到新表
（6）关闭游标

create procedure p11(in uage int)
begin
# 必须先声明普通变量再声明游标
declare uname varchar(100);
declare upro varchar(100);
declare u_cursor cursor for select name, profession from user where age <= uage; # 声明游标，存储查询结果集

drop table if exists user_pro;
create table if not exists 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 user_pro values(null, uname, upro);
end while;

close u_cursor ;
end;

call p11(30);

(while true do # 获取游标中的记录
fetch u_cursor into uname, upro;
insert into user_pro values(null, uname, upro);
end while;
)
以上代码出现问题，没有退出循环，所以会用到条件处理程序。

八、条件处理程序
条件处理程序（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代码的简写
例：

create procedure p11(in uage int)
begin
# 必须先声明普通变量再声明游标
declare uname varchar(100);
declare upro varchar(100);
declare u_cursor cursor for select name, profession from user where age <= uage; # 声明游标，存储查询结果集
declare exit handler for SQLSTATE '02000' close u_cursor ; # 还可以写SQLWARNING、NOT FOUND、SQLEXCEPTION

drop table if exists user_pro;
create table if not exists 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 user_pro values(null, uname, upro);
end while;

close u_cursor ;
end;

call p11(30);

例2：

create procedure p12(in uage int)
begin
# 必须先声明普通变量再声明游标
declare uname varchar(100);
declare upro varchar(100);
declare u_cursor cursor for select name, profession from user where age <= uage; # 声明游标，存储查询结果集
declare exit handler for not found close u_cursor ; # 还可以写SQLWARNING、NOT FOUND、SQLEXCEPTION

drop table if exists user_pro;
create table if not exists 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 user_pro values(null, uname, upro);
end while;

close u_cursor ;
end;

call p12(30);