MySql自定义函数示例

MySQL 有三种不同变量类型：

• 本地变量(Local variables):本地变量的工作范围是在代码或者一段代码的范围内。一旦代码或者一段代码执行完成，变量就失效了。使用DECLARE语句来声明本地变量，并且可以用DEFAULT来设置默认值。、

DECLARE no_more_record INT DEFAULT false;

• 会话变量(Session variables):会话变量的工作范围是当前连接到MySQL服务器的会话的范围。一个会话从连接到服务器时开始，当连接关闭时结束。会话变量在连接终止后便失效了。会话变量只在当前连接中有效，无法被其他会话进行使用或参考。要声明或使用一个会话变量，需要在变量名称前加上@符号：

SET @count = 100;

• 全局变量（Global variables）：全局变量可以跨会话使用。全局变量不是自定义的，是绑定在正在运行的服务器的配置中。

SET GLOBAL max_connections = 300;

可以用SET语句来为本地变量，会话变量和全局变量进行赋值：

SET @cost = @cost + 5.00;

原文地址：https://blog.csdn.net/kannte/article/details/78926021

---

语法：

创建新函数：

　　　　Create function function_name(参数列表)

　　　　returns返回值类型

　　　　函数体内容

---

示例：

-- 定时月初统计上个月的目标与业绩
DROP PROCEDURE IF EXISTS proceduce_add_business_goal; 
delimiter //
create PROCEDURE proceduce_add_business_goal()
begin
  DECLARE no_more_record INT DEFAULT 0;
	DECLARE start_time TIMESTAMP DEFAULT (SELECT DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00')); -- 上个月开始时间
	DECLARE end_time TIMESTAMP default (SELECT DATE_FORMAT( LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)), '%Y-%m-%d 23:59:59')); -- 上个月结束时间
	DECLARE cur_id int(10); -- 用户id
	DECLARE cur_level int(2) DEFAULT 0; -- 用户等级
	DECLARE cur_province int(10) DEFAULT 0; -- 用户所在省
	DECLARE cur_city int(10) DEFAULT 0; -- 用户所在市
	DECLARE cur_district int(10) DEFAULT 0; -- 用户所在区
	DECLARE p_actual DECIMAL(12,3) DEFAULT 0; -- 业绩
	DECLARE p_goal DECIMAL(12,3) DEFAULT 0; -- 目标
	DECLARE P_is_exists int(1) default 0; -- 是否添加了统计
	DECLARE current_month varchar(10) DEFAULT (select DATE_FORMAT(CURRENT_DATE,'%Y-%m')); -- 上个月份
	DECLARE cur_user CURSOR FOR SELECT id,level,province,city,district FROM zx_user;-- 定义游标
	DECLARE CONTINUE HANDLER FOR NOT FOUND  SET no_more_record = 1; /*这个是个条件处理,针对NOT FOUND的条件,当没有记录时赋值为1*/-- 遍历集合结束后的处理 
	
	OPEN cur_user;	-- 打开游标
	
	WHILE no_more_record != 1 DO
		FETCH cur_user INTO cur_id,cur_level,cur_province,cur_city,cur_district;-- 游标指向第一行，将第一行数据写入变量
	  SELECT function_lastPerformanceOfMonth(cur_id,start_time,end_time) into p_actual; -- 调用函数
		
		IF (p_actual) THEN
			INSERT into zx_business_goal(uid,time,goal,actual,type) value(cur_id,current_month,p_goal,p_actual,1);
		END IF;
		
		
		-- 初始化业绩
		set p_actual = 0;
	END WHILE;

	-- 释放游标
	CLOSE cur_user;
	
end //

CALL proceduce_add_business_goal -- 调用存储过程


-- 插入数据

-- 获取单个用户上个月的业绩
DROP function IF EXISTS function_lastPerformanceOfMonth;
delimiter //
CREATE FUNCTION function_lastPerformanceOfMonth(uid_in INT(10),start_time TIMESTAMP,end_time TIMESTAMP)
RETURNS DECIMAL(12,3)
READS SQL DATA -- 自定义函数没有sql语句
COMMENT '获取上个月的业绩'
BEGIN
		DECLARE res DECIMAL(12,3);
    select sum(money) INTO res from zx_account_log where uid = uid_in and created_at BETWEEN start_time and end_time and type in (6,10,11,12,13,20,21,22,23); -- 将查询的数据放到res变量（into res）
    return res;
END //
delimiter ;

-- 功能类型：
-- 1、CONTAINS SQL表示子程序不包含读或写数据的语句。
-- 2、NO SQL表示子程序不包含SQL语句。
-- 3、READS SQL DATA表示子程序包含读数据的语句，但不包含写数据的语句。
-- 4、MODIFIES SQL DATA表示子程序包含写数据的语句。
-- 如果这些特征没有明确给定，默认的是CONTAINS SQL。