15 MySQL 存储过程

作者: Kokoronashi | 来源:发表于2019-02-13 23:08 被阅读17次

MySQL 存储过程

[toc]

存储过程概述

存储过程介绍

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

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

存储过程优点

提高性能
可减轻网络负担
可以防止对表的直接访问
避免重复编写 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命令

存储过程进阶

变量类型

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

名称	说明
会话变量	会话变量和全局变量叫系统变量使用 set 命令定义
全局变量	全局变量的修改会影响到整个服务器,但是对会话变量的修改,只会影响当前的会话
用户变量	在客户端连接到数据库服务的整个过程中都是有效的.当当前连接断开后所有用户变量失效. 定义 set @变量名=值; 输出 select @变量名;
局部变量	存储过程中的 begin end,其有效范围仅限于该语句块中,语句块执行完毕后,变量失效. declare专门用来定义局部变量局部变量和参数变量调用时,变量名前不需要加@

示例

#用户变量 自定义变量
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 |
+-------+

参数类型

语法格式

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

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

参数格式

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

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

类型

关键字	名称	描述
in	输入型参数 (默认类型)	作用是给存储过程传值,必须在调用存储过程时赋值,在存储过程中该参数的值不允许修改;默认类型是in
out	输出型参数	该值可在存储过程内部被改变,并可返回.
inout	输入/输出型参数	调用时指定,并且可被改变和返回

示例

>>>>>>>>>>>>>> 输入参数 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)

算术运算

运算符号

符号	描述	示例
+	加法运算	`SET @var1=2+2;` 4
-	减法运算	`SET @var2=3-2;` 1
*	惩罚运算	`SET @var3=3*2;` 6
/	除法运算	`SET @var4=10/3;` 3.333333333
DIV	整出运算	`SET @var5=10 DIV 3;` 3
%	取模	`SET @var6=10%3;` 1

示例

#加法
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 |
+------+

流程控制

条件测试

数值比较

类型	说明
`=`	等于
`> >=`	大于大于或等于
`< <=`	小于小于或等于
`!=`	不等于
BETWEEN ... AND ..	在 .. 与 .. 之间

示例

逻辑比较

类型	用途
`OR AND !`	逻辑或逻辑与逻辑非
`IN ... NOT IN ...`	在 .. 范围内不在 .. 范围内
`IS NULL`	字段的值为空
`IS NOT NULL`	字段的值不为空
`LIKE`	模糊匹配
`REGEXP`	正则匹配

示例

选择结构

语法

# 格式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)

控制循环

选项	说明	shell相似
LEAVE 标签名	结束循环的执行	break
ITERATE 标签名	结束当前循环,并执行下一次循环	continue

示例

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 ;

15 MySQL 存储过程

MySQL 存储过程

存储过程概述

存储过程介绍

存储过程优点

基本使用

创建存储过程

查看存储过程

方法1

方法2

调用 存储过程

删除存储过程

示例

注意

存储过程进阶

变量类型

示例

参数类型

语法格式

类型

示例

算术运算

运算符号

示例

流程控制

条件测试

数值比较

示例

逻辑比较

示例

选择结构

语法

示例

循环结构

while 条件式循环

示例

loop 死循环

示例

repeat 条件式循环

示例

控制循环

示例

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

数据库

调用存储过程