Konzepte für gespeicherte Prozeduren

Eine gespeicherte Prozedur ist eine Sammlung von einer oder mehreren T-SQL-Anweisungen, die in einer Gruppe von logischen Verarbeitungseinheiten zusammengefasst und auf dem Datenbankserver gespeichert werden. Wenn die gespeicherte Prozedur ausgeführt wird, führt SQL Server sie beim ersten Aufruf aus und speichert sie in einem Cache, dem so genannten Plan-Cache, beim nächsten Mal verwendet SQL Server den Plan-Cache wieder, sodass die Verarbeitung beschleunigt wird. Dies ist sehr effektiv beim Optimieren großer Datenbanken mit Hochleistungsanwendungen

Darüber hinaus ist die gespeicherte Prozedur für Datenbankadministratoren (DBA) äußerst praktisch. Sie hilft DBA, benannte Befehlsblöcke zu erstellen und sie an Entwickler zu senden, ohne sich darum zu kümmern, was die gespeicherte Prozedur enthält. Kümmern Sie sich einfach um die Eingabe- und Ausgabeparameter. Wenn Sie beispielsweise eine gespeicherte Prozedur schreiben, die die Liste der Bestellungen für den Monat abruft und sie dann dem Entwickler zur Integration in die Anwendung gibt, muss der Entwickler nicht in die Datenbank eingreifen, um neuen Code zu schreiben, sondern nur die gespeicherte aufrufen Prozedur , die Sie deklariert haben, und verwenden Sie sie einfach.

Sie schreiben zum Beispiel eine gespeicherte Prozedur, um eine Liste der meistverkauften Produkte pro Tag zu erhalten, dann senden Sie diese gespeicherte mit dem Benutzerhandbuch an die Entwicklungsabteilung, dann interessiert sich die DEV-Abteilung nicht für den darin enthaltenen Inhalt, sondern nur für gespeicherte benötigen Informationen über die übergebenen Parameter und das gespeicherte Rückgabeergebnis.

Vorteile der Verwendung von Stored Procedures:

• Verbesserte Sicherheit: Datenbankadministratoren können die Sicherheit erhöhen, indem sie Berechtigungen mit Speicherprozeduren verknüpfen. Benutzer können die Berechtigung erhalten, gespeicherte Prozeduren auszuführen, obwohl sie keinen Zugriff auf Tabellen oder Ansichten haben.
• Vorkompilierte Ausführung: Die gespeicherte Prozedur wird bei der ersten Ausführung kompiliert. Bei jeder nachfolgenden Ausführung verwendet SQL Server die vorkompilierte Version erneut, wodurch die Ausführungszeit verkürzt und die Leistung gesteigert wird.
• Reduzierter Client/Server-Datenverkehr: Die gespeicherte Prozedur hilft, den Systemdatenverkehr zu reduzieren, wenn die T-SQL-Anweisung ausgeführt wird, verwendet das Netzwerk separate Ressourcen für jede Ausführung. Wenn die gespeicherte Prozedur ausgeführt wird, werden die SQL-Anweisungen gruppiert und als Einheit ausgeführt, wodurch der Netzwerkverkehr reduziert wird.
• Wiederverwendung von Code: Gespeicherte Prozeduren können viele Male wiederverwendet werden. Dadurch entfällt die Notwendigkeit, jedes Mal Hunderte von Transact-SQL-Anweisungen einzugeben, wenn eine ähnliche Aufgabe ausgeführt wird.

Arten von gespeicherten Prozeduren

Benutzerdefinierte gespeicherte Prozeduren :

können benutzerdefinierte gespeicherte Prozeduren genannt werden, diese Prozeduren werden verwendet, um T-SQL-Anweisungen für die Verarbeitung von lapwj-Aufgaben wiederzuverwenden. Es gibt zwei Arten von benutzerdefinierten gespeicherten Prozeduren: gespeicherte T-SQL-Prozeduren und gespeicherte Prozeduren der Common Language Runtime (CLR). Gespeicherte CLR-Prozeduren basieren auf mehreren Methoden von .NET Framework, die beide Parameter entgegennehmen und zurückgeben können.

Gespeicherte Prozeduren erweitern:

Extend Stored Procedure hilft SQL Server bei der Interaktion mit dem Betriebssystem. Erweiterte gespeicherte Prozeduren befinden sich nicht in Objekten von SQL Server. Sie sind gespeicherte Prozeduren, die als Dinamic Link Libraries (DDL) entwickelt wurden und außerhalb der SQL Server-Umgebung ausgeführt werden. Die Anwendung interagiert mit SQL Server und ruft die DLL während der Ausführung auf, SQL Server weist Speicherplatz zu, um die erweiterten gespeicherten Prozeduren auszuführen. Erweiterte gespeicherte Prozeduren verwenden das Präfix „xp“.

Gespeicherte Systemprozeduren:

Gespeicherte Systemprozeduren werden häufig verwendet, um mit Systemtabellen zu interagieren und Verwaltungsaufgaben wie das Aktualisieren von Systemtabellen und gespeicherten Systemprozeduren mit dem Präfix „sp_“ zu erledigen. Diese Prozeduren befinden sich in der Datenbank Resource . Prozeduren können im sys-Schema jedes Systems oder jeder benutzerdefinierten Datenbank gefunden werden. Gespeicherte Systemprozeduren ermöglichen GRANT-, DENY- und REVOKE -Berechtigungen.

Eine gespeicherte Systemprozedur ist ein vorkompilierter Satz von T-SQL-Anweisungen, die als Einheit ausgeführt werden. Systemprozeduren werden bei der Datenbankverwaltung und Verwaltung von Systemaktivitäten und -informationen verwendet. Diese Prozeduren bieten eine Lösung für den einfachen Zugriff auf Metadateninformationen zu Datenbankobjekten wie Systemtabellen, benutzerdefinierten Tabellen, Ansichten und Indizes.

Gespeicherte Systemprozeduren erscheinen logisch im sys-Schema des Systems und in der benutzerdefinierten Datenbank. Wenn auf gespeicherte Systemprozeduren verwiesen wird, wird die sys -Schemakennung verwendet. Gespeicherte Systemprozeduren im System werden physisch in einer versteckten Datenbank innerhalb einer Datenbankressource mit dem Präfix sp_ gespeichert . Gespeicherte Systemprozeduren gehören dem Datenbankadministrator (der höchsten Verwaltungsebene im Datenbanksystem).

Hinweis: Systemtabellen werden standardmäßig zum Zeitpunkt der Erstellung einer neuen Datenbank erstellt. Diese Tabellen speichern Metadateninformationen zu benutzerdefinierten Objekten wie Tabellen und Ansichten. Benutzer können nicht mithilfe von gespeicherten Systemprozeduren auf Systemtabellen zugreifen oder diese aktualisieren, es sei denn, der Datenbankadministrator erteilt ihnen Berechtigungen.

Klassifizierung von gespeicherten Systemprozeduren

• Gespeicherte Katalogprozeduren: Alle Informationen zu Tabellen in der Datenbank werden in einem Satz von Tabellen gespeichert, der als Systemkatalog bezeichnet wird. Informationen aus dem Systemkatalog können mithilfe von Katalogprozeduren abgerufen werden. Beispielsweise ist L sp_tables eine gespeicherte Katalogprozedur, die eine Liste der Tabellen der aktuellen Datenbank anzeigt
• Gespeicherte Sicherheitsprozeduren: Gespeicherte Sicherheitsprozeduren werden verwendet, um die Sicherheit in der Datenbank zu verwalten. Beispielsweise ist sp_changedbowner eine gespeicherte Sicherheitsprozedur, die verwendet wird, um den Eigentümer der aktuellen Datenbank zu ändern.
• Gespeicherte Cursorprozeduren: Cursorprozeduren werden verwendet, um Zeigerfunktionen zu implementieren. Beispielsweise ist sp_cursor_list eine gespeicherte Prozedur, die alle von der Verbindung geöffneten Zeiger abruft und ihre Eigenschaften beschreibt.
• Gespeicherte Prozeduren für verteilte Abfragen: Verteilte gespeicherte Prozeduren werden verwendet, um verteilte Abfragen zu verwalten. Beispielsweise ist sp_indexes eine gespeicherte Prozedur für verteilte Abfragen, die Indexinformationen für eine bestimmte Tabelle zurückgibt.
• Gespeicherte Prozeduren für Datenbank-E-Mail und SQL Mail: werden verwendet, um Aufgaben zu erledigen, die mit E-Mail in SQL Server arbeiten. Beispielsweise ist sp_send_dbmail eine gespeicherte Datenbank-E-Mail-Prozedur, die eine E-Mail an einen angegebenen Empfänger sendet. Der E-Mail-Inhalt kann ein Ergebnissatz, eine Anhangsdatei oder beides sein.

Temporär gespeicherte Prozeduren

Gespeicherte Prozeduren, die für die vorübergehende Verwendung innerhalb einer Sitzung erstellt wurden, werden als temporär gespeicherte Prozeduren bezeichnet. Diese Prozeduren werden in der tempdb- Tabelle gespeichert. Die Systemtabelle tempdb ist eine globale Ressource, die allen Benutzern zur Verfügung steht, die eine Verbindung über die SQL Server-Instanz herstellen. Es enthält alle temporären Tabellen und temporär gespeicherten Prozeduren.

SQL Server unterstützt zwei Arten von temporär gespeicherten Prozeduren, lokal und global, mit den folgenden Unterschieden:

Hinweis: Eine Sitzung wird eingerichtet, wenn der Benutzer eine Verbindung zur Datenbank herstellt, und endet, wenn der Benutzer die Verbindung trennt. Der vollständige Name der globalen temporären gespeicherten Prozedur einschließlich des Präfixes ## darf 128 Zeichen nicht überschreiten. Der endgültige Name der lokal temporär gespeicherten Prozedur einschließlich des #-Präfixes darf 116 Zeichen nicht überschreiten.

Remote Store-Prozeduren

Gespeicherte Prozeduren, die auf einem Remote-SQL-Server ausgeführt werden können, werden als gespeicherte Remote-Prozeduren bezeichnet. Das Remote-Soted-Verfahren kann nur verwendet werden, wenn der Server einen Remote-Zugriff zulässt (Remote-Zugriff, kein lokaler Zugriff). Wenn die remote gespeicherte Prozedur von einer lokalen SQL Server-Instanz auf dem Client ausgeführt wird, kann die Anweisung auf einen Abbruchfehler stoßen. Wenn ein Fehler auftritt, wird der Befehl, der den Fehler verursacht hat, beendet, aber die remote gespeicherte Prozedur wird weiterhin ausgeführt.

Erweiterte gespeicherte Prozeduren

Erweiterte gespeicherte Prozeduren werden verwendet, um Aufgaben zu verarbeiten, die mit normalen T-SQL-Syntaxen nicht verarbeitet werden können. Erweiterte gespeicherte Prozeduren verwenden das Präfix „xp_“ . Diese gespeicherten Prozeduren werden im dbo -Schema des Datenbankmasters gespeichert.

Syntax:

 EXECUTE <procedure_name>

Beispiel für die Verwendung der erweiterten gespeicherten Prozedur xp_fileexist, um zu prüfen, ob die Datei Mytext.txt vorhanden ist:

 EXECUTE xp_fileexist 'CMyTest.txt'

Benutzerdefinierte gespeicherte Prozedur

In SQL Server dürfen Benutzer benutzerdefinierte gespeicherte Prozeduren erstellen, um verschiedene Aufgaben auszuführen. Diese gespeicherten Prozeduren werden als benutzerdefinierte oder benutzerdefinierte gespeicherte Prozeduren bezeichnet.

Wenn beispielsweise die Customer_Details -Tabelle alle Kundendaten speichert, müssten wir jedes Mal T-SQL-Anweisungen schreiben, wenn wir die detaillierten Daten über den Kunden anzeigen möchten. Stattdessen können wir eine benutzerdefinierte gespeicherte Prozedur erstellen, die Kundendetails jedes Mal anzeigt, wenn die Prozedur zur Wiederverwendung ausgeführt wird.

Das Erstellen einer benutzerdefinierten gespeicherten Prozedur erfordert die CREATE PROCEDURE-Berechtigung in der Datenbank und die ALTER-Schema-Berechtigung für die erstellte Prozedur.

Syntax:

 CREATE {PROC|PROCEDURE} proc_ame [{@parameter data_type}] AS <sql_statement>

Beispiel für die Initialisierung einer benutzerdefinierten gespeicherten Prozedur namens uspGetCustTerritory, die zum Anzeigen von Kundendetails verwendet wird:

 use AdventureWorks2019 CREATE PROCEDURE uspGetCustTerritory AS SELECT TOP 10 CustomerID,Customer.TerritoryID,Sales.SalesTerritory.Name FROM Sales.Customer JOIN Sales.SalesTerritory ON Sales.Customer.TerritoryID = Sales.SalesTerritory.TerritoryID go exec uspGetCustTerritory

Verwenden von Parametern (Parameter)

Die Leistungsfähigkeit der gespeicherten Prozedur ergibt sich aus der Möglichkeit, Parameter darin zu platzieren, die Daten werden durch das aufrufende Programm an die gespeicherte Prozedur übergeben, es gibt zwei Arten von Parametern wie folgt:

• Eingabeparameter ermöglichen es dem aufrufenden Programm, Werte an die gespeicherte Prozedur zu übergeben. Diese Werte werden in der in den gespeicherten Prozeduren definierten Variablen erfasst.
• Ausgabeparameter ermöglichen es der gespeicherten Prozedur, Werte an das aufrufende Programm zurückzugeben. Diese Werte werden in der Variablen des aufrufenden Programms abgeholt.

Eingabeparameter

Werte werden vom aufrufenden Programm an die gespeicherte Prozedur übergeben, diese Werte werden in der in den gespeicherten Prozeduren definierten Variablen erfasst. Der Eingabeparameter wird zum Zeitpunkt der Erstellung der gespeicherten Prozedur definiert. Die an den Eingabeparameter übergebenen Werte können Variablen oder Konstanten sein, der Wert wird zum Zeitpunkt des Aufrufs der Prozedur an die gespeicherte Prozedur übergeben. Speicherprozeduren verarbeiten bestimmte Aufgaben mit diesen Werten.

Syntax:

 CREATE PROCEDURE <procedure_name> @parameter <data_type> AS <sql_statement> EXEC| EXECUTE <procedure_name> <parameters>

Erstellen Sie beispielsweise eine gespeicherte uspGetSales- Prozedur mit dem Gebietsparameter, um den Namen des Gebiets abzurufen und die Verkaufsdetails und die Verkäufer-ID für dieses Gebiet anzuzeigen. Als Eingabeparameter wird dann der Code übergeben, der die Stored Procedure mit dem Wert 'Northwest' ausführt:

 use AdventureWorks2019; GO CREATE PROCEDURE uspGetSales @territory varchar(40) AS SELECT BusinessEntityID, B.SalesYTD, B.SalesLastYear FROM Sales.SalesPerson A JOIN sales.SalesTerritory B ON A.TerritoryID = B.TerritoryID WHERE B.Name=@territory; GO exec uspGetSales 'Northwest'

Ausgangsparameter

Gespeicherte Prozeduren müssen manchmal Ausgaben an das aufrufende Programm zurückgeben. Es übergibt Daten von der gespeicherten Prozedur an das aufrufende Programm und wird über Ausgabeparameter verarbeitet. Ausgabeparameter werden zum Zeitpunkt der gespeicherten Prozedur definiert. Zur Angabe des Ausgabeparameters wird bei der Deklaration des Parameters das Schlüsselwort OUTPUT verwendet. Und beim Aufrufen der Anweisung muss die Variable auch mit dem Schlüsselwort OUTPUT definiert werden.

Syntax:

 CREATE PROCEDURE <procedure_name> @parameter <data_type> OUTPUT AS <sql_statement> EXEC| EXECUTE <procedure_name> <parameters>

Beispiel für eine gespeicherte Prozedur upsGetTotalSales mit dem Eingabeparameter @territory zum Abrufen des Namens des Gebiets und dem Ausgabeparameter @sum zum Abrufen der Summe des bisherigen Verkaufsjahres:

 use AdventureWorks2019; GO CREATE PROCEDURE uspGetTotalSales @territory varchar(40), @sum int OUTPUT AS SELECT @sum= SUM(B.SalesYTD) FROM Sales.SalesPerson A JOIN Sales.SalesTerritory B ON A.TerritoryID = B.TerritoryID WHERE B.Name = @territory GO

Im nächsten Schritt führen wir die obige gespeicherte Prozedur aus, indem wir die Variable @sumsale deklarieren, um den Ausgabewert zu erhalten.

 DECLARE @sumsale int; exec uspGetTotalSales 'NorthWest', @sumsale OUTPUT; SELECT @sumsale AS 'Total sales northWest';

OUTPUT-Parameter haben folgende Eigenschaften:

• Text- oder Bilddatentyp kann nicht übertragen werden
• Die Aufrufanweisung muss eine Variable enthalten, um den Ausgabewert zu erhalten.
• Variable, die im nächsten Callback der T-SQL-Anweisung verwendet werden kann, um sie an den Benutzer zurückzugeben

Die OUTPUT-Klausel gibt Informationen für jede Zeile zurück, in der die INSERT-, UPDATE- und DELETE-Anweisungen ausgeführt wurden. Diese Klausel ist nützlich, um die Identität oder berechnete Spaltenwerte nach dem Ausführen einer INSERT- oder UPDATE-Operation abzurufen.

Natürlich können wir auch SSMS verwenden, um Stored Procedures zu erstellen.

ALTER (Ändern) Gespeicherte Prozedur

Die der gespeicherten Prozedur zugeordneten Berechtigungen gehen verloren, wenn die gespeicherte Prozedur neu initialisiert wird. Wenn sie jedoch über die ALTER-Anweisung geändert werden, bleiben die für die Prozedur definierten Berechtigungen im Zustand.

Syntax:

 ALTER PROCEDURE <procedure_name> @parameter <data_type> [OUTPUT] [WITH {ENCRYPTION|RECOMPILE}] AS <sql_statement>

Ändern Sie die Definition einer gespeicherten Prozedur mit dem Namen uspGetTotals , um eine CostYTD- Spalte in der Sales.SalesTerritory- Tabelle hinzuzufügen

 ALTER PROCEDURE [dbo].[uspGetTotal] @territory varchar = 40 AS SELECT BusinessEntityID, B.SalesYTD, B.CostYTD, B.SalesLastYear FROM Sales.SalesPerson A JOIN Sales.SalesTerritory B ON A.TerritoryID = B.TerritoryID WHERE B.Name = @territory GO

Hinweis: Wenn Sie die Definition in der gespeicherten Prozedur ändern, können die abhängigen Objekte einen Ausführungsfehler aufweisen. Dieses Problem tritt auf, wenn die abhängigen Objekte nicht aktualisiert werden, um die Änderung der gespeicherten Prozedur widerzuspiegeln.

Gespeicherte DROP-Prozedur

Gespeicherte Prozeduren können gelöscht werden, wenn sie nicht mehr benötigt werden.Wenn das Programm eine gelöschte Prozedur aufruft, wird ein Fehler zurückgegeben.

Wenn eine neue Prozedur mit demselben Namen und denselben Parametern wie die gelöschte Prozedur erstellt wird, werden alle Aufrufe der alten Prozedur erfolgreich ausgeführt, da sie auf die neue Prozedur mit demselben Namen und denselben Parametern wie die gelöschte Prozedur verweist.

Bevor Sie die gespeicherte Prozedur löschen, können Sie eine gespeicherte Systemprozedur namens sp_depends ausführen, um zu ermitteln, welche Objekte von der Prozedur abhängen.

Syntax:

 DROP PROCEDURE <procedure_name>

 DROP PROCEDURE uspGetTotals

Verschachtelte gespeicherte Prozeduren

SQL Server 2019 ermöglicht das Aufrufen gespeicherter Prozeduren innerhalb anderer gespeicherter Prozeduren. Diese Architektur für gespeicherte Prozeduren ruft eine andere gespeicherte Prozedur auf, die als verschachtelte gespeicherte Prozedur bezeichnet wird.

Wenn eine gespeicherte Prozedur eine andere gespeicherte Prozedur aufruft, wird die Verschachtelungsschicht um eins erhöht. Wenn die gespeicherte Prozedur die Ausführung abschließt und die Ablaufsteuerung an die aufrufende gespeicherte Prozedur übergibt, wird die Verschachtelungsschicht auf ähnliche Weise um eins reduziert. Die maximale von SQL Server 2019 unterstützte Verschachtelungsebene ist 32.

Zum Beispiel:

 CREATE PROCEDURE NestedProcedure AS BEGIN EXEC uspGetCustTerritory EXEC uspGetSales 'France' END