Mysql生成プレフィックス+日付+流れのシリアルナンバーフォーマット
2337 ワード
オンラインsqlserverの書き方を参考にして、mysqlの保存過程に変換します。間違ったところがありましたら、教えてください。
参考URL:http://www.accessoft.com/blog/article-show.asp?userid=24010&Id=17344
参考URL:http://www.accessoft.com/blog/article-show.asp?userid=24010&Id=17344
-- =============================================
-- Description:
-- : 0 e.g.:150424
-- 1 e.g.:1504
-- 2 e.g.:15
-- =============================================
DELIMITER //
DROP PROCEDURE IF EXISTS atuoNo;
create procedure atuoNo( in prefix VARCHAR(20),--
in modeType int , --
in nolen int , --
in listna varchar(20), --
in tablena varchar(50), --
out runningnum varchar(20) --
)
COMMENT ' + + '
begin
DECLARE currentDate CHAR (13) ;-- currentDate , : + +
DECLARE Maxnum int ;--
IF modeType=0 THEN
SELECT DATE_FORMAT(NOW(), '%Y%m%d') INTO currentDate ;-- : + , :2018042600002
set @v_sql=CONCAT('select ifnull(Max(CONVERT(SUBSTRING(',listna,',-',nolen,'),SIGNED)),0)+1 into @Maxnum from ',tablena ,' where SUBSTRING(',listna,', 1, 8) = ',currentDate); --
ELSEIF modeType =1 THEN
SELECT DATE_FORMAT(NOW(), '%Y%m') INTO currentDate ;-- : + , :20180400002
set @v_sql=CONCAT('select ifnull(Max(CONVERT(SUBSTRING(',listna,',-',nolen,'),SIGNED)),0)+1 into @Maxnum from ',tablena ,' where SUBSTRING(',listna,', 1, 6) = ',currentDate); --
ELSEIF modeType =2 THEN
SELECT DATE_FORMAT(NOW(), '%Y') INTO currentDate ;-- : + , :201800002
set @v_sql=CONCAT('select ifnull(Max(CONVERT(SUBSTRING(',listna,',-',nolen,'),SIGNED)),0)+1 into @Maxnum from ',tablena ,' where SUBSTRING(',listna,', 1, 4) = ',currentDate); --
END IF;
prepare stmt from @v_sql; -- SQL, stmt
EXECUTE stmt; -- SQL
deallocate prepare stmt; --
set Maxnum=@Maxnum;
set runningnum =CONCAT(prefix,currentDate, LPAD((Maxnum), nolen, '0')) ; -- LPAD((maxNo + 1), 5, '0'): 5 , 0
end
//
DELIMITER ;