更新记录
转载请注明出处。
2022年9月4日 发布。
2022年9月4日 从笔记迁移到博客。

存储过程与函数

说明

存储过程和函数是一条或多条SQL语句的集合

存储过程的返回值通过输出参数实现,函数可以直接返回标量

存储过程和自定义函数区别

相同点：

​ 都是自定义逻辑的SQL集合

不同点：

存储过程没有返回值，但是可以有多个OUT参数

函数只有一个返回值，只有IN参数，没有OUT参数

存储过程和函数作用

实现复杂的SQL逻辑

减少传输量，SQL存储在服务器端

提高查询速度

设置结束符

DELIMITER 结束符

创建存储过程

CREATE PROCEDURE 存储过程名([IN | OUT | INOUT] 参数名  参数类型.....)
[特性]
BEGIN

END

特性可取值：
LANGUAGE SQL
指明使用的是SQL语言 [默认值]
[NOT] DETERMINISTIC
表示结果是否确定
DETERMINISTIC表示确定，每次输入相同就会输出相同
NOT DETEMINISTIC 表示不确定，每次输出可能不同 [默认值]
[NOT SQL | READS SQL DATA | MODIFIES SQL DATA | CONTAINT SQL]
表示包含的语句的限制
NOT SQL 表示没有包含的语句中没有SQL
READS SQL DATA 表示包含的语句有读取的SQL
MODIFIES SQL DATA 表示包含的语句有写入、修改的SQL
CONTAINT SQL 表示包含SQL，并且包含读取、写入的SQL。[默认值]
COMMENT '注释' 注释信息
SQL SECURITY [DEFINER | INVOKER]
表示谁有权限执行
DEFINER 表示定义者可以执行[默认值]
INVOKER 表示调用者可以执行
注意：特性是可选的

调用存储过程

CALL 存储过程名(参数);

备注：

​ 参数不需要带IN/OUT

修改存储过程

ALTER PROCEDURE存储过程名 (参数)
[特性]
BEGIN

END

查看存储过程和函数

SHOW CREATE [PROCEDURE | FUNCTION ] 存储过程名;

或者

SHOW [PROCEDURE | FUNCTION ] STATUS [LIKE '存储过程名'];

或者

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME = '存储过程名';

删除存储过程和函数

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

创建函数

CREATE FUNCTION 函数名([IN | OUT | INOUT] 参数名  参数类型.....)
RETURNS 返回类型
[特性]
BEGIN
  RETURN 值;
END

调用函数

函数名(参数);

修改函数

ALTER FUNCTION 函数名(参数)
RETURNS 返回类型
[特性]
BEGIN

END

参数格式

[IN | OUT| INOUT] 参数名 数据类型

比如

IN arg1 INT, OUT arg2 DATE

变量定义与赋值

声明1

DECLARE 变量名,变量名,... 数据类型 [DEFAULT 默认值];

赋值1

SET 变量名 = 值;

赋值2-多个变量赋值

注意：可以直接在存储过程之外进行声明和赋值变量

SELECT 列名, 列名 INTO @变量名, @变量名... FROM 表名;

或者

SELECT 列名, 列名 INTO 变量名,变量名,... FROM 表名;

临时变量

注意：可以直接在存储过程之外进行声明和赋值变量

定义语法：

SET @变量名 := 值;

赋值语法：

SET @变量名 := 值;

备注：

​ 必须带@符号，不论是定义还是使用，初始化必须赋值

​ 定义和赋值是一样的语法

游标定义与使用

定义游标

DECLARE 游标名
CURSOR FOR

选择语句;

打开游标

OPEN 游标名;

使用游标

FETCH 游标名 INTO 变量名,变量名,....

关闭游标

CLOSE 游标名;

条件与循环语句

IF

IF 条件
THEN 执行语句
END IF

IF-ELSE

IF 条件
THEN 执行语句
[ELSE IF 条件
THEN 执行语句]
[ELSE 执行语句]
END IF

CASE 1

CASE 表达式
  WHEN 值
THEN 执行语句
  WHEN 值
THEN 执行语句
  ...
  [ELSE 执行语句]
END CASE

CASE 2

CASE
  WHEN 条件
THEN 执行语句
  WHEN 条件
THEN 执行语句
  ...
  [ELSE 执行语句]
END CASE

LOOP

[label]: LOOP
  语句
  LEAVE label;
END LOOP [label]

LEAVE

LEAVE label;  //类似break;用于退出循环和选择
ITERATE
ITERATE label;  //类似continue;用于退出循环和选择

WHILE

[label] WHILE 条件
DO
  执行语句
END WHILE [label]

REPEAT

[label]: REPEAT
   语句
UNTIL 条件
END REPEAT [label]

定义错误捕获

语法：

DECLARE 错误名 CONDITION FOR 错误类型;

错误类型可取值：

​ 错误码

​ SQLSTATE'状态码';

备注：

​ CONDITION FOR 后面的错误码和状态码任选一个即可，注意状态码要加上SQLSTATE

定义错误处理

语法：

DECLARE 处理方式 HANDLER FOR 错误的类型 执行的SQL语句;

处理方式可取值：

CONTINUE  继续执行
EXIT    立即停止执行

错误的类型可取值：

​ 自定义的错误名

```sql
SQLSTATE '状态码'
```

​ 错误码

SQL WARNING    (01开头的全部状态码)
NOT FIND      (02开头的全部状态码)
SQLEXCEPTION   (没有被捕获的状态码)