mysql 存储过程

2021-07-28 02:38:18

一、概述

　　存储过程可以理解为一段 SQL 语句的集合（相当于 PHP 中的一个函数方法，去实现业务逻辑），它们被事先编译好并且存储在数据库中。

　　调用存储过程与直接执行 SQL 语句的效果是相同的，但是存储过程的一个好处是处理逻辑都封装在数据库端。

　　当我们调用存储过程的时候，我们不需要了解其中的处理逻辑，一旦处理逻辑发生变化，只需要修改存储过程即可，对调用它的程序完全无影响。

　　调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，减少了和脚本语言的交互以及带宽，可以提高数据处理的效率。

二、存储过程结构

create procedure 【存储过程名(参数列表)】
begin
    【存储过程体】
end

call 存储过程名(参数列表)

三、使用示例

实例1、新建一张数据表，并向这张数据表中添加 100 万条记录。

（1）新建数据表

CREATE TABLE `test_table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `loop` int(10) unsigned NOT NULL DEFAULT '0',
  `name` varchar(256) NOT NULL DEFAULT '',
  `pen_name` varchar(256) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

（2）新建存储过程

DROP PROCEDURE IF EXISTS insert_many_rows;

CREATE PROCEDURE insert_many_rows (IN loopTime INT)
BEGIN
    DECLARE executedTime INT ;
    SET executedTime = loopTime;
    while executedTime > 0 DO
        INSERT INTO test_table(NULL, 0, 'sss', 'kkk');
        SET executedTime = executedTime - 1;
    END WHILE;
END;

（3）呼叫存储过程

CALL insert_many_rows(1000000);

结果应该是新建的数据表中已经有了 100 万条记录。

　
实例2：通过存储过程创建10个数据表，分别为test_table_0 ~ test_table_9

（1）创建存储过程

DROP PROCEDURE IF EXISTS create_test_tables;
CREATE PROCEDURE `create_test_tables`()
BEGIN
    DECLARE i INT;
    DECLARE tableName VARCHAR(30);
    DECLARE sqlText text;

    SET i = 0;

    WHILE i < 10 DO
      SET tableName = CONCAT('test_table_' , i);
      SET sqlText = CONCAT('CREATE TABLE ', tableName , '(
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `loop` int(10) unsigned NOT NULL DEFAULT ''0'',
  `name` varchar(256) NOT NULL DEFAULT '''',
  `pen_name` varchar(256) NOT NULL DEFAULT '''',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2001001 DEFAULT CHARSET=utf8;');
      SET @sqlText = sqlText;

      PREPARE stmt
      FROM
          @sqlText;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
      SET i = i + 1;
    END WHILE;
END

（2）呼叫存储过程

CALL create_test_tables();

四、分析

1、存储过程的参数类型：

（1）IN 表示只是用来输入。

（2）OUT 表示只是用来输出。

（3）INOUT 可以用来输入，也可以用作输出。

2、存储过程中的变量声明

通过 DECLARE 来声明一个局部变量，该变量的作用域只是 begin....end 块中。

变量的声明可以添加默认值，比如：

DECLARE executedTime INT DEFAULT 0;

3、流程控制语句语法

if 的语法格式为：
if 条件表达式 then 语句
    [elseif 条件表达式 then 语句] ....
    [else 语句]
end if

case 的语法格式
首先是第一种写法：
case 表达式
    when 值 then 语句
    when 值 then 语句
    ...
    [else 语句]
end case
然后是第二种写法：
case
    when 表达式 then 语句
    when 表达式 then 语句
    ....
    [else 语句]
end case

loop 循环 语法格式为：
[标号:] loop
    循环语句
end loop [标号]

while 语法
while a>100 do
 循环语句
End while

Repeat        //游标
  SQL语句1
  UNTIL 条件表达式
END Repeat;

Loop
  SQL语句
  所有的条件判断和跳出需要自己实现
End loop

leave 语句用来从标注的流程构造中退出，它通常和 begin...end 或循环一起使用
leave 标号;

声明语句结束符，可以自定义:
DELIMITER [符合]
delimiter $$

$$

4、存储过程中的数据类型

数值类型：Int、float、double、decimal

日期类型：timestamp、date、year

字符串：char、varchar、text

五、存储过程优缺点

1、优点：

（1）执行速度快。因为我们的每个 SQL 语句都需要经过编译，然后再运行。但是存储过程都是直接编译好了之后，直接运行即可。

（2）减少网络流量，我们传输一个存储过程比我们传输大量的 SQL 语句的开销要小得多。

（3）提高系统安全性，因为存储过程可以使用权限控制，而且参数化的存储过程可以有效地防止 SQL 注入攻击。保证了其安全性。

（4）耦合性降低。当我们的表结构发生了调整或变动之后，我们可以修改相应的存储过程，我们的应用程序在一定程度上需要改动的地方就较小了。

（5）重用性强，因为我们写好一个存储过程之后，再次调用它只需要一个名称即可，也就是”一次编写，随处调用”,而且使用存储过程也可以让程序的模块化加强。

2、缺点：

（1）可移植性差。因为存储过程是和数据库绑定的，如果我们要更换数据库之类的操作，可能很多地方都需要改动。

（2）修改不方便。因为对于存储过程而言，我们并不能特别有效的调试，它的一些 bug 可能发现的更晚一些，增加了应用的危险性。

（3）优势不明显和赘余功能。对于小型 web 应用来说，如果我们使用语句缓存，发现编译 SQL 的开销并不大，但是使用存储过程却需要检查权限一类的开销，这些赘余功能也会在一定程度上拖累性能。

六、PHP 中使用存储过程

　　PHP 中也是可以使用存储过程的，存储过程的使用也很简单。只要将存储过程的创建语句和call语句分别执行就可以了。

　　这里使用最简单的 pdo 调用方式，如果在框架中，为了保持代码的美观，请使用框架自带的查询执行语句。

<?php
declare(strict_types = 1);

// 注意：创建存储过程和call存储过程要分开执行，创建存储过程之后，将创建存储过程部分注释掉，然后打开call存储过程代码执行

// 连接 pdo
$dsn = "mysql:dbname=test;host=127.0.0.1";
$pdo = new PDO($dsn,'root','123456');

# ------------------------------- 创建存储过程 --------------------------
// 创建存储过程语句赋值到变量
$sql = 'DROP PROCEDURE IF EXISTS insert_many_rows_2;
CREATE PROCEDURE insert_many_rows_2 (IN loopTime INT)
BEGIN
    DECLARE executedTime INT ;
    SET executedTime = loopTime;
    while executedTime > 0 DO
        INSERT INTO test_table(NULL, 0, \'sss\', \'kkk\');
        SET executedTime = executedTime - 1;
    END WHILE;
END;';

// 执行
$stmt = $pdo->query($sql);
var_dump($stmt->fetchAll(2));

#---------------------- call 存储过程 --------------------------

/*$callSql = 'CALL insert_many_rows(1000000);';
$stmt = $pdo->query($callSql);
var_dump($stmt->fetchAll(2));*/

七、总结

存储过程只做了解即可，事实上很多公司都是禁止使用存储过程的，主要是因为一旦使用存储过程，新人接手将会非常困难，并且难以调试和扩展，而且没有可移植性。

何况存储过程能够解决的问题，一般程序代码也是可以解决的，因此在非必要情况下，还是使用代码去实现，而不是考虑去用存储过程。

码农公寓

相关文章