MySQL存储过程入门

我不是女神ヾ 2022-04-24 13:54 163阅读 0赞

存储过程是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可以通过指定存储过程的名字并给定参数来调用执行。存储过程思想很简单，就是数据库SQL语言层面的代码封装与重用。存储过程在MySQL5.0版本开始支持。  
   存储过程可以完成一些普通SQL语句无法完成的任务：比如需要批量插入10W条记录。  
   假设这里有一个用户表：

drop table if exists xx_user;
    create table xx_user(id int primary key auto_increment,name varchar(20),age int);

我们为了构造一个10w或者100w用户表记录，用户id自动增长，name为user-i，i为变量，比如user-1,user-2,user-3等等，age可以固定为18。普通的SQL语句来实现这个功能，只能是利用批量插入insert xx\_user(name,age) values ('user-1',18),('user-2',18),...,('user-100000',18);要么就是通过现有的表，然后将表记录复制过去。  
  如果有了存储过程，我们可以在存储过程中，借助变量i，然后做一个while循环1000000次，然后插入记录。具体的实现过程如下所示：

mysql> delimiter //
    mysql> create procedure add_user()
        -> begin
        -> declare i int default 0;
        -> set i=0;
        -> while i<1000000 do
        -> insert into xx_user (name,age) values (concat('user-',i),18);
        -> set i=i+1;
        -> end while;
        -> end;
        -> //
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> delimiter ;
    mysql> call add_user;
    Query OK, 1 row affected (34.41 sec)
    
    mysql> select count(*) from xx_user;
    +----------+
    | count(*) |
    +----------+
    |  1000000 |
    +----------+
    1 row in set (0.21 sec)

在这个存储过程中，我们实现了这个批量插入的操作。  
我们再回过头来，了解一下存储过程的定义和创建语法，以及如何在SQL命令行下查看存储过程，调用存储过程。  
存储过程的定义：  
create procedure pro\_name()  
begin  
   declare i int default 0;  
   set i=0;  
   while i<100000 do  
      //todo  
   end while;  
end;

**语法：**

存储过程有一个相对固定的写法，就是会以begin开始，然后以end结束，在这中间，我们可以声明变量declare i int;也可以给变量赋值，或者改变变量的值，这都是set来实现，和一般的赋值语句稍微不一样的，就是前面一定要跟set关键字。

在过程体中，可以使用while循环，当while循环结束了，需要使用“end while；”来结束这个循环。也可以使用if else条件分支语句，条件和一般的判断也不一样，当需要判断是否相等的时候，只需要一个“=”号。例如：

![20190426170246790.png][]

当条件判断语句结束了，也需要跟上“end if;”结尾，表示这一段是一个整体。

**参数：**

存储过程也可以携带参数，这个参数就是在定义存储过程名的时候指定，根据参数的方向in或者out，可以设置输入参数，输出参数，输入参数就是存储过程所需要的参数，输出参数就是存储过程执行完成之后，改变过的参数，可以直接在SQL命令行下查看。

**调用：**

调用存储过程语法是call proc\_name()，如果没有参数可以省略“()”，直接简写为call proc\_name，如果存储过程携带参数，那么就是call proc\_name(param1,param2);

**小知识：**

1、和很多语言一样，存储过程中，如果需要打印一个中间结果，可以直接通过select xx来打印。记住是select。  
2、存储过程中字符串用一个单引号'str'表示，如果字符串中还包含字符串，就用两个单引号''str''来表示。是两个单引号，不是双引号。  
3、字符串拼接是concat，不能用”+“号。  
4、存储过程中的判断语句如果是等于，那么就用一个“=”号，不是两个“==”。  
5、SQL语句一般会用分号“；”表示语句结束，告诉编译器可以执行了，但是在存储过程的编写中，过程没有编写完成，中间就有很多分号“；”，因此在存储过程的编写中，会先定义语句结束符为“//”，当我们编写存储过程结束"end;"之后，我们再输入“//”就表示存储过程结束，定义存储过程就结束了，最后回到命令行下，我们需要通过“delimiter ;”将语句结束符修改为分号“；”。其中“//”可以定义为“$$”，默认一般使用“//”。

下面我们通过前面的介绍，来编写我们的第一个存储过程helloworld。

mysql> delimiter //
    mysql> create procedure hello(str varchar(20))
        -> begin
        -> select str;
        -> end;
        -> //
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> call hello('helloworld');
        -> //
    +------------+
    | str        |
    +------------+
    | helloworld |
    +------------+
    1 row in set (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    mysql>

第二个小例子就是输入，输出参数的感知学习。

mysql> delimiter //
    mysql> create procedure paramtest(in p1 int,out p2 int)
        -> begin
        -> select p1;
        -> set p2=100;
        -> select p2;
        -> end;
        -> //
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> delimiter ;
    mysql> set @p2=99;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> call paramtest(99,@p2);
    +------+
    | p1   |
    +------+
    |   99 |
    +------+
    1 row in set (0.00 sec)
    
    +------+
    | p2   |
    +------+
    |  100 |
    +------+
    1 row in set (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select @p2;
    +------+
    | @p2  |
    +------+
    |  100 |
    +------+
    1 row in set (0.00 sec)
    
    mysql>

这里对于输入参数没什么好说的，调用的时候，直接传入对应的类型，就可以了。但是对于输出参数，需要说明一下，这个输出参数在调用之前，需要先设置即set @p2=99.而且设置的时候，前面必须带上@，否则会出现如下所示的错误：

mysql> set p2=99;
    ERROR 1193 (HY000): Unknown system variable 'p2'

如果传入的是一个具体的数字，那么会报出如下错误：

mysql> call paramtest(99,null);
    ERROR 1414 (42000): OUT or INOUT argument 2 for routine test.paramtest is not a variable or NEW pseudo-variable in BEFORE trigger
    mysql> call paramtest(99,100);
    ERROR 1414 (42000): OUT or INOUT argument 2 for routine test.paramtest is not a variable or NEW pseudo-variable in BEFORE trigger

有了这两个简单的示例，我们基本掌握了存储过程的大致写法和存储过程调用。和创建表一样，我们希望知道我们创建的存储过程时什么样或者存储过程的创建语句或者状态，我们给出以下三种查看存储过程的办法：

a、select \* from mysql.proc where db='test';  
b、show procedure status where db='test';  
c、show create procedure paramtest;

效果分别如下：

查看存储过程的名字

![20190426181727633.png][]

查看状态：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70][]

查看创建语句：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70 1][]

一个小建议：刚入门写存储过程， 最好是把语句写在一个sql文件里面，然后在SQL命令行下运行source xx.sql执行，这样可以避免书写错误，无法回退，需要反复创建的问题。另外在创建之前，最好先删除以前有可能已经存在的存储过程，避免创建出错。如下所示：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70 2][]

[20190426170246790.png]: /images/20220214/dcd75182ae374e3da63844ed891e874b.png
[20190426181727633.png]: /images/20220214/704438951fe642d9859b670ee5139dc2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70]: /images/20220214/19ecc5bf09ca4f12b237a8690279e8ab.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70 1]: /images/20220214/2b1fbcc3d63f4ffd8b5cef01c772b34f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaW5pZmk_size_16_color_FFFFFF_t_70 2]: /images/20220214/1e7cec29139c4b4baf17128f1030b466.png