Arbeiten mit Stored Procedures

[Martin Gottwald]

Hallo,

kann mir jemand kurz erklären worin der riesen Vorteil bei Stored Procedures liegt und wie diese funtionieren?

Gruss

Martin

[Andreas Kosch]

Hallo,

der Zugriff auf Stored Procedures einer SQL-Datenbank hat zusammengefasst 3 wesentliche Vorteile: <br>
1. Bessere Leistung <br>
2. Einfache Wartung/Erweiterung <br>
3. Bessere Sicherheit

Bevor ein SQL-Server eine Anweisung ausführt, muss der Text geparst, die Zugriffsrechte auf die beteiligten Datenbankobjekte geprüft und im Fall einer SELECT-Anweisung der optimale Ausführungspfad analysiert werden. Bei einer normalen SELECT-Abfrage arbeitet der Server diese Schritte im ungünstigsten Fall bei jedem Aufruf erneut ab. Bei einer Stored Procedure in der Regel aber nur einmal (d.h. beim Anlegen bzw. beim ersten tatsächlichen Aufruf).

Ein weiterer Vorteil einer Stored Procedure liegt darin, dass dort ein Teil der Programmlogik implementiert werden kann und somit eine Aufgabe komplett auf dem Server abgearbeitet wird. Der Client ruft nur die Stored Procedure auf und übergibt dabei alle notwendigen Daten als Parameter. Alles weitere erledigt der SQL-Server intern, ohne das zusätzliche Netzwerkzugriffe zum Client notwendig werden. Das folgende Beispiel demonstriert dies:

<pre>

CREATE PROCEDURE TransferMoney
@iFromKontoNr INT, @iToKontoNr INT, @mValue MONEY, @iStatus INT OUTPUT
AS
DECLARE @iError INT
DECLARE @iRowCount INT
SET @iStatus = 0
-- Schritt 1: Transaktion starten
BEGIN TRANSACTION
-- Schritt 2: Konto des Absenders belasten
UPDATE Konto
SET Betrag = Betrag - @mValue
WHERE KontoNr = @iFromKontoNr
SELECT @iError = @@ERROR, @iRowCount = @@ROWCOUNT
-- Schritt 3: Prüfen, ob Schritt 2 erfolgreich war
IF @iRowCount = 0
-- Status 1 = Konto des Absenders wurde nicht aktualisiert
SELECT @iStatus = 1
-- Zweiten Teilschritt nur im Erfolgsfall starten
IF (@iERROR = 0) AND (@iRowCount = 1)
BEGIN
-- Schritt 4: Konto des Empfängers gutschreiben
UPDATE Konto
SET Betrag = Betrag + @mValue
WHERE KontoNr = @iToKontoNr
SELECT @iError = @@ERROR, @iRowCount = @@ROWCOUNT
IF @iRowCount = 0
-- Status 2 = Konto des Empfängers wurde nicht aktualisiert
SELECT @iStatus = 2
END
-- Schritt 5: Transaktion im Fehlerfall widerrufen
IF (@iError = 0) AND (@iStatus = 0)
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
-- Fehlernummer zurückliefern
RETURN(@iError)

</pre>

Wenn der Client nur über Stored Procedure auf die Datenbank zugreift und dabei alle Daten als Parameter (Input- und Output-Parameter) übergeben werden, kann Datenbank-intern die Tabellenstruktur vollständig umgewandelt werden, ohne das der Client davon betroffen ist. Denn solange sich die Schnittstelle (Name der Stored Procedure und Parameter-Anordnung) nicht ändert, bekommt der Client von Umstrukturierungen in der Datenbank nichts mit.

Wenn der Client oder das Application Object nur über Stored Procedures auf die Datenbank zugreift, aber ansonsten keine Rechte an den Tabellen hat, legt nur die Stored Procedure fest, ob ein SELECT, INSERT, UPDATE oder DELETE-Aufruf ausgeführt werden darf oder nicht. Der Client erhält nur das EXECUTE-Recht an den Stored Procedures. Dazu werden alle Objekte (Tabellen und Stored Procedure) vom selben Owner erstellt