MySQL存储过程

MySQL存储过程

存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,存储在数据库中经过第一次编译后再次调用不需要再次编译,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象,任何一个设计良好的数据库应用程序都应该用到存储过程。

存储过程简介

存储过程是存储在数据库目录中的一段声明式SQL语句。它可以被触发器、其它存储过程及应用(如Java、Python、PHP等)调用。调用自身的存储过程称为递归存储过程。大部分数据库管理系统都支持递归存储过程,然而MySQL对递归存储过程的支持并不是特别好,因此在MySQL中使用递归存储过程之前应该先对当前的MySQL数据库版本是否支持递归存储过程进行检测。

MySQL是最流行的开源RDBMS(关系型数据库管理系统),它在社区和企业中都得到了广泛的应用。然而,在MySQL存在的前十年,它并不支持存储过程(stored procedures)、存储函数(stored functions)、触发器(triggers)和事件(events)。从5.0版本开始,这些特性才被加到MySQL数据库引擎中,使得它变得更加灵活和强大。

  • MySQL存储过程的优点

    • 存储过程通常有助于提升应用的性能,一旦创建,存储过程就被编译存储于数据库中。然而,MySQL中的存储过程的实现机制略微不同,它只有在需要的时候才进行编译。在编译后,MySQL把它放到缓存中。对于每个单独的连接,MySQL都维护一个存储过程缓存。如果一个应用在一个连接中多次使用一个存储过程,那么它用的就是编译过的版本,否则存储过程的工作方式就像查询一样。

    • 存储过程有助于减少应用和数据库之间的流量。因为它不再需要发送多个冗长的SQL语句,只需要发送存储过程的名字和参数即可。

    • 存储过程是可复用的且对应用透明。存储过程为所有应用提供数据库接口,因此开发人员不必再开发早已在存储过程中支持的功能。

    • 存储过程是安全的。数据库管理员可以为应用授予访问数据库中存储过程的权限,而不必授予底层的数据库表的权限。

  • MySQL存储过程的缺点

    • 如果使用大量的存储过程,那么每个连接的内存使用将大大增加。另外,如果在存储过程中滥用大量的逻辑操作,那么cpu的使用也会增加,这是因为在数据库的设计中对逻辑运算的支持并不是很好。

    • 存储过程的结构使得开发具有复杂业务逻辑的存储过程时变得非常困难。

    • 存储过程的debug也是困难的。只有一少部分数据库管理系统支持存储过程的debug。不幸的是,MySQL没有提供调试存储过程的组件。

    • 存储过程的开发和维护是困难的。开发和维护存储过程通常要求具备一些专业技能,然而这并不是所有应用开发人员都具有的。这可能导致在应用开发和维护阶段的问题。

MySQL的存储过程有利有弊,在开发过程中,要根据自己的业务需求决定是否使用存储过程。

MySQL存储过程实例

本部分以MySQL Workbench为例,创建一个用于批量建表的存储过程,该语句可用于分库分表中表的批量建立。

  1. 右键单击数据库下面的Stored Procedures, 选择Create Stored Procedure..., 会建立一个新的存储过程,默认名字为new_procedure,将其修改为自己的存储过程的名字,例如batch_create_table。如图所示:

    新建存储过程

  2. 编辑存储过程,完成批量建立4个表的功能。
    表名分别为:order_remarks_0,order_remarks_1,order_remarks_2,order_remarks_3.
    完整的存储过程代码:

    CREATE PROCEDURE `batch_create_table` ()
    BEGIN
    DECLARE i INT;
    DECLARE table_name VARCHAR(20);
    DECLARE table_pre VARCHAR(20);
    DECLARE sql_text VARCHAR(2000);
    SET i=0;
    SET table_name='';
    SET table_pre='order_remarks_';
    SET sql_text='';
    
    while i<4 do
        SET table_name=CONCAT(table_pre,i);
        SET sql_text=CONCAT('CREATE TABLE ', table_name, '(
                  id                int(10)     UNSIGNED  AUTO_INCREMENT   ,
                  mall_id           int(11)     UNSIGNED  NOT NULL         ,
                  order_sn          varchar(255)          NOT NULL         ,
                  remarks_type      tinyint(4)      NOT NULL  DEFAULT 1    ,
                  remark            mediumtext      NOT NULL               ,
                  operator_id       int(11)         NOT NULL  DEFAULT 0    ,
                  operator_name     varchar(255)    NOT NULL  DEFAULT \'\'   ,
                  operator_type     tinyint(4)      NOT NULL  DEFAULT 0    ,
                  created_at        timestamp       NOT NULL  DEFAULT CURRENT_TIMESTAMP ,
                  updated_at        timestamp       NOT NULL  DEFAULT \'0000-00-00 00:00:00\'  ,
                  visible           tinyint(4)      NOT NULL  DEFAULT 1    ,
                  PRIMARY KEY (id),
                  KEY order_sn  (order_sn),
                  KEY created_at  (created_at),
                  KEY updated_at  (updated_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; ' );
        SELECT sql_text;
        SET @sql_text=sql_text;
        PREPARE stmt FROM @sql_text;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;  
        SET i=i+1;
    end while;
    END
  3. 点击右下角的Apply,弹出Review SQL Script的提示,代码里会自动增加一些代码,如图所示:
    apply

  4. 确保无误后,点击Apply将该SQL脚本应用与数据库。点击SCHEMAS右边的更新按钮,可以看到Stored Procedures下多了一个名为batch_create_table的存储过程。如图所示:

    这里写图片描述

  5. 最终生成的存储过程第一行由CREATE PROCEDURE `batch_create_table` ()变成了CREATE DEFINER=`root`@`localhost` PROCEDURE `batch_create_table`()
    其它内容不变。如图所示:

    final

  6. 调用存储过程,在Query标签中使用call batch_create_table();调用存储过程。刷新SCHEMA后会看到新建了4个表,如图所示:
    tables

  • 如果提示Error Code: 1067 Invalid default value for 'updated_at',说明当前MySQL版本的SQL_MODE限制不能为timestamp字段设置默认值。
  • 这种情况一般可以使用DEFAULT 0DEFAULT CURRENT_TIMESTAMP来设置默认值,但后者一般只能出现一次,这与MySQL版本有关。或者可以把SQL_MODE设为ALLOW_INVALID_DATES。这样就不必修改代码了。依次执行下列语句:
    SET sql_mode = 'ALLOW_INVALID_DATES';
    call batch_create_table();

变量和参数

存储过程中的变量

本部分将介绍存储过程中变量的声明、使用及作用域。

  • 变量声明

    在存储过程中,使用DECLARE来声明变量。DECLARE的语法结构如下所示:

    DECLARE variable_name datatype(size) DEFAULT default_value;
    • 首先,在DECLARE关键字后指明变量名。注意:* 变量名必须遵循MySQL表中字段名的命名规则. *
    • 其次,指定变量的数据类型和大小。一个变量可以是任意的MySQL数据类型,例如INTVARCHARDATETIME等。
    • 最后,声明一个变量后,它的初始值是NULL。可以使用DEFAULT关键字为变量指定一个默认值。

    变量声明的一些例子:

    1. 声明变量total_sale,数据类型为INT,默认值为0

    DECLARE total_sale INT DEFAULT 0;

    1. 同时声明具有相同的数据类型和默认值的多个变量

    DECLARE x, y INT DEFAULT 0;

  • 变量赋值

    1. 使用SET为变量赋值

      DECLARE total_count INT DEFAULT 0;
      SET total_count = 10;
    2. 使用SELECT INTO将一次查询的结果赋值给变量

      DECLARE total_products INT DEFAULT 0
      SELECT COUNT(*) INTO total_products FROM products
  • 变量作用域

    • 在存储过程中声明一个变量,它的作用域到存储过程的END语句为止。
    • BEGIN END语句块中声明一个变量,它的作用域到END为止。变量只在它自己的作用域中有效,因此在不同的作用域中可以声明多个同名的变量。但是,这并不是一个好的编程习惯。
    • @符号开始的变量是会话变量(‘session variable’),它在整个session的声明周期中都是有效的。

存储过程参数

本部分将介绍带参的MySQL存储过程。

  • 参数介绍
    参数使得存储过程更加灵活和有用。在MySQL中,参数有三种形式:INOUTINOUT.

    • IN – 默认的参数模式。当在存储过程中定义一个IN参数时,调用程序必须传一个参数给存储过程。另外,IN参数的值是受保护的。这意味着,即使参数的值在存储过程中被改变了,它的原始值在存储过程结束后仍然得到了保留。也就是说,存储过程是在IN参数的副本上工作的。
    • OUTOUT参数的值可以在存储过程中被改变,并且新值会被返回给调用程序。注意,存储过程在开始时不能获得OUT参数的初始值。
    • INOUTINOUTINOUT参数的结合。这意味着,调用程序可以传递该参数,并且调用程序可以修改INOUT参数并把新值返回给调用程序。

    在存储过程中定义一个参数的语法如下:

    MODE param_name param_type(param_size)

    1. MODE可以是INOUTINOUT,取决于参数在存储过程中的作用。
    2. 参数名param_name必须遵循MySQL中列名的命名规则。
    3. 参数名后是数据类型和大小,与变量类似,数据类型可以是任意的MySQL数据类型。

    如果存储过程有多个参数,则参数之间以逗号,分隔。

  • 实例

    • IN参数

      在存储过程GetOfficeByCountry中使用IN参数来查找指定国家的办公室信息:

      DELIMITER //
      CREATE PROCEDURE GetOfficeByCountry(IN countryName VARCHAR(255))
      BEGIN
      SELECT *
      FROM offices
      WHERE country = countryName;
      END //
      DELIMITER ;

      调用存储过程CALL GetOfficeByCountry('USA');,结果如图所示:
      IN1

      调用存储过程CALL GetOfficeByCountry('France');,结果如图所示:
      in2

    • OUT参数

      存储过程CountOrderByStatus

      DELIMITER $$
        CREATE PROCEDURE CountOrderByStatus(
         IN orderStatus VARCHAR(25),
         OUT total INT)
        BEGIN
         SELECT count(orderNumber)
         INTO total
         FROM orders
         WHERE status = orderStatus;
        END$$
      DELIMITER ;

      调用存储过程,指定参数orderStatus为‘Shipped’,参数@total来获得返回值。

      CALL CountOrderByStatus('Shipped',@total);
      SELECT @total;

      结果如图所示:这里写图片描述

    • INOUT参数
      使用INOUT参数的存储过程:

      DELIMITER $$
        CREATE PROCEDURE set_counter(INOUT count INT(4),IN inc INT(4))
        BEGIN
         SET count = count + inc;
        END$$
      DELIMITER ;

      在该存储过程中有两个参数,一个是INOUT参数count,另一个是IN参数inc。该存储过程把count参数增加inc
      调用存储过程:

      SET @counter = 1;
      CALL set_counter(@counter,1); -- 2
      CALL set_counter(@counter,1); -- 3
      CALL set_counter(@counter,5); -- 8
      SELECT @counter; -- 8

多返回值存储过程

MySQL IF语句

  • IF语法

    IF expression THEN 
     statements;
    END IF;

    if

  • IF ELSE语法

    IF expression THEN
     statements;
    ELSE
     else-statements;
    END IF;

    if

  • IF ELSEIF ELSE语法

    IF expression THEN
     statements;
    ELSEIF elseif-expression THEN
     elseif-statements;
    ...
    ELSE
     else-statements;
    END IF;

    if

  • 实例

    DELIMITER $$
    
    CREATE PROCEDURE GetCustomerLevel(
      in  GetCustomerLevel() int(11), 
      out p_customerLevel  varchar(10))
    BEGIN
      DECLARE creditlim double;
    
      SELECT creditlimit INTO creditlim
      FROM customers
      WHERE customerNumber = p_customerNumber;
    
      IF creditlim > 50000 THEN
    SET p_customerLevel = 'PLATINUM';
      ELSEIF (creditlim <= 50000 AND creditlim >= 10000) THEN
          SET p_customerLevel = 'GOLD';
      ELSEIF creditlim < 10000 THEN
          SET p_customerLevel = 'SILVER';
      END IF;
    
    END$$

    在存储过程GetCustomerLevel()中,有两个参数:客户编号GetCustomerLevel()和客户级别p_customerLevel。该存储过程的功能是根据客户信用额度来判断客户编号对应的客户级别。
    首先在customers表中查询客户的信用额度,然后根据该额度对客户的级别进行判断,如果信用额度大于50000,则是PLATINUM;如果在[10000,50000]之间,则是GOLD;如果小于10000,则是SILVER

    判断逻辑如图所示:

    example

MySQL CASE语句

循环

游标Cursor

错误处理

列出存储过程

参考文献

  1. MySQL Stored Procedure
相关推荐
<p> <span style="font-size:14px;color:#E53333;">限时福利1:</span><span style="font-size:14px;">购课进答疑群专享柳峰(刘运强)老师答疑服务</span> </p> <p> <br /> </p> <p> <br /> </p> <p> <span style="font-size:14px;"></span> </p> <p> <span style="font-size:14px;color:#337FE5;"><strong>为什么需要掌握高性能的MySQL实战?</strong></span> </p> <p> <span><span style="font-size:14px;"><br /> </span></span> <span style="font-size:14px;">由于互联网产品用户量大、高并发请求场景多,因此对MySQL的性能、可用性、扩展性都提出了很高的要求。使用MySQL解决大量数据以及高并发请求已经是程序员的必备技能,也是衡量一个程序员能力和薪资的标准之一。</span> </p> <p> <br /> </p> <p> <span style="font-size:14px;">为了让大家快速系统了解高性能MySQL核心知识全貌,我为你总结了</span><span style="font-size:14px;">「高性能 MySQL 知识框架图」</span><span style="font-size:14px;">,帮你梳理学习重点,建议收藏!</span> </p> <p> <br /> </p> <p> <img alt="" src="https://img-bss.csdnimg.cn/202006031401338860.png" /> </p> <p> <br /> </p> <p> <span style="font-size:14px;color:#337FE5;"><strong>【课程设计】</strong></span> </p> <p> <span style="font-size:14px;"><br /> </span> </p> <p> <span style="font-size:14px;">课程分为四大篇章,将为你建立完整的 MySQL 知识体系,同时将重点讲解 MySQL 底层运行原理、数据库的性能调优、高并发、海量业务处理、面试解析等。</span> </p> <p> <span style="font-size:14px;"><br /> </span> </p> <p> <span style="font-size:14px;"></span> </p> <p style="text-align:justify;"> <span style="font-size:14px;"><strong>一、性能优化篇:</strong></span> </p> <p style="text-align:justify;"> <span style="font-size:14px;">主要包括经典 MySQL 问题剖析、索引底层原理和事务与锁机制。通过深入理解 MySQL 的索引结构 B+Tree ,学员能够从根本上弄懂为什么有些 SQL 走索引、有些不走索引,从而彻底掌握索引的使用和优化技巧,能够避开很多实战中遇到的“坑”。</span> </p> <p style="text-align:justify;"> <br /> </p> <p style="text-align:justify;"> <span style="font-size:14px;"><strong>二、MySQL 8.0新特性篇:</strong></span> </p> <p style="text-align:justify;"> <span style="font-size:14px;">主要包括窗口函数和通用表表达式。企业中的许多报表统计需求,如果不采用窗口函数,用普通的 SQL 语句是很难实现的。</span> </p> <p style="text-align:justify;"> <br /> </p> <p style="text-align:justify;"> <span style="font-size:14px;"><strong>三、高性能架构篇:</strong></span> </p> <p style="text-align:justify;"> <span style="font-size:14px;">主要包括主从复制和读写分离。在企业的生产环境中,很少采用单台MySQL节点的情况,因为一旦单个节点发生故障,整个系统都不可用,后果往往不堪设想,因此掌握高可用架构的实现是非常有必要的。</span> </p> <p style="text-align:justify;"> <br /> </p> <p style="text-align:justify;"> <span style="font-size:14px;"><strong>四、面试篇:</strong></span> </p> <p style="text-align:justify;"> <span style="font-size:14px;">程序员获得工作的第一步,就是高效的准备面试,面试篇主要从知识点回顾总结的角度出发,结合程序员面试高频MySQL问题精讲精练,帮助程序员吊打面试官,获得心仪的工作机会。</span> </p>
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页