MySQL函数案例

函数实例

CREATE DEFINER=`root`@`%` PROCEDURE `query_recipes`(timePeriod varchar(50),mealType varchar(2))
    COMMENT '根据时间筛选和餐次类型查询所有菜品表数据 * @param timePeriod 时间段 * @param mealType   餐次类型 0早餐/1午餐/2晚餐 没有则查全部'
BEGIN
  DECLARE dishes_ids VARCHAR(1000) DEFAULT '';
	DECLARE dishes_ids_str VARCHAR(1000) DEFAULT '';
	DECLARE dishes_ids_count int(11) DEFAULT 0;
  DECLARE i int(11) DEFAULT 0;
  DECLARE dishes_id bigint(20);

set @selectSql =  concat("select group_concat(DISTINCT dishes_id) as dishes_ids_str into @dishes_ids_str from tmd_menus WHERE date_format(menu_usage_time, '%Y-%m-%d') = '",timePeriod,"'");
	if(mealType <> '') THEN
		set @selectSql = concat(@selectSql," and meal_type = ",mealType);
	END IF;

 PREPARE stmt FROM @selectSql;
 EXECUTE stmt ;
 DEALLOCATE PREPARE stmt;
 set @dishes_ids = replace(replace(@dishes_ids_str,'[',''),']','');
--  拼接字符串去重
 select pscs.separator_str_distinct(@dishes_ids,',') into @dishes_ids;
 set @dishes_ids_count= 1+(length(@dishes_ids) - length(replace(@dishes_ids,',','')));
 DROP TEMPORARY TABLE IF EXISTS tmd_provisional_list_dishes;

  CREATE TEMPORARY TABLE tmd_provisional_list_dishes (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `create_date` datetime NOT NULL COMMENT '创建时间',
  `modify_date` datetime NOT NULL COMMENT '修改时间',
  `dishes_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '菜品名称',
  `photo` varchar(400) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '菜品照片',
  `remark` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '菜品备注',
  `company_id` bigint(20) NULL DEFAULT NULL COMMENT '公司id',
  `company_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '公司名称',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 200 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '菜品临时表' ROW_FORMAT = Dynamic;

	while i < @dishes_ids_count
	do
		set i = i + 1;

		SELECT SUBSTRING_INDEX((SELECT SUBSTRING_INDEX(@dishes_ids, ',' , i)), ',' , -1) into @dishes_id;

    INSERT INTO tmd_provisional_list_dishes (create_date,modify_date,id,dishes_name,photo,remark,company_id,company_name) 
		select create_date,modify_date,id,dishes_name,photo,remark,company_id,company_name from tmd_dishes di where di.id = @dishes_id;
-- 
  end while;
-- 	select @selectSql;
	select * from tmd_provisional_list_dishes;
END

根据下标获取指定位置的字符

CREATE DEFINER=`root`@`%` FUNCTION `separator_str`(str VARCHAR(225), separator_str CHAR(1), idx int(10)) RETURNS varchar(50) CHARSET utf8
    COMMENT '根据下标获取指定位置的字符'
BEGIN
    RETURN SUBSTRING_INDEX(SUBSTRING_INDEX(str, separator_str, idx), separator_str, -1);
END

获取去重字符串

CREATE DEFINER=`root`@`%` FUNCTION `separator_str_distinct`(str VARCHAR(225), separator_str CHAR(1)) RETURNS varchar(225) CHARSET utf8
    COMMENT '获取去重字符串'
BEGIN
    DECLARE size INT DEFAULT separator_str_len(str, separator_str);
    DECLARE i INT DEFAULT 0;
    DECLARE tmp VARCHAR(50);
    DECLARE str_distinct VARCHAR(225) DEFAULT '';
    WHILE i < size DO
        SET i = i + 1;
        SET tmp = separator_str(str, separator_str, i);
        SET str_distinct = if(FIND_IN_SET(tmp, str_distinct) > 0, str_distinct, CONCAT(str_distinct, separator_str, tmp));
    END WHILE;
    RETURN RIGHT(str_distinct, CHAR_LENGTH(str_distinct) - 1);
END

获取分割后的字符串数量

CREATE DEFINER=`root`@`%` FUNCTION `separator_str_len`(str VARCHAR(225), separator_str CHAR(1)) RETURNS int(10)
    COMMENT '获取分割后的字符串数量'
BEGIN
    RETURN (LENGTH(str) - LENGTH(REPLACE(str, separator_str, '')))+1;
END

函数事务实例

CREATE DEFINER=`root`@`%` PROCEDURE `update_order_food_flag_amount`(`creation_time` varchar(50))
BEGIN
	DECLARE id int DEFAULT 0;
	-- 定义一个错误的变量,类型是整形，默认是0
  DECLARE t_error INTEGER DEFAULT 0;
	
  -- 捕获到sql的错误，就设置t_error为1
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;
  -- 开启事务
  START TRANSACTION;

	insert into aged_user(creation_time) VALUES (creation_time);
	SELECT LAST_INSERT_ID() INTO @id;
			
			update aged_user set aged_user_name = '行';
			
	-- 如果捕获到错误
  IF t_error=1 THEN
     -- 回滚
     ROLLBACK;
  ELSE
     -- 提交
     COMMIT;
  END IF;
  -- 查看返回的结果,并给到输出的变量中
  SELECT t_error;
-- 
END