mysql - 创建存储过程 if 语句仅给出语法错误

Question

DELIMITER $$

CREATE
     PROCEDURE pro_academic_yr(ac_year VARCHAR(100),ac_year_status VARCHAR(10),ac_from_dt DATE,ac_to_dt DATE)
     BEGIN
     IF EXISTS (SELECT * FROM tsb_acadamic_yr WHERE ac_year_status='current') THEN
     UPDATE tsb_acadamic_yr SET ac_year_status='Previous' WHERE ac_year_status='current';
     END IF;
     INSERT INTO tsb_acadamic_yr (ac_year,ac_year_status,ac_from_dt,ac_to_dt) VALUES(ac_year,ac_year_status,ac_from_dt,ac_to_dt)
     END $$

DELIMITER ;

Answer 1

请尝试这个。

DELIMITER $$
CREATE
      PROCEDURE pro_academic_yr(ac_year VARCHAR(100),ac_year_status VARCHAR(10),ac_from_dt DATE,ac_to_dt DATE)
     BEGIN
     DECLARE VAR_COUNT INT;
     SELECT count(*) INTO VAR_COUNT FROM tsb_acadamic_yr WHERE ac_year_status='current';
     IF (VAR_COUNT > 0) THEN
     UPDATE tsb_acadamic_yr SET ac_year_status='Previous' WHERE ac_year_status='current';
     END IF;
     INSERT INTO tsb_acadamic_yr (ac_year,ac_year_status,ac_from_dt,ac_to_dt) VALUES(ac_year,ac_year_status,ac_from_dt,ac_to_dt);
 END$$
 DELIMITER ;

希望这对您有帮助。