mysql生成系统单据号

CREATE DEFINER=`root`@`%` PROCEDURE `sp_create_documentno`(IN `p_prefix` varchar(10),IN `p_suffix` varchar(10) ,IN `p_datepart` varchar(10),IN `p_qty` int)
BEGIN

declare cc int DEFAULT 0;
declare dcKey varchar(50) DEFAULT '';

DECLARE errcode CHAR(5) DEFAULT '00000';
DECLARE msg varchar(500);
DECLARE document_code varchar(50);
DECLARE current_serialno int ;

DECLARE t_error INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
-- 获取异常code,异常信息
GET DIAGNOSTICS CONDITION 1
errcode = RETURNED_SQLSTATE, msg = MESSAGE_TEXT;
SET t_error=1;
END;

START TRANSACTION;

set dcKey = CONCAT(p_prefix, (CASE WHEN IFNULL( p_datepart, '' ) = '' THEN '' ELSE concat('-',p_datepart ) END)
,(CASE WHEN IFNULL( p_suffix, '' ) = '' THEN '' ELSE concat('-',p_suffix ) END));

IF p_qty=0 THEN
set p_qty = p_qty + 1;
END IF;

update documentno set serialno=serialno+p_qty where document_key=dcKey;
set cc = FOUND_ROWS();
# SELECT @cc INTO rcount ;

IF cc=0 THEN
INSERT INTO documentno(document_key,prefix,suffix,datepart,serialno) VALUES(dcKey,p_prefix,p_suffix,p_datepart, p_qty);
END IF;

select CONCAT(
prefix,(CASE WHEN IFNULL( datepart, '' ) = '' THEN '' ELSE datepart END) ,
(CASE WHEN LENGTH(CONVERT(serialno,CHAR))<=4 then LPAD(CONVERT(serialno,CHAR), 4, '0') else CONVERT(serialno,CHAR) END) ,
(CASE WHEN IFNULL( suffix, '' ) = '' THEN '' ELSE concat('-',suffix ) END)),serialno into document_code,current_serialno
from documentno
where document_key=dcKey for update;

#select sleep(20);
select t_error as error_flag,errcode as error_code,msg error_msg,document_code,current_serialno;
IF t_error = 1 THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
#select t_error,errcode,msg,document_code into op_error,op_errcode,op_errmsg,document_code; #返回错误的结果集；

END