参数:开始年份,结束年份
过程说明:生成年月数据插入期间有中,若发现已经存在某年的数据则跳过。
DROP PROCEDURE IF EXISTS createPayPeriod;
DELIMITER //
CREATE PROCEDURE createPayPeriod(IN START_YEAR INT,IN END_YEAR int)
BEGIN
DECLARE v int;
declare max_year int;
DECLARE c INT;
set v=START_YEAR;
set max_year = END_YEAR;
LOOP_LABLE:LOOP
IF v<=END_YEAR THEN
select count(1) into c from pay_period t where t.period_year = v;
if c>0 then
SET v=v+1;
ITERATE LOOP_LABLE;
end if;
INSERT INTO pay_period (pay_period_id,period_name,period_year,period_month)VALUES(null,concat(v,'-01'),v,'01');
INSERT INTO pay_period (pay_period_id,period_name,period_year,period_month)VALUES(null,concat(v,'-02'),v,'02');
INSERT INTO pay_period (pay_period_id,period_name,period_year,period_month)VALUES(null,concat(v,'-03'),v,'03');
INSERT INTO pay_period (pay_period_id,period_name,period_year,period_month)VALUES(null,concat(v,'-04'),v,'04');
INSERT INTO pay_period (pay_period_id,period_name,period_year,period_month)VALUES(null,concat(v,'-05'),v,'05');
INSERT INTO pay_period (pay_period_id,period_name,period_year,period_month)VALUES(null,concat(v,'-06'),v,'06');
INSERT INTO pay_period (pay_period_id,period_name,period_year,period_month)VALUES(null,concat(v,'-07'),v,'07');
INSERT INTO pay_period (pay_period_id,period_name,period_year,period_month)VALUES(null,concat(v,'-08'),v,'08');
INSERT INTO pay_period (pay_period_id,period_name,period_year,period_month)VALUES(null,concat(v,'-09'),v,'09');
INSERT INTO pay_period (pay_period_id,period_name,period_year,period_month)VALUES(null,concat(v,'-10'),v,'10');
INSERT INTO pay_period (pay_period_id,period_name,period_year,period_month)VALUES(null,concat(v,'-11'),v,'11');
INSERT INTO pay_period (pay_period_id,period_name,period_year,period_month)VALUES(null,concat(v,'-12'),v,'12');
SET v=v+1;
ITERATE LOOP_LABLE;
END IF;
IF v>END_YEAR THEN
LEAVE LOOP_LABLE;
END IF;
END LOOP;
END ;
//
DELIMITER ;
在navicat中执行存储过程
SET @start_year = 2020;
set @end_year = 2100;
CALL createPayPeriod (@start_year ,@end_year);
改进版
DROP PROCEDURE IF EXISTS createPayPeriod2;
DELIMITER //
CREATE PROCEDURE createPayPeriod2(IN START_YEAR INT,IN END_YEAR int)
BEGIN
DECLARE v int;
declare max_year int;
declare v_month int;
DECLARE s_month varchar(7);
DECLARE c INT;
set v=START_YEAR;
set max_year = END_YEAR;
LOOP_LABLE:LOOP
IF v<=END_YEAR THEN
set v_month = 1;
LOOP_LABLE2: LOOP
IF v_month <= 12 THEN
select count(1) into c from pay_period t where t.period_year = v AND t.period_month = v_month;
if c>0 THEN
SET v_month=v_month+1;
ITERATE LOOP_LABLE2;
END IF;
select concat(v,'-',v_month) into s_month;
if v_month < 10 THEN
select concat(v,'-0',v_month) into s_month;
END IF;
INSERT INTO pay_period (pay_period_id,period_name,period_year,period_month)VALUES(null,s_month,v,v_month);
SET v_month=v_month+1;
ITERATE LOOP_LABLE2;
END IF;
IF v_month > 12 THEN
LEAVE LOOP_LABLE2;
END IF;
END LOOP;
SET v=v+1;
ITERATE LOOP_LABLE;
END IF;
IF v>END_YEAR THEN
LEAVE LOOP_LABLE;
END IF;
END LOOP;
END ;
//
DELIMITER ;