MySQL8.0 存储过程和函数

S_zhangmin

已于 2022-02-10 10:30:07 修改

阅读量5.2k

点赞数 2

于 2022-02-08 22:59:39 首次发布

本文链接：https://blog.csdn.net/S_zhangmin/article/details/122823349

版权

MySQL 同时被 2 个专栏收录

25 篇文章 1 订阅

订阅专栏

SQL

18 篇文章 0 订阅

订阅专栏

PART1. 创建存储过程和函数

存储程序可以分为存储过程和函数。在MySQL中，创建存储过程和函数使用的语句分别是CREATE PROCEDURE 和 CREATE FUNCTION。使用CALL语句来调用存储过程，只能用输出变量返回值。函数可以从语句外调用（引用函数名），也能返回标量值。存储过程也可以调用其他存储过程。

1. 创建存储过程

创建存储过程，需要使用CREATE PROCEDURE语句：

CREATE PROCEDURE sp_name ( [proc_parameter] )
[characteristics ... ] routine_body

CREATE PROCEDURE为用来创建存储函数的关键字；

sp_name为存储过程的名称；

pro_parameter为指定存储过程的参数列表：

[ IN | OUT | INOUT ] param_name type

其中，IN 表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出；

param_name表示参数名称；

type表示参数的类型。

characteristics 指定存储过程的特性，有以下取值；

1）LANGUAGE SQL: 说明 routine_body 部分是由SQL语句组成的，当前系统支持的语言为SQL。SQL是LANGUAGE特性的唯一值。

2）[NOT] DETERMINISTIC: 指明存储过程执行的结果是否正确。默认为 NOT DETERMINISTIC。

3）{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }: 指明子程序使用SQL语句的限制。表明子程序包含SQL语句，但是不包含读写数据的语句；NO SQL 表面子程序不包含SQL语句；READS SQL DATA说明子程序包含读数据的语句；MODIFIES SQL DATA表明子程序包含写数据的语句。默认情况下，系统指定为CONTAINS SQL。

4）SQL SECURITY{ DEFINER | INVOKER }：指明谁有权限来执行。DEFINER 表示只有定义者才能执行。INVOKER 表示拥有权限的调用者可以执行。默认情况下，系统会指定为DEFINER。

5）COMMENT ‘string'：注释信息，可以用来描述存储过程或函数。

routine_body 是 SQL 代码的内容，可以用BEGIN…END来表示 SQL 代码的开始和结束。

DELIMITER //
CREATE PROCEDURE Proc()
BEGIN
SELECT * FROM fruits;
END // 
DELIMITER;

注意：“DELIMITER // ”语句的作用是将MySQL的结束符设置为//，因为MySQL默认的语句结束符号为分号’；‘。为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER 改变存储过程的结束符，并以 “END //” 结束存储过程。存储过程定义完毕之后再使用 ”DELIMITER；“恢复默认结束符。DELIMITER也可以指定其他符号作为结束符。

注意：当使用 DELIMITER 命令时，应该避免使用反斜杠（’\'）字符，因为反斜线是MySQL的转义字符。

2. 创建存储函数

创建存储函数，需要使用CREATE FUNCTION 语句，且用SELECT 来调用：

CREATE FUNCTION func_name ( [func_parameter] )
RETURNS type
[characteristic ... ] routine_body

CREATE FUNCTION 为用来创建存储函数的关键字：func_name 表示存储函数的名称；

func_parameter为存储过程的参数列表：

[ IN | OUT | INOUT ] param_name type

其中，1）IN 表示输入参数，2）OUT表示输出参数，3）INOUT表示既可以输入也可以输出；

4）param_name表示参数名称；

5）type表示参数的类型；

RETURNS type 语句表示函数返回数据的类型。

DELIMITER //
CREATE FUNCTION NameByZip()
RETURNS CHAR(50)
RETURN (SELECT s_name FROM suppliers WHERE s_call= '48075');

DELIMITER;

如果在存储函数中的RETURN 语句返回一个类型不同于函数的RETURNS子句中指定类型的值，返回值将被强制为恰当的类型。但是，如果一个函数返回一个INT值，但是RETURN语句返回一个字符串，从函数返回的值时会报错。

注意：指定参数为IN、OUT或INOUT只对PROCEDURE是合法的。（FUNCTION中总是默认为IN 参数）。RETURNS子句只能对FUNCTION做指定，对函数而言这是强制的。它用来指定函数的返回类型，而且函数体必须包含一个RETURN value语句。

3. 变量的使用

变量可以在子程序中声明并使用，这些变量的作用范围是在BEGIN…END程序中。

1. 定义变量

DECLARE var_name[,varname] ... data_type [DEFAULT value];

var_name为局部变量的名称。DEFAULT value子句给变量提供了一个默认值。值除了可以被声明为一个常数之外，还可以被指定为一个表达式。如果没有 DEFAULT子句，初始值为NULL。

2. 为变量赋值

定义变量之后，为变量赋值可以改变变量的默认值。在MySQL中，使用SET语句为变量赋值：

SET var_name = expr [, var_name = expr] ...;

在存储程序中的SET语句作为预先存在的SET语法的一部分来实现，允许SET a=x，b=y，… 这样的扩展语法。其中，不同的变量类型（局域变量和全局变量）可以被混合起来。这也允许把局部变量和一些只对系统变量有意义的选项合并起来。

DECLARE var1, var2, var3 INT;
SET var1 = 10, var2 = 20;
SET var3 = var1 + var2;

在MySQL中，还可以通过SELECT…INTO为一个或多个变量赋值，语法如下：

SELECT col_name [, ...] INTO var_name[,...] table_expr;

col_name表示字段名称；var_name表示定义的变量名称；table_expr表示查询条件表达式，包括表名称和WHERE子句。

#举例
DECLARE fruitname CHAR(50);
DECLARE fruitprice DECIMAL(8,2);
SELECT f_name,f_price INTO fruitname, fruitprice
FROM fruits WHERE f_id = 'a1';

4. 定义条件和处理程序

特定条件需要特定处理。这些条件可以联系到错误以及子程序中的一般流程控制。定义条件是事先定义程序执行过程中遇到的问题，处理程序定义了在遇到这些问题时应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行。这样可以增强存储程序处理问题的能力，避免程序异常停止运行。

1. 定义条件

定义条件使用DECLARE语句：

DECLARE condition_name CONDITION FOR [codition_type]
[condition_type]:
SQLSTATE [VALUE] sqlstate_value | mysql_error_code

1）condition_name参数表示条件的名称；

2）condition_type参数表示条件的类型；

3）sqlstate_value和MySQL_error_code都可以表示MySQL的错误，sqlstate_value为长度为5的字符串类型错误代码，MySQL_error_code为数值类型错误代码。例如ERROR 1142（42000）中，sqlstate_value的值是42000，MySQL_error_code的值是1142。

这个语句指定需要特殊处理的条件。它将一个名字和指定的错误条件关联起来。这个名字可以随后被用在定义处理程序的DECLARE HANDLER语句中。

//方法一：使用sqlstate_value
DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000';
//方法二：使用mysql_error_code
DECLARE command_not_allowed CONDITION FOR 1148;

2. 定义处理程序

DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement
handle_type:
    CONTINUE | EXIT | UNDO
condition_value:
    SQLSTATE [VALUE] sqlstate_value
| condition_name
| SQLWARNING
| NOT FOUND
| SQLEXCEPTION
| mysql_error_code

handler_type 为错误处理方式，参数取3个值：CONTINUE、 EXIT 和 UNDO。

1）CONTINUE 表示遇到错误不处理，继续执行；

2）EXIT表示遇到错误马上退出；

3）UNDO表示遇到错误后撤回之前的操作，MySQL中暂时不支持这样的操作。

condition_value 表示错误类型，可以有以下取值：

1）SQLSTATE[VALUE]sqlstate_value 包含5个字符的字符串错误值；

2）condition_name 表示 DECLARE CONDITION 定义的错误条件名称；

3）SQLWARNING 匹配所有以01开头的SQLSTATE错误代码；

4）NOT FOUND 匹配所有以02开头的SQLSTATE错误代码；

5）SQLEXCEPTION 匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码。

6）MySQL_error_code 匹配数值类型错误代码。

sp_statement参数为程序语句段，表示在遇到定义的错误时需要执行的存储过程或函数。

//方法一：捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info='NO_SUCH_TABLE';

//方法二：捕获mysql_error_code
DECLARE CONTINUE HANDLER FOR 1146 SET @info = 'NO_SUCH_TABLE';

//方法三：先定义条件，然后调用
DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info = 'NO_SUCH_TABLE';

//方法四：使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info= 'ERROR';

//方法五：使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info = 'NO_SUCH_TABLE';

//方法六：使用SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info = 'ERROR';

方法一：捕获sqlstate_value值。如果遇到sqlstate_value值为“42S02”，执行CONTINUE操作，并且输出“NO_SUCH_TABLE”信息。

方法二：捕获MySQL_error_code值。如果遇到MySQL_error_code值为1146，执行CONTINUE操作，并且输出“NO_SUCH_TABLE”信息。

方法三：先定义条件，再调用条件。这里先定义no_such_table条件，遇到1146错误就执行CONTINUE操作。

方法四：使用SQLWARNING。SQLWARNING捕获所有以01开头的sqlstate_value值，然后执行EXIT操作，并且输出“ERROR”信息。

方法五：使用NOT FOUND。NOT FOUND捕获所有以02开头的sqlstate_value值，然后执行EXIT操作，并且输出“NO_SUCH_TABLE”信息。

方法六：使用SQLEXCEPTION。SQLEXCEPTION捕获所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value值，然后执行EXIT操作，并且输出“ERROR”信息。

CREATE TABLE t (s1 INT,PRIMARY KEY (s1));
DELIMITER //
CREATE PROCEDURE handlerdemo()
	BEGIN
		DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
        SET @x = 1;
        INSERT INTO t VALUES (1);
        SET @x = 2;
        INSERT INTO t VALUES (1);
        SET @x = 3;
    END;
    //
DELIMITER ;
/*调用存储过程*/
CALL handlerdemo();
/*查看调用过程结果*/
SELECT @x;

@x是一个用户变量，执行结果@x等于3，这表明MySQL被执行到程序的末尾。如果“DECLARE CONTINUE HANDLER FOR SQLSTATE ‘23000’ SET @x2 = 1；”这一行不在，第二个INSERT因PRIMARY KEY强制而失败之后，MySQL可能已经采取默认（EXIT）路径，并且SELECT @x 可能已经返回2。

注意：“@var_name”表示用户变量，使用SET语句为其赋值，用户变量与连接有关，一个客户端定义的变量不能被其它客户端看到或被使用。当客户端退出时，该客户端连接的所有变量将自动释放。

5. 光标的使用（SQLSERVER里叫游标）

查询语句可能返回多条记录，如果数据量非常大，需要在存储过程和存储函数中使用光标来逐条读取查询结果集中的记录。应用程序可以根据需要滚动或浏览其中的数据。

光标必须在声明处理程序之前被声明，并且变量和条件还必须在声明光标或处理程序之前被声明。

1. 声明光标

DECLARE cursor_name CURSOR FOR select_statement

cursor_name 参数表示光标的名称；

select_statement 参数表示SELECT 语句的内容，返回一个用于创建光标的结果集。

例如：声明名称为cursor_fruit 的光标。

DECLARE cursor_fruit CURSOR FOR SELECT f_name, f_price FROM fruits;

光标的名称cur_fruit,SELECT 语句部分从fruits 表查询出f_name 和 f_price 字段的值。

2. 打开光标

OPEN cursor_name {光标名称}

这个语句打开前声明的名称为cursor_name的光标。

例：打开cursor_fruit的光标。

OPEN cursor_fruit ;

3. 使用光标

FETCH cursor_name INTO var_name {, var_name} ... {参数名称}

cursor_name 参数表示光标的名称；var_name 参数表示将光标中的SELECT 语句查询出来的信息存入该参数中，var_name 必须在声明光标之前就定义好。

例：cursor_fruit 的光标将查询出来的数据存入fruit_name 和fruit_price 这两个变量中：

FETCH cursor_fruit INTO fruit_name, fruit_price;

fruit_name, fruit_price 必须在前面已经定义。

4. 关闭游标

CLOSE cursor_name{光标名称}

语句关闭先前打开的光标。如果未能明确地关闭，光标在它被声明的复合语句的末尾关闭。

注意：MySQL中的光标只能在存储过程和函数中使用。

6. 流程控制的使用

MySQL中用来构造控制流程的语句有IF语句、CASE语句、LOOP语句、LEAVE语句、ITERATE语句、REPEAT语句和WHILE语句。

1. IF语句

IF 语句包含多个条件判断，根据判断的结果为TRUE或FALSE执行相应的语句。

IF　expr_condition THEN statement_list
    [ELSEIF expr_condition THEN statement_list] ...
    [ELSE statement_list]
END IF

IF 实现了一个基本的条件结构。如果expr_condition 求值为真（TRUE），相应的SQL语句列表被执行；如果没有expr_condition 匹配，则ELSE 子句里面的语句被执行。statement_list 可以包括一个或多个语句。

IF语句都需要使用END IF来结束。

注意：MySQL中还有一个IF() 函数，不同于这里描述的IF语句。

2. CASE语句

CASE有两种格式进行条件判断：

#格式1：
CASE case_expr
    WHEN when_value THEN statement_list
    [WHEN when_vlaue THEN statement_list] ...
    [ELSE statement_list]
END CASE

1）case_expr 参数表示条件判断的表达式，决定了哪一个WHEN子句会被执行；

2）when_value 参数表示表达式可能的值，如果某个when_value表达式与case_expr 表达式结果相同，则执行对应THEN 关键字后的statement_list 中的语句；

3）statement_list 参数表示不同when_value 值的执行语句。

#格式2：
CASE
    WHEN expr_condition THEN statement_list
    [WHEN expr_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

注意：这里介绍的用在存储程序里的CASE 语句与”控制流程函数“里描述的SQL CASE 表达式的CASE 语句有轻微不同。这里的CASE 语句不能有ELSE NULL子句，并且用END CASE代替END 来终止。

3. LOOP语句

LOOP循环语句用来重复执行某些语句，与IF和CASE相比，LOOP 只是创建一个循环操作的过程，并不进行条件判断。LOOP内的语句一直重复执行直到循环被退出（使用LEAVE子句），跳出循环过程。

[loop_label] LOOP
    statement_list
END LOOP [loop_label]

1）loop_label 表示LOOP 语句的标注名称，该参数可以省略，不建议省略；

2）statement_list 参数表示需要循环执行的语句。

4. LEAVE语句

LEAVE语句用来退出任何被标注的流程控制结构：

LEAVE label

label参数表示循环的标志。LEAVE 和BEGIN ... END 或循环一起被使用。就

例：使用LEAVE语句退出循环

add_num: LOOP
SET @count = @count + 1;
IF @count = 50 THEN LEAVE add_num;
END LOOP add_num;

5. ITERATE语句

ITERATE只可以将执行顺序转到语句段开头处

ITERATE label

例：ITERATE 语句示例：

CREATE PROCEDURE doiterate()
BEGIN
DECLARE p1 INT DEFAULT 0;
my_loop: LOOP
    SET p1 = p1 + 1;
    IF p1 < 10 THEN ITERATE my_loop;
    ELSEIF p1 > 20 THEN LEAVE my_loop;
    END IF;
    SELECT CONCAT('p1 is between ',P1);
END LOOP my_loop;
END

6. REPEAT 语句

REPEAT 语句创建一个带条件判断的循环过程，每次语句执行完毕之后会对条件表达式进行判断，如果表达式为真，则循环结束；否则重复执行循环中的语句：

[repeat_label:] REPEAT
    statement_list
UNTIL expr_condition
END REPEAT [repeat_label]

1）repeat_label 为REPEAT语句的标注名称，该参数可以省略；

2）REPEAT语句内的语句或语句群被重复，直到expr_condition为真。

例：REPEAT语句示例：

DECLARE id INT DEFAULT 0;
REPEAT SET id = id + 1;
UNTIL id >= 10
END

7. WHILE 语句

WHILE语句创建一个带条件判断的循环过程，与REPEAT不同，WHILE在执行语句执行时，先对指定的表达式进行判断，如果为真，就执行循环内的语句，否则退出循环：

[while_label:] WHILE expr_condition DO
    statement_list
END WHILE [while_label]

while_label 为WHILE语句的标注名称；expr_condition为进行判断的表达式，如果表达式结果为真，WHILE 语句内的语句或语句群被执行，直到expr_condition为假，退出循环。

PART2. 调用存储过程和函数

1. 调用存储过程

CALL sp_name([parameter[,...]])

CALL语句调用一个先前用CREATE PROCEDURE创建的存储过程，其中sp_name为存储过程名称，parameter为存储过程的参数。

2. 调用存储函数

在MySQL中，存储函数的使用方法与MySQL内部函数的使用方法是一样的。换言之，用户自己定义的存储函数与MySQL内部函数是一个性质的。区别在于，存储函数是用户自己定义的，而内部函数是MySQL的开发者定义的。

SELECT FUNCTION_NAME(parameter);

注意：如果在创建存储函数中报错”you *might* want to use the less safe log_bin_trust_function_createors variable“，需要执行如下代码：

SET GLOBAL log_bin_trust_function_creators = 1;

PART3. 查看存储过程和函数

MySQL存储了存储过程和函数的状态信息，用户可以使用SHOW STATUS 语句或SHOW CREATE 语句来查看，也可直接从系统的information_schema数据库中查询。

1. 使用SHOW STATUS语句查看存储过程和函数的状态

SHOW {PROCEDURE | FUCTION} STATUS [LIKE 'pattern']

这个语句是一个MySQL的扩展，返回子程序的特征，如数据库、名字、类型、创建者及创建和修改日期。如果没有指定样式，那么根据使用的语句，所有存储程序或存储函数的信息都会被列出。其中，PROCEDURE和FUNCTION分别表示查看存储过程和函数；LIKE语句表示匹配存储过程或函数的名称。

2. 使用SHOW CREATE语句查看存储过程和函数的定义

SHOW CREATE {PROCEDURE | FUNCTION} sp_name

这个语句是一个MySQL的扩展。类似于SHOW CREATE TABLE，它返回一个可用来重新创建已命名子程序的确切字符串。PROCEDURE 和 FUNCTION 分别表示查看存储过程和函数；

sp_name参数表示匹配存储过程或函数的名称。

3. 从information_schema.Routines 表中查看存储过程和函数的信息

SELECT * FROM information_schema.Rountines
WHERE ROUTINE_NAME = 'sp_name';

其中ROUTINE_NAME字段中存储的是存储过程和函数的名称；sp_name 参数表示存储过程或函数的名称。

PART4. 修改存储过程和函数

ALTER {PROCDURE | FUNCTION} sp_name [characteristic ...]

sp_name参数表示存储过程或函数的名称；characteristic参数指定存储函数的特性，可能的取值有：

1）CONTAINS SQL，表示子程序包含SQL语句，但不包含读或写数据的语句。

2）NO SQL，表示子程序中不包含SQL语句。

3）READS SQL DATA，表示子程序包含读数据的语句

4）MODIFIES SQL DATA，表示子程序中包含写数据的语句。

5）SQL SECURITY{DEFINER | INVOKER}，指明谁有权限来执行。

6）DEFINER，表示只有定义者自己才能执行。

7）INVOKER，表示调用者可以执行。

8）COMMENT 'string'，表示注释信息。

注意：修改存储过程和修改存储函数语句的结构是一样的，语句中的所有参数也是一样的。而且它们与创建存储过程或函数的语句中的参数也是基本一样的。

PART5. 删除存储过程和函数

DROP [PROCEDURE | FUNCTION] [IF EXISTS] sp_name;

用来移除一个存储过程或函数。sp_name为要移除的存储过程或函数的名称。

IF EXISTS子句是一个MySQL的扩展。如果程序或函数不存储，它可以防止发生错误，产生一个用SHOW WARNINGS 查看的警告。

PART6. MySQL8.0的新特性——全局变量的持久化

在MySQL数据库中，全局变量可以通过SET GLOBAL语句来设置。

SET GLOBAL MAX_EXECUTION_TIME = 2000;

使用SET GLOBAL 语句设置的变量值只会临时生效。数据库重启后，服务器又会从MySQL配置文件中读取变量的默认值。

MySQL8.0 版本新增了SET PERSIST命令。例如，设置服务器的最大连接数为1000：

SET PERSIST max_connections = 1000;

MySQL会将该命令的配置保存到数据目录下的mysqld-auto.cnf 文件中，下次启动时会读取该文件，用其中的配置来覆盖默认的配置文件。

重启服务全局变量变回151。

-- end

S_zhangmin

关注

2
点赞
踩
8

收藏

觉得还不错? 一键收藏
0
评论
MySQL8.0 存储过程和函数

存储程序可以分为存储过程和函数。在MySQL中，创建存储过程和函数使用的语句分别是CREATE PROCEDURE 和 CREATE FUNCTION。使用CALL语句来调用存储过程，只能用输出变量返回值。函数可以从语句外调用（引用函数名），也能返回标量值。存储过程也可以调用其他存储过程。
复制链接

扫一扫