• MySQL——存储过程


    核心知识点:

    1.什么存储过程?它都有哪些优点?

    2.存储过程的语法和参数?

    3.存储过程有哪些操作?

    4.存储过程常用的控制语句?

    一、存储过程概论

    SQL语句需要先编译然后执行,而存储过程是一组为了完成特定功能的SQL语句集

    经编译后存储在数据库中,用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它。

    存储过程是可编程的函数在数据库中创建并保存,可以由SQL语句和控制结构组成。

    当想要在不同的应用程序或平台上执行相同的函数,或者封装特定功能时,存储过程是非常有用的。

    数据库中的存储过程可以看作是函数,就是对命令的封装,你可以多次调用它,并传递不同的参数。

    存储过程是数据库的一个重要的功能,MySQL5.0以前并不支持存储过程,这使得MySQL在应用上大打折扣,

    好在MySQL5.0开始支持存储过程,这样即可以大大提高数据库的处理速度,同时也可以提高数据库编程的灵活性。

    虽然存储过程有时候很好用,但是千万不要滥用,因为这会给以后的程序开发带来意向不到的问题。

    二、存储过程的优点

    (1)增强SQL语言的功能和灵活性

    存储过程可以用控制语句来编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算。

    (2)标准组件式编程

    存储过程被创建后,可以在程序中被多次调用,而不必重新编写该存储过程的SQL语句。

    而且数据库专业人士可以随时对存储过程进行修改,对应用程序源码毫无影响。

    (3)较快的执行速度

    存储过程要比批处理(同一个代码重复执行多次)的执行速度快很多,因为存储过程是预编译的。

    在首次运行一个存储过程时,优化器对其进行分析优化,并且给出最终在系统表中的执行计划。

    而批处理的SQL语句在每次运行时都要进行编译和优化,速度相对要慢一些。

    (4)较少网络流量

    针对同一个数据库对象的操作(如查询、修改),如果这一操作所涉及的SQL语句被组织进存储进程,那么当在客户端计算机上调用该存储过程时,网络中传送的只是该调用该语句,从而大大减少网络流量并降低了网络负载。

    (5)作为一种安全机制来充分利用

    通过对执行某一存储过程的权限进行限制,能够实现对相应的数据的访问权限的限制,避免了非授权用户对数据的访问,保证了数据的安全。

    三、认识存储过程

    现在我有一张表,其中有三个字段完全是空,需要填值。如果我一条一条的输入,每个字段值都需要一条SQL语句,感觉很麻烦,此时我们就可以使用存储过程,一次创建,多次使用。

    #有三个字段需要填值,全部使用SQL语句很繁琐
    mysql> select * from student_score;
        -> //
    +----+--------+-------+-------+--------+
    | id | name   | linux | mysql | python |
    +----+--------+-------+-------+--------+
    |  1 | 科比   |  NULL |  NULL |   NULL |
    |  2 | 毛线   |  NULL |  NULL |   NULL |
    |  3 | 黄鱼   |  NULL |  NULL |   NULL |
    |  4 | 子豪   |  NULL |  NULL |   NULL |
    |  5 | 星爷   |  NULL |  NULL |   NULL |
    |  6 | 代鹏   |  NULL |  NULL |   NULL |
    |  7 | 子栋   |  NULL |  NULL |   NULL |
    |  8 | 周攀   |  NULL |  NULL |   NULL |
    |  9 | 大爷   |  NULL |  NULL |   NULL |
    | 10 | 小鸟   |  NULL |  NULL |   NULL |
    +----+--------+-------+-------+--------+
    10 rows in set (0.00 sec)
    有三个字段需要填写
    #存储过程
    mysql> delimiter //
    mysql> create procedure auto_add(in a int,in b int,in c int,in d int)
        -> begin
        -> update student_score set linux = a where id = d;
        -> update student_score set mysql = b where id = d;
        -> update student_score set python = c where id = d;
        -> end;
        -> //
    Query OK, 0 rows affected (0.07 sec)
    创建一个存储过程
    mysql> call auto_add(45,67,56,2)//
    Query OK, 1 row affected (0.09 sec)
    
    mysql> call auto_add(89,34,67,3)//
    Query OK, 1 row affected (0.01 sec)
    
    mysql> call auto_add(45,78,90,4)//
    Query OK, 1 row affected (0.07 sec)
    
    mysql> call auto_add(56,78,56,4)//
    Query OK, 1 row affected (0.18 sec)
    
    mysql> call auto_add(56,90,78,5)//
    Query OK, 1 row affected (0.06 sec)
    
    mysql> call auto_add(89,96,87,6)//
    Query OK, 1 row affected (0.07 sec)
    
    mysql> call auto_add(67,76,45,7)//
    Query OK, 1 row affected (0.01 sec)
    
    mysql> call auto_add(34,56,45,8)//
    Query OK, 1 row affected (0.07 sec)
    
    mysql> call auto_add(56,54,60,9)//
    Query OK, 1 row affected (0.06 sec)
    
    mysql> call auto_add(78,75,67,10)//
    Query OK, 1 row affected (0.01 sec)
    调用函数,填制
    mysql> select * from student_score;
        -> //
    +----+--------+-------+-------+--------+
    | id | name   | linux | mysql | python |
    +----+--------+-------+-------+--------+
    |  1 | 科比   |    63 |    33 |     76 |
    |  2 | 毛线   |    45 |    67 |     56 |
    |  3 | 黄鱼   |    89 |    34 |     67 |
    |  4 | 子豪   |    56 |    78 |     56 |
    |  5 | 星爷   |    56 |    90 |     78 |
    |  6 | 代鹏   |    89 |    96 |     87 |
    |  7 | 子栋   |    67 |    76 |     45 |
    |  8 | 周攀   |    34 |    56 |     45 |
    |  9 | 大爷   |    56 |    54 |     60 |
    | 10 | 小鸟   |    78 |    75 |     67 |
    +----+--------+-------+-------+--------+
    10 rows in set (0.00 sec)
    查看最终的结果

    由上面的例子可以看出,使用存储过程可以简化操作的过程。

    补充:分隔符

    为什么我使用双反斜杠?

    MySQL默认以’“;”作为分隔符,如果没有声明分隔符,则编译器会把存储过程当成SQL语句进行处理,因此编译过程会报错,所以事先要用“DELIMITER //”声明当前段分隔符,让编译器把两个’//‘之间的内容当作存储过程的代码。

    四、存储过程的创建

    语法:

    CREATE PROCEDURE 过程名([IN|OUT|INOUT] 参数名 参数类型,[IN|OUT|INOUT] 参数名 参数类型....) BEGIN 操作 END

    参数:

    存储过程根据需要可能会有输入、输出、输入输出参数,如果有多个参数用’,‘分隔开。

    MySQL存储过程的参数用在存储过程的定义,共有三种参数类型:IN、OUT、INOUT

    (1)IN:参数的值必须在调用存储过程时指定,在存储过程或修改该参数的不能返回,为默认值

    mysql> delimiter //  #声明分隔符
    mysql> create PROCEDURE in_param(IN p_in int)   #创建存储过程
        -> BEGIN   #开始
        -> SELECT p_in;   #打印变量,类似于print
        -> SET p_in = 2;   #设置变量大小
        -> SELECT p_in;   #打印
        -> END;
        -> //
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> set @p_in = 1//   #赋值参数
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> call in_param(@p_in);   #执行存储过程
        -> //
    +------+
    | p_in |
    +------+
    |    1 |
    +------+
    1 row in set (0.00 sec)
    
    +------+
    | p_in |
    +------+
    |    2 |
    +------+
    1 row in set (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select @p_in//   #由于存储过程的中的值不能返回,所以没有改变变量的值
    +-------+
    | @p_in |
    +-------+
    |     1 |
    +-------+
    1 row in set (0.00 sec)
    使用IN参数的过程讲解

    (2)OUT:该值可在存储过程内部被改变,并可返回

    mysql> CREATE PROCEDURE out_param(OUT p_out int)
        -> BEGIN
        -> SELECT p_out;
        -> SET p_out = 2;
        -> SELECT p_out;
        -> END;
        -> //
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> set @p_out = 1
        -> //
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> CALL out_param(@p_out) //
    +-------+
    | p_out |
    +-------+
    |  NULL |
    +-------+
    1 row in set (0.00 sec)   #还没有值,而外面的值又没有传进来
    
    +-------+
    | p_out |
    +-------+
    |     2 |
    +-------+
    1 row in set (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SELECT @p_out //   #内部的值可以返回
    +--------+
    | @p_out |
    +--------+
    |      2 |
    +--------+
    1 row in set (0.00 sec)
    使用OUT参数的过程讲解

    (3)INOUT:调用时指定,并且可被改变和返回

    mysql> CREATE PROCEDURE inout_param(INOUT p_inout int)
        -> BEGIN
        -> SELECT p_inout;
        -> SET p_inout = 2;
        -> SELECT p_inout;
        -> END;
        -> //
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SET @p_inout = 1;
        -> //
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> CALL inout_param(@p_inout) //   #外面的值既可以传进来
    +---------+
    | p_inout |
    +---------+
    |       1 |
    +---------+
    1 row in set (0.00 sec)
    
    +---------+
    | p_inout |
    +---------+
    |       2 |
    +---------+
    1 row in set (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SELECT @p_inout //   #里面的值也能够返回
    +----------+
    | @p_inout |
    +----------+
    |        2 |
    +----------+
    1 row in set (0.00 sec)
    使用INOUT参数的过程详解

    五、存储过程的操作

    1.存储过程的调用

    用CALL和你过程名以及一个括号,括号里面根据需要,加入参数。

    mysql> CALL out_param(@p_out) //  #相当于程序中代码的执行
    +-------+
    | p_out |
    +-------+
    |  NULL |
    +-------+
    1 row in set (0.00 sec)
    
    +-------+
    | p_out |
    +-------+
    |     2 |
    +-------+
    1 row in set (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    存储过程的调用

    2.存储过程的查询

    查询某一个库中有那些存储过程有多种方法

    (1)SELECT name FROM mysql.proc WHERE db = '数据库名';

    mysql> select name from mysql.proc where db = 'class_7' //
    +-------------+
    | name        |
    +-------------+
    | auto_add    |
    | inout_param |
    | in_param    |
    | out_param   |
    +-------------+
    4 rows in set (0.00 sec)
    只查询存储过程名

    (2)SELECT routine_name FROM infomation_schema.routines WHERE routine_schema='数据库名';

    mysql> select routine_name from information_schema.routines 
        -> where routine_schema = 'class_7' //
    +--------------+
    | routine_name |
    +--------------+
    | auto_add     |
    | inout_param  |
    | in_param     |
    | out_param    |
    +--------------+
    4 rows in set (0.00 sec)
    只查询存储过程的名字

    (3)SHOW PROCEDURE STATUS WHERE db ='数据库名';

    mysql> show procedure status where db = 'class_7' //
    +---------+-------------+-----------+----------------+---------------------+---------------------+--------------------+
    | Db      | Name        | Type      | Definer        | Modified            |collation_connection | Database Collation |
    +---------+-------------+-----------+----------------+---------------------+---------------------+--------------------+
    | class_7 | auto_add    | PROCEDURE | root@localhost | 2017-12-12 16:21:34 |utf8_general_ci      | utf8_general_ci    |
    | class_7 | inout_param | PROCEDURE | root@localhost | 2017-12-12 18:59:33 |utf8_general_ci      | utf8_general_ci    |
    | class_7 | in_param    | PROCEDURE | root@localhost | 2017-12-12 18:48:34 |utf8_general_ci      | utf8_general_ci    |
    | class_7 | out_param   | PROCEDURE | root@localhost | 2017-12-12 18:53:44 |utf8_general_ci      | utf8_general_ci    |
    +---------+-------------+-----------+----------------+---------------------+---------------------+--------------------+
    4 rows in set (0.00 sec)
    查询存储过程的详细信息

    (4)SHOW CREATE PROCEDURE 数据库.存储过程名

    mysql> show create procedure class_7.auto_add //
    +-----------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
    | Procedure | sql_mode | Create Procedure                                                                                                                                                                                                                                          | character_set_client | collation_connection | Database Collation |
    +-----------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
    | auto_add  |          | CREATE DEFINER=`root`@`localhost` PROCEDURE `auto_add`(in a int,in b int,in c int,in d int)
    begin
    update student_score set linux = a where id = d;
    update student_score set mysql = b where id = d;
    update student_score set python = c where id = d;
    end | utf8                 | utf8_general_ci      | utf8_general_ci    |
    +-----------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
    1 row in set (0.00 sec)
    查询某一个具体的存储过程的详细信息

    3.存储过程的删除

    DROP PROCEDURE 存储过程名;

    从MySQL上的表格中删除一个或多个存储过程。

    还可以对存储过程进行修改,由于不常用,这里不做介绍。

    六、存储过程的控制语句

    1.变量作用域

    内部变量在其作用域范围内享有更高的优先权,当执行到end时,内部变量消失。

    在存储过程外部再也找不到这个内部变量,但是可以通过out参数或者将其值指派给会话变量来保存其值。

    mysql> delimiter //
    mysql> create procedure proc()
        -> begin
        -> declare x1 varchar(5) default 'outer';
        -> begin
        -> declare x1 varchar(5) default 'inner';
        -> select x1;
        -> end;    #end结束的时候,x1 = inner不能再使用
        -> select x1;
        -> end;
        -> //
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> CALL proc()
        -> //
    +-------+
    | x1    |
    +-------+
    | inner |
    +-------+
    1 row in set (0.00 sec)
    
    +-------+
    | x1    |
    +-------+
    | outer |
    +-------+
    1 row in set (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    变量作用域过程讲解

    2.if-then-else语句

    mysql> delimiter //
    mysql> create procedure proc3(IN par int)
        -> begin
        -> declare var int;
        -> set var = par + 1;
        -> if var = 0 THEN
        -> insert into judge(state) values('空值');
        -> end if;    #结束这个判断
        -> if par = 0 THEN
        -> insert into judge(state) values('0');
        -> else
        -> insert into judge(state) values('真值');
        -> end if;
        -> end;   #与begin对照
        -> //
    Query OK, 0 rows affected (0.00 sec)
    if-then-else过程解析

    3.while循环

    mysql> create proceduce proc6(inout n int)
        -> begin
        -> declare sum int default 0;
        -> declare i int default 0;
        -> while i <= n DO   #do开始循环
        -> set sum = sum + i;
        -> set i = i + 1;
        -> end while;   #endwhile结束循环
        -> select sum;    #打印和
        -> set n = sum;   #传参,让外界可以接收这个值
        -> end;   #与begin一起表示过程体
        -> //
    mysql> set @n = 100;   #外界用户变量必须以@开头
        -> //
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> call proc10(@n)//   #执行函数
    +------+
    | sum  |
    +------+
    | 5050 |
    +------+
    1 row in set (0.00 sec)
    
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select @n //    #打印变量值
    +------+
    | @n   |
    +------+
    | 5050 |
    +------+
    1 row in set (0.00 sec)
    while循环

    其实,控制语句还有loop...end loop、repeat...end repeat、case-when-then-else等语句

  • 相关阅读:
    python datetime unix时间戳以及字符串时间戳转换
    Linux下Shell的for循环语句
    分布式学习最佳实践:从分布式系统的特征开始(附思维导图)
    什么是分布式系统,如何学习分布式系统
    Spring Boot 之发送邮件
    v8是怎么实现更快的 await ?深入理解 await 的运行机制
    分布式=高并发=多线程
    半个月使用rust语言的体验
    Enter Query Mode Search Tricks Using Enter_Query Built-in in Oracle Forms
    How to Log Users Login and Logout Details Through Oracle Forms
  • 原文地址:https://www.cnblogs.com/yangmingxianshen/p/8029923.html
Copyright © 2020-2023  润新知