MySQL存储过程和函数随笔

存储过程

存储过程: 可以有0个或多个返回值,适合做批量插入,批量更新;
函数: 有且只有一个返回值,适合做处理数据后返回一个结果

创建语法

CREATE PROCEDURE 存储过程名(参数模式 参数名 参数类型)
BEGIN
  存储过程体(一组合法的SQL语句)
END

参数模式:

IN: 调用方传入值
OUT: 返回值
INOUT: 既可以作为输入,也可以作为返回值

参数类型:

int
bigint
varchar(20)
...

如果存储过程体只有一句SQL,BEGIN END可以省略;
存储过程体中的每条SQL结尾都要加分号;
存储过程的结尾可以用 DELIMITER 重新设置
语法
DELIMITER $(结束标识)

调用语法

call 存储过程名(实参列表) $

存储过程栗子

举个栗子,insert

DELIMITER $
CREATE PROCEDURE insert_test()
BEGIN
  INSERT INTO table(id,field1,field2) VALUES(UUID,'value1','value2'),(UUID,'value1','value2'),(UUID,'value1','value2')...;
END $
call insert_test() $

举个栗子2,查询

DELIMITER $
CREATE PROCEDURE selet_test(IN targetName VARCHAR(20)) //多参数:CREATE PROCEDURE select_test(IN targetName1 VARCHAR(20),IN targetName2 VARCHAR(20))
BEGIN
  DECLARE result VARCHAR(20) DEFALUT '';//设置变量
  SELECT a.grade INTO result FROM a LEFT JOIN b ON a.id=b.aid WHERE a.name = targetName;//变量赋值
  SELECT IF(result<>'','成功','失败');  //变量使用/打印
END $
call selet_test('测试') $

举个栗子3,OUT查询

DELIMITER $
CREATE PROCEDURE select_test(IN targetName VARCHAR(20),OUT target VARCHAR)
BEGIN
  SELECT a.name INTO target FROM a WHERE a.targetName = targetName;
END $
SET @name $  //设置用户变量,也可不设置,在call中直接使用@name
call select_test('目标',@name) $
SELECT @name  //打印

举个栗子4,INOUT查询

DELIMITER $
CREATE PROCEDURE select_test(INOUT a INT,INOUT b INT)
BEGIN
  SET a = a*2;
  SET b = b*2;
END $
SET @m = 10 $
SET @n = 15 $
call select_test(@m,@n) $  //不能直接传入常量,因为要返回变量
SELECT @m,@n$  

查看存储过程信息

SELECT 'name' FROM mysql.proc WHERE db='database_name' AND 'type'='PROCEDURE';//查询指定数据库所有的存储过程名,关键字使用``转义

SHOW CREATE PROCEDURE 存储过程名;

存储过程的删除

DROP PROCEDURE 存储过程名;//只能单个删除

---

函数

创建语法

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
  函数体
END

参数列表:

参数名
参数类型(数据库字段类型)

函数体: 肯定会有return语句,没有会报错;但是如果return语句没有放在函数体最后也不会报错但是不介意这样
函数体中仅有一句话时也可省略BEGIN END
使用DELIMITER $语句标识结束

调用语句

SELECT 函数名(参数列表) $

函数栗子

无参有返回

CREATE FUNCTION test() RETURNS INT
BEGIN
  SET @counts = 0;  //定义变量
  SELECT COUNT(*) INTO @counts FROM a WHERE id=1;  //INTO赋值
  RETURN @counts;
END $
SELECT test() $  

有参有返回

CREATE FUCNTION test(targetName VARCHAR(20)) RETURNS VARCHAR
BEGIN
  DECLARE logos VARCHAR(50) DEFAULT '';  //声明变量
  SELECT logo INTO logos FROM a WHERE name=targetName;  //变量赋值
  RETURN logos;
END $
SELECT test('测试') $

查看函数

SHOW CREATE FUNCTION 函数名

删除函数

DROP FUNCTION 函数名