MySQL存储过程

目录

• 一、存储过程概述
• 二、存储过程的优点
• 三、自定义存储过程示例
• 四、带while循环的存储过程
• 五、带if判断的存储过程
• 六、带有case的存储过程
• 七、将存储过程传出到全局环境变量
• 八、其他关于存储过程的操作语句
• 九、附加：如何复制表
  • ① 方法一
  • ② 方法二

一、存储过程概述

存储过程是一组为了完成特定功能的SQL语句集合。使用存储过程的目的就是将常用或复杂的工作预先用SQL语句写好并用一个指定名称存储起来，这个过程编译和优化后存储在数据库服务器中，因此称为存储过程。当以后需要数据库提供与定义好的存储过程的功能相同的服务时，只需要调用“CALL 存储过程名称”即可使用相应的功能！

一个存储过程是一个可编程的函数，它在数据库中创建并保存，一般由SQL语句和一些特殊的控制结构组成！

当希望在不同的应用程序或平台上执行相应的特定功能时，存储过程就变得尤为合适！

二、存储过程的优点

• 封装性：存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句，并且DBA可以随时对存储过程进行修改，而不会影响到调用它的应用程序源代码；
• 可增强：SQL语句的功能和灵活性、存储过程可以用流程控制语句编写，有很强的灵活性，可以完成复杂的判断和复杂的运算；
• 可减少网络流量：由于存储过程时在服务端运行的，且执行速度快，因此当客户计算上调用该存储过程时，网络中传送的只是调用该语句，从而降低网络负载；
• 高性能：存储过程执行一次后，产生的二进制代码就驻留在缓冲区，在以后的调用中，只需要从缓冲区执行二进制代码即可，从而提供了系统的效率和性能；
• 提高数据库的安全性和数据的完整性：使用存储过程可以完成所有数据库操作，并且可以通过编程的方式控制数据库信息访问的权限；

三、自定义存储过程示例

mysql> select * from fruits;
#查询一条SQL语句
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
| a1   |  101 | apple      |    5.20 |
| a2   |  103 | apricot    |    2.20 |
| b1   |  101 | blackberry |   10.20 |
| b2   |  104 | berry      |    7.60 |
| b5   |  107 | xxxx       |    3.60 |
| bs1  |  102 | orange     |   11.20 |
| bs2  |  105 | melon      |    8.20 |
| c0   |  101 | cherry     |    3.20 |
| l2   |  104 | lemon      |    6.40 |
| m1   |  106 | mango      |   15.70 |
| m2   |  105 | xbabay     |    2.60 |
| m3   |  105 | xxtt       |   11.60 |
| o2   |  103 | coconut    |    9.20 |
| t1   |  102 | banana     |   10.30 |
| t2   |  102 | grape      |    5.30 |
| t4   |  107 | xbababa    |    2.60 |
+------+------+------------+---------+
mysql> delimiter //
#更改其默认的分隔符为“//”，也可以是其他任意符号，只要不是默认的“;”就行
mysql> create procedure test()     #定义存储过程test
    -> begin
    -> select * from fruits;       #将sql语句写入存储过程，可以写多条
    -> end// 
mysql> delimiter ;        #更改为默认的分割符
mysql> call test();       #调用刚才定义的test存储过程
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
| a1   |  101 | apple      |    5.20 |
| a2   |  103 | apricot    |    2.20 |
| b1   |  101 | blackberry |   10.20 |
| b2   |  104 | berry      |    7.60 |
| b5   |  107 | xxxx       |    3.60 |
| bs1  |  102 | orange     |   11.20 |
| bs2  |  105 | melon      |    8.20 |
| c0   |  101 | cherry     |    3.20 |
| l2   |  104 | lemon      |    6.40 |
| m1   |  106 | mango      |   15.70 |
| m2   |  105 | xbabay     |    2.60 |
| m3   |  105 | xxtt       |   11.60 |
| o2   |  103 | coconut    |    9.20 |
| t1   |  102 | banana     |   10.30 |
| t2   |  102 | grape      |    5.30 |
| t4   |  107 | xbababa    |    2.60 |
+------+------+------------+---------+

通过上面的例子可以看出，存储过程是类似于一个shell脚本的，存放的是一些sql语句的集合，当然，它同样有一些判断、循环等语句。

四、带while循环的存储过程

下面的例子是存储过程借助while循环来计算1+2+3...+100的结果是多少。

mysql> delimiter //                 #改变默认的截断符为“//”
mysql> create procedure test1()     #创建存储过程
    -> begin                        #存储过程开始
    -> declare n int;               #定义变量名称
    -> declare summary int;         #定义变量名称
    -> set n=0;                     #设置变量的初始值为0
    -> set summary=0;               #设置变量的初始值为0
    -> while n<=100                 #当n小于或等于100时，执行以下操作
    -> do
    -> set summary=summary+n;       
    -> set n=n+1;                   
    -> end while;                   #循环结束
    -> select summary;              #查询summary的值
    -> end //                       #存储过程结束
mysql> delimiter ;                  #将截断符改回默认的
mysql> call test1();                #调用存储过程 
+---------+
| summary |
+---------+
|    5050 |
+---------+

五、带if判断的存储过程

以下实现的是如果传参的值大于或等于10，则执行else下面的SQL语句，如果传参的值小于10，则执行then下面的SQL语句。

mysql> delimiter //
mysql> create procedure test2(in num int)
    -> begin
    -> if num < 10 then
    -> select * from fruits  where f_price<10;
    -> else
    -> select * from fruits  where f_price>=10;
    -> end if;
    -> end //
mysql> delimiter ;
mysql> call test2(9);                 #定义传入值为9
+------+------+---------+---------+
| f_id | s_id | f_name  | f_price |
+------+------+---------+---------+
| a1   |  101 | apple   |    5.20 |
| a2   |  103 | apricot |    2.20 |
| b2   |  104 | berry   |    7.60 |
| b5   |  107 | xxxx    |    3.60 |
| bs2  |  105 | melon   |    8.20 |
| c0   |  101 | cherry  |    3.20 |
| l2   |  104 | lemon   |    6.40 |
| m2   |  105 | xbabay  |    2.60 |
| o2   |  103 | coconut |    9.20 |
| t2   |  102 | grape   |    5.30 |
| t4   |  107 | xbababa |    2.60 |
+------+------+---------+---------+
mysql> call test2(10);                 #定义传入值为10
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
| b1   |  101 | blackberry |   10.20 |
| bs1  |  102 | orange     |   11.20 |
| m1   |  106 | mango      |   15.70 |
| m3   |  105 | xxtt       |   11.60 |
| t1   |  102 | banana     |   10.30 |
+------+------+------------+---------+

六、带有case的存储过程

该存储过程实现结果为：当传入的值为偶数时，输出fruits表中s_id列为偶数的行，如果传入的值为奇数，输出s_id列为奇数的行，否则输出空。

mysql> delimiter //
mysql> create procedure test3(in num int)
    -> begin
    -> case num%2
    -> when 0 then
    -> select * from fruits  where s_id%2=0;
    -> when 1 then
    -> select * from fruits  where s_id%2=1;
    -> else
    -> select null;
    -> end case;
    -> end //
mysql> delimiter ;
mysql> call test3(4);           #传入偶数测试
+------+------+--------+---------+
| f_id | s_id | f_name | f_price |
+------+------+--------+---------+
| b2   |  104 | berry  |    7.60 |
| bs1  |  102 | orange |   11.20 |
| l2   |  104 | lemon  |    6.40 |
| m1   |  106 | mango  |   15.70 |
| t1   |  102 | banana |   10.30 |
| t2   |  102 | grape  |    5.30 |
+------+------+--------+---------+
mysql> call test3(3);           #传入奇数测试
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
| a1   |  101 | apple      |    5.20 |
| a2   |  103 | apricot    |    2.20 |
| b1   |  101 | blackberry |   10.20 |
| b5   |  107 | xxxx       |    3.60 |
| bs2  |  105 | melon      |    8.20 |
| c0   |  101 | cherry     |    3.20 |
| m2   |  105 | xbabay     |    2.60 |
| m3   |  105 | xxtt       |   11.60 |
| o2   |  103 | coconut    |    9.20 |
| t4   |  107 | xbababa    |    2.60 |
+------+------+------------+---------+

七、将存储过程传出到全局环境变量

mysql> delimiter //
mysql> create procedure test4(out num float)
    -> begin
    -> select max(f_price) into num from fruits ;
    -> end //
mysql> delimiter ;
mysql> call test4(@num);
mysql> select @num;            #由于最大的值是浮点数，所以会这样
+--------------------+
| @num               |
+--------------------+
| 15.699999809265137 |
+--------------------+

八、其他关于存储过程的操作语句

mysql> help procedure;         #查看帮助
topics:
   ALTER PROCEDURE            #修改procedure，一般用不到，需要修改的话，直接删除再创建即可
   CREATE PROCEDURE           #创建procedure
   DROP PROCEDURE             #删除procedure
   PROCEDURE ANALYSE         
   SELECT
   SHOW
   SHOW CREATE PROCEDURE  存储过程名    #查看某个存储过程的详细信息

九、附加：如何复制表

① 方法一

like方法能一模一样的将一个表的结果复制生成一个新表，包括复制表的备注、索引、主键外键、存储引擎等。但是不包括表数据，如下：

mysql> create table new_fruits like fruits;
mysql> desc new_fruits;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| f_id    | char(10)     | NO   | PRI | NULL    |       |
| s_id    | int(11)      | NO   |     | NULL    |       |
| f_name  | char(255)    | NO   |     | NULL    |       |
| f_price | decimal(8,2) | NO   |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+

② 方法二

select的方法值复制字段属性，其它的主键、索引、表备注、存储引擎都没有复制。如下：

mysql> create table new_fruits_select select * from fruits;
mysql> select * from new_fruits_select;
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
| a1   |  101 | apple      |    5.20 |
| a2   |  103 | apricot    |    2.20 |
| b1   |  101 | blackberry |   10.20 |
| b2   |  104 | berry      |    7.60 |
| b5   |  107 | xxxx       |    3.60 |
| bs1  |  102 | orange     |   11.20 |
| bs2  |  105 | melon      |    8.20 |
| c0   |  101 | cherry     |    3.20 |
| l2   |  104 | lemon      |    6.40 |
| m1   |  106 | mango      |   15.70 |
| m2   |  105 | xbabay     |    2.60 |
| m3   |  105 | xxtt       |   11.60 |
| o2   |  103 | coconut    |    9.20 |
| t1   |  102 | banana     |   10.30 |
| t2   |  102 | grape      |    5.30 |
| t4   |  107 | xbababa    |    2.60 |
+------+------+------------+---------+