create PROCEDURE v4(in c_year int)
BEGIN
declare num int(10) default 0;
declare num1 int(10);
select code into num from class where class=c_year order by code desc limit 1;
IF exists(select * from class where class=c_year) THEN
insert into class(class,code)values(c_year,num+1);
else
insert into class(class,code)values(c_year,1);
end if;
end;
BEGIN
DECLARE a_class VARCHAR (10);
DECLARE a_code int (10);
DECLARE a_int INT (10);
SELECT IFNULL(class, class) INTO a_class;
select SUBSTR('0000',4,4)+'0' into a_code;
select a_int=a_code+1;
IF class = a_class THEN
IF a_int<10 THEN
insert into class(class,code)VALUES(a_class,CONCAT(a_class,'000',a_int));
elseIF a_int <100 THEN
insert into class(class,code)VALUES(a_class,CONCAT(a_class,'00',a_int));
elseIF a_int <1000 THEN
insert into class(class,code)VALUES(a_class,CONCAT(a_class,'0',a_int));
elseif a_int<10000 THEN
insert into class(class,code)VALUE(a_class,concat(a_class,a_int));
end if;
end if;
set a_int=a_code+1;
end
create PROCEDURE class_a(in c_year int)
begin
declare num int;
if exists(select * from class where class=c_year) then
update class set code=code+1 where class=c_year;
else
insert into class(class,code)values(c_year,1);
end if;
select code into num from class where class=c_year;
select CONCAT(c_year, lpad(num,4,0)) as code_a;
end;