# 存储过程和函数

是事先经过编译并存储在数据库中的一段sql语句的集合，调用后能减少数据在数据库和应用服务器只见的传输
存储过程不需要返回值，函数需要返回值
存储过程的参数可以使用in/out/inout
函数只能用in
相关操作需要对应权限
修改需要执行alter
demo

-- 为了区分存储过程中的分号 ; -> change to $$ or anything u want
mysql> delimiter $$
mysql> create procedure test_p(in p_1 int,in p_2 int) 
READS SQL DATA 
BEGIN 
SELECT *  FROM t1; -- don't forget ' ; '
end $$
mysql> delimiter ; -- set reback to ;

-- use it
mysql> call test_p(1,1);

-- delete it
mysql> drop procedure test_p;

-- scan it
mysql> show procedure status like 'test_p' \G;
*************************** 1. row ***************************
                  Db: yiki
                Name: test_p
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2020-02-21 15:00:20
             Created: 2020-02-21 15:00:20
       Security_type: DEFINER
             Comment: 
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> show create procedure test_p \G;
*************************** 1. row ***************************
           Procedure: test_p
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `test_p`(in p_1 int,in p_2 int)
    READS SQL DATA
BEGIN SELECT *  FROM t1; end
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

其他：写存储过程像编程，那些语法就不细说了，用到再查吧