Mysql - 存储过程/自定义函数

create table ztree
(
    id int(11) not null PRIMARY key auto_increment,
    name varchar(20) not null comment '节点名称',
    pid int(11) not null comment '父节点id'
) comment '树形表';

delimiter $
DROP FUNCTIONIF EXISTS GetChildNodes ;
CREATE FUNCTION `GetChildNodes` (`rootId` INT) RETURNS VARCHAR (1000)
BEGIN
    DECLARE res VARCHAR (1000) DEFAULT '-1'; 
    DECLARE temp VARCHAR (1000) DEFAULT CAST(rootId AS CHAR);
    -- SET res = '' ;
    -- SET DECLARE = CAST(rootId AS CHAR) ;
    WHILE temp IS NOT NULL DO

        SET res = CONCAT(res, ',', temp) ; 

        SELECT GROUP_CONCAT(id) INTO temp FROM ztree WHERE FIND_IN_SET(pid, temp) > 0 ;
        
    END WHILE ; 
    RETURN res ;
    END$
delimiter ;

SELECT GetChildNodes (2);

CREATE TABLE `serialno` (
  `Id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Id',
  `Pre` varchar(10) NOT NULL COMMENT '编号',
  `Description` varchar(10) DEFAULT NULL COMMENT '说明',
  `Res` varchar(20) DEFAULT NULL COMMENT '流水号(不加编号的)',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='流水号表'

delimiter $
drop PROCEDURE if EXISTS p_GetSerialNo;
CREATE PROCEDURE `p_GetSerialNo`(IN preValue VARCHAR(10), IN preDate VARCHAR(10),IN des varchar(20), in length int)
BEGIN
    DECLARE  t_error INT DEFAULT 0;
    DECLARE  resValue VARCHAR(20) DEFAULT NULL;
    -- DECLARE
    --     CONTINUE HANDLER FOR SQLEXCEPTION,
    --     SQLWARNING,
    --     NOT FOUND
    -- SET t_error = 1;
    
    START TRANSACTION;

    SELECT Res INTO resValue FROM serialno WHERE Pre=preValue;
    IF resValue IS NULL  THEN
        SET resValue= CONCAT(preDate, LPAD(1, length, '0'));
        INSERT INTO serialno (Pre, Description, Res) VALUES (preValue, des, resValue);
    ELSE
        IF preDate =  (SUBSTRING(resValue,1,8) + '0') THEN
            SET resValue = CAST(resValue AS SIGNED) + 1;
            if preDate <> SUBSTRING(resValue,1,8) THEN
                set t_error = -1;
            end if;
        ELSE
            SET resValue= CONCAT(preDate, LPAD(1, length, '0'));
        END IF;
        UPDATE serialno SET Res = resValue WHERE Pre = preValue;
    END IF;
    
    #IF t_error = 1 then 
    IF @@error_count <> 0 | t_error <> 0 THEN
        ROLLBACK;
        select t_error;
    ELSE 
        COMMIT;
        SELECT CONCAT(preValue, resValue);
    END IF;
END $
delimiter ;

call p_GetSerialNo( 'b', '20170101',  'b', 4);