存储过程¹

1 介绍

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

2 特点

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

3 存储过程操作

3.1. 创建

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

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

3.2. 调用

CALL 存储过程名称([参数列表]);

3.3. 查看

-- 查询指定数据库的存储过程
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINES_SCHEMA = '数据库名';

-- 查询某个存储过程的定义
SHOW CREATE PROCEDURE 存储过程名称;

3.4. 删除

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

4 变量

4.1 系统变量

系统变量
是MySQL服务器提供，不是用户定义的，属于服务器层面
分为全局变量（GLOBAL）和会话变量（SESSION）

查看系统变量

-- 查看所有系统变量
SHOW [SESSION|GLOBAL] VARIABLES;
-- 通过 like 模糊匹配查看系统变量
SHOW [SESSION|GLOBAL] VARIABLES LIKE '...';
-- 查看指定系统变量
SELECT @@[SESSION.|GLOBAL.] 系统变量名;

设置系统变量
```
SET [SESSION|GLOBAL] 系统变量名=值;
SET @@[SESSION|GLOBAL]系统变量名=值;
```
注意：
- 如果没有指定 SESSION/GLOBAL，默认是 SESSION
- MySQL服务重新启动后，所有设置的全局参数会失效，要不想失效，可在 /etc/my.cnf 中配置

4.2. 用户定义变量

用户定义变量
是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用 @变量名 使用就可以
作用域为 当前连接
注意：用户定义的变量无需对其进行声明或初始化，只不过获取到的值为 NULL
SELECT @未声明的变量名;
-- null

赋值

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;
```

4.3. 局部变量

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

声明

DECLARE 变量名 变量类型 [DEFAULT ...];
-- 变量类型： INT BIGINT CHAR VARCHAR DATE TIME 等

赋值

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

4.4. if判断

语法

IF THEN
	...
ELSEIF THEN	-- 可选
	...
ELSE		---可选
	...
END IF;

4.5. 参数（IN、OUT、INOUT）

类型
类型含义
IN 输入参数，调用存储过程时传入（默认）
OUT 输出参数，该参数可以作为返回值
INOUT 即可作为输入，也可作为输出

类型	含义
IN	输入参数，调用存储过程时传入（默认）
OUT	输出参数，该参数可以作为返回值
INOUT	即可作为输入，也可作为输出

用法

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

示例

无参

create procedure now_time()
begin
    declare now datetime default now();
    select now;
end;
-- 调用
call now_time();

in / out 参数

create procedure score_to_level(in score int, out level varchar(8))
begin
    if score >= 85 then
        set level = '优秀';
    elseif score >= 60 then
        set level = '及格';
    else
        set level = '不及格';
    end if;
end;
-- 调用
call score_to_level(68, @level);
select @level;

inout 参数

create procedure score_to_per(inout score double)
begin
    set score := score * 0.5;
end;
-- 调用
set @score :=75;
call score_to_per(@score);
select @score;

4.6 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;

4.7. while 循环

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

-- 先判断条件，再执行逻辑
WHILE 条件 DO
	SQL逻辑...
END WHILE;

4.8. repeat 循环

repeat 是有条件的循环控制语句，当满足条件的时候退出循环

-- 先执行一次逻辑，然后判断是否满足 until ，满足则退出
REPEAT 
	SQL逻辑
	UNTIL 条件
END REPEAT;

4.9. loop 循环

LOOP 实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用来实现简单的 死循环。
LOOP 可配合关键字使用：
LEAVE：配合循环使用，退出循环
ITERATE：必须用在循环中，用来跳过当前循环，直接进入下一次循环

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

示例

create procedure odd_accumulate_loop(in n int)
begin
    declare sum int default 0;
    acc:
    loop
        if n % 2 = 0 then
            set n := n - 1;
            iterate acc;
        end if;
        if n <= 0 then
            leave acc;
        end if;
        set sum := sum + n;
        set n := n - 1;
    end loop acc;
    select sum;
end;

4.10. cursor 游标

游标（*CURSOR*）
是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环处理

声明游标

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

打开游标
```
OPEN 游标名称;
```

获取游标记录

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

关闭游标
```
CLOSE 游标名称;
```

示例

注意
声明顺序
handler处理

create procedure insert_to_user_pro(in uage int)
begin
	-- 注意声明顺序
    declare uname varchar(10);
    declare upro varchar(64);
    declare u_cursor cursor for select name, profession from tb_user where age <= uage;
    declare exit handler for not found close u_cursor;
    drop table if exists tb_user_pro;
    create table tb_user_pro(
        id         int primary key not null auto_increment comment '主键ID',
        name       varchar(10)     not null comment '姓名',
        profession varchar(64) comment '职业'
    );
    open u_cursor;
    while true
        do
            fetch u_cursor into uname,upro;
            insert into tb_user_pro value (null, uname, upro);
        end while;
end;

4.11. handler 条件处理程序

条件处理函数（Handler）
可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。

DECLARE handler_action HANDLER FOR condition_value [,condition_value].. statement;

handler_action
CONTIINUE：继续执行当前程序
EXIT：终止当前程序
condition_value
SQLSTATE ‘SQL状态码’：状态码，如 02000
SQL状态码文档
SQLWARNING：所有以 01 开头的 SQLSTATE 代码的简写
NOT FOUND：所有以 02 开头的 SQLSTATE 代码的简写
SQLEXCEPTION：所有没被 SQLWARNING 和 NOT FOUND 捕获的 SQLSTATE 代码的简写

5 存储函数*

存储函数是有返回值的存储过程，存储函数的参数只能是 IN 类型

CREATE FUNCTION 存储函数名称([参数列表])
RETURNS type [characteristic ...]
BEGIN
	-- SQL语句
	RETURN ...;
END;

characteristic 说明
DETERMINISTIC：相同的输入参数总是产生相同的结果
NO SQL：不包含SQL语句
READS SQL DATA：包含读取数据，但不包含写入数据

示例

create function accumulate_func(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 accumulate_func(100);

参考黑马程序员 ↩

存储过程

存储过程1

1 介绍

2 特点

3 存储过程操作

3.1. 创建

3.2. 调用

3.3. 查看

3.4. 删除

4 变量

4.1 系统变量

4.2. 用户定义变量

4.3. 局部变量

4.4. if判断

4.5. 参数（IN、OUT、INOUT）

4.6 case

4.7. while 循环

4.8. repeat 循环

4.9. loop 循环

4.10. cursor 游标

4.11. handler 条件处理程序

5 存储函数*

相关文章

SQL

触发器

锁

存储过程¹