mysql存储过程、函数

• 一组预编译好的sql语句集合，理解成批处理语句。
• 优点：
  • 提高代码的重用性
  • 简化操作
  • 减少编译次数并且和数据库服务器连接的次数，提高效率

创建存储过程

• create procedure 存储过程名([参数模式] 参数名 参数类型)
• begin
  • 存储过程体
• end
  • 参数模式：
    • in：该参数可以作为输入，也就是该参数需要调用方传入值
    • out：该参数可以作为输出，也就是说该参数可以作为返回值
    • inout：该参数既可以作为输入也可以作为输出，也就是该参数需要在调用的时候传入值，又可以作为返回值
    • 参数模式默认为IN
    • 一个存储过程可以有多个输入、多个输出、多个输入输出参数

调用存储过程

• call 存储过程名称(参数列表);
• 注意：调用存储过程关键字是call

删除存储过程

• drop procedure [if exists] 存储过程名称;
  • 存储过程只能一个个删除，不能批量删除
  • if exists：表示存储过程存在的情况下删除

修改存储过程

• 存储过程不能修改，若涉及到修改的，可以先删除，然后重建

查看存储过程

• show create procedure 存储过程名称;
  • 可以查看存储过程详细创建语句
• 空参数列表

-- 创建存储过程
/*设置结束符为$*/
DELIMITER $
/*如果存储过程存在则删除*/
DROP PROCEDURE IF EXISTS procl;
/*创建存储过程procl*/
CREATE PROCEDURE procl()
  BEGIN
    INSERT INTO t_user VALUES (1,30,'啊啊啊');
    INSERT INTO t_user VALUES (2,50,'是是是');
  END $
/*将结束符置为;*/
DELIMITER ;
-- delimiter用来设置结束符，当mysql执行脚本的时候，遇到结束符的时候，会把结束符前面的所有语句作为一个整体运行
-- 存储过程中的脚本有多个sql，但是需要作为一个整体运行，所以此处用到了delimiter
-- mysql默认结束符是分号
/*调用存储过程*/
CALL procl();
/*参看添加效果*/
select * from t_user;
/*
+----+-----+---------------+
| id | age | name |
+----+-----+---------------+
| 1 | 30 | 啊啊啊 |
| 2 | 50 | 是是是 |
+----+-----+---------------+
2 rows in set (0.00 sec)
*/

• 带in参数的存储过程

/*设置结束符为$*/
DELIMITER $
/*如果存储过程存在则删除*/
DROP PROCEDURE IF EXISTS proc2;
/*创建存储过程proc2*/
CREATE PROCEDURE proc2(id int,age int,in name varchar(16))
  BEGIN
    INSERT INTO t_user VALUES (id,age,name);
  END $
/*将结束符置为;*/
DELIMITER ;
-- 调用存储过程
/*创建了3个自定义变量*/
SELECT @id:=3,@age:=56,@name:='小数点';
/*调用存储过程*/
CALL proc2(@id,@age,@name);
-- 查看
select * from t_user;
/*
+----+-----+---------------+
| id | age | name |
+----+-----+---------------+
| 1 | 30 | 啊啊啊 |
| 2 | 50 | 是是是 |
| 3 | 56 | 小数点 |
+----+-----+---------------+
2 rows in set (0.00 sec)
*/

• 带out参数的存储过程

delete a from t_user a where a.id = 4;
/*如果存储过程存在则删除*/
DROP PROCEDURE IF EXISTS proc3;
/*设置结束符为$*/
DELIMITER $
/*创建存储过程proc3*/
CREATE PROCEDURE proc3(id int,age int,in name varchar(16),out user_count int,out max_id INT)
  BEGIN
    INSERT INTO t_user VALUES (id,age,name);
    /*查询出t_user表的记录，放入user_count中,max_id用来存储t_user中最小的id*/
    SELECT COUNT(*),MAX(id) INTO user_count,max_id from t_user;
  END $
/*将结束符置为;*/
DELIMITER ;
-- proc3中前2个参数，没有指定参数模式，默认为in
-- 调用存储过程
/*创建了3个⾃定义变量*/
SELECT @id:=4,@age:=55,@name:='ssss';
/*调⽤存储过程*/
CALL proc3(@id,@age,@name,@user_count,@max_id);
-- 查看
select @user_count,@max_id;
/*
+-------------+---------+
| @user_count | @max_id |
+-------------+---------+
| 4 | 4 |
+-------------+---------+
1 row in set (0.00 sec)
*/

• 带inout参数的存储过程

/*如果存储过程存在则删除*/
DROP PROCEDURE IF EXISTS proc4;
/*设置结束符为$*/
DELIMITER $
/*创建存储过程proc4*/
CREATE PROCEDURE proc4(INOUT a int,INOUT b int)
  BEGIN
    SET a = a * 2;
    SELECT b*2 into b;
  END $
/*将结束符置为;*/
DELIMITER ;
-- 嗲用存储过程
/*创建了2个⾃定义变量*/
set @a=10,@b:=20;
/*调⽤存储过程*/
CALL proc4(@a,@b);
-- 查看
SELECT @a,@b;
/*
+------+------+
| @a | @b |
+------+------+
| 20 | 40 |
+------+------+
1 row in set (0.00 sec)
*/
-- 上面的两个自定义变量@a、@b作为入参，然后在存储过程内部进行了修改，又作为了返回值

查看存储过程

show create procedure proc4;
/*
+-------+-------+-------+-------+-------+-------+
| Procedure | sql_mode | Create Procedure | character_set_client |
collation_connection | Database Collation |
+-------+-------+-------+-------+-------+-------+
| proc4 |
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ER
ROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc4`(INOUT a int,INOUT
b int)
BEGIN
SET a = a*2;
select b*2 into b;
END | utf8 | utf8_general_ci | utf8_general_ci
|
+-------+-------+-------+-------+-------+-------+
1 row in set (0.00 sec)
*/

---

函数

• 一组预编译好的sql语句集合，理解成批处理语句
• 创建函数
• create function 函数名(参数名称 参数类型)
• returns 返回类型
• begin
  • 函数体
• end
  • 参数是可选的
  • 返回值是必须的

调用函数

• select 函数名(实参列表);

删除函数

• drop function [if exists] 函数名;

查看函数详情

• show create function 函数名;
• 无参函数

/*删除fun1*/
DROP FUNCTION IF EXISTS fun1;
/*设置结束符为$*/
DELIMITER $
CREATE FUNCTION fun1()
  returns INT
  BEGIN
    DECLARE max_id int DEFAULT 0;
    SELECT max(id) INTO max_id FROM t_user;
    return max_id;
  END $
/*设置结束符为;*/
DELIMITER ;
-- 调用函数
select fun1();
/*
+--------+
| fun1() |
+--------+
| 4 |
+--------+
1 row in set (0.00 sec)
*/

• 有参函数

/*删除函数*/
DROP FUNCTION IF EXISTS get_user_id;
/*设置结束符为$*/
DELIMITER $
/*创建函数*/
CREATE FUNCTION get_user_id(v_name VARCHAR(16))
  returns INT
  BEGIN
    DECLARE r_id int;
    SELECT id INTO r_id FROM t_user WHERE name = v_name;
    return r_id;
  END $
/*设置结束符为;*/
DELIMITER ;
-- 调用函数
SELECT get_user_id(name) from t_user;
/*
+-------------------+
| get_user_id(name) |
+-------------------+
| 1 |
| 2 |
| 3 |
| 4 |
+-------------------+
4 rows in set (0.00 sec)
*/

---

存储过程和函数的区别

• 存储过程的关键字为procedure，返回值可以有多个，调用时用call ，一般用于执行比较复杂的的过程体、更新、创建等语句
• 函数的关键字为function，返回值必须有一个，调用select，一般用于查询单个值并返回

[参考学习自 公众号：大侠学JAVA]