MySQL之存储过程实战

最新推荐文章于 2024-05-17 13:58:29 发布

时空无限

最新推荐文章于 2024-05-17 13:58:29 发布

阅读量626

点赞数

分类专栏：数据库mysql 文章标签： mysql

本文链接：https://blog.csdn.net/weixin_40548182/article/details/109714649

版权

数据库mysql 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

存储过程概述

存储过程是将SQL语句放到一个集合里，然后直接调用存储过程来执行已经定义好的SQL语句集合，这样可以避免开发人员重复编写相同的SQL语句。另外，存储过程还可以减少数据在数据库和应用服务器之间的传输，可以提高数据的处理效率。

存储过程的优缺点

优点
1、允许标准组件式编程，提高了SQL语句的重用性、共享性和可移植性。
2、能够实现较快的执行速度，节省网络流量。
3、可以作为一种安全机制来使用。
缺点
1、编写存储过程比编写单个SQL语句复杂，需要用户具有丰富的经验。
2、编写存储过程需时需要创建这些数据库对象的权限。

存储过程具体操作

数据准备

CREATE TABLE users(id INT PRIMARY KEY,name VARCHAR(50),age INT, email VARCHAR(50));

INSERT INTO users(id,name,age,email) VALUES (1,'zs',22,'zs@qq.com'),
    -> (2,'ls',25,'ls@qq.com'),
    -> (3,'ww',28,'ww@qq.com');

SELECT * FROM users;
+----+------+------+-----------+
| id | name | age  | email     |
+----+------+------+-----------+
|  1 | zs   |   22 | zs@qq.com |
|  2 | ls   |   25 | ls@qq.com |
|  3 | ww   |   28 | ww@qq.com |
+----+------+------+-----------+
3 rows in set (0.00 sec)

CREATE TABLE stu(stu_id INT NOT NULL,stu_name CHAR(10) NOT NULL,stu_class INT NOT NULL,stu_sex CHAR(2) NOT NULL,stu_age INT NOT NULL,PRIMARY KEY (stu_id));

INSERT INTO stu VALUES (1,'aa',3,'女',23),(2,'bb',1,'男',12),(3,'cc',30,'女',11),(4,'dd',2,'男',22),(5,'ee',1,'女',23),(6,'ff',2,'女',13),(7,'gg',3,'男',10),(8,'hh',2,'女',11),(9,'ii',1,'男',13),(10,'jj',3,'女',27);

CREATE TABLE stu_score(stu_id INT NOT NULL,stu_score INT NOT NULL,FOREIGN KEY (stu_id) REFERENCES stu(stu_id));

INSERT INTO stu_score VALUES (1,91),(2,62),(3,18),(4,95),(5,71),(6,82),(7,60),(8,52),(9,99),(10,46);

创建存储过程

查看手否有创建存储过程的权限

SELECT create_routine_priv FROM mysql.user WHERE user='root';
+---------------------+
| create_routine_priv |
+---------------------+
| Y                   |
| Y                   |
| Y                   |
| Y                   |
+---------------------+
4 rows in set (0.01 sec)

语法

CREATE PROCEDURE sp_name([proc_parameter[...]]) [characteristic...] routine_body;

CREATE PROCEDURE：表示创建存储过程的关键字。
sp_name：表示存储过程的名称。
proc_parameter:表示存储过程的参数列表。
characteristic:用于指定存储过程的特性。
routine_body:表示存储过程的主题部分，包含了在过程调用时必须执行的SQL语句。它以BEGIN开始，以END结束。如果在存储过程体中只有一条SQL语句，可以省略BEGIN-END标志。

以上是创建存储过程的语法格式，proc_parameter为指定存储过程的参数列表，该参数列表的形式如下。

[IN|OUT|INOUT] param_name type

另外在创建存储过程的格式中chracteristic有5个可选值，具体如下。

COMMENT 'string'：用于对存储过程的描述，其中string为描述内容，COMMENT为关键字。
LANGUAGE SQL:用于指明编写存储过程的语言为SQL语言。
DETERMINISTIC：表示存储过程对同样的输入参数产生相同的结果。NOT DETERMINISTIC 表示会产生不确定的结果（默认）。
{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}:指明使用SQL语句的限制。CONTAINS SQL表示子程序不包含读或写数据的语句。NO SQL表示子程序不包含SQL语句。REDAS SQL DATE表示子程序包含读数据的语句，但不包含写数据的语句。MODIFIES SQL DATA表示子程序包含写数据的语句。如果这些特征没有明确给定，默认为CONTAINS SQL。
SQL SECURITY {DEFINER|INVOKER}:指定有权限执行存储过程的用户，其中DEFINER代表定义者，INVOKER代表调用者，默认为DEFINER。

创建一个带IN的存储过程，用于通过传入用户查询users表中的用户信息。
修改mysql的命令结束符

DELIMITER //

创建存储过程

CREATE PROCEDURE SP_SEARCH(IN p_name CHAR(20)) BEGIN IF p_name IS NULL OR p_name='' THEN SELECT * FROM users;ELSE SELECT * FROM users WHERE name LIKE p_name;END IF;END//

恢复mysql的命令结束符

DELIMITER ;

用CALL调用存储过程

CALL SP_SEARCH('zs');
+----+------+------+-----------+
| id | name | age  | email     |
+----+------+------+-----------+
|  1 | zs   |   22 | zs@qq.com |
+----+------+------+-----------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

接着创建一个OUT的存储过程，用于通过传入用户年龄查询users表中大于该年龄的用户信息，并且输出查询到的用户个数。

CREATE PROCEDURE SP_SEARCHE2 (IN p_age INT,OUT p_int INT) BEGIN IF p_age IS NULL OR p_age=' ' THEN SELECT * FROM users;ELSE SELECT * FROM users WHERE age>p_age;END IF;SELECT FOUND_ROWS() INTO p_int;END //

DELIMITER ;

CALL SP_SEARCHE2(22,@p_num);
+----+------+------+-----------+
| id | name | age  | email     |
+----+------+------+-----------+
|  2 | ls   |   25 | ls@qq.com |
|  3 | ww   |   28 | ww@qq.com |
+----+------+------+-----------+
2 rows in set (0.01 sec)

Query OK, 1 row affected (0.01 sec)

通过CALL关键字调用了存储过程SP_SEARCHE2并传入参数22，执行存储过程后成功查询到users表中年龄大于22的用户的信息。通过查询@p_num可以得到存储过程执行后的输出内容，即用户的个数。

SELECT @p_num;
+--------+
| @p_num |
+--------+
|      2 |
+--------+
1 row in set (0.01 sec)

创建一个INOUT的存储过程，用于将输入的参数乘以10后输出。

CREATE PROCEDURE SP_INOUT (INOUT p_num INT) BEGIN SET p_num=p_num*10;END //

调用存储过程

恢复默认结束符
DELIMITER ;
设置变量并赋值
SET @p_num2=5;
调用存储过程
CALL SP_INOUT(@P_NUM2);

SELECT @p_num2;
+---------+
| @p_num2 |
+---------+
|      50 |
+---------+
1 row in set (0.00 sec)

查看存储过程

1、使用SHOW PROCEDURE STATUS语句查看存储过程

SHOW PROCEDURE STATUS LIKE 'S%';
+---------+-------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db      | Name        | Type      | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+---------+-------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| gws_cfg | SP_INOUT    | PROCEDURE | root@localhost | 2020-11-16 11:22:59 | 2020-11-16 11:22:59 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
| gws_cfg | SP_SEARCH   | PROCEDURE | root@localhost | 2020-11-16 10:49:00 | 2020-11-16 10:49:00 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
| gws_cfg | SP_SEARCHE2 | PROCEDURE | root@localhost | 2020-11-16 11:12:13 | 2020-11-16 11:12:13 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
+---------+-------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
3 rows in set (0.00 sec)


SHOW PROCEDURE STATUS LIKE 'S%'\G;
*************************** 1. row ***************************
                  Db: gws_cfg
                Name: SP_INOUT
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2020-11-16 11:22:59
             Created: 2020-11-16 11:22:59
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
*************************** 2. row ***************************
                  Db: gws_cfg
                Name: SP_SEARCH
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2020-11-16 10:49:00
             Created: 2020-11-16 10:49:00
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
*************************** 3. row ***************************
                  Db: gws_cfg
                Name: SP_SEARCHE2
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2020-11-16 11:12:13
             Created: 2020-11-16 11:12:13
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
3 rows in set (0.00 sec)

ERROR: No query specified

1、使用SHOW CREATE PROCEDURE语句查看存储过程

注意：在哪个库下创建的存储过程就use到那个库下查看。

SHOW CREATE PROCEDURE SP_SEARCH\G;
*************************** 1. row ***************************
           Procedure: SP_SEARCH
            sql_mode: 
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `SP_SEARCH`(IN p_name CHAR(20))
BEGIN IF p_name IS NULL OR p_name='' THEN SELECT * FROM users;ELSE SELECT * FROM users WHERE name LIKE p_name;END IF;END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.01 sec)

ERROR: No query specified

1、从information_schema.Routines表中查看存储过程

SELECT * FROM information_schema.Routines\G;
*************************** 1. row ***************************
           SPECIFIC_NAME: SP_INOUT
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: gws_cfg
            ROUTINE_NAME: SP_INOUT
            ROUTINE_TYPE: PROCEDURE
               DATA_TYPE: 
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
          DTD_IDENTIFIER: NULL
            ROUTINE_BODY: SQL
      ROUTINE_DEFINITION: BEGIN SET p_num=p_num*10;END
           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: NULL
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: NO
         SQL_DATA_ACCESS: CONTAINS SQL
                SQL_PATH: NULL
           SECURITY_TYPE: DEFINER
                 CREATED: 2020-11-16 11:22:59
            LAST_ALTERED: 2020-11-16 11:22:59
                SQL_MODE: 
         ROUTINE_COMMENT: 
                 DEFINER: root@localhost
    CHARACTER_SET_CLIENT: utf8
    COLLATION_CONNECTION: utf8_general_ci
      DATABASE_COLLATION: utf8_general_ci
*************************** 2. row ***************************
           SPECIFIC_NAME: SP_SEARCH
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: gws_cfg
            ROUTINE_NAME: SP_SEARCH
            ROUTINE_TYPE: PROCEDURE
               DATA_TYPE: 
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
          DTD_IDENTIFIER: NULL
            ROUTINE_BODY: SQL
      ROUTINE_DEFINITION: BEGIN IF p_name IS NULL OR p_name='' THEN SELECT * FROM users;ELSE SELECT * FROM users WHERE name LIKE p_name;END IF;END
           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: NULL
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: NO
         SQL_DATA_ACCESS: CONTAINS SQL
                SQL_PATH: NULL
           SECURITY_TYPE: DEFINER
                 CREATED: 2020-11-16 10:49:00
            LAST_ALTERED: 2020-11-16 10:49:00
                SQL_MODE: 
         ROUTINE_COMMENT: 
                 DEFINER: root@localhost
    CHARACTER_SET_CLIENT: utf8
    COLLATION_CONNECTION: utf8_general_ci
      DATABASE_COLLATION: utf8_general_ci
*************************** 3. row ***************************
           SPECIFIC_NAME: SP_SEARCHE2
         ROUTINE_CATALOG: def
          ROUTINE_SCHEMA: gws_cfg
            ROUTINE_NAME: SP_SEARCHE2
            ROUTINE_TYPE: PROCEDURE
               DATA_TYPE: 
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
          DTD_IDENTIFIER: NULL
            ROUTINE_BODY: SQL
      ROUTINE_DEFINITION: BEGIN IF p_age IS NULL OR p_age=' ' THEN SELECT * FROM users;ELSE SELECT * FROM users WHERE age>p_age;END IF;SELECT FOUND_ROWS() INTO p_int;END
           EXTERNAL_NAME: NULL
       EXTERNAL_LANGUAGE: NULL
         PARAMETER_STYLE: SQL
        IS_DETERMINISTIC: NO
         SQL_DATA_ACCESS: CONTAINS SQL
                SQL_PATH: NULL
           SECURITY_TYPE: DEFINER
                 CREATED: 2020-11-16 11:12:13
            LAST_ALTERED: 2020-11-16 11:12:13
                SQL_MODE: 
         ROUTINE_COMMENT: 
                 DEFINER: root@localhost
    CHARACTER_SET_CLIENT: utf8
    COLLATION_CONNECTION: utf8_general_ci
      DATABASE_COLLATION: utf8_general_ci
3 rows in set (0.00 sec)

ERROR: No query specified

修改存储过程

查看是否有修改存储过程的权限

SELECT alter_routine_priv FROM mysql.user WHERE user='root';
+--------------------+
| alter_routine_priv |
+--------------------+
| Y                  |
| Y                  |
| Y                  |
| Y                  |
+--------------------+
4 rows in set (0.00 sec)

语法

ALTER PROCEDURE sp_name [characteristic...]

sp_name表示存储过程的名称，characteristic表示修改存储过程的具体部分，它由6个可选值，具体如下。
CONTAINS SQL:表示子程序包含SQL语句，但不包含读或写数据的语句。
NO SQL:表示子程序不包含SQL语句。
READS SQL DATA:表示程序中包含读数据的语句。
MODIFIES SQL DATA:表示程序中包含写数据的语句。
SQL SECURITY {DEFINER|INVOKER}:指明有权限执行的用户，其中DEFINER表示只有定义者才能执行，INVOKER表示只有调用者才能执行。
COMMENT 'string'：表示注释信息。

修改存储过程SP_SEARCH的定义，将读写权限修改为MODIEIES SQL DATA，并指明只有调用者可以执行。

ALTER PROCEDURE SP_SEARCH MODIFIES SQL DATA SQL SECURITY INVOKER;

删除存储过程

语法

DROP PROCEDURE [IF EXISTS] sp_name;

DROP PROCEDURE SP_SEARCH;

SHOW PROCEDURE STATUS;
+---------+-------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db      | Name        | Type      | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+---------+-------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| gws_cfg | SP_INOUT    | PROCEDURE | root@localhost | 2020-11-16 11:22:59 | 2020-11-16 11:22:59 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
| gws_cfg | SP_SEARCHE2 | PROCEDURE | root@localhost | 2020-11-16 11:12:13 | 2020-11-16 11:12:13 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
+---------+-------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
2 rows in set (0.00 sec)

局部变量的使用

局部变量可以在子程序中声明并使用，其作用范围是在BEGIN-END程序中。在存储过程中使用局部变量需要定义局部变量，MySQL提供了DECLARE语句定义局部变量。

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

var_name为局部变量的名称。使用该语句可以定义多个局部变量，各个变量之间使用逗号隔开。DEFAULT value子句可以为局部变量提供默认值，如果没有该子句，局部变量的初始值为NULL。接下来定义一个名为tmp的局部变量，类型为VARCHAR(10)，默认值为abc，具体代码如下。

DECLARE tmp VARCHAR(10) DEFAULT 'abc';

为变量赋值

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

实战命令

DELIMITER //

CREATE PROCEDURE SP_SEARCH(IN p_name CHAR(20)) BEGIN DECLARE tmp INT;SET tmp=10;IF p_name IS NULL OR p_name='' THEN SELECT * FROM users;ELSE SELECT * FROM users WHERE name LIKE p_name;END IF;END//
Query OK, 0 rows affected (0.01 sec)

定义条件和处理程序

定义条件是事先定义程序执行过程中遇到的问题，处理程序是定义在遇到问题时应当采取的处理方式。

1、定义条件

语法

DECLARE condition_name CONDITION FOR [condition_type];

condition_type:
SQLSTATE [VALUE] sqldate_value | mysql_error_code

condition_name代表定义的条件名称，condition_type代表条件的类型，sqlstate_value和mysql_error_code都可以表示MySQL的错误，其中salstate_value是长度为5的字符串类型错误代码，mysql_error_code是数值类型的错误代码。
定义“ERROR1148（42000）”错误，名称为command_not_allowed。

DELIMITER //

CREATE PROCEDURE SP_SEARCH(IN p_name CHAR(20)) BEGIN DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000';IF p_name IS NULL OR p_name='' THEN SELECT * FROM users;ELSE SELECT * FROM users WHERE name LIKE p_name;END IF;END//

或者

CREATE PROCEDURE SP_SEARCH(IN p_name CHAR(20)) BEGIN DECLARE command_not_allowed CONDITION FOR 1148;IF p_name IS NULL OR p_name='' THEN SELECT * FROM users;ELSE SELECT * FROM users WHERE name LIKE p_name;END IF;END//

2、处理程序

定义处理程序的语法如下

DECLARE hander_type HANDLER FOR condition_value [,...] sp_statement;

handler_type:
CONTINUE|EXIT|UNDO
condition_value:
SQLDATE [VALUE] sqlstate_value|condition_name|SQLWARING|NOT FOUND|SQLEXCEPTION|mysql_error_code

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

实际演示
方法1：捕获sqlstate_value

CREATE PROCEDURE SP_SEARCH(IN p_name CHAR(20)) BEGIN DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info='NO_SUCH_TABLE';IF p_name IS NULL OR p_name='' THEN SELECT * FROM users;ELSE SELECT * FROM users WHERE name LIKE p_name;END IF;END//

方法2：捕获mysql_error_code

CREATE PROCEDURE SP_SEARCH(IN p_name CHAR(20)) BEGIN DECLARE CONTINUE HANDLER FOR 1146 SET @info='NO_SUCH_TABLE';IF p_name IS NULL OR p_name='' THEN SELECT * FROM users;ELSE SELECT * FROM users WHERE name LIKE p_name;END IF;END//

方法3：先定义条件，然后再调用

CREATE PROCEDURE SP_SEARCH(IN p_name CHAR(20)) BEGIN DECLARE no_such_table CONDITION FOR 1146;DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info='ERROR';IF p_name IS NULL OR p_name='' THEN SELECT * FROM users;ELSE SELECT * FROM users WHERE name LIKE p_name;END IF;END//

方法4：使用SQLWARING

CREATE PROCEDURE SP_SEARCH(IN p_name CHAR(20)) BEGIN DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';IF p_name IS NULL OR p_name='' THEN SELECT * FROM users;ELSE SELECT * FROM users WHERE name LIKE p_name;END IF;END//

方法5：使用NOT FOUND

CREATE PROCEDURE SP_SEARCH(IN p_name CHAR(20)) BEGIN DECLARE EXIT HANDLER FOR NOT FOUND SET @info='NO_SUCH_TABLE';IF p_name IS NULL OR p_name='' THEN SELECT * FROM users;ELSE SELECT * FROM users WHERE name LIKE p_name;END IF;END//

方法6：使用SQLEXCEPTION

CREATE PROCEDURE SP_SEARCH(IN p_name CHAR(20)) BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';IF p_name IS NULL OR p_name='' THEN SELECT * FROM users;ELSE SELECT * FROM users WHERE name LIKE p_name;END IF;END//

以上处理方式讲解

1、捕获sqlstate_value值：如果遇到sqlstate_value值为‘42S02’，则执行CONTINUE操作，并且输出NO_SUCH_TABLE信息。
2、捕获mysql_error_code值：如果遇到mysql_error_code值为1146，则执行CONTINUE操作，并且输出NO_SUCH_TABLE信息。
3、先定义条件，然后再调用：此处先定义no_such_table条件，若遇到1146错误，就执行CONTINUE操作。
4、使用SQLWARNING:SQLWARNING捕获所有以01开头的sqlstate_value值，然后执行EXIT操作，并且输出NO_SUCH_TABLE信息。
5、使用NOT FOUND：NOT FOUND捕获所有以02开头的sqlstate_value值，然后执行EXIT操作，并且输出NO_SUCH_TABLE信息。
6、使用SQLEXCEPTION:SQLEXCEPTION捕获所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value值，然后执行EXIT操作，并且输出ERROR信息。

光标的使用

查询语句可能会返回多条记录，在存储过程中可以使用光标逐条读取查询结果集中的记录。

声明光标

使用光标前需要先声明光标，并且必须声明在处理程序之前，变量和条件之后。
语法

DECLARE cursor_name CURSOR FOR select_statement;

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

打开光标

语法

OPEN cursor_name;

使用光标

语法

FETCH  cursor_name INTO var_name [,var_name...];

关闭光标

语法

CLOSE cursor_name;

实战命令

CREATE PROCEDURE SP_SEARCH(IN p_name CHAR(20)) BEGIN DECLARE cur_users CURSOR FOR SELECT name FROM users;OPEN cur_users;FETCH cur_users INTO p_name;CLOSE cur_users;IF p_name IS NULL OR p_name='' THEN SELECT * FROM users;ELSE SELECT * FROM users WHERE name LIKE p_name;END IF;END//

流程控制

1、IF语句

语法

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

search_condition表示条件判断语句，statement_list表示不同条件的执行语句。
使用IF语句编写存储过程，通过传入参数返回各个分数等级的学生标号和学生分数。

CREATE PROCEDURE SP_SCHOLARSHIP_LEVEL (IN p_level CHAR(1)) BEGIN IF p_level='A' THEN SELECT * FROM stu_score WHERE stu_score >= 90;ELSEIF p_level='B' THEN SELECT * FROM stu_score WHERE stu_score < 90 AND stu_score >= 80;ELSEIF p_level='C' THEN SELECT * FROM stu_score WHERE stu_score < 80 AND stu_score >= 70;ELSEIF p_level='D' THEN SELECT * FROM stu_score WHERE stu_score < 60;ELSE SELECT * FROM stu_score;END IF;END //

CALL SP_SCHOLARSHIP_LEVEL('A')//
+--------+-----------+
| stu_id | stu_score |
+--------+-----------+
|      1 |        91 |
|      4 |        95 |
|      9 |        99 |
+--------+-----------+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

2、CASE语句

语法

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

case_value表示条件判断的表达式，when_value表示表达式可能的值。若果某个when_value表达式与case_value表达式的结果相同，则执行对应THEN关键字后的statement_list中的语句。
使用CASE语句编写存储过程，通过传入的参数返回各个分数等级的学生信息。

CREATE PROCEDURE SP_SCHOLARSHIP_LEVEL3 (IN p_level CHAR(1)) BEGIN DECLARE p_num INT DEFAULT 0;CASE p_level WHEN 'A' THEN SET p_num=90;WHEN 'B' THEN SET p_num=80;WHEN 'C' THEN SET p_num=70;WHEN 'D' THEN SET p_num=60;ELSE SET p_num=0;END CASE;SELECT * FROM stu_score sc,stu s WHERE sc.stu_id=s.stu_id AND sc.stu_score >= p_num;END //

CALL SP_SCHOLARSHIP_LEVEL3('d')//
+--------+-----------+--------+----------+-----------+---------+---------+
| stu_id | stu_score | stu_id | stu_name | stu_class | stu_sex | stu_age |
+--------+-----------+--------+----------+-----------+---------+---------+
|      1 |        91 |      1 | aa       |         3 | 女      |      23 |
|      2 |        62 |      2 | bb       |         1 | 男      |      12 |
|      4 |        95 |      4 | dd       |         2 | 男      |      22 |
|      5 |        71 |      5 | ee       |         1 | 女      |      23 |
|      6 |        82 |      6 | ff       |         2 | 女      |      13 |
|      7 |        60 |      7 | gg       |         3 | 男      |      10 |
|      9 |        99 |      9 | ii       |         1 | 男      |      13 |
+--------+-----------+--------+----------+-----------+---------+---------+
7 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

3、WHILE语句

语法

[begin_label:]
WHILE search_condition DO
statement_list
END WHILE
[end_label]

search_condition为进行判断的表达式，如果表达式的结果为真，WHILE语句内的语句或语句块被执行，直到search_condition为假，退出循环。
使用WHILE语句编写存储过程。

CREATE PROCEDURE sp_call(IN p_num INT,OUT p_result INT) BEGIN SET p_result=1;WHILE p_num > 1 DO SET p_result = p_num * p_result;SET p_num = p_num-1;END WHILE;END //

CALL sp_call(5,@result)//
Query OK, 0 rows affected (0.00 sec)

select @result //
+---------+
| @result |
+---------+
|     120 |
+---------+
1 row in set (0.00 sec)

从以上执行结果可以看出，听过调用存储过程sp_cal并传入参数5成功计算出循环结束后的值为120。

事件调度器

事件调度器是MySQL 5.1后新增的功能，可以将数据库按自定义的时间周期触发某种操作，可以理解为时间触发器。在MySQL中事件调度器默认是关闭的，用户可以先查看是否已经开启事件调度器，具体语法格式如下。

SELECT @@event_scheduler;

SELECT @@event_scheduler;
+-------------------+
| @@event_scheduler |
+-------------------+
| OFF               |
+-------------------+
1 row in set (0.00 sec)

从以上执行结果可以看出，当前MySQL中没有开启事件调度器。开启事件调度器的语法格式如下。

SET GLOBAL event_scheduler=ON;

SELECT @@event_scheduler;
+-------------------+
| @@event_scheduler |
+-------------------+
| ON                |
+-------------------+
1 row in set (0.00 sec)

从以上执行结果可以看出，事件调度器开启成功。
创建事件调度器的语法格式如下

CREATE EVENT [IF NOT EXISTS] event_name
ON SCHEDULER scheduler
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'comment']
DO sql_statement;
schedule:
AT timestamp [+ INTERVAL interval] ... | EVERY interval [STARTS timestamp [+ INTERVAL interval]...] [ENDS timestamp [+ INTERVAL interval]...]
interval:
quantity {YEAR | QUARTER |MONTH | DAY | HOUR | MINUTE | WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

event_name代表创建的事件名称；scheduler代表执行计划，它有两个选项，一是在某一时刻执行，二是从某时到某时每隔一段时间执行；interval代表时间间隔，可以精确到秒。

案例

创建一张测试表

CREATE TABLE test_event(id INT PRIMARY KEY AUTO_INCREMENT,create_time DATETIME);

接着创建事件调度器，实现每隔5秒向test_event表中插入一条记录。

CREATE EVENT test_event_1 ON SCHEDULE EVERY 5 SECOND DO INSERT INTO test_event(create_time) VALUES(now());

查看表中数据

SELECT * FROM test_event;
+----+---------------------+
| id | create_time         |
+----+---------------------+
|  1 | 2020-11-16 16:04:29 |
|  2 | 2020-11-16 16:04:34 |
|  3 | 2020-11-16 16:04:39 |
|  4 | 2020-11-16 16:04:44 |
|  5 | 2020-11-16 16:04:49 |
|  6 | 2020-11-16 16:04:54 |
|  7 | 2020-11-16 16:04:59 |
|  8 | 2020-11-16 16:05:04 |
|  9 | 2020-11-16 16:05:09 |
| 10 | 2020-11-16 16:05:14 |
| 11 | 2020-11-16 16:05:19 |
+----+---------------------+
11 rows in set (0.00 sec)

注意：drop掉表后，时间调度器仍然存在，当下次再创建该表的时候，仍然会写入数据。

查看时间调度器

show events\G;
*************************** 1. row ***************************
                  Db: gws_cfg
                Name: test_event_1
             Definer: root@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 5
      Interval field: SECOND
              Starts: 2020-11-16 16:04:29
                Ends: NULL
              Status: ENABLED
          Originator: 0
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.01 sec)

ERROR: No query specified

删除事件调度器

drop event test_event_1;
Query OK, 0 rows affected (0.00 sec)

时空无限

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL之存储过程实战

存储过程概述存储过程是将SQL语句放到一个集合里，然后直接调用存储过程来执行已经定义好的SQL语句集合，这样可以避免开发人员重复编写相同的SQL语句。另外，存储过程还可以减少数据在数据库和应用服务器之间的传输，可以提高数据的处理效率。存储过程的优缺点优点1、允许标准组件式编程，提高了SQL语句的重用性、共享性和可移植性。2、能够实现较快的执行速度，节省网络流量。3、可以作为一种安全机制来使用。缺点1、编写存储过程比编写单个SQL语句复杂，需要用户具有丰富的经验。2、编写存储过程需时需要创建
复制链接

扫一扫