MySQL 存储过程,函数,触发器

存储过程

1.分类

存储过程的参数类型可以是IN、OUT和INOUT 
--注意：IN、OUT、INOUT 都可以在一个存储过程中带多个
1、没有参数（无参数无返回） 
2、仅仅带 IN 类型（有参数无返回） 
3、仅仅带 OUT 类型（无参数有返回） 
4、既带 IN 又带 OUT（有参数有返回） 
5、带 INOUT（有参数有返回）

2.创建存储过程

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...] 
BEGIN
    存储过程体
END

说明：

1.参数前面的符号的意思
IN ：当前参数为输入参数，也就是表示入参；
    存储过程只是读取这个参数的值。如果没有定义参数种类， 默认就是 IN ，表示输入参数。
OUT ：当前参数为输出参数，也就是表示出参；
    执行完成之后，调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了。
INOUT ：当前参数既可以为输入参数，也可以为输出参数。

2、形参类型可以是 MySQL数据库中的任意类型。
3. characteristics 表示创建存储过程时指定的对存储过程的约束条件，其取值信息如下：
-----------------------------------------------------------------------------------------
LANGUAGE SQL 
| [NOT] DETERMINISTIC 
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } 
| SQL SECURITY { DEFINER | INVOKER } 
| COMMENT 'string'
-----------------------------------------------------------------------------------------
1).LANGUAGE SQL ： 说明存储过程执行体是由SQL语句组成的，当前系统支持的语言为SQL。
2).[NOT] DETERMINISTIC ：指明存储过程执行的结果是否确定。
    --DETERMINISTIC 表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。
    --NOT DETERMINISTIC 表示结果是不确定的，相同的输入可能得到不同的输出。
    --如果没有指定任意一个值，默认为NOT DETERMINISTIC。
3).{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } ：指明子程序使用SQL语句的限制。
    --CONTAINS SQL表示当前存储过程的子程序包含SQL语句，但是并不包含读写数据的SQL语句；
    --NO SQL表示当前存储过程的子程序中不包含任何SQL语句；
    --READS SQL DATA表示当前存储过程的子程序中包含读数据的SQL语句；
    --MODIFIES SQL DATA表示当前存储过程的子程序中包含写数据的SQL语句。
    --默认情况下，系统会指定为CONTAINS SQL。
4).SQL SECURITY { DEFINER | INVOKER } ：执行当前存储过程的权限，即指明哪些用户能够执行当前存储过程。
    --DEFINER 表示只有当前存储过程的创建者或者定义者才能执行当前存储过程；
    --INVOKER 表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。
    --如果没有设置相关的值，则MySQL默认指定值为DEFINER。
    --COMMENT 'string' ：注释信息，可以用来描述存储过程。

4.存储过程体中可以有多条 SQL 语句，如果仅仅一条SQL 语句，则可以省略 BEGIN 和 END
1). BEGIN…END：BEGIN…END 中间包含了多个语句，每个语句都以（;）号为结束符。
2). DECLARE：DECLARE 用来声明变量，使用的位置在于 BEGIN…END 语句中间，而且需要在其他语句使用之前进 行变量的    声明。 
3). SET：赋值语句，用于对变量进行赋值。 
4). SELECT… INTO：把从数据表中查询的结果存放到变量中，也就是为变量赋值。
5、需要设置新的结束标记
    DELIMITER 新的结束标记
    因为MySQL默认的语句结束符号为分号‘;’。为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER改变存储过程的结束符。
    --比如：“DELIMITER //”语句的作用是将MySQL的结束符设置为//，并以“END //”结束存储过程。存储过程定
    --义完毕之后再使用“DELIMITER ;”恢复默认结束符。
--示例1
DELIMITER $ 
CREATE PROCEDURE select_all_data() 
BEGIN
    SELECT * FROM emps; 
END $ 
DELIMITER ;

--示例2
DELIMITER // 
CREATE PROCEDURE avg_employee_salary () 
BEGIN
    SELECT AVG(salary) AS avg_salary FROM emps; 
END // 
DELIMITER ;

--示例3
CREATE PROCEDURE show_max_salary()
    LANGUAGE SQL 
    NOT DETERMINISTIC 
    CONTAINS SQL 
    SQL SECURITY DEFINER 
    COMMENT '查看最高薪资' 
BEGIN
    SELECT MAX(salary) FROM emps; 
END // 
DELIMITER ;

--举例4：
DELIMITER // 
CREATE PROCEDURE show_min_salary(OUT ms DOUBLE) 
    BEGIN
        SELECT MIN(salary) INTO ms FROM emps; 
    END // 
DELIMITER ;

--举例5：
DELIMITER // 
CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20)) 
    BEGIN
        SELECT salary FROM emps WHERE ename = empname; 
    END // 
DELIMITER ;

--举例6：
DELIMITER // 
CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(20),OUT empsalary DOUBLE) 
    BEGIN
        SELECT salary INTO empsalary FROM emps WHERE ename = empname; 
    END // 
DELIMITER ;

3.调用存储过程

CALL 存储过程名(实参列表);
1、调用in模式的参数：
    CALL sp1('值');
2、调用out模式的参数：
    SET @name; 
    CALL sp1(@name); 
    SELECT @name;
3、调用inout模式的参数：
    SET @name=值; 
    CALL sp1(@name); 
    SELECT @name;

存储函数

1.语法格式：

CREATE FUNCTION 函数名(参数名 参数类型,...)
RETURNS 返回值类型 [characteristics ...] 
BEGIN
    函数体 --函数体中肯定有 RETURN 语句 
END

参数说明:
1、参数列表：FUNCTION中总是默认为IN参数。
2、RETURNS type 语句表示函数返回数据的类型；(函数体必须包含一个 RETURN value 语句。)
3、characteristic 创建函数时指定的对函数的约束
4、函数体也可以用BEGIN…END来表示SQL代码的开始和结束。如果函数体只有一条语句，可以省略BEGIN…END。

2调用存储函数

SELECT 函数名(实参列表)

示例1:

DELIMITER // 
CREATE FUNCTION email_by_name() 
RETURNS VARCHAR(25) 
DETERMINISTIC 
CONTAINS SQL 
BEGIN
    RETURN (SELECT email FROM employees WHERE last_name = 'Abel'); 
END // 
DELIMITER ;
--调用：
SELECT email_by_name();

举例2:

DELIMITER // 
CREATE FUNCTION email_by_id(emp_id INT) 
RETURNS VARCHAR(25) 
DETERMINISTIC 
CONTAINS SQL 
    BEGIN
        RETURN (SELECT email FROM employees WHERE employee_id = emp_id); 
    END // 
DELIMITER ;
--调用方式1：
SET @emp_id = 102; 
SELECT email_by_id(102);--直接传值就行
--调用方式2:
SET @emp_id = 102; 
SELECT email_by_id(@emp_id);

错误处理

若在创建存储函数中报错“ you might want to use the less safe log_bin_trust_function_creators variable ”，有两种处理方法：

mysql> SET GLOBAL log_bin_trust_function_creators = 1;

对比存储函数和存储过程

	关键字	调用语法	返回值	应用场景
存储过程	PROCEDURE	CALL 存储过程()	理解为有0个或多个	一般用于更新
存储函数	FUNCTION	SELECT 函数()	只能是一个	一般用于查询结果为一个值并返回时

1.存储函数可以放在查询语句中使用，存储过程不行。
2.存储过程能够执行对表的操作（比如创建表，删除表等）和事务操作，这些功能是存储函数不具备的。

3.查看存储过程

--从information_schema.Routines表中查看存储过程和函数的信息
SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='存储过程或函数的名' [AND ROUTINE_TYPE = {'PROCEDURE|FUNCTION'}];

--示例
SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='count_by_id' AND ROUTINE_TYPE = 'FUNCTION' \G

变量

1. 系统变量

1.查看系统变量

1.1查看所有系统变量

--查看所有全局变量 
SHOW GLOBAL VARIABLES; 
--查看所有会话变量 
SHOW SESSION VARIABLES; 
或
SHOW VARIABLES;

1.2查看部分系统变量

--查看满足条件的部分系统变量。
SHOW GLOBAL VARIABLES LIKE '%标识符%'; 
--查看满足条件的部分会话变量 
SHOW SESSION VARIABLES LIKE '%标识符%';

2.查看指定系统变量

MySQL 中的系统变量以 两个“@” 开头，
“@@global” 仅用于标记全局系统变量，
“@@session”仅用于标记会话系统变量。
“@@”首先标记会话系统变量，如果会话系统变量不存在，则标记全局系统变量。

--查看指定的系统变量的值 
SELECT @@global.变量名; 
--查看指定的会话变量的值 
SELECT @@session.变量名; 
或者 
SELECT @@变量名;

3.修改系统变量的值

--为某个系统变量赋值 
--方式1： 
    SET @@global.变量名=变量值; 
--方式2： 
    SET GLOBAL 变量名=变量值; 

--为某个会话变量赋值 
--方式1： 
    SET @@session.变量名=变量值; 
--方式2： 
    SET SESSION 变量名=变量值;

2.用户变量

2.1用户变量分类

MySQL 中的用户变量以 一个“@” 开头。
--分为 会话用户变量 和 局部变量
--会话用户变量：
    作用域和会话变量一样，只对 当前连接 会话有效。
--局部变量：(只能在 存储过程和函数 中使用)
    只在 BEGIN 和 END 语句块中有效。局部变量只能在 存储过程和函数 中使用。

2.2 会话用户变量

2.2.1用户变量的定义

--
#方式1：“=”或“:=” 
SET @用户变量 = 值; 
SET @用户变量 := 值; 
#方式2：“:=” 或 INTO关键字 
SELECT @用户变量 := 表达式 [FROM 等子句]; 
SELECT 表达式 INTO @用户变量 [FROM 等子句];

2.2.2查看用户变量的值

SELECT @用户变量
--查看某个未声明的变量时，将得到NULL值

--示例
SELECT @num := COUNT(*) FROM employees; SELECT @num;

3.局部变量

定义：可以使用 `DECLARE` 语句定义一个局部变量
作用域：仅仅在定义它的 `BEGIN ... END` 中有效
位置：只能放在 `BEGIN ... END` 中，#而且只能放在第一句

#示例:
BEGIN
    #声明局部变量 
        DECLARE 变量名1 变量数据类型 [DEFAULT 变量默认值]; 
        DECLARE 变量名2,变量名3,... 变量数据类型 [DEFAULT 变量默认值]; 
    #为局部变量赋值 
        SET 变量名1 = 值; 
        SELECT 值 INTO 变量名2 [FROM 子句]; 
    #查看局部变量的值 
        SELECT 变量1,变量2,变量3; 
END

3.1定义变量

DECLARE 变量名 类型 [default 值]; --如果没有DEFAULT子句，初始值为NULL
--举例
DECLARE myparam INT DEFAULT 100;

3.2变量赋值

--方式1：一般用于赋简单的值
SET 变量名=值;
SET 变量名:=值;
--方式2：一般用于赋表中的字段值
SELECT 字段名或表达式 INTO 变量名 FROM 表;

3.3.使用变量（查看、比较、运算等）

SELECT 局部变量名;

3.4示例

--声明 
DELIMITER // 
CREATE PROCEDURE different_salary(IN emp_id INT,OUT dif_salary DOUBLE) 
BEGIN
    --声明局部变量 
    DECLARE emp_sal,mgr_sal DOUBLE DEFAULT 0.0; 
    DECLARE mgr_id INT; 

    SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id; 
    SELECT manager_id INTO mgr_id FROM employees WHERE employee_id = emp_id; 
    SELECT salary INTO mgr_sal FROM employees WHERE employee_id = mgr_id;
    SET dif_salary = mgr_sal - emp_sal;
END // 
DELIMITER ;

--调用 
SET @emp_id = 102; 
CALL different_salary(@emp_id,@diff_sal);

--查看 
SELECT @diff_sal;

定义条件与处理程序

1.定义错误码

--语法
DECLARE 错误名称 CONDITION FOR 错误码（或错误条件）
--错误码说明
MySQL_error_code 和 sqlstate_value 都可以表示MySQL的错误。
1) MySQL_error_code是数值类型错误代码。
2) sqlstate_value是长度为5的字符串类型错误代码。
--示例
#使用MySQL_error_code 
    DECLARE Field_Not_Be_NULL CONDITION FOR 1048; 
#使用sqlstate_value 
    DECLARE Field_Not_Be_NULL CONDITION FOR SQLSTATE '23000';

2.定义处理程序

--语法
DECLARE 处理方式 HANDLER FOR 错误类型 处理语句
--参数值说明
处理方式：处理方式有3个取值：CONTINUE、EXIT、UNDO。 
    'CONTINUE' ：表示遇到错误不处理，继续执行。
    'EXIT' ：    表示遇到错误马上退出。
    'UNDO' ：    表示遇到错误后撤回之前的操作。MySQL中暂时不支持这样的操作。
错误类型（即条件）可以有如下取值：
    'SQLSTATE '字符串错误码' '：表示长度为5的sqlstate_value类型的错误代码； MySQL_error_code ：匹配数值     类型错误代码；
    '错误名称' ：表示DECLARE ... CONDITION定义的错误条件名称。
    'SQLWARNING' ：匹配所有以01开头的SQLSTATE错误代码；
    'NOT FOUND' ：匹配所有以02开头的SQLSTATE错误代码；
    'SQLEXCEPTION' ：匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码；

处理语句：
    如果出现上述条件之一，则采用对应的处理方式，并执行指定的处理语句。语句可以是像“ SET 变量 = 值 ”这样的        简单语句，也可以是使用 BEGIN ... END 编写的复合语句。
--示例:
--方法1：捕获sqlstate_value 
    DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info = 'NO_SUCH_TABLE'; 
--方法2：捕获mysql_error_value 
    DECLARE CONTINUE HANDLER FOR 1146 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 SQLWARNING SET @info = 'ERROR'; 
--方法5：使用NOT FOUND 
    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值为23000时，执行EXIT操
作，并且将@proc_value的值设置为-1。

DELIMITER // 
    CREATE PROCEDURE InsertDataWithCondition() 
    BEGIN   
        --定义错误码和处理程序是一起用的
        DECLARE duplicate_entry CONDITION FOR SQLSTATE '23000' ; 
        DECLARE EXIT HANDLER FOR duplicate_entry SET @proc_value = -1; 
    SET @x = 1; 
        INSERT INTO departments(department_name) VALUES('测试'); 
    SET @x = 2; 
        INSERT INTO departments(department_name) VALUES('测试'); 
    SET @x = 3; 
END // 
DELIMITER ;

--调用存储过程：
mysql> CALL InsertDataWithCondition();
mysql> SELECT @x,@proc_value;

流程控制

--MySQL 的流程控制语句主要有 3 类。注意：只能用于存储程序。
'条件判断语句' ：IF 语句和 CASE 语句
'循环语句' ：LOOP、WHILE 和 REPEAT 语句
'跳转语句' ：ITERATE 和 LEAVE 语句

3.1 分支结构之 IF

IF 表达式1 THEN 操作1 
[ELSEIF 表达式2 THEN 操作2]…… 
[ELSE 操作N] 
END IF

举例

声明存储过程“update_salary_by_eid1”，定义IN参数emp_id，输入员工编号。判断该员工
薪资如果低于8000元并且入职时间超过5年，就涨薪500元；否则就不变。

DELIMITER // 
    CREATE PROCEDURE update_salary_by_eid1(IN emp_id INT) 
    BEGIN
    DECLARE emp_salary DOUBLE; 
    DECLARE hire_year DOUBLE; 
    SELECT salary INTO emp_salary FROM employees WHERE employee_id = emp_id; 
    SELECT DATEDIFF(CURDATE(),hire_date)/365 INTO hire_year FROM employees WHERE employee_id =     emp_id; 
    IF emp_salary < 8000 AND hire_year > 5 
    THEN UPDATE employees SET salary = salary + 500 WHERE employee_id = emp_id; 
    END IF; 
END //
DELIMITER ;

3.2分支结构之 CASE

CASE 语句的语法结构1：

#情况一：类似于switch 
CASE 表达式 
WHEN 值1 THEN 结果1或语句1(如果是语句，需要加分号) 
WHEN 值2 THEN 结果2或语句2(如果是语句，需要加分号) ... 
ELSE 结果n或语句n(如果是语句，需要加分号) 
END [CASE]（如果是放在begin end中需要加上case，如果放在select后面不需要）

CASE 语句的语法结构2：

#情况二：类似于多重if 
CASE 
WHEN 条件1 THEN 结果1或语句1(如果是语句，需要加分号) 
WHEN 条件2 THEN 结果2或语句2(如果是语句，需要加分号) ... 
ELSE 结果n或语句n(如果是语句，需要加分号) 
END [CASE]（如果是放在begin end中需要加上case，如果放在select后面不需要）

--示例
声明存储过程“update_salary_by_eid4”，定义IN参数emp_id，输入员工编号。判断该员工
薪资如果低于9000元，就更新薪资为9000元；薪资大于等于9000元且低于10000的，但是奖金比例
为NULL的，就更新奖金比例为0.01；其他的涨薪100元。

DELIMITER //
CREATE PROCEDURE update_salary_by_eid4(IN emp_id INT) 
BEGIN
DECLARE emp_sal DOUBLE; 
DECLARE bonus DECIMAL(3,2); 
SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id; 
SELECT commission_pct INTO bonus FROM employees WHERE employee_id = emp_id; 
CASE
    WHEN emp_sal<9000 
        THEN UPDATE employees SET salary=9000 WHERE employee_id = emp_id; 
    WHEN emp_sal<10000 AND bonus IS NULL 
        THEN UPDATE employees SET commission_pct=0.01 WHERE employee_id = emp_id; 
    ELSE 
        UPDATE employees SET salary=salary+100 WHERE employee_id = emp_id; 
    END CASE; 
END // 
DELIMITER ;

3.3循环结构之LOOP

LOOP内的语句一直重复执行直到循环被退出（使用LEAVE子句），跳出循环过程。
--LOOP语句的基本格式:
[loop_label:] LOOP 
    循环执行的语句 
END LOOP [loop_label]
--其中，loop_label表示LOOP语句的标注名称，该参数可以省略

举例1:

--使用LOOP语句进行循环操作，id值小于10时将重复执行循环过程。
DECLARE id INT DEFAULT 0; 
add_loop:LOOP 
    SET id = id +1; 
    IF id >= 10 THEN LEAVE add_loop; 
    END IF; 
END LOOP add_loop;

--示例:
公司为了奖励大家，决定给大家涨工资。声明存储过程“update_salary_loop()”，声明OUT参数num，输出循环次数。存储过程中实现循环给大家涨薪，薪资涨为原来的1.1倍。直到全公司的平均薪资达到12000结束。并统计循环次数。

DELIMITER // 
    CREATE PROCEDURE update_salary_loop(OUT num INT) 
    BEGIN
        DECLARE avg_salary DOUBLE; 
        DECLARE loop_count INT DEFAULT 0; 
        SELECT AVG(salary) INTO avg_salary FROM employees; 

        label_loop:LOOP 
            IF avg_salary >= 12000 THEN LEAVE label_loop; 
            END IF; 

            UPDATE employees SET salary = salary * 1.1; 
            SET loop_count = loop_count + 1; 
            SELECT AVG(salary) INTO avg_salary FROM employees; 
    END LOOP label_loop; 
        SET num = loop_count; 
    END // 
DELIMITER ;

3.4循环结构之WHILE

WHILE在执行语句执行时，先对指定的表达式进行判断，如果为真，就执行循环内的语句，否则退出循环。
--WHILE语句的基本格式如下：
[while_label:] WHILE 循环条件 DO 
    循环体 
END WHILE [while_label];
--while_label为WHILE语句的标注名称；如果循环条件结果为真，WHILE语句内的语句或语句群被执行，直
--至循环条件为假，退出循环。

示例:

市场环境不好时，公司为了渡过难关，决定暂时降低大家的薪资。声明存储过程
“update_salary_while()”，声明OUT参数num，输出循环次数。存储过程中实现循环给大家降薪，薪资降
为原来的90%。直到全公司的平均薪资达到5000结束。并统计循环次数。

DELIMITER // 
    CREATE PROCEDURE update_salary_while(OUT num INT) 
    BEGIN
        DECLARE avg_sal DOUBLE ; 
        DECLARE while_count INT DEFAULT 0;
    SELECT AVG(salary) INTO avg_sal FROM employees; 
        WHILE avg_sal > 5000 DO UPDATE employees 
        SET salary = salary * 0.9; 
        SET while_count = while_count + 1; 
        SELECT AVG(salary) INTO avg_sal FROM employees; 
    END WHILE; 
        SET num = while_count;
    END // 
DELIMITER ;

3.5REPEAT (带条件判断的循环过程)

REPEAT 循环首先会执行一次循
环，然后在 UNTIL 中进行表达式的判断，如果满足条件就退出，即 END REPEAT；如果条件不满足，则会
就继续执行循环，直到满足退出条件为止。
--语法
[repeat_label:] REPEAT 
    循环体的语句 
UNTIL 结束循环的条件表达式 
END REPEAT [repeat_label]

示例:

当市场环境变好时，公司为了奖励大家，决定给大家涨工资。声明存储过程
“update_salary_repeat()”，声明OUT参数num，输出循环次数。存储过程中实现循环给大家涨薪，薪资涨
为原来的1.15倍。直到全公司的平均薪资达到13000结束。并统计循环次数。

DELIMITER // 
CREATE PROCEDURE update_salary_repeat(OUT num INT) 
    BEGIN
        DECLARE avg_sal DOUBLE ; 
        DECLARE repeat_count INT DEFAULT 0; 

        SELECT AVG(salary) INTO avg_sal FROM employees; 

        REPEAT
            UPDATE employees SET salary = salary * 1.15; 
            SET repeat_count = repeat_count + 1; 
            SELECT AVG(salary) INTO avg_sal FROM employees; 
            UNTIL avg_sal >= 13000 
        END REPEAT; 
        SET num = repeat_count; 
    END // 
DELIMITER ;

3.6循环对比

 LOOP：一般用于实现简单的"死"循环 
 WHILE：先判断后执行 
 REPEAT：先执行后判断，无条件至少执行一次

3.7跳转语句之LEAVE语句

可以用在循环语句内，
或者以 BEGIN 和 END 包裹起来的程序体内，
表示跳出循环或者跳出程序体的操作。
如果你有面向过程的编程语言的使用经验，
你可以把 LEAVE 理解为 break。
--语法格式
LEAVE 标记名
--示例

DELIMITER // 
CREATE PROCEDURE leave_while(OUT num INT) 
    BEGIN
        DECLARE avg_sal DOUBLE;--记录平均工资
        DECLARE while_count INT DEFAULT 0; --记录循环次数
        SELECT AVG(salary) INTO avg_sal FROM employees; --① 初始化条件
        while_label:WHILE TRUE DO --② 循环条件

        --③ 循环体 
        IF avg_sal <= 10000 THEN 
            LEAVE while_label; 
        END IF; 

        UPDATE employees SET salary = salary * 0.9; 
        SET while_count = while_count + 1;

        --④ 迭代条件 
        SELECT AVG(salary) INTO avg_sal FROM employees;

    END WHILE;

    --赋值 
    SET num = while_count;

    END // 
DELIMITER ;

3.8跳转语句之ITERATE语句

ITERATE语句：只能用在循环语句（LOOP、REPEAT和WHILE语句）内，表示重新开始循环，将执行顺序转到语句段开头处。
可以把 ITERATE 理解为 'continue'，意思为“再次循环”。
--语法
ITERATE label
--示例
定义局部变量num，初始值为0。循环结构中执行num + 1操作。
如果num < 10，则继续执行循环；
如果num > 15，则退出循环结构；
DELIMITER // 
    CREATE PROCEDURE test_iterate() 
BEGIN
    DECLARE num INT DEFAULT 0;
    my_loop:LOOP 
        SET num = num + 1;
        IF num < 10 
            THEN ITERATE my_loop; 
        ELSEIF num > 15
            THEN LEAVE my_loop; 
        END IF; 
        SELECT 'xxxxxxxxxxx'; 
    END LOOP my_loop; 
END // 
DELIMITER ;

游标

1.使用游标步骤

游标必须在'声明处理程序之前被声明'，
并且'变量和条件'还必须在声明游标或处理程序之前被声明。
#1.第一步，声明游标  使用DECLARE关键字来声明游标(MySQL，SQL Server，DB2 和 MariaDB。)
DECLARE cursor_name CURSOR FOR select语句;
#如果是用 Oracle 或者 PostgreSQL，需要写成
DECLARE cursor_name CURSOR IS select语句;

#2.第二步,打开游标
#语法
OPEN cursor_name
打开游标的时候 SELECT 语句的查询结果集就会送到游标工作区，为后面游标的 逐条读取 结果集中的记录做准备。

#3.第三步，使用游标（从游标中取得数据）
#语法
FETCH cursor_name INTO var_name [, var_name] ... #var_name 字段名
#示例:
FETCH cur_emp INTO emp_id, emp_sal ;

#4.关闭游标
CLOSE cursor_name

示例:

--声明游标
DECLARE cur_emp CURSOR FOR SELECT employee_id,salary FROM employees;
--打开游标
OPEN cur_emp
--使用游标
FETCH cur_emp INTO emp_id, emp_sal ;
--关闭游标
CLOSE cur_emp

游标举例

创建存储过程“get_count_by_limit_total_salary()”，声明IN参数 limit_total_salary，DOUBLE类型；声明
OUT参数total_count，INT类型。函数的功能可以实现累加薪资最高的几个员工的薪资值，直到薪资总和
达到limit_total_salary参数的值，返回累加的人数给total_count。

DELIMITER //

CREATE PROCEDURE get_count_by_limit_total_salary(IN limit_total_salary DOUBLE,OUT total_count INT)

BEGIN
    DECLARE sum_salary DOUBLE DEFAULT 0; #记录累加的总工资 
    DECLARE cursor_salary DOUBLE DEFAULT 0; #记录某一个工资值 
    DECLARE emp_count INT DEFAULT 0; #记录循环个数 #定义游标 
    DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC; 
    #打开游标 
    OPEN emp_cursor;
    REPEAT
        #使用游标（从游标中获取数据） 
        FETCH emp_cursor INTO cursor_salary; 
        SET sum_salary = sum_salary + cursor_salary; 
        SET emp_count = emp_count + 1; 
        UNTIL sum_salary >= limit_total_salary
    END REPEAT; 
    SET total_count = emp_count; 
    #关闭游标 
    CLOSE emp_cursor; 
END // 
DELIMITER ;

MySQL 8.0的新特性—全局变量的持久化

--SET GLOBAL 临时生效
--SET PERSIST 永久生效

--1.设置服务器语句超时的限制
SET GLOBAL MAX_EXECUTION_TIME=2000;

--设置服务器的最大连接数为1000：
SET PERSIST global max_connections = 1000;
--MySQL会将该命令的配置保存到数据目录下的 mysqld-auto.cnf 文件中，
--下次启动时会读取该文件，用其中的配置来覆盖默认的配置文件。

触发器

1.创建触发器语法

CREATE TRIGGER 触发器名称 
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名 
FOR EACH ROW 
触发器执行的语句块;

--说明
1.表名 ：表示触发器监控的对象。
2.BEFORE|AFTER ：表示触发的时间。BEFORE 表示在事件之前触发；AFTER 表示在事件之后触发。
3.INSERT|UPDATE|DELETE ：表示触发的事件。
    INSERT 表示插入记录时触发；
    UPDATE 表示更新记录时触发；
    DELETE 表示删除记录时触发。
4.触发器执行的语句块 ：可以是单条SQL语句，也可以是由BEGIN…END结构组成的复合语句块。

示例:

创建触发器：创建名称为before_insert的触发器，向test_trigger数据表插入数据之前，向
test_trigger_log数据表中插入before_insert的日志信息。
--创建触发器
DELIMITER // 
    CREATE TRIGGER before_insert 
    BEFORE INSERT ON test_trigger 
    FOR EACH ROW 
    BEGIN
        INSERT INTO test_trigger_log (t_log) VALUES('before_insert'); 
    END // 
DELIMITER ;

--测试  向test_trigger数据表中插入数据
 INSERT INTO test_trigger (t_note) VALUES ('测试 BEFORE INSERT 触发器');

--查看test_trigger_log数据表中的数据  在执行一句sql的同事,同时执行另外一句sql
mysql> SELECT * FROM test_trigger_log;

2.查看、删除触发器

#1.查看当前数据库的所有触发器的定义
SHOW TRIGGERS\G;
#2.查看当前数据库中某个触发器的定义
SHOW CREATE TRIGGER 触发器名
#3.从系统库information_schema的TRIGGERS表中查询“salary_check_trigger”触发器的信息。
SELECT * FROM information_schema.TRIGGERS;

3.删除触发器

--语法如下：
DROP TRIGGER IF EXISTS 触发器名称;