Was ist eine gespeicherte Prozedur? Wie funktionieren sie? Was ist der Aufbau einer Stored Procedure (was muss alles eine Stored Procedure sein)?
Stored Procedures sind ein Stapel von SQL-Anweisungen, die auf verschiedene Weise ausgeführt werden können. Die meisten großen DBMs unterstützen Stored Procedures, jedoch nicht alle. Sie müssen in der Hilfe-Dokumentation Ihres DBMS nachsehen, ob es Besonderheiten gibt. Da ich mit SQL Server am besten vertraut bin, werde ich dieses System als Beispiel verwenden.
Um eine gespeicherte Prozedur zu erstellen, ist die Syntax recht einfach:
CREATE PROCEDURE <owner>.<procedure name>
<param> <datatype>
AS
<Body>
Also zum Beispiel:
CREATE PROCEDURE Users_GetUserInfo
@login nvarchar(30)=null
AS
SELECT * from [Users]
WHERE ISNULL(@login,login)=login
Ein Vorteil von gespeicherten Prozeduren ist, dass Sie die Datenzugriffslogik an einer einzigen Stelle zentralisieren können, die dann von DBA's leicht zu optimieren ist. Gespeicherte Prozeduren haben auch einen Sicherheitsvorteil, da Sie einer gespeicherten Prozedur Ausführungsrechte erteilen können, der Benutzer aber keine Lese-/Schreibrechte für die zugrunde liegenden Tabellen haben muss. Dies ist ein guter erster Schritt gegen SQL-Injection.
Gespeicherte Prozeduren haben jedoch auch Nachteile, vor allem den Wartungsaufwand, der mit der grundlegenden CRUD-Operation verbunden ist. Nehmen wir an, für jede Tabelle gibt es ein Insert, Update, Delete und mindestens ein Select auf der Basis des Primärschlüssels, d.h. jede Tabelle hat 4 Prozeduren. Nehmen Sie nun eine Datenbank mit 400 Tabellen und Sie haben 1600 Prozeduren! Und das unter der Annahme, dass Sie keine Duplikate haben, was wahrscheinlich der Fall sein wird.
Dies ist, wo mit einem ORM oder eine andere Methode zur automatischen Generierung Ihrer grundlegenden CRUD-Operationen hat eine Tonne von Verdienst.
Eine gespeicherte Prozedur ist eine Reihe von vorkompilierten SQL-Anweisungen, die zur Ausführung einer bestimmten Aufgabe verwendet werden.
Beispiel: Wenn ich eine Tabelle Mitarbeiter
habe
Employee ID Name Age Mobile
---------------------------------------
001 Sidheswar 25 9938885469
002 Pritish 32 9178542436
Zuerst rufe ich die Tabelle "Mitarbeiter" ab:
Create Procedure Employee details
As
Begin
Select * from Employee
End
Um die Prozedur auf dem SQL Server auszuführen:
Execute Employee details
--- (Employee details is a user defined name, give a name as you want)
Als zweites füge ich den Wert in die Tabelle "Mitarbeiter" ein
Create Procedure employee_insert
(@EmployeeID int, @Name Varchar(30), @Age int, @Mobile int)
As
Begin
Insert Into Employee
Values (@EmployeeID, @Name, @Age, @Mobile)
End
Um die parametrisierte Prozedur auf SQL Server auszuführen:
Execute employee_insert 003,’xyz’,27,1234567890
--(Parameter size must be same as declared column size)
Beispiel: @Name Varchar(30)
In der Tabelle "Mitarbeiter" muss die Spalte "Name" die Größe "Varchar(30)" haben.
Im Allgemeinen ist eine gespeicherte Prozedur eine "SQL-Funktion." Sie haben:
-- a name
CREATE PROCEDURE spGetPerson
-- parameters
CREATE PROCEDURE spGetPerson(@PersonID int)
-- a body
CREATE PROCEDURE spGetPerson(@PersonID int)
AS
SELECT FirstName, LastName ....
FROM People
WHERE PersonID = @PersonID
Dies ist ein auf T-SQL ausgerichtetes Beispiel. Stored Procedures können die meisten SQL-Anweisungen ausführen, geben skalare und tabellenbasierte Werte zurück und gelten als sicherer, da sie SQL-Injection-Angriffe verhindern.