MySQL 存储过程

[toc]

存储过程概述

存储过程介绍

存储过程,相当于是 MySQL 语句组成的脚本

• 指的是数据库中保存的一系列 SQL 命令的集合
• 可以在存储过程中使用变量, 条件判断, 流程控制等

存储过程优点

1. 提高性能
2. 可减轻网络负担
3. 可以防止对表的直接访问
4. 避免重复编写 SQL 操作

基本使用

创建存储过程

语法格式

• delimiter 关键字用来指定 SQL 语句的分隔符 ( 默认为; )
• 若没有指定分割,编译器会把存储过程当成 SQL 语句进行处理,从而执行出错

delimiter //
create procedure 名称()
begin
    功能代码
    ...
    ...
end
//
delimiter ;

查看存储过程

方法1

#查看 use 库中存储过程.
mysql> show procedure status;

方法2

#查看某存储过程
select db,name,type,body from mysql.proc where name="存储过程名";

调用 存储过程

#不传参时,括号可省略
call 存储过程名();

删除存储过程

drop procedure 存储过程名;

示例

#创建存储过程 say1 
mysql> delimiter //
mysql> create procedure say1()
    -> begin
    -> select id,name,shell  from db9.user;
    -> select user,host from mysql.user where user="root";
    -> end
    -> //
mysql> delimiter ;

#调用存储过程
mysql> call say1();

#查看所有存储过程,type 是Procedure的是存储过程,Function是函数
mysql> select db,name,type from mysql.proc;
+------+-------------------------------------+-----------+
| db   | name                                | type      |
+------+-------------------------------------+-----------+
| db10 | say1                                | PROCEDURE |
| sys  | extract_schema_from_file_name       | FUNCTION  |


#查看某个存储过程
mysql> select * from mysql.proc where name="say1"\G
*************************** 1. row ***************************
                  db: db10
                name: say1
                type: PROCEDURE
       specific_name: say1
            language: SQL
     sql_data_access: CONTAINS_SQL
    is_deterministic: NO
       security_type: DEFINER
          param_list: 
             returns: 
                body: begin
select id,name,shell  from db9.user;
select user,host from mysql.user where user="root";
end
             definer: root@localhost
             created: 2019-02-12 22:59:07
            modified: 2019-02-12 22:59:07
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
        db_collation: latin1_swedish_ci
           body_utf8: begin
select id,name,shell  from db9.user;
select user,host from mysql.user where user="root";
end

#查看存储过程 say1 具体功能
mysql> select body from mysql.proc where name="say1"\G;
*************************** 1. row ***************************
body: begin
select id,name,shell  from db9.user;
select user,host from mysql.user where user="root";
end

#删除存储过程
mysql> drop procedure say1;

#创建存储过程 p1 ,功能显示user表中 shell 是 /bin/bahs的用户个数,调用p1
mysql> delimiter //
mysql> create procedure p1()
    -> begin
    -> select count(name) from db9.user where shell="/bin/bash";
    -> end
    -> //
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> call p1();
+-------------+
| count(name) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

注意

编写功能体代码时,可以使用变量 条件判断 流程控制 (if 循环) 算数计算 SQL命令

存储过程进阶

变量类型

调用局部变量时,变量名前不需要加@

示例

#用户变量 自定义变量
mysql> set @x=20;
Query OK, 0 rows affected (0.00 sec)

mysql> select @x;
+------+
| @x   |
+------+
|   20 |


#局部变量 只在存储过程中生效
mysql> delimiter //
mysql> create procedure p2()
    -> begin
    -> declare x int default 77;
    -> declare y char(10);
    -> set y="yaya";
    -> select x;
    -> select y;
    -> end
    -> //
mysql> delimiter ;

#调用存储过程
mysql> call p2;
+------+
| x    |
+------+
|   77 |
+------+
1 row in set (0.00 sec)

+------+
| y    |
+------+
| yaya |
+------+
1 row in set (0.00 sec)

查看变量

#查看全局变量
mysql> show global variables;
#查看会话变量
mysql> show session variables;
#设置会话变量
mysql> set seesion sort_buffer_size = 4000;
#查看会话变量
mysql> show session variables like "sort_buffer_size";
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| sort_buffer_size | 262144 |
+------------------+--------+

#查看全局变量
mysql> show global variables like "%关键字%";

#用户自定义变量 赋值
mysql>mysql> set @y=3;
mysql> select @y;
+-------+
| @y    |
+-------+
| 3     |
+-------+

# 使用 SQL 查询结果 赋值
mysql> select max(uid) into @y from db9.user;
Query OK, 1 row affected (0.00 sec)

mysql> select @y;
+-------+
| @y    |
+-------+
| 65534 |
+-------+

参数类型

语法格式

1. 调用参数时,名称前不需要加@

create procedure 名称( 参数1, 参数2 )

2. 参数格式

参数1= 类型 参数名 数据类型

create procedure p1( 类型1 参数名1 数据类型1(宽度), 类型2 参数名2 数据类型2(宽度) )

类型

示例

>>>>>>>>>>>>>> 输入参数 in 类型

mysql> delimiter //
#创建 in 类型 输入参数变量 username
mysql> create procedure p4(in username char(10))
    -> begin
    -> select username;
    -> select name,shell from db9.user where name=username;
    -> end
    -> //
mysql> delimiter ;

#调用存储过程时赋值
mysql> call p4("mysql");
+----------+
| username |
+----------+
| mysql    |
+----------+
1 row in set (0.00 sec)

+-------+------------+
| name  | shell      |
+-------+------------+
| mysql | /bin/false |
+-------+------------+
1 row in set (0.00 sec)

#创建p5存储过程,可以接受用户输入shell的名字,统计user表中用户输入shell名字的个数.
mysql> delimiter //
mysql> create procedure p5(in shellname char(20))
    -> begin
    -> select count(name),shell from db9.user where shell=shellname;
    -> end
    -> //
mysql> delimiter ;

mysql> call p5("/sbin/nologin");
+-------------+---------------+
| count(name) | shell         |
+-------------+---------------+
|          18 | /sbin/nologin |
+-------------+---------------+



>>>>>>>>>>>>>> 输出参数 out 类型

mysql> set @x=1;
mysql> drop procedure if exists p2;
mysql> delimiter //
# 创建 out 类型 输出参数变量 num
mysql> create procedure p2(out num int(2))
    -> begin
    # 查看初始值
    -> select num;
    # 改变 num 初始值
    -> set num=7;
    -> select num;
    # SQL查询 赋值给 num
    -> select count(name) into num from db9.user where shell != "/bin/bash";
    -> select num;
    -> end
    -> //
mysql> delimiter ;

# 调用报错 "错误的参数个数" 无论输入参数输出参数调用时都必须给出.
mysql> call p2();
ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE db9.p2; expected 1, got 0

# 调用报错 "输出参数或输入输出参数不是变量" 只有输入参数可以传入常量,输出或输入输出参数都必须是变量.
mysql> call p2(22);
ERROR 1414 (42000): OUT or INOUT argument 1 for routine db9.p2 is not a variable or NEW pseudo-variable in BEFORE trigger

# 默认未将 @x 的值赋给 输出参数 变量num,只是占位所以为空. 
mysql> set @x=1;
mysql> call p2(@x);
+------+
| num  |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

+------+
| num  |
+------+
|    7 |
+------+
1 row in set (0.00 sec)

+------+
| num  |
+------+
|   22 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

# 输出参数 最后会讲存储过程中的值 赋给 @x变量
mysql> select @x;
+------+
| @x   |
+------+
|   22 |
+------+



>>>>>>>>>>>>>> 输入输出参数 intou类型

mysql> drop procedure if exists p3;
mysql> delimiter //
#创建p3 定义输入输出参数类型 num 变量
mysql> create procedure p3(inout num int(2))
    -> begin
    -> select num;
    -> set num=7;
    -> select num;
    -> select count(name) into num from db9.user where shell != "/bin/bash";
    -> select num;
    -> end
    -> //

mysql> delimiter ;
mysql> set @x=1;
#调用p3 传入 @x 值, @x值传给 num变量
mysql> call p3(@x);
+------+
| num  |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

+------+
| num  |
+------+
|    7 |
+------+
1 row in set (0.00 sec)

+------+
| num  |
+------+
|   22 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select @x;
+------+
| @x   |
+------+
|   22 |
+------+
1 row in set (0.00 sec)

算术运算

运算符号

示例

#加法
mysql> SET @var1=2+2; 
mysql> select @var1;
+-------+
| @var1 |
+-------+
|     4 |
+-------+

#乘法
mysql> set @i=2;
mysql> set @j=3;
mysql> set @z=@i*@j;
mysql> select @z;
+------+
| @z   |
+------+
|    6 |
+------+

# 创建存储过程p6 计算 /bin/bash 和 /sbin/nologin 用户共有多少
mysql> delimiter //
mysql> create procedure p6()
    -> begin
    -> declare x int;
    -> declare y int;
    -> declare z int;
    -> select count(shell) into x from db9.user where shell="/bin/bash";
    -> select count(shell) into y from db9.user where shell="/sbin/nologin";
    -> set z=x+y;
    -> select z;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call p6;
+------+
| z    |
+------+
|   19 |
+------+

#创建存储过程p1 计算 传入参数 var1 和 var2 shell的 用户共有多少
mysql> drop procedure if exists p1;
mysql> delimiter //
mysql> create procedure p1(
in var1 char(20), in var2 char(25),out x int,out y int)
    -> begin
    -> declare z int;
    -> set z=0;
    -> select count(name) into x from db9.user where shell=var1;
    -> select count(name) into y from db9.user where shell=var2;
    -> set z=x+y;
    -> select z;
    -> end
    -> //
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
#没有输出参数报错
mysql> call p1;
ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE db9.p1; expected 4, got 0

mysql> call p1("/bin/bash","/sbin/nologin",@x,@y);
+------+
| z    |
+------+
|   19 |
+------+

流程控制

条件测试

数值比较

示例

逻辑比较

示例

选择结构

语法

# 格式1 if
if 条件测试 then
    代码 .. ..
    .. ..
end if;

#格式2 if else
if 条件测试 then
    代码1 .. ..
    .. ..
else
    代码2 .. ..
    .. ..
end if;

示例

mysql> drop procedure if exists p4;
mysql> delimiter //
mysql> create procedure p4( in num int(2))
    -> begin
    ->     if num  <= 10 then
    ->         select id,name,shell from db9.user where id <=num;
    ->     end if;
    -> end
    -> //
mysql> delimiter ;

mysql> call p4(3);
+----+--------+---------------+
| id | name   | shell         |
+----+--------+---------------+
|  1 | tom    | /bin/bash     |
|  2 | bin    | /sbin/nologin |
|  3 | daemon | /sbin/nologin |
+----+--------+---------------+

mysql> call p4(11);
Query OK, 0 rows affected (0.00 sec)


#if else end if
mysql> drop procedure if exists p5;
mysql> create procedure p5( in num int(2))
    -> begin
    ->     if num  is null then
    ->         select id,name,shell from db9.user where id =2;
    ->     else
    ->         select id,name,shell from db9.user where id<=num;
    ->     end if;
    -> end
    -> //
mysql> delimiter ;

mysql> set @x=null;
mysql> call p5(@x);
+----+------+---------------+
| id | name | shell         |
+----+------+---------------+
|  2 | bin  | /sbin/nologin |
+----+------+---------------+

mysql> call p5(2);
+----+------+---------------+
| id | name | shell         |
+----+------+---------------+
|  1 | tom  | /bin/bash     |
|  2 | bin  | /sbin/nologin |
+----+------+---------------+

循环结构

while 条件式循环

反复测试条件,只要成立就执行命令序列

while 条件判断 do
    循环体
    .. ..
end while;

示例

# 输出1-5
mysql> delimiter //
mysql> create procedure p10()
    -> begin 
    -> declare i int(2);
    -> set i=1;
    -> while i<=5 do
    -> select i;
    -> set i=i+1;
    -> end while;
    -> end
    -> //
mysql> delimiter ;
mysql> call p10();
+------+
| i    |
+------+
|    1 |
+------+

+------+
| i    |
+------+
|    2 |
+------+

+------+
| i    |
+------+
|    3 |
+------+

+------+
| i    |
+------+
|    4 |
+------+

+------+
| i    |
+------+
|    5 |
+------+


#输出 uid 为偶数 的 id,name,uid 
mysql> drop procedure if exists p6;
mysql> delimiter //
mysql> create procedure p6()
    -> begin
    -> declare i int(2);
    -> declare j int(2);
    -> declare z int(2);
    #查看表总行数 行数 存入 i
    -> select count(id) into i from db9.user; 
    -> 
    # id 从1开始 放入变量j 每次自加1
    -> set j=1;
    -> while j<=i do
    ->     select uid into z from db9.user where id=j;
    ->     if z%2=0 then
    ->         select id,name,uid from db9.user where id=j;
    -> end if;
    -> set j=j+1;
    -> end while;
    -> end
    -> //
mysql> delimiter ;

mysql> call p6();
+----+------+------+
| id | name | uid  |
+----+------+------+
|  1 | tom  |    0 |
+----+------+------+
1 row in set (0.00 sec)

+----+--------+------+
| id | name   | uid  |
+----+--------+------+
|  3 | daemon |    2 |
+----+--------+------+
1 row in set (0.00 sec)

+----+------+------+
| id | name | uid  |
+----+------+------+
|  5 | lp   |    4 |
+----+------+------+
1 row in set (0.00 sec)

+----+----------+------+
| id | name     | uid  |
+----+----------+------+
|  7 | shutdown |    6 |
+----+----------+------+
1 row in set (0.00 sec)

+----+------+------+
| id | name | uid  |
+----+------+------+
|  9 | mail |    8 |
+----+------+------+
1 row in set (0.00 sec)

+----+-------+------+
| id | name  | uid  |
+----+-------+------+
| 11 | games |   12 |
+----+-------+------+
1 row in set (0.00 sec)

+----+------+------+
| id | name | uid  |
+----+------+------+
| 12 | ftp  |   14 |
+----+------+------+
1 row in set (0.00 sec)

+----+-----------------+------+
| id | name            | uid  |
+----+-----------------+------+
| 14 | systemd-network |  192 |
+----+-----------------+------+
1 row in set (0.00 sec)

+----+------+------+
| id | name | uid  |
+----+------+------+
| 17 | sshd |   74 |
+----+------+------+
1 row in set (0.00 sec)

+----+------+------+
| id | name | uid  |
+----+------+------+
| 19 | ntp  |   38 |
+----+------+------+
1 row in set (0.00 sec)

+----+------+------+
| id | name | uid  |
+----+------+------+
| 21 | rpc  |   32 |
+----+------+------+
1 row in set (0.00 sec)

+----+-----------+-------+
| id | name      | uid   |
+----+-----------+-------+
| 23 | nfsnobody | 65534 |
+----+-----------+-------+

loop 死循环

loop
    循环体
    .. ..
end loop;

示例

#死循环 不按 ctrl + c 会一直执行.
drop procedure if exists p7;
delimiter //
create procedure p7()
begin
    declare i int(2);
    set i=1;
    loop
        select i;
        set i=i+1;
    end loop;
end
//
delimiter ;

call p7();

repeat 条件式循环

条件不成立时,执行循环,符合条件时,结束循环

repeat
    循环体
    .. ..
    #untill 后面不需要使用;
    until 条件判断
end repeat;

示例

mysql> drop procedure if exists p8;
mysql> delimiter //
mysql> create procedure p8()
    -> begin
    -> declare i int(2);
    -> set i=1;
    -> repeat
    -> select i;
    -> set i=i+1;
    -> until i=3
    -> end repeat;
    -> end
    -> //

mysql> delimiter ;
mysql> call p8();
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

+------+
| i    |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

控制循环

示例

drop procedure if exists p9;
delimiter //
create procedure p9()
begin
    declare i int(2);
    set i=0;
    # 为循环设置 lab1 标签名
    lab1:loop
        set i=i+1;
        if i=3 then
        #跳出本次循环,继续下一次循环
        ITERATE lab1;
        end if;
         if i=5 then 
         #结束循环执行
        LEAVE lab1;
        end if; 
        select i;       
    end loop;
end
//
delimiter ;