1、什么是存储过程？
存储过程是一组为了完成特定功能的SQL语句集合。使用存储过程的目的是将常用或复杂的工作预先用SQL语句写好并用一个指定名称存储起来，这个过程经编译和优化后存储在数据库服务器中，因此成为存储过程。当以后需要数据库提供与定义好的存储过程的功能相同的服务时，只需要调用“CALL 存储过程名字”即可自动完成。
一个存储过程是一个可编程的函数，它在数据库中创建并保存，一般由 SQL 语句和一些特殊的控制结构组成。
当希望在不同的应用程序或平台上执行相同的特定功能时，存储过程尤为合适。
2、存储过程有哪些优点？
- 封装性：存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的 SQL 语句，并且DBA可以随时对存储过程进行修改，而不会影响到调用它的应用程序源代码。
- 可增强：SQL 语句的功能和灵活性 存储过程可以用流程控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
- 可减少网络流量：由于存储过程是在服务器端运行的，且执行速度快，因此当客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而可降低网络负载。
- 高性能：存储过程执行一次后，产生的二进制代码就驻留在缓冲区，在以后的调用中，只需要从缓冲区中执行二进制代码即可，从而提高了系统的效率和性能。
- 提高数据库的安全性和数据的完整性：使用存储过程可以完成所有数据库操作，并且可以通过编程的方式控制数据库信息访问的权限。
3、自定义存储过程举例
#查询一条sql语句
mysql> select * from t1;
+------+------+------------+---------+
| 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 |
+------+------+------------+---------+
16 rows in set (0.00 sec)
mysql> delimiter // # 更改其默认的分隔符为“//”，也可以是其他任意符号，只要不是默认的“;”就行
mysql> create procedure test() # 定义存储过程test
-> begin
-> select * from t1; # 将sql语句写入存储过程，可以写多条
-> end //
mysql> delimiter ; # 改回默认的分隔符
mysql> call 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 |
+------+------+------------+---------+
16 rows in set (0.00 sec)通过上面的例子可以看出，存储过程是类似于一个shell脚本的，存放的是一些sql语句的集合，当然，它同样有一些判断、循环等语句，如下：
4、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; # summary就+n
-> set n=n+1; # 然后n+1
-> end while; # 循环结束
-> select summary; # 查询summary的值
-> end // # 存储过程结束
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ; # 将截断符改回默认的
mysql> call test1(); # 调用存储过程
+---------+
| summary |
+---------+
| 5050 |
+---------+
1 row in set (0.01 sec)5、带有if判断的存储过程
以下实现的是如果传参的值大于或等于10，则执行else下面的SQL语句，如果传参的值小于10，则执行then下面的SQL语句
mysql> delimiter //
mysql> create procedure test3(in num int)
-> begin
-> if num < 10 then
-> select * from t1 where f_price<10;
-> else
-> select * from t1 where f_price>=10;
-> end if;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call test3(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 |
+------+------+---------+---------+
11 rows in set (0.01 sec)
mysql> call test3(10); # 传入值w为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 |
+------+------+------------+---------+
5 rows in set (0.00 sec)
6、带有case的存储过程
该存储过程实现结果为：当传入的值为偶数时，输出t1表中s_id列为偶数的行，如果传入的值为奇数，输出s_id列为奇数的行，否则输出空
mysql> delimiter //
mysql> create procedure test4(in num int)
-> begin
-> case num%2
-> when 0 then
-> select * from t1 where s_id%2=0;
-> when 1 then
-> select * from t1 where s_id%2=1;
-> else
-> select null;
-> end case;
-> end//
mysql> delimiter ;
mysql> call test4(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 |
+------+------+--------+---------+
6 rows in set (0.00 sec)
mysql> call test4(1); # 传入奇数
+------+------+------------+---------+
| 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 |
+------+------+------------+---------+
10 rows in set (0.00 sec)7、将存储过程传出到全局环境变量
mysql> delimiter //
mysql> create procedure test6(out num float)
-> begin
-> select max(f_price) into num from t1;
-> end //
mysql> delimiter ;
mysql> call test6(@num);
Query OK, 1 row affected (0.00 sec)
mysql> select @num;
+--------------------+
| @num |
+--------------------+
| 15.699999809265137 | # 由于最大的值是浮点数，所以会这样
+--------------------+
1 row in set (0.00 sec)8、其他关于存储过程的操作语句
mysql> help procedure; # 查看帮助
topics:
ALTER PROCEDURE # 修改procedure，一般用不到，需要修改的话，直接删除再创建即可
CREATE PROCEDURE # 创建procedure
DROP PROCEDURE # 删除procedure
PROCEDURE ANALYSE
SELECT
SHOW
SHOW CREATE PROCEDURE 存储过程名 # 查看某个存储过程的详细信息9、附加：如何复制表
方法1：like方法能一模一样的将一个表的结果复制生成一个新表，包括复制表的备注、索引、主键外键、存储引擎等。但是不包括表数据，如下：
mysql> create table new_t1 like t1;
Query OK, 0 rows affected (0.01 sec)
mysql> desc new_t1;
+---------+--------------+------+-----+---------+-------+
| 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 | |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)方法2： select的方法值复制字段属性，其它的主键、索引、表备注、存储引擎都没有复制。如下：
mysql> create table new_t1_2 select * from t1;
Query OK, 16 rows affected (0.01 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql> select * from new_t1_2;
+------+------+------------+---------+
| 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 |
+------+------+------------+---------+
16 rows in set (0.00 sec)10、删除存储过程
mysql> drop procedure test4;