2 u014659749 u014659749 于 2014.04.29 21:55 提问

大家好,我用mysql写存储过程自动产生编号,运行之后编号只有1,请问哪儿有问题呢?

BEGIN
DECLARE subjectNo1 char(8);
DECLARE subjectNo2 char(8);
DECLARE nowYear char(4);
DECLARE Num DECIMAL;

select date_format(now(),'%Y') into nowYear from DUAL;
select ifnull(max(Subject_No),'0') into subjectNo1
from subject
where Subject_No like nowYear||'%';
if subjectNo1='0' then
set subjectNo2=nowYear||'0001';
else
set Num=to_number(substr(subjectNo1,5,4))+1;
if Num<10 then set subjectNo2=nowYear||'000'||ltrim(to_char(Num));
elseif Num<100 then set subjectNo2=nowYear||'00'||ltrim(to_char(Num));
elseif Num<1000 then set subjectNo2=nowYear||'0'||ltrim(to_char(Num));
else
set subjectNo2=nowYear||ltrim(to_char(Num));
end if;
end if;
set newSubjectNo=subjectNo2;
END

Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!