mysql存储过程创建项目编号实操

CREATE DEFINER=`root`@`%` PROCEDURE `getProjectCode`(out result varchar(64))
BEGIN
    DECLARE dateStr varchar(10);
    DECLARE maxNo varchar(3);
		DECLARE count int(4);
-- 		DECLARE count1 int(3);
-- 		DECLARE count2 int(3);
-- 		DECLARE projectCode1 VARCHAR(64);
-- 		DECLARE projectCode2 VARCHAR(64);
    SELECT DATE_FORMAT(NOW(),'%Y%m%d') INTO dateStr;
-- 		SELECT count(*) from crocosmia_project_base WHERE TO_DAYS(create_time)=TO_DAYS(now()) INTO count1;
--     SELECT count(*) from crocosmia_project_declare WHERE TO_DAYS(create_time)=TO_DAYS(now()) INTO count2;
-- 		SELECT count(*) from crocosmia_project_research WHERE TO_DAYS(create_time)=TO_DAYS(now()) INTO count3;
--     SELECT count1 + count2+count3 INTO count;
    
		
		select count(1) from (
select  id   from crocosmia_project_base where TO_DAYS(create_time)=TO_DAYS(now())
union all
select  id   from crocosmia_project_declare  where TO_DAYS(create_time)=TO_DAYS(now())
union all
select  id   from crocosmia_project_research  where TO_DAYS(create_time)=TO_DAYS(now())
) aa into count;

--     ----先判断是否第一次表中插入数据（即当前表中是否存在数据）
    IF (count < 99 ) THEN
		 select concat(dateStr,LPAD(count+1, 3, 0)) into result;
--         ----获取数据表中最近产生的一个project_code，并截取最后3位流水号，对3位流水号进行加1，最后赋值给@maxNo
--         SELECT project_code FROM crocosmia_project_base ORDER BY  project_code DESC LIMIT 1 INTO projectCode1;
-- 				SELECT project_code FROM crocosmia_project_declare ORDER BY  project_code DESC LIMIT 1 INTO projectCode2;
-- 				SELECT project_code FROM crocosmia_project_research ORDER BY  project_code DESC LIMIT 1 INTO projectCode3;
-- 				IF(projectCode1 IS NULL) THEN
-- 					SELECT SUBSTRING(projectCode2,9,3)+1 INTO maxNo;
-- 				ELSEIF (projectCode2 IS NULL ) THEN
-- 					SELECT SUBSTRING(projectCode1,9,3)+1 INTO maxNo;
--         ELSE
-- 					SELECT SUBSTRING(IF(projectCode1 > projectCode2,projectCode1,projectCode2),9,3)+1 INTO maxNo;
--         END IF;
-- --         ----对上诉的@maxNo做判断，确保流水号一定是3位，不够的在前面补0
--         IF (maxNo < 10) THEN
-- -- 				--如果是1位数的话，需要在前面加3个0，补够3位
-- 						SELECT CONCAT( dateStr, CONCAT( '00', maxNo)) INTO result ;
--         ELSEIF (maxNo < 100) THEN
-- -- 				--如果是2位数的话，需要在前面加1个0，补够4位
--             SELECT CONCAT( dateStr, CONCAT( '0', maxNo)) INTO result ;
--         ELSE
-- --         ----将上诉结果合成一个新的pk_id
-- 				SELECT CONCAT( dateStr, maxNo) INTO result ;
-- 				END IF;
--      
--     ELSE
-- --        ----如实当前插入的数据时第一条数据，那么直接把3位流水号设为001
--       SELECT CONCAT( dateStr, '001') INTO result ;
		ELSE
				 select concat(dateStr, count+1 )  into result;

    END IF;
    SELECT result;
END