Musterlösung Stored Procedures File

IT
Datenbanksysteme
MySQL: Stored Procedures
Name: Tim Tenbusch
Datum: 5.5.2015
Dokumentation
Klasse: OG x
Blatt Nr.: 1/4
Lfd. Nr.:
1. Aufgabe – Erstellen und Löschen von Stored Procedures (E - 30min)
 Erstellen Sie die Stored Procedure halloWelt() und Huelle().
 DELIMITER ist das Trennzeichen, welches in SQL die
Befehle voneinander separiert. Standard ist das Semikolon.
Da in Stored Procedures Semikolons vorkommen können ist
das Ändern mit dem Schlüsselwort DELIMITER nötig. Wichtig ist nach den vereinbaren der Stored
Procedure den DELIMITER wieder zurück zu ändern
Beispielcoode:
DELIMITER ??
CREATE PROCEDURE ... mit vielen ;;;;;
DELITMITER ;
 Erstellen Sie die Stored Procedure GetAlleBanken()
CREATE PROCEDURE GetAlleBanken()
SELECT * FROM bank;
 Rufen Sie die Stored Procedure auf




CALL GetAlleBanken();
 Löschen Sie die Stored Procedure
DROP PROCEDURE IF EXISTS GetAlleBanken;
2. Aufgabe – Variablen (E - 30min)
 Erstellen Sie die Stored Procedure Variable()
DELIMITER ??
DROP PROCEDURE IF EXISTS Variable??
CREATE PROCEDURE Variable()
BEGIN
DECLARE total_banks INT
DEFAULT 0;
DECLARE total_count INT
DEFAULT 0;
SELECT COUNT(*) INTO total_banks FROM bank;
SET total_count = 10;
SELECT total_banks, total_count;
END??
DELIMITER ;
 Geben Sie die 2 Variablen aus.
CALL Variable();
 Recherchieren Sie das @-Zeichen vor Variablendeklarartionen
Mit dem @-Zeichen werden Variablen abfrageübergreifend für eine einzelne Session und Nutzer zur
Verfügung gestellt.
3. Aufgabe – Parameter (E - 45min)
 Erstellen der Stored Procedure GetMitglied()
DELIMITER ??
DROP PROCEDURE IF EXISTS GetMitglied??
CREATE PROCEDURE GetMitglied(IN Mitgliedsnummer Integer(10))
BEGIN
SELECT * FROM mitglied
WHERE MNr = Mitgliedsnummer;
END??
DELIMITER ;
 Aufruf der Stored Procedure
CALL GetMitglied(1980);
©Tenbusch
IT
Datenbanksysteme
MySQL: Stored Procedures
Name: Tim Tenbusch
Datum: 5.5.2015

Dokumentation
Klasse: OG x
Blatt Nr.: 2/4
Lfd. Nr.:
Erstellen der Stored Procedure MitgliederName()
DELIMITER ??
DROP PROCEDURE IF EXISTS MitgliederName??
CREATE PROCEDURE MitgliederName (IN Name VARCHAR(20), OUT total INTEGER)
BEGIN
SELECT COUNT(*) INTO total FROM mitglied
WHERE mitglied.Name LIKE Name;
END??
DELIMITER ;
 Aufruf der Stored Procedure
CALL MitgliederName('M%', @total);
SELECT @total;
 Erstellen der Stored Procedure GetMitgliederOnDayAndUnit()
DELIMITER ??
DROP PROCEDURE IF EXISTS GetMitgliederOnDayAndUnit??
CREATE PROCEDURE GetMitgliederOnDayAndUnit (IN pUNr INT(11), IN pTag
VARCHAR(12))
BEGIN
SELECT *
FROM mitglied
INNER JOIN teilnahme ON mitglied.MNr = teilnahme.MNr
INNER JOIN trainingseinheit ON teilnahme.UNr = trainingseinheit.UNr
WHERE pTag = trainingseinheit.Tag
AND pUNr = teilnahme.UNr;
END??
DELIMITER ;
 Aufruf der Stored Procedure
CALL GetMitgliederOnDayAndUnit(3, 'Montag')
 Recherchieren Sie wie der INOUT-Mode funktioniert.
Der INOUT-Parameter gibt den Wert an die Stored Procedure weiter und liefert den evtl. geänderten
Wert zurück in die Variable.
 Schreiben Sie SetCounter()
DELIMITER ??
DROP PROCEDURE IF EXISTS SetCounter??
CREATE PROCEDURE SetCounter (INOUT counter INT, IN increment INT)
BEGIN
SET counter = counter + increment;
END??
DELIMITER ;
 Aufruf der Stored Procedure
SET @counter = 1;
CALL setCounter( @counter, 1);
CALL setCounter( @counter, 1);
CALL setCounter( @counter, 5);
SELECT @counter;
4. Aufgabe
 Schreiben Sie GetPremiumMitglieder()
DELIMITER ??
DROP PROCEDURE IF EXISTS GetPremiumMitglieder??
CREATE PROCEDURE GetPremiumMitglieder (IN Mitgliedsnummer INT(10), OUT
MitgliedsLevel VARCHAR(20))
BEGIN
DECLARE beitrag DECIMAL(5,2) DEFAULT 0.0;
SELECT mitglied.Beitrag INTO beitrag
FROM mitglied
WHERE MNr = Mitgliedsnummer;
IF beitrag >= 52 THEN
SET Mitgliedslevel = 'Platinum';
ELSEIF beitrag >= 30 AND beitrag <=50 THEN
©Tenbusch
IT
Datenbanksysteme
MySQL: Stored Procedures
Name: Tim Tenbusch
Datum: 5.5.2015
Dokumentation
Klasse: OG x
Blatt Nr.: 3/4
SET Mitgliedslevel = 'Gold';
ELSE
SET Mitgliedslevel = 'Silver';
END IF;
END??
DELIMITER ;
 Aufruf der Stored Procedure
CALL GetPremiumMitglieder (1987, @MitgliedsLevel);
SELECT @Mitgliedslevel;
5. Aufgabe
 Schreiben Sie Zahlen1Bis50(OUT zeichenkette)
DELIMITER ??
DROP PROCEDURE IF EXISTS Zahlen1Bis50??
CREATE PROCEDURE Zahlen1Bis50(OUT zeichenkette VARCHAR(255))
BEGIN
DECLARE counter INT DEFAULT 1;
SET zeichenkette = '0';
WHILE counter < 50 DO
SET zeichenkette = CONCAT(zeichenkette, ', ' , counter);
SET counter = counter + 1;
END WHILE;
END??
DELIMITER ;
 Aufruf der Stored Procedure
CALL Zahlen1Bis50(@zeichenkette);
SELECT @zeichenkette;
 Teamplanbuch – auf die ; wurde verzichtet, da dies mit Java viel einfacher zu realisieren ist
DELIMITER ??
DROP PROCEDURE IF EXISTS TopTen??
CREATE PROCEDURE TopTen(IN pUNr INT(10))
BEGIN
SELECT CONCAT(SUBSTR(Vorname,1,1),'. ',Name)
FROM mitglied INNER JOIN teilnahme
ON mitglied.MNr = teilnahme.MNr
WHERE UNr = pUNr
AND mitglied.Beitrag >= 52
ORDER BY mitglied.Beitrag DESC
LIMIT 1,10;
END??
DELIMITER ;
 Aufruf der Stored Procedure
CALL TopTen(11);
6. Aufgabe
 Stored Funktions haben im Gegensatz zu den Procedures immer einen Rückgabewert
Syntax:
CREATE FUNCTION name (parameter)
zusätze RETURNS typ
BEGIN
Anweisungen
Return Rückgabewert;
END
 Erstellen einer Stored Funktion mit dem Rückgabewert 2
DELIMITER ??
DROP FUNCTION IF EXISTS gib2??
CREATE FUNCTION gib2() RETURNS INT
BEGIN
RETURN 2;
END??
DELIMITER ;
©Tenbusch
Lfd. Nr.:
IT
Datenbanksysteme
MySQL: Stored Procedures
Name: Tim Tenbusch
Datum: 5.5.2015

Dokumentation
Klasse: OG x
Blatt Nr.: 4/4
Lfd. Nr.:
Aufruf der Stored Function
SELECT gib2()
 DETERMINISTIC
Dieses Schlüsselwort gibt an, ob bei gleichen Eingabeparametern immer das gleiche Ergebnis
produziert wird. Das heißt hier kann nicht auf dynamischen Daten gearbeitet werden.
©Tenbusch