mysql13-存储过程和函数

1、存储过程和函数

存储过程就是一条或者多条SQL语句的集合,
存储程序可以分为存储过程和函数。
- 创建存储过程：CREATE PROCEDURE
- 创建存储函数：CREATE FUNCTION
- 使用CALL语句来调用存储过程，只能用输出变量返回值。存储过程也可以调用其他存储过程。
- 函数可以从语句外调用(即通过引用函数名) ，也能返回标量值。
测试数据

create database hh;
use hh;

create table tb1(
id int primary key auto_increment,
name char(15),
sex enum('男','女')
);

create table tb2(
id int primary key auto_increment,
name char(15)
);

insert into tb1(name,sex) values('张三','男'),('李四','男'),('王二','男'),('麻子','男'),('翠花','女'),('小红','女');

测试数据

2、存储过程

1、创建存储过程

编写存储过程并不是件简单的事情，可能存储过程中需要复杂的SQL语句，并且要有创建存储过程的权限。
使用存储过程将简化操作，减少冗余的操作步骤，还可以减少操作过程中的失误，提高效率，因此存储过程是非常有用的。

基本语法格式如下：

CREATE [DEFINER = user] PROCEDURE sp_name ([proc_parameter[, ...]])
    [characteristic ...] 
    routine_body    --创建存储过程

proc_parameter：    --定义存储过程的参数
    [IN param_name type],
    ...
    [OUT param_name type],
    ...
    [INOUT param_name type],
    ...

characteristic：
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

routine_body：      --SQL代码
    BEGIN
        SQL...
    END

- CREATE PROCEDURE为用来创建存储函数的关键字。
- sp_name为存储过程的名称。
- [IN | OUT | INOUT] param_name type：指定存储过程的参数列表
  - IN表示输入参数。
  - OUT表示输出参数。
  - INOUT表示既可以输入也可以输出。
  - param_name表示参数名称。
  - type表示参数的类型，该类型可以是MysQL数据库中的任意类型。
- routine_body是SQL代码的内容，可以用BEGIN...END来表示SQL代码的开始和结束。

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

LANGUAGE SQL：说明routine_body部分是由SQL语句组成的，当前系统支持的语言为SQL，SQL是LANGUAGE特性的唯一值。
[NOT] DETERMINISTIC：指明存储过程执行的结果是否确定。
- DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。
- NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。
- 默认情况下，系统指定为NOT DETERMINISTIC。
{CONTAINS SQL| NO SQL | READS SQL DATA | MODIFIES SQL DATA}：指明子程序使用SQL语句的限制。
- CONTAINS SQL表明子程序包含SQL语句，但是不包含读写数据的语句。
- NO SQL表明子程序不包含SQL语句。
- READS SQL DATA说明子程序包含读数据的语句。
- MODIFIES SQL DATA表明子程序包含写数据的语句。
- 默认情况下，系统指定为CONTAINS SQL。
SQL SECURITY {DEFINER | INVOKER}：指明谁有权限来执行。
- DEFINER表示只有定义者才能执行。
- INVOKER表示拥有权限的调用者可以执行。
- 默认情况下，系统指定为DEFINER。
COMMENT 'string'：注释信息，可以用来描述存储过程或函数。

示例1：无参数

此存储过程没有参数，但是后面的()仍然需要。
"DELIMITER //"语句的作用是将MySQL的结束符设置为//，因为MySQL默认的语句结束符号为分号";"，为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER改变存储过程的结束符，并以"END/"结束存储过程。存储过程定义完毕之后再使用"DELIMITER ;"恢复默认结束符。
注意，当使用DELIMITER命令时，应该避免使用反斜杠("\")字符，因为反斜线是MysQL的转义字符。

delimiter //
CREATE PROCEDURE pro_tb1_name1()
    BEGIN
        SELECT name from tb1;    --返回tb1表中的name列
    END //
delimiter ;

示例2：有参数

delimiter //
CREATE PROCEDURE pro_tb1_name2(IN var_id INT, OUT var_name char(15))
    BEGIN
        SELECT name INTO var_name FROM tb1 WHERE id = var_id;    --将字段name的值赋给变量var_name
        SELECT name from tb1;                                    --返回tb1表中的name列
    END //
delimiter ;

2、调用存储过程

存储过程必须使用CALL语句调用，并且存储过程和数据库相关，如果要执行其他数据库中的存储过程，需要指定数据库名称，例如CALL dbname.procname。
存储过程的返回值可以有两种，一种是一个结果集（即select查询的结果），另一种是返回值（out、inout参数的返回值）。

基本语法格式如下：

CALL procname ([parameter[, ....]])
CALL dbname.procname ([parameter[, ...]])

- procname为存储过程的名称。
- parameter为存储过程的参数。

示例：

--调用上面示例1的存储过程
mysql> call pro_tb1_name1;
+------+
| name |
+------+
| 张三 |
| 李四 |
| 王二 |
| 麻子 |
| 翠花 |
| 小红 |
+------+

--调用上面示例2的存储过程
mysql> select @var_name;    --在MySQL中使用变量，在变量名的前面必须加@符号，变量的默认值是NULL
+-----------+
| @var_name |
+-----------+
| NULL      |
+-----------+
mysql> call pro_tb1_name2(1,@var_name);    --调用存储过程tb_name2，（IN var_id =1，OUT var_name=@var_name）
+------+
| name |
+------+
| 张三 |
| 李四 |
| 王二 |
| 麻子 |
| 翠花 |
| 小红 |
+------+
mysql> select @var_name;              --
+-----------+
| @var_name |
+-----------+
| 张三      |
+-----------+

3、查看存储过程

1、查看存储过程的状态

基本语法格式如下：

SHOW PROCEDURE STATUS [LIKE 'pattern'];

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

示例：

SHOW PROCEDURE STATUS LIKE '%tb%';

2、查看存储过程的定义语句

基本语法格式如下：

SHOW CREATE PROCEDURE sp_name;

这个语句是一个MySQL的扩展，它返回一个可用来重新创建己命名子程序的确切字符串。
sp_name参数表示匹配存储过程的名称。

示例：

SHOW CREATE PROCEDURE 'pro_tb1_name2';

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

基本语法格式如下：

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

ROUTINE_NAME字段中存储的是存储过程的名称。
sp_name参数表示存储过程的名称。

示例：

SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='pro_tb1_name2';

4、修改存储过程

使用ALTER语句可以修改存储过程的特性。

基本语法格式如下：

ALTER PROCEDURE sp_name [characteristic ...]

sp_name参数表示存储过程的名称
characteristic参数指定存储过程的特性，可能的取值有:
- CONTAINS SQL表示子程序包含SQL语句，但不包含读或写数据的语句。
- NO SQL表示子程序中不包含SQL语句。
- READS SQL DATA表示子程序中包含读数据的语句。
- MODIFIES SQL DATA表示子程序中包含写数据的语句。
- SQL SECURITY {DEFINER | INVOKER}指明谁有权限来执行。
- DEFINER表示只有定义者自己才能够执行。
  - INVOKER表示调用者可以执行。
  - COMMENT 'string'表示注释信息。
与创建存储过程的语句中的参数也是基本一样的。

5、删除存储过程

使用DROP语句删除存储过程。

基本语法格式如下：

DROP PROCEDURE [IF EXISTS] sp_name

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

示例：

DROP PROCEDURE pro_tb1_name2;

3、存储函数

1、创建存储函数

基本语法格式如下：

CREATE [DEFINER = user] FUNCTION func_name ([func_parameter[, ...]])
    RETURNS type
    [characteristic ...]
    routine_body

func_parameter:    --定义存储函数的参数
    param_name type

characteristic: {
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
}

routine_body：      --SQL代码
    BEGIN
        SQL...
    END

- CREATE FUNCTION为用来创建存储函数的关键字。
- func_name表示存储函数的名称。
- param_name type：指定存储过程的参数列表，只有输入参数。
  - param_name表示参数名称。
  - type表示参数的类型，该类型可以是MysQL数据库中的任意类型。
- RETURNS type语句表示函数返回数据的类型，可以是MysQL数据库中的任意类型。（存储函数必须有RETURNS子句）
- characteristic指定存储函数的特性，取值与创建存储过程时相同。
- routine_body是SQL代码的内容，可以用BEGIN...END来表示SQL代码的开始和结束。

示例1：无参数

此存储函数返回tb1表中的id等于1的name值

delimiter //
CREATE FUNCTION func_tb1_id1()
    RETURNS CHAR(15)
    RETURN (SELECT name FROM tb1 WHERE id = 1);
//
delimiter ;

示例2：有参数

此存储函数返回tb1表中的id等于参数val_id的name值

DELIMITER //
CREATE FUNCTION func_tb1_id2(val_id INT)
    RETURNS CHAR(15)
    BEGIN
        RETURN (SELECT name FROM tb1 WHERE id = val_id);
    END //
DELIMITER ;

2、调用存储函数

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

示例：

--调用前面示例1的存储函数
mysql> select func_tb1_id1();
+----------------+
| func_tb1_id1() |
+----------------+
| 张三           |
+----------------+

--调用前面示例2的存储函数
mysql> select func_tb1_id2(3);
+-----------------+
| func_tb1_id2(3) |
+-----------------+
| 王二            |
+-----------------+

3、查看存储函数

1、查看存储函数的状态

基本语法格式如下：

SHOW FUNCTION STATUS [LIKE 'pattern'];

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

示例：

SHOW FUNCTION STATUS LIKE '%tb%';

2、查看存储函数的定义语句

基本语法格式如下：

SHOW CREATE FUNCTION sp_name;

这个语句是一个MySQL的扩展，它返回一个可用来重新创建己命名子程序的确切字符串。
sp_name参数表示匹配存储函数的名称。

示例：

SHOW CREATE FUNCTION 'func_tb1_id1';

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

基本语法格式如下：

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

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

示例：

SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='func_tb1_id1';

4、修改存储函数

使用ALTER语句可以修改存储函数的特性。

基本语法格式如下：

ALTER FUNCTION sp_name [characteristic ...]

sp_name参数表示存储函数的名称
characteristic参数指定存储函数的特性，可能的取值有:
- CONTAINS SQL表示子程序包含SQL语句，但不包含读或写数据的语句。
- NO SQL表示子程序中不包含SQL语句。
- READS SQL DATA表示子程序中包含读数据的语句。
- MODIFIES SQL DATA表示子程序中包含写数据的语句。
- SQL SECURITY {DEFINER | INVOKER}指明谁有权限来执行。
- DEFINER表示只有定义者自己才能够执行。
  - INVOKER表示调用者可以执行。
  - COMMENT 'string'表示注释信息。
与创建存储函数的语句中的参数也是基本一样的。

5、删除存储函数

使用DROP语句删除存储函数。

基本语法格式如下：

DROP FUNCTION [IF EXISTS] sp_name

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

示例：

DROP FUNCTION 'func_tb1_id1';

4、存储过程和函数进阶

1、变量的使用

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

1、定义变量

基本语法格式如下：

DECLARE var_name1 [, var_name2, ...] date_type [DEFAUL value];

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

示例：

定义名称为var_name的变量，类型为INT类型，默认值为100

DECLARE var_name INT DEFAUL 100;

2、为变量赋值

定义变量之后，为变量赋值可以改变变量的默认值，MySQL中使用SET语句为变量赋值。
基本语法格式如下：

SET var_name1 = expr [, var_name2 = expr, ...];

示例：

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

3、SELECT ... INTO

MySQL中还可以通过SELECT ... INTO为一个或多个变量赋值。

基本语法格式如下：

SELECT col_name1 [,col_name2, ...] INTO var_name1 [, var_name2, ...] table_expr;

这个SELECT语法把选定的列直接存储到对应位置的变量。
- col_name表示字段名称。
- var_name表示定义的变量名称。
- table_expr表示查询条件表达式，包括表名称和WHERE子句。

示例1：

DECLARE tb1_name CHAR(50);
DECLARE tb_sex enum('男','女');
SELECT name, sex INTO var_name, var_sex FROM tb1 WHERE id = 2;

2、定义条件和处理程序

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

1、定义条件

基本语法格式如下：

DECLARE condition_name CONDITION FOR condition_value

condition_value:
    SQLSTATE [VALUE] sqlstate_value
  | mysql_error_code

- condition_name表示条件的名称;。
- condition_type表示条件的类型。
- salstate_value和mysql_error_code都可以表示MySQL的错误。
  - sqlstate_value为长度为5的字符串类型错误代码。
  - mysql_error_code为数值类型错误代码。
  - 例如: ERROR 1142 (42000)中，sqlstate_value的值是42000，mysql_error_code的值是1142。
这个语句指定需要特殊处理的条件。它将一个名字和指定的错误条件关联起来。这个名字可以随后被用在定义处理程序的DECLARE HANDLER语句中。

示例：

定义"ERROR 1148(42000)"错误，名称为command_not_allowed。

--方法一:使用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
 
handler_type:
    CONTINUE | EXIT | UNDO
 
condition_value:
    SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
  | mysql_error_code

handler_type为错误处理方式，有三种参数：CONTINUE、EXIT和UNDO
- CONTINUE表示遇到错误后执行处理程序语句，然后继续执行。
- EXIT遇到错误马上退出。
- UNDO表示遇到错误后撤回之前的操作，MysQL中暂时不支持这样的操作。
condition_value表示错误类型，可以有以下取值：
- SQLSTATE [VALUE] sqlstate_value：包含5个字符的字符串错误值。
- condition_name：表示DECLARE CONDITION定义的错误条件名称。
- SQLWARNING：匹配所有以01开头的SQLSTATE错误代码。
- NOT FOUND：匹配所有以02开头的SQLSTATE错误代码。
- SQLEXCEPTION：匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码。
- MySQL_error_code：匹配数值类型错误代码。
sp_statement参数为程序语句段，表示在遇到定义的错误时，需要执行的存储过程或函数。

示例：

--方法1:捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info='NO_SUCH_TABLE';
--方法2:捕获mysql_error_code
DECLARE CONTINUE HANDLER FOR 1146 SET SET @info='NO_SUCH_TABLE';
--方法3:先定义条件,然后调用
DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info='NO_SUCH_TABLE';
--方法4：使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLNARNING SET @info='ERROR';
--方法5:使用NOT EOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info='NO_SUCH_TABLE';
--方法6:使用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"信息。

3、光标的使用

查询语句可能返回多条记录，如果数据量非常大，需要在存储过程和储存函数中使用光标来逐条读取查询结果集中的记录。应用程序可以根据需要滚动或浏览其中的数据。
光标必须在声明处理程序之前被声明，并且变量和条件还必须在声明光标或处理程序之前被声明。
光标只能在存储过程和函数中使用。

1、声明光标

基本语法格式如下：

DECLARE cursor_name CURSOR FOR select_statement

- cursor_name表示光标的名称。
- select_tatement表示SELECT语句的内容，返回一个用于创建光标的结果集。

示例：

光标的名称为cur_fruit，SELECT语句部分从fruits表中查询出fname和fprice字段的值。

DECLARE cursor_fruit CURSOR FOR SELECT f_name, f_price FROM fruits;

2、打开光标

基本语法格式如下：

OPEN cursor_name        --打开先前声明的光标

示例：

OPEN cursor_fruit;      --打开名称为cursor_fruit的光标

3、使用光标

基本语法格式如下：

FETCH cursor_name INTO var_name1 [, var_name2, ...]

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

示例：

将光标cursor_fruit中SELECT语句查询出来的信息存入fruit_name和fruit_price 中，fruit_name和fruit_price必须在前面已经定义。

FETCH cursor_fruit INTO fruit_name, fruit_price;

4、关闭光标

基本语法格式如下：

CLOSE cursor_name      --关闭先前打开的光标

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

示例：

CLOSE cursor_fruit;    --关闭名称为cursor_fruit的光标

4、控制流程的使用

流程控制语句用来根据条件控制语句的执行。
MySQL中用来构造控制流程的语句有：IF语句、CASE语句、LOOP语句、LEAVE语句、ITERATE语句、REPEAT语句和WHILE语句。
每个流程中可能包含一个单独语句，或者是使用BEGIN ... END构造的复合语句，构造可以被嵌套。

1、IF语句（条件判断）

IF语句包含多个条件判断，根据判断的结果为TRUE或FALSE执行相应的语句。
基本语法格式如下：

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

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

示例：

IF n > 0 THEN 
	SELECT 'n是正数';
ELSEIF  n = 0 THEN 
	SELECT 'n是零';
ELSE 
    SELECT 'n是负数';
END IF;

2、CASE语句（条件判断）

CASE条件判断语句，该语句有2种语句格式。
第一种格式，基本语法格式如下：

CASE case_expr
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

- case_expr表示条件判断的表达式，决定了哪一个WHEN子句会被执行。
- when_value表示表达式可能的值，如果某个when_value表达式与case_expr表达式结果相同，则执行对应THEN关键字后的statement_list中的语句。
- statement_list参数表示不同when_value值的执行语句。
第二种格式，基本语法格式如下：

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

- search_condition表示条件判断语句。
- statement_list表示不同条件的执行语句。
- WHEN语句将被逐个执行，直到某个search_condition表达式为真，则执行对应THEN关键字后面的statement_listt语句。如果没有条件匹配，ELSE子句里的语句被执行。
注意：这里介绍的用在存储程序里的CASE语句与“控制流程函数”里描述的SQL CASE表达式的CASE语句有轻微不同。这里的CASE语句不能有ELSE NULL子旬,并且用ENDCASE替代END来终止。

示例1：

当val值为1时，输出字符串"val is 1"；当 val值为2时，输出字符串"val is 2" ；否则输出字符串"val is not 1 or 2"。

CASE val
    WHEN 1 THEN SELECT 'val is 1';
    WHEN 2 THEN SELECT 'val is 21;
    ELSE SELECT 'val is not 1 or 2';
END CASE;

示例2：

当val值为空输出字符串"val is NULL"；当val值小于0时，输出字符串"val is less than 0"；当val值大于0时，输出字符串"val is greater than 0" ；否则输出字符串"val is 0"

CASE
    WHEN val is NUIL THEN SELECT 'val is NULL';
    WHEN val <O THEN SELECT 'val is less than 0';
    WHEN val > 0 THEN SELECT 'val is greater than 0';
    ELSE SELECT 'val is 0';
END CASE;

3、LOOP语句（循环）

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

[loop_label:] LOOP
    statement_list
END LOOP [loop_label]

- loop_label表示LOOP语句的标注名称，该参数可以省略。
- statement_list表示需要循环执行的语句。

示例：

循环执行id加1的操作。当id值小于10时，循环重复执行;当id值大于或者等于10时，使用LEAVE语句退出循环。
LOOP循环都以END LOOP结束。

DECLARE id INT DEFAUIT 0;
add_loop: LOOP
    SET id = id +1
    IF id >= 10 THEN LEAVE add_loop;
    END IF;
END LOOP add_looр;

4、REPEAT语句（循环）

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

基本语法格式如下：

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

- repeat_label为REPEAT语句的标注名称, 该参数可以省略。
- statement_list表示需要循环执行的语句。
- REPEAT语句内的语句或语句群被重复，直至expr_condition为真。

示例：

循环执行id加1的操作。当id值小于10时，循环重复执行；当id值大于或者等于10时，退出循环。
REPEAT循环都以END REPEAT结束。

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

5、WHILE语句（循环）

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

基本语法格式如下：

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

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

示例：

i值小于10时，将重复执行循环过程

DECLARE i INT DEFAULT 0;
WHILE i< 10 DO
    SET i = i + 1;
END WHILE;

6、LEAVE语句

LEAVE语句用来退出任何被标注的流程控制构造。
LEAVE和BEGIN ... END或循环一起被使用。
基本语法格式如下：

LEAVE label

- label参数表示循环的标志。

示例：

循环执行count加1的操作。当count的值等于50时，使用LEAVE语句跳出循环。

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

7、ITERATE语句

ITERATE语句将执行顺序转到语句段开头处。
ITERATE只可以出现在LOOP、REPEAT和WHILE语句内。

基本语法格式如下：

ITERATE label

- ITERATE的意思为“再次循环"，label参数表示循环的标志。
- ITERATE语句必须跟在循环标志前面。

示例：

p1=0，如果pl的值小于10时，重复执行p1加1操作；当p1大于等于10并且小于20时，打印消息"p1 is between 10 and 20"；当p1大于20时，退出循环。

CREATE PROCEDURE doiterate ()
BEGIN
    DECLARE p1 INT DEEAULT 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 'p1 is between 10 and 20';
    END LOOP my_loop;
END

5、进阶示例

1、变量的使用

delimiter //
create procedure pro1_tb1(in id1 int, out sum int, out name1 char(15), inout num int)
    begin 
        declare p1 int;
        declare p2 int default 100;
        set p1 = 1;
        set sum = p1 + p2;
        select name into name1 from tb1 where id =id1 + num;

        select name from tb1;
    end //
delimiter ;

mysql> set @num = 4;
mysql> call pro1_tb1(1,@sum,@name,@num);
+------+
| name |
+------+
| 张三 |
| 李四 |
| 王二 |
| 麻子 |
| 翠花 |
| 小红 |
+------+
mysql> select @sum,@name,@num;
+------+-------+------+
| @sum | @name | @num |
+------+-------+------+
|  101 | 翠花  |    4 |
+------+-------+------+

2、IF语句

delimiter //
create procedure pro1_tb2(in num int, in name1 char(15), in sex1 enum('男','女'))
    begin 
        if num = 1 then
            select * from tb1 where name = name1 and sex = sex1;
        elseif num = 2 then
            select * from tb1 where name = name1 or sex = sex1;
        end if;
    end //
delimiter ;

mysql> call pro1_tb2(1,'张三','男');
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | 张三 | 男   |
+----+------+------+
mysql> call pro1_tb2(2,'张三','男');
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | 张三 | 男   |
|  2 | 李四 | 男   |
|  3 | 王二 | 男   |
|  4 | 麻子 | 男   |
+----+------+------+

#                                                                                                                      #

posted @ 2021-09-22 15:49 麦恒阅读(137) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

麦恒

mysql13-存储过程和函数

1、存储过程和函数

2、存储过程

1、创建存储过程

2、调用存储过程

3、查看存储过程

1、查看存储过程的状态

2、查看存储过程的定义语句

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

4、修改存储过程

5、删除存储过程

3、存储函数

1、创建存储函数

2、调用存储函数

3、查看存储函数

1、查看存储函数的状态

2、查看存储函数的定义语句

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

4、修改存储函数

5、删除存储函数

4、存储过程和函数进阶

1、变量的使用

1、定义变量

2、为变量赋值

3、SELECT ... INTO

2、定义条件和处理程序

1、定义条件

2、定义处理程序

3、光标的使用

1、声明光标

2、打开光标

3、使用光标

4、关闭光标

4、控制流程的使用

1、IF语句（条件判断）

2、CASE语句（条件判断）

3、LOOP语句（循环）

4、REPEAT语句（循环）

5、WHILE语句（循环）

6、LEAVE语句

7、ITERATE语句

5、进阶示例

1、变量的使用

2、IF语句

公告