Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement