Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
a simple schema to demonstrate stored procedures in mysql
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
ALTER SCHEMA `calendar` DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci ;
CREATE TABLE IF NOT EXISTS `calendar`.`Absence` (
`idAbsence` INT(11) NOT NULL,
`date` DATE NOT NULL,
`idReason` TINYINT(4) NOT NULL,
PRIMARY KEY (`idAbsence`),
INDEX `INDEX` (`idReason` ASC),
CONSTRAINT `fx_AbsenceReason`
FOREIGN KEY (`idReason`)
REFERENCES `calendar`.`Reason` (`idReason`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
CREATE TABLE IF NOT EXISTS `calendar`.`Reason` (
`idReason` TINYINT(4) NOT NULL,
`reason` VARCHAR(45) NOT NULL,
PRIMARY KEY (`idReason`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
DELIMITER $$
USE `calendar`$$
CREATE PROCEDURE `addAbsence`(
IN p_startDate DATE,
IN p_endDate DATE,
IN p_idReason TINYINT
)
BEGIN
DECLARE v_date DATE DEFAULT p_startDate;
DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN
ROLLBACK;
RESIGNAL;
END;
IF p_endDate < p_startDate THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'End date before start date';
END IF;
START TRANSACTION;
REPEAT
INSERT INTO
Absence
SET
idAbsence = NULL,
date = v_date,
idReason = p_idReason;
SET v_date = DATE_ADD(v_date, INTERVAL 1 DAY);
UNTIL v_date > p_endDate END REPEAT;
COMMIT;
SELECT 1 as `status`;
END$$
DELIMITER ;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment