117 lines
3.2 KiB
SQL
117 lines
3.2 KiB
SQL
DELIMITER $$
|
|
|
|
-- Saal-Package
|
|
|
|
|
|
DROP FUNCTION IF EXISTS `saal_pub_search_saal`$$
|
|
CREATE FUNCTION `saal_pub_search_saal` (p_saalname VARCHAR(255))
|
|
RETURNS INTEGER UNSIGNED
|
|
BEGIN
|
|
DECLARE res INTEGER UNSIGNED;
|
|
|
|
SELECT Saal.ID INTO res FROM Saal WHERE Name=p_saalname;
|
|
|
|
IF res IS NULL THEN
|
|
SIGNAL SQLSTATE '50101' SET MESSAGE_TEXT = 'Kein Saal mit dem Namen gefunden!';
|
|
END IF;
|
|
RETURN res;
|
|
END $$
|
|
|
|
|
|
|
|
|
|
|
|
|
|
DROP FUNCTION IF EXISTS saal_pub_search_sitzplatz_saalID$$
|
|
CREATE FUNCTION saal_pub_search_sitzplatz_saalID (p_saalID INTEGER UNSIGNED, p_reihe INTEGER UNSIGNED, p_platz INTEGER UNSIGNED)
|
|
RETURNS INTEGER UNSIGNED
|
|
BEGIN
|
|
DECLARE res INTEGER UNSIGNED;
|
|
|
|
SELECT Sitzplatz.ID INTO res FROM Sitzplatz WHERE fk_Saal_ID=p_saalID AND Reihe=p_reihe AND Platz=p_platz;
|
|
|
|
IF res IS NULL THEN
|
|
SIGNAL SQLSTATE '50111' SET MESSAGE_TEXT = 'Kein Sitzplatz gefunden!';
|
|
END IF;
|
|
RETURN res;
|
|
END $$
|
|
|
|
|
|
DROP FUNCTION IF EXISTS `saal_pub_search_sitzplatz`$$
|
|
CREATE FUNCTION `saal_pub_search_sitzplatz` (p_saalname VARCHAR(255), p_reihe INTEGER UNSIGNED, p_platz INTEGER UNSIGNED)
|
|
RETURNS INTEGER UNSIGNED
|
|
BEGIN
|
|
RETURN saal_pub_search_sitzplatz_saalID(saal_pub_search_saal(p_saalname), p_reihe, p_platz);
|
|
END $$
|
|
|
|
|
|
|
|
|
|
|
|
DROP PROCEDURE IF EXISTS `saal_pub_add_saal3`$$
|
|
CREATE PROCEDURE `saal_pub_add_saal3` (p_saalname VARCHAR(50))
|
|
BEGIN
|
|
CALL saal_pub_add_saal2(p_saalname, NULL);
|
|
END $$
|
|
|
|
DROP PROCEDURE IF EXISTS `saal_pub_add_saal2`$$
|
|
CREATE PROCEDURE `saal_pub_add_saal2` (p_saalname VARCHAR(50), p_beschreibung MEDIUMTEXT)
|
|
BEGIN
|
|
CALL saal_pub_add_saal(p_saalname, p_beschreibung, 0, 0, 0);
|
|
END $$
|
|
|
|
DROP PROCEDURE IF EXISTS `saal_pub_add_saal1`$$
|
|
CREATE PROCEDURE `saal_pub_add_saal1` (p_saalname VARCHAR(50), p_reihen INTEGER UNSIGNED, p_breite INTEGER UNSIGNED, p_preis FLOAT UNSIGNED)
|
|
BEGIN
|
|
CALL saal_pub_add_saal(p_saalname, NULL, p_reihen, p_breite, p_preis);
|
|
END $$
|
|
|
|
|
|
|
|
DROP PROCEDURE IF EXISTS `saal_pub_add_saal`$$
|
|
CREATE PROCEDURE `saal_pub_add_saal` (p_saalname VARCHAR(50), p_beschreibung MEDIUMTEXT, p_reihen INTEGER UNSIGNED, p_breite INTEGER UNSIGNED, p_preis FLOAT UNSIGNED)
|
|
BEGIN
|
|
DECLARE EXIT HANDLER FOR 1062 BEGIN -- [1062] Duplicate entry 'xxx' for key 'u_saal_name'
|
|
SIGNAL SQLSTATE '50120' SET MESSAGE_TEXT = 'Saal mit dem Namen existiert bereits!';
|
|
END;
|
|
|
|
INSERT INTO Saal (Name, Beschreibung) VALUES (p_saalname, p_beschreibung);
|
|
CALL saal_pub_update_preis(p_saalname, 1, p_reihen, 1, p_breite, p_preis);
|
|
END $$
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
DROP PROCEDURE IF EXISTS `saal_pub_update_preis`$$
|
|
CREATE PROCEDURE `saal_pub_update_preis` (
|
|
p_saalname VARCHAR(50),
|
|
p_start_reihe INTEGER UNSIGNED,
|
|
p_ende_reihe INTEGER UNSIGNED,
|
|
p_start_breite INTEGER UNSIGNED,
|
|
p_ende_breite INTEGER UNSIGNED,
|
|
p_preis FLOAT UNSIGNED
|
|
)
|
|
BEGIN
|
|
DECLARE v_reihe INTEGER UNSIGNED DEFAULT p_start_reihe;
|
|
DECLARE v_breite INTEGER UNSIGNED DEFAULT p_start_breite;
|
|
DECLARE v_saalID INTEGER UNSIGNED DEFAULT saal_pub_search_saal(p_saalname);
|
|
|
|
WHILE v_reihe <= p_ende_reihe DO
|
|
SET v_breite = 1;
|
|
WHILE v_breite <= p_ende_breite DO
|
|
INSERT INTO Sitzplatz (Reihe, Platz, fk_Saal_ID, Preis)
|
|
VALUES (v_reihe, v_breite, v_saalID, p_preis)
|
|
ON DUPLICATE KEY UPDATE Preis=p_preis;
|
|
SET v_breite = v_breite+1;
|
|
END WHILE;
|
|
SET v_reihe = v_reihe+1;
|
|
END WHILE;
|
|
END $$
|
|
|
|
|
|
|
|
|
|
DELIMITER ; |