[大数据]【mysql学习笔记27】存储过程

语法

create procedure 存储过程名称([参数列表])
begin
—SQL语句
end;

调用

call procedure_name([参数列表]);

查看

select * from information_schema.ROUTINES where routine_schema=‘xxx’;–查询指定数据库的存储过程及状态
show create procedure procedure;–查看存储过程定义

删除

drop procedure [if exists] procedure_name;

变量

查看系统变量

show global variables;
show global variables like ‘xxx’;
或
select @@global.xxx;

查看会话变量

show session variables;
或
select @@session.xxx;

设置系统变量

set [global|session] xxx=xxx;
set @@[global|session].xxx=xxx;

用户自定义变量

介绍
用户自定义变量是用户根据自己需要定义的变量，用户变量不需要提前声明，用的时候直接“@变量名”就可以使用，其作用域为当前连接。未被初始化的变量默认为NULL。
赋值
SET @变量名=value;
SET @变量名:=value;
SELECT @变量名:=value;
SELECT 字段名 INTO @变量名 FROM 表名;
建议使用:=代替=，以此区分赋值和等值
使用
SELECT @var_name

局部变量

介绍
局部变量是声明在局部的变量，访问之前需要declare声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在BEGIN…END块内。
声明
DECLARE 变量名 类型 [DEFAULT …]
赋值
SET 变量名=值;
SET 变量名:=值;
SELECT 字段名 INTO 变量名 FROM 表名;

if语句

语法

if 条件1 then
…
elseif 条件2 then
…
else
…
end if;

存储过程参数

类型

in 输入参数
out 输出参数，也就是可以作为返回值
inout 既可作为输入参数，也可作为输出参数

用法

create procedure procedure_name([in/out/inout 参数名 参数类型])
begin
— sql语句
end;

case

用法

case case_value
when when_value1 then statement_list1
[when when_value2 then statement_list2]
[else statement_list]
end case;
或
case
when search_condition1 then statement_list1
[when search_condition2 then statement_list2]
[else statement_list]
end case;

while

用法

while 条件 do
sql逻辑
end while;

repeat

repeat
sql逻辑
until 条件
end repeat;

loop

介绍

loop实现简单的循环,可以使用leave退出循环,iterate进入下一次循环,如果不用这两个语句跳出循环,就会死循环

用法

[begin_label:] LOOP
SQL逻辑…
END LOOP [end_label];

leave label; 退出指定标记的循环体
iterate label; 进入下一循环

游标

介绍

游标(cursor)是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环处理。游标的使用包括游标的declare、open、fetch和close。

声明游标

declare 游标名称 cursor for 查询语句;

打开游标

open 游标名称;

获取游标记录

fetch 游标名称 into 变量1[,变量2,…];

关闭游标

close 游标名称;

条件处理程序

介绍
条件处理程序用来做异常处理。
语法
DECLARE handler_action HANDLER FOR condition_value [,condition_value,…] statment;
handler_action
CONTINUE:继续执行当前程序
EXIT:终止执行当前程序
condition_value
SQLSTATE:状态码，如游标fetch空行的状态码是02000
SQLWARNING:所有以01开头的SQLSTATE的统称
NOT FOUND:所有以02开头的SQLSTATE的统称
SQLEXCEPTION:除了SQLWARNING和NOT FOUND外的剩余状态码的统称
状态码查询网站
https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html

游标处理标准模板

declare name varchar(100);
declare u_cursor for select name from tb_user where age<=50;
declare exit handler for SQLSTATE '02000' close u_cursor;
open u_cursor;
while true do
	fetch u_cursor into name;
	insert into tb_user_name values(null,name);
end while;
close u_cursor;