Eine Stored Procedure ist ein gespeichertes Programm in der Datenbank, das eine oder mehrere SQL-Anweisungen enthält und wiederholt ausgeführt werden kann.
Eine einfache Stored Procedure wird wie folgt erstellt:
DELIMITER //
CREATE PROCEDURE mitarbeiter_anzeigen()
BEGIN
SELECT * FROM mitarbeiter;
END //
DELIMITER ;
-- Aufruf
CALL mitarbeiter_anzeigen();-- Mit Ein- und Ausgabeparametern
DELIMITER //
CREATE PROCEDURE gehalt_erhoehen(
IN p_mitarbeiter_id INT,
IN p_prozent DECIMAL(5,2),
OUT p_neues_gehalt DECIMAL(10,2)
)
BEGIN
-- Lokale Variable deklarieren
DECLARE v_altes_gehalt DECIMAL(10,2);
-- Wert aus Tabelle lesen
SELECT gehalt INTO v_altes_gehalt
FROM mitarbeiter
WHERE mitarbeiter_id = p_mitarbeiter_id;
-- Neues Gehalt berechnen
SET p_neues_gehalt = v_altes_gehalt * (1 + p_prozent/100);
-- Update durchführen
UPDATE mitarbeiter
SET gehalt = p_neues_gehalt
WHERE mitarbeiter_id = p_mitarbeiter_id;
END //
DELIMITER ;
-- Aufruf mit Variablen
SET @neues_gehalt = 0;
CALL gehalt_erhoehen(123, 10, @neues_gehalt);
SELECT @neues_gehalt;DELIMITER //
CREATE PROCEDURE bestellung_pruefen(
IN p_bestell_id INT,
OUT p_status VARCHAR(50)
)
BEGIN
DECLARE v_betrag DECIMAL(10,2);
-- Bestellbetrag ermitteln
SELECT SUM(menge * einzelpreis)
INTO v_betrag
FROM bestellpositionen
WHERE bestell_id = p_bestell_id;
-- Betrag prüfen
IF v_betrag IS NULL THEN
SET p_status = 'Bestellung nicht gefunden';
ELSEIF v_betrag = 0 THEN
SET p_status = 'Leere Bestellung';
ELSEIF v_betrag < 100 THEN
SET p_status = 'Kleine Bestellung';
ELSE
SET p_status = 'Große Bestellung';
END IF;
END //
DELIMITER ;DELIMITER //
CREATE PROCEDURE rabatt_berechnen(
IN p_kunde_id INT,
IN p_bestellwert DECIMAL(10,2),
OUT p_rabatt DECIMAL(5,2)
)
BEGIN
DECLARE v_kundentyp CHAR(1);
-- Kundentyp ermitteln
SELECT kundentyp INTO v_kundentyp
FROM kunden
WHERE kunden_id = p_kunde_id;
-- Rabatt je nach Kundentyp und Bestellwert
CASE v_kundentyp
WHEN 'A' THEN
CASE
WHEN p_bestellwert >= 1000 THEN SET p_rabatt = 15;
WHEN p_bestellwert >= 500 THEN SET p_rabatt = 10;
ELSE SET p_rabatt = 5;
END CASE;
WHEN 'B' THEN
CASE
WHEN p_bestellwert >= 1000 THEN SET p_rabatt = 10;
WHEN p_bestellwert >= 500 THEN SET p_rabatt = 5;
ELSE SET p_rabatt = 2;
END CASE;
ELSE
SET p_rabatt = 0;
END CASE;
END //
DELIMITER ;DELIMITER //
CREATE PROCEDURE zinsen_berechnen(
IN p_jahre INT,
IN p_startkapital DECIMAL(10,2),
IN p_zinssatz DECIMAL(5,2)
)
BEGIN
DECLARE v_jahr INT DEFAULT 1;
DECLARE v_kapital DECIMAL(10,2);
SET v_kapital = p_startkapital;
loop_label: LOOP
-- Zinsen addieren
SET v_kapital = v_kapital * (1 + p_zinssatz/100);
-- Jahr erhöhen
SET v_jahr = v_jahr + 1;
-- Prüfen ob fertig
IF v_jahr > p_jahre THEN
LEAVE loop_label;
END IF;
END LOOP;
SELECT v_kapital AS endkapital;
END //
DELIMITER ;DELIMITER //
CREATE PROCEDURE lagerbestand_pruefen()
BEGIN
DECLARE v_produkt_id INT;
DECLARE v_bestand INT;
DECLARE done BOOLEAN DEFAULT FALSE;
-- Cursor für alle Produkte
DECLARE cur CURSOR FOR
SELECT produkt_id, lagerbestand
FROM produkte;
-- Handler für Ende der Cursor-Daten
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET done = TRUE;
-- Cursor öffnen
OPEN cur;
-- Schleife über alle Produkte
WHILE NOT done DO
FETCH cur INTO v_produkt_id, v_bestand;
-- Wenn Bestand zu niedrig
IF NOT done AND v_bestand < 10 THEN
INSERT INTO nachbestellungen
(produkt_id, menge, datum)
VALUES
(v_produkt_id, 100, CURRENT_DATE);
END IF;
END WHILE;
-- Cursor schließen
CLOSE cur;
END //
DELIMITER ;DELIMITER //
CREATE PROCEDURE backup_bereinigen()
BEGIN
DECLARE v_anzahl INT;
REPEAT
-- Alte Backup-Einträge löschen
DELETE FROM backup_log
WHERE datum < DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
LIMIT 1000;
-- Anzahl gelöschter Zeilen
SET v_anzahl = ROW_COUNT();
UNTIL v_anzahl = 0
END REPEAT;
END //
DELIMITER ;DELIMITER //
CREATE PROCEDURE geld_ueberweisen(
IN p_von_konto INT,
IN p_nach_konto INT,
IN p_betrag DECIMAL(10,2),
OUT p_erfolg BOOLEAN
)
BEGIN
DECLARE v_von_kontostand DECIMAL(10,2);
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SET p_erfolg = FALSE;
END;
START TRANSACTION;
-- Kontostand prüfen
SELECT kontostand INTO v_von_kontostand
FROM konten
WHERE konto_id = p_von_konto
FOR UPDATE;
IF v_von_kontostand >= p_betrag THEN
-- Geld abbuchen
UPDATE konten
SET kontostand = kontostand - p_betrag
WHERE konto_id = p_von_konto;
-- Geld gutschreiben
UPDATE konten
SET kontostand = kontostand + p_betrag
WHERE konto_id = p_nach_konto;
SET p_erfolg = TRUE;
COMMIT;
ELSE
SET p_erfolg = FALSE;
ROLLBACK;
END IF;
END //
DELIMITER ;Ein Cursor ermöglicht die zeilenweise Verarbeitung von Abfrageergebnissen:
DELIMITER //
CREATE PROCEDURE jahresabschluss()
BEGIN
DECLARE v_mitarbeiter_id INT;
DECLARE v_name VARCHAR(100);
DECLARE v_gehalt DECIMAL(10,2);
DECLARE done BOOLEAN DEFAULT FALSE;
-- Cursor definieren
DECLARE cur CURSOR FOR
SELECT mitarbeiter_id, name, gehalt
FROM mitarbeiter
WHERE status = 'aktiv';
-- Handler für Ende der Daten
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET done = TRUE;
-- Cursor öffnen
OPEN cur;
-- Schleife über alle Datensätze
read_loop: LOOP
-- Nächste Zeile lesen
FETCH cur INTO v_mitarbeiter_id, v_name, v_gehalt;
-- Prüfen ob am Ende
IF done THEN
LEAVE read_loop;
END IF;
-- Mitarbeiter verarbeiten
INSERT INTO gehaltshistorie
(mitarbeiter_id, name, gehalt, jahr)
VALUES
(v_mitarbeiter_id, v_name, v_gehalt, YEAR(CURRENT_DATE));
END LOOP;
-- Cursor schließen
CLOSE cur;
END //
DELIMITER ;Praktische Tipps:
DELIMITER //
CREATE PROCEDURE sicheres_update()
BEGIN
-- Handler für Fehler
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Ein Fehler ist aufgetreten';
END;
START TRANSACTION;
-- SQL-Anweisungen
COMMIT;
END //
DELIMITER ;DELIMITER //
CREATE PROCEDURE neue_bestellung(
/* Parameter-Dokumentation:
p_kunde_id: ID des Kunden
p_artikel_id: ID des Artikels
p_menge: Bestellmenge
p_erfolg: Rückgabewert für Erfolg */
IN p_kunde_id INT,
IN p_artikel_id INT,
IN p_menge INT,
OUT p_erfolg BOOLEAN
)
BEGIN
-- Implementierung
END //
DELIMITER ;-- Kleine, spezialisierte Prozeduren
CREATE PROCEDURE kunde_pruefen(...);
CREATE PROCEDURE artikel_pruefen(...);
CREATE PROCEDURE bestellung_anlegen(...);
-- Hauptprozedur ruft Teilprozeduren auf
CREATE PROCEDURE bestellprozess(...)
BEGIN
CALL kunde_pruefen(...);
CALL artikel_pruefen(...);
CALL bestellung_anlegen(...);
END;