Mysql 函数的应用

CREATE TABLE `code_generate_dd` (
  `id` int(50) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `first_code` varchar(10) NOT NULL COMMENT '第一段code',
  `second_code` varchar(8) NOT NULL COMMENT '第二段code',
  `last_code` varchar(10) NOT NULL COMMENT '尾段',
  `generate_type` varchar(20) NOT NULL COMMENT '编码类型',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COMMENT='单据号规则订单(只针对订单)'

DELIMITER $$

CREATE DEFINER = `root` @`%` FUNCTION `fn_max` (f_generate_type VARCHAR (50)) RETURNS VARCHAR (50) 
BEGIN
  DECLARE result VARCHAR (50) ;
  SET result = 
  (SELECT 
    MAX(cg.`last_code`) 
  FROM
    code_generate cg 
  WHERE 1 = 1 
    AND cg.`second_code` = DATE_FORMAT(NOW(), '%Y%m%d') 
    AND cg.`generate_type` = f_generate_type) ;
  RETURN result ;
  
END $$


DELIMITER ;

SELECT fn_max("brd")
; 
=======================================
DELIMITER $$

DROP FUNCTION IF EXISTS `fn_lastcode`$$

CREATE DEFINER = `root` @`%` FUNCTION `fn_lastcode` (f_generate_type VARCHAR (50)) RETURNS VARCHAR (50) CHARSET utf8 
BEGIN
  DECLARE result VARCHAR (50) ;
  IF (fn_max (f_generate_type) = NULL) 
  THEN SET result = 
  (SELECT 
    RIGHT(CONCAT('00000', '00000' + 1), 5)) ;
  ELSE SET result = 
  (SELECT 
    RIGHT(
      CONCAT(
        '00000',
        (fn_max (f_generate_type)) + 1
      ),
      5
    )) ;
  END IF ;
  RETURN result ;
END $$

DELIMITER ;

SELECT fn_lastcode('brd');
;
====================================================
DELIMITER $$

CREATE DEFINER = `root` @`%` FUNCTION `fn_getBRD` (f_generate_type VARCHAR (50)) RETURNS VARCHAR (50) 
BEGIN
  DECLARE result VARCHAR (50) ;
  INSERT INTO code_generate_dd (
    first_code,
    second_code,
    last_code,
    generate_type
  ) 
  VALUES
    (
      f_generate_type,
      DATE_FORMAT(NOW(), '%Y%m%d'),
      fn_lastcode (f_generate_type),
      f_generate_type
    ) ;
  SET result = 
  (SELECT 
    CONCAT(
      dd.`first_code`,
      dd.`second_code`,
      dd.last_code
    ) aa 
  FROM
    code_generate_dd dd 
  WHERE 1 = 1 
    AND id = 
    (SELECT 
      MAX(id) 
    FROM
      code_generate_dd)) ;
  RETURN result ;
END $$


DELIMITER ;


SELECT fn_getBRD("brd"); 

这个是在数据库底层，直接调用，返回编码；用sql 写的函数；

注意:mysql 和oracle在函数创建的时候，规范有点不一样；