MySQL(存储过程篇)

一、概述

  存储过程可以理解为一段 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));*/

七、总结

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

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