Benutzerdefinierte Stored Procedures in SQL

Du verwendest Microsoft SQL Server als Datenbank. Das in diesem Tutorial behandelte Konzept gilt im Prinzip für die meisten relationalen Datenbankmanagementsysteme, allerdings unterscheiden sich die Syntaxen. Die hier gezeigte Idee funktioniert jedoch für alle Datenbanken gut.

Eine Stored Procedure ist ein Block aus SQL-Anweisungen in einem Relational Database Management System (RDBMS). Sie wird in der Regel von Programmiererinnen und Programmierern, Datenbankadministratorinnen und -administratoren oder Data Analysts geschrieben, gespeichert und in mehreren Programmen wiederverwendet. Je nach RDBMS gibt es verschiedene Arten, doch die zwei wichtigsten Stored Procedures in jedem RDBMS sind:

• Benutzerdefinierte Stored Procedure
• System-Stored Procedure

Eine Stored Procedure ist vorbereiteter, kompilierter Code, der gespeichert bzw. zwischengespeichert und dann wiederverwendet wird. Durch den Cache kannst du Code mehrfach verwenden, was die Wartung deutlich erleichtert, da Änderungen nicht an zig Stellen nötig sind, und es hilft außerdem bei der Absicherung.

Eine Tabelle erstellen

Jetzt erstellst du eine Tabelle mit dem Namen table_Employees wie folgt.

CREATE TABLE table_Employees
(
    EmployeeId INT PRIMARY KEY NOT NULL,
    EmployeeFirstName VARCHAR(25) NOT NULL,
    EmployeeLastName VARCHAR(25) NOT NULL,
    EmployeeGender VARCHAR(25) NOT NULL,
    EmployeeDepartmentID INT

)

Die Tabelle enthält Informationen über Mitarbeitende in einem Unternehmen und umfasst folgende Spalten:

1. EmployeeId: Der Primärschlüssel, eindeutig und vom Datentyp Integer; der Wert wird automatisch hochgezählt.
2. EmployeeFirstName: Der Vorname eines Mitarbeitenden, Zeichentyp, darf nicht leer sein.
3. EmployeeLast Name: Der Nachname eines Mitarbeitenden, Zeichentyp, darf nicht leer sein.
4. EmployeeGender: Das Geschlecht des Mitarbeitenden, ebenfalls Zeichentyp.
5. EmployeeDepartmentId: Die Abteilungs-ID des Mitarbeitenden, Integer.

Du kannst eine Abfrage schreiben, um Werte in die Tabelle einzufügen. Die allgemeine Form zum Einfügen lautet:

    INSERT INTO <TABLE_NAME>(<COLUMNS_OF_TABLE>) VALUES(<CORRESPONDING_VALUES_TO_MATCH_COLUMN>)

Nachdem du die Werte eingefügt hast, ist table_Employees erstellt.

Benutzerdefinierte Stored Procedure erstellen

Mit Stored Procedures schreibst du eine Abfrage einmal, vergibst ihr einen Namen, speicherst sie und kannst sie bei Bedarf beliebig oft ausführen. Durch das Ausführen der Abfragen wird im Objekt-Explorer ein Ordner unter Programmability->Stored Procedures angelegt, darin liegt die Datei dbo.uprocGetEmployees.

Die Syntax zum Erstellen einer Stored Procedure ist:

CREATE PROCEDURE <<<procedure_name>>>
AS
BEGIN

   '''Required SQL Queries'''

END

Du kannst eine Stored Procedure mit einer der folgenden SQL-Anweisungen erstellen.

• CREATE PROCEDURE procedure_name
• CREATE PROC procedure_name

Verwende außerdem eine Namenskonvention ohne das Präfix "sp_". Im Beispiel heißt die Stored Procedure: uprocGetEmployees.

Stored Procedure ausführen

Zum Ausführen einer Stored Procedure gibt es drei Möglichkeiten:

• EXEC <>
• EXECUTE <>
• <>

Im obigen Programm kannst du den Prozedurnamen uprocGetEmployees verwenden und ihn zur Ausführung auswählen.

Stored Procedure mit Parametern

Stored Procedures können einzelne oder mehrere Parameter annehmen. Mehrere Parameter zu verstehen, macht das Verständnis für einzelne Parameter besonders einfach.

Die Syntax zum Erstellen einer Stored Procedure mit mehreren Parametern lautet:

CREATE PROCEDURE <<procedure_name>> <<procedure_parameter>>
AS
    BEGIN
            <<sql_query>>
    END

Im Beispiel hat sich der Ordnerpfad entsprechend geändert.
EMPLOYEE->Programmability->Stored Procedures->dbo.uprocGetEmployessGenderAndDepartment

Das Konzept ähnelt Funktionen in gängigen Programmiersprachen wie Python oder Java. Die oben gezeigte Prozedur heißt "uprocGetEmployeesGenderAndDepartment" und hat die Parameter @EmployeeGender und @EmployeeDepartmentId. Sie werden „aufgerufen“, indem du die benötigten Werte an die Prozedur übergibst. Das geht auf zwei Arten, die unten erklärt sind.

Die Parameter in diesem Programm sind @EmployeeGender und @EmployeeDepartmentId; sie werden angesprochen, indem du ihnen einen Wert übergibst.

Die Werte kannst du auf zwei Arten angeben. Du kannst eine der beiden Methoden nutzen, um die Stored Procedure mit mehreren Parametern auszuführen.

• Indem du in der Abfrage die Parameternamen nennst und die gewünschten Werte übergibst:

• Oder indem du die Werte in der Reihenfolge übergibst, die der Parameterreihenfolge der Stored Procedure entspricht:

Stored Procedure ändern

Du kannst eine Stored Procedure auch mit dem Befehl ALTER PROCEDURE anpassen.

    ALTER PROCEDURE uprocGetEmployeesGenderAndDepartment
@EmployeeGender nvarchar(25),
@EmployeeDepartmentId int
BEGIN
    SELECT EmployeeFirstName,EmployeeGender,EmployeeDepartmentId FROM table_Employees WHERE
    EmployeeGender = @EmployeeGender AND EmployeeDepartmentId = @EmployeeDepartmentId
END

Stored Procedure löschen

Stored Procedures kannst du schnell mit folgenden Befehlen löschen:

DROP PROC <> DROP PROCEDURE <>

Sicherheit per Verschlüsselung hinzufügen

Das Schlosssymbol links im Bild zeigt an, dass die Stored Procedure verschlüsselt ist. So haben nur autorisierte Personen Zugriff.

Fazit

Du hast soeben die Grundlagen benutzerdefinierter Stored Procedures kennengelernt. Die behandelten Themen sind konzeptionell für jedes RDBMS relevant. Mehr erfährst du auf DataCamp im Kurs Intermediate SQL Server. Außerdem hilft dir dieses Tutorial zu Stored Procedures beim Vertiefen.