Erstellen einer gespeicherten SQL-Prozedur. Gespeicherte Prozeduren in der MS-SQL-Serverumgebung. Erstellen einer gespeicherten Prozedur

Gespeicherte Prozeduren

Das Thema dieses Kapitels ist eines der leistungsstärksten Tools, die Entwicklern von InterBase-Datenbankanwendungen zur Implementierung von Geschäftslogik angeboten werden. Gespeicherte Prozeduren (englisch, stoied proceduies) ermöglichen es Ihnen, einen erheblichen Teil der Anwendungslogik auf Datenbankebene zu implementieren und damit zu steigern Sie verbessern die Leistung der gesamten Anwendung, zentralisieren die Datenverarbeitung und reduzieren die Menge an Code, die zur Erledigung zugewiesener Aufgaben erforderlich ist. Nahezu jede ziemlich komplexe Datenbankanwendung kommt nicht ohne die Verwendung gespeicherter Prozeduren aus.
Zusätzlich zu diesen bekannten Vorteilen der Verwendung gespeicherter Prozeduren, die den meisten relationalen DBMS gemeinsam sind, können gespeicherte InterBase-Prozeduren als nahezu vollständige Datensätze fungieren, sodass die von ihnen zurückgegebenen Ergebnisse in normalen SQL-Abfragen verwendet werden können.
Anfänger stellen sich gespeicherte Prozeduren oft einfach als eine Reihe spezifischer SQL-Abfragen vor, die etwas innerhalb der Datenbank tun, und es besteht die Meinung, dass die Arbeit mit gespeicherten Prozeduren viel schwieriger ist als die Implementierung derselben Funktionalität in einer Clientanwendung in einem hochentwickelten System. Ebene Sprache
Was sind also gespeicherte Prozeduren in InterBase?
Eine gespeicherte Prozedur (SP) ist ein Teil der Datenbankmetadaten, bei dem es sich um eine in die interne Darstellung von InterBase kompilierte Unterroutine handelt, die in einer speziellen Sprache geschrieben ist und deren Compiler in den Kern des InteiBase-Servers integriert ist
Eine gespeicherte Prozedur kann von Clientanwendungen, von Triggern und von anderen gespeicherten Prozeduren aufgerufen werden. Die gespeicherte Prozedur wird im Serverprozess ausgeführt und kann Daten in der Datenbank manipulieren sowie die Ergebnisse ihrer Ausführung an den Client zurückgeben, der sie aufgerufen hat (d. h. Trigger, HP, Anwendung).
Die Grundlage der leistungsstarken Funktionen von HP ist eine prozedurale Programmiersprache, die sowohl modifizierte Anweisungen von regulärem SQL wie INSERT, UPDATE und SELECT als auch Tools zum Organisieren von Verzweigungen und Schleifen (IF, WHILE) umfasst Fehlerbehandlungstools und Ausnahmesituationen Mit der Sprache gespeicherter Prozeduren können Sie komplexe Algorithmen für die Arbeit mit Daten implementieren. Aufgrund des Fokus auf die Arbeit mit relationalen Daten ist HP viel kompakter als ähnliche Prozeduren in herkömmlichen Sprachen.
Es ist zu beachten, dass für Trigger die gleiche Programmiersprache verwendet wird, mit Ausnahme einer Reihe von Funktionen und Einschränkungen. Die Unterschiede zwischen der Teilmenge der in Triggern verwendeten Sprache und der HP-Sprache werden ausführlich im Kapitel „Trigger“ (Teil 1) besprochen.

Beispiel einer einfachen gespeicherten Prozedur

Es ist an der Zeit, Ihre erste gespeicherte Prozedur zu erstellen und sie als Beispiel zu verwenden, um den Prozess der Erstellung gespeicherter Prozeduren zu erlernen. Aber zuerst sollten wir ein paar Worte zur Arbeit mit gespeicherten Prozeduren sagen. Tatsache ist, dass HP seinen Ruf als obskures und unbequemes Tool den extrem schlechten Standardtools zum Entwickeln und Debuggen gespeicherter Prozeduren verdankt. In der InterBase-Dokumentation wird empfohlen, Prozeduren mithilfe von SQL-Skriptdateien zu erstellen, die HP-Text enthalten, die als Eingabe für den ISO-Interpreter bereitgestellt werden, und so HP If in diesem SQL-Skript in der Phase der Kompilierung des Textes der Prozedur in BLR zu erstellen und zu ändern (ca BLR, siehe Kapitel „InterBase-Datenbankstruktur“ (Teil 4)) Wenn ein Fehler auftritt, zeigt isql eine Meldung darüber an, in welcher Zeile der SQL-Skriptdatei dieser Fehler aufgetreten ist. Korrigieren Sie den Fehler und wiederholen Sie den Vorgang. Von Debugging im modernen Sinne des Wortes, also der Ausführungsverfolgung mit der Möglichkeit, Zwischenwerte von Variablen anzuzeigen, ist überhaupt keine Rede. Offensichtlich trägt dieser Ansatz nicht zur Steigerung der Attraktivität gespeicherter Prozeduren in den Augen des Entwicklers bei
Allerdings zusätzlich zum standardmäßigen minimalistischen Ansatz der HP-Entwicklung<_\ществ\ют также инструменты сторонних разработчиков, которые делают работу с хранимыми процедурами весьма удобной Большинство универсальных продуктов для работы с InterBase, перечисленных в приложении "Инструменты администратора и разработчика InterBase", предоставляют удобный инструментарий для работы с ХП. Мы рекомендуем обязательно воспользоваться одним из этих инструментов для работы с хранимыми процедурами и изложение материала будем вести в предположении, что у вас имеется удобный GUI-инструмент, избавляющий от написания традиционных SQL-скриптов
Die Syntax gespeicherter Prozeduren wird wie folgt beschrieben:

CREATE PROCEDURE-Name
[ (param datatype [, param datatype ...]) ]
)]
ALS
;
< procedure_body> = []
< block>
< vanable_declaration_list> =
DECLARE VARIABLE var Datentyp;

=
BEGINNEN
< compound_statement>
[< compound_statement> ...]
ENDE
< compound_statement> = (Stellungnahme;)

Es sieht ziemlich umfangreich aus und ist vielleicht sogar umständlich, aber in Wirklichkeit ist alles sehr einfach. Um die Syntax nach und nach zu beherrschen, schauen wir uns nach und nach komplexere Beispiele an.
Hier ist ein Beispiel für eine sehr einfache gespeicherte Prozedur, die zwei Zahlen als Eingabe verwendet, diese addiert und das Ergebnis zurückgibt:

CREATE PROCEDURE SP_Add(first_arg DOUBLE PRECISION,
second_arg (DOUBLE PRECISION)
RETURNS (Ergebnis DOPPELTE PRÄZISION)
ALS
BEGINNEN
Ergebnis=first_arg+second_arg;
AUSSETZEN;
ENDE

Wie Sie sehen, ist alles einfach: Nach dem Befehl CREATE PROCEDURE wird der Name der neu erstellten Prozedur angegeben (der innerhalb der Datenbank eindeutig sein muss) – in diesem Fall SP_Add, dann sind es die HP-Eingabeparameter – first_arg und second_arg werden in Klammern aufgeführt, durch Kommas getrennt und geben ihren Typ an.
Die Liste der Eingabeparameter ist ein optionaler Teil der CREATE PROCEDURE-Anweisung – es gibt Fälle, in denen eine Prozedur alle Daten für ihre Arbeit durch Abfragen an Tabellen im Hauptteil der Prozedur erhält.

Gespeicherte Prozeduren verwenden beliebige skalare Datentypen. InteiBase erlaubt nicht die Verwendung von Arrays und benutzerdefinierten Typen – Domänen

Als nächstes kommt das Schlüsselwort RETURNS, nach dem die zurückgegebenen Parameter in Klammern aufgeführt werden und ihre Typen angeben – in diesem Fall nur einen – Ergebnis.
Wenn die Prozedur keine Parameter zurückgeben soll, fehlen das Wort RETURNS und die Liste der zurückgegebenen Parameter.
Nach RETURNSQ wird das Schlüsselwort AS angegeben. Bevor das Schlüsselwort AS kommt Titel, und danach - Technik Verfahren.
Der Hauptteil einer gespeicherten Prozedur ist eine durch ein Semikolon (;) getrennte Liste von Beschreibungen ihrer internen (lokalen) Variablen (falls vorhanden, wir werden sie weiter unten genauer betrachten) und ein in Operatorklammern eingeschlossener Block von Anweisungen ANFANG ENDE. In diesem Fall ist der Hauptteil des HP sehr einfach: Wir bitten darum, zwei Eingabeargumente hinzuzufügen, ihr Ergebnis dem Ausgabeargument zuzuweisen und dann den Befehl SUSPEND aufzurufen. Etwas später werden wir die Essenz der Aktion dieses Befehls erklären, aber zunächst nur darauf hinweisen, dass er erforderlich ist, um die Rückgabeparameter dorthin zu übertragen, wo die gespeicherte Prozedur aufgerufen wurde.

Trennzeichen in gespeicherten Prozeduren

Beachten Sie, dass eine Anweisung innerhalb einer Prozedur mit einem Semikolon (;) endet. Wie Sie wissen, ist das Semikolon ein Standard-Befehlstrennzeichen in SQL – es ist ein Signal an den SQL-Interpreter, dass der Befehlstext vollständig eingegeben wurde und mit der Verarbeitung beginnen soll. Wäre es nicht so, dass der SQL-Interpreter, wenn er ein Semikolon in der Mitte des HP findet, davon ausgeht, dass der Befehl vollständig eingegeben wurde, und versucht, einen Teil der gespeicherten Prozedur auszuführen? Diese Annahme ist nicht unbegründet. Wenn Sie tatsächlich eine Datei erstellen, in die Sie das obige Beispiel schreiben, einen Verbindungsbefehl aus der Datenbank hinzufügen und versuchen, dieses SQL-Skript mit dem ISO-Interpreter auszuführen, wird aufgrund des nach Ansicht des Interpreters unerwarteten Endes ein Fehler zurückgegeben des Befehls zum Erstellen einer gespeicherten Prozedur. Wenn Sie gespeicherte Prozeduren mithilfe von SQL-Skriptdateien erstellen, ohne spezielle InterBase-Entwicklertools zu verwenden, müssen Sie vor jedem HP-Erstellungsbefehl (dasselbe gilt für Trigger) das Skriptbefehlstrennzeichen in ein anderes Zeichen als ein Semikolon und nach dem Text HP ändern um es wiederherzustellen. Der isql-Befehl, der das SQL-Klauseltrennzeichen ändert, sieht folgendermaßen aus:

Laufzeit festlegen

Ein typischer Fall der Erstellung einer gespeicherten Prozedur sieht folgendermaßen aus:

TERM EINSTELLEN^;
PROZEDUR ERSTELLEN some_procedure
... . .
ENDE
^
BEGRIFF EINSTELLEN ;^

Aufrufen einer gespeicherten Prozedur

Aber kehren wir zu unserer gespeicherten Prozedur zurück. Nachdem es nun erstellt wurde, müssen Sie es irgendwie aufrufen, ihm Parameter übergeben und die Ergebnisse zurückgeben. Das geht ganz einfach – schreiben Sie einfach eine SQL-Abfrage wie diese:

WÄHLEN *
FROM Sp_add(181.35, 23.09)

Diese Abfrage gibt uns eine Zeile mit nur einem Ergebnisfeld zurück, das die Summe der Zahlen 181,35 und 23,09, also 204,44, enthält.
Somit kann unser Verfahren in gewöhnlichen SQL-Abfragen verwendet werden, die sowohl in Client-Programmen als auch in anderen HP oder Triggern ausgeführt werden. Diese Verwendung unserer Prozedur wird durch die Verwendung des SUSPEND-Befehls am Ende der gespeicherten Prozedur ermöglicht.
Tatsache ist, dass es in InterBase (und in allen seinen Klonen) zwei Arten von gespeicherten Prozeduren gibt: auswählbare Prozeduren und ausführbare Prozeduren. Der Unterschied in der Funktionsweise dieser beiden HP-Typen besteht darin, dass Sampling-Prozeduren normalerweise viele Sätze von Ausgabeparametern zurückgeben, die Zeile für Zeile gruppiert sind und wie ein Datensatz aussehen, während ausführbare Prozeduren entweder überhaupt keine Parameter oder nur solche zurückgeben können ein Satz von Ausgabeparametern, aufgelistet in Returns, wobei eine Zeile mit Parametern. Auswahlprozeduren werden in SELECT-Abfragen aufgerufen und ausführbare Prozeduren werden mit dem Befehl EXECUTE PROCEDURE aufgerufen.
Beide Arten von gespeicherten Prozeduren haben die gleiche Erstellungssyntax und unterscheiden sich formal nicht, sodass jede ausführbare Prozedur in einer SELECT-Abfrage aufgerufen werden kann und jede Auswahlprozedur mit EXECUTE PROCEDURE aufgerufen werden kann. Die Frage ist, wie sich HP bei verschiedenen Arten von Anrufen verhält. Mit anderen Worten: Der Unterschied liegt in der Gestaltung des Verfahrens für eine bestimmte Anrufart. Das heißt, die Auswahlprozedur wird speziell für den Aufruf aus einer SELECT-Abfrage erstellt, und die ausführbare Prozedur wird speziell für den Aufruf mit EXECUTE PROCEDURE erstellt. Schauen wir uns die Unterschiede im Design dieser beiden HP-Typen an.
Um zu verstehen, wie das Stichprobenverfahren funktioniert, müssen Sie etwas tiefer in die Theorie eintauchen. Stellen wir uns eine reguläre SQL-Abfrage wie SELECT ID, NAME FROM Table_example vor. Als Ergebnis seiner Ausführung erhalten wir eine Tabelle, die aus zwei Spalten (ID und NAME) und einer bestimmten Anzahl von Zeilen besteht (gleich der Anzahl der Zeilen in der Tabelle Table_example). Die als Ergebnis dieser Abfrage zurückgegebene Tabelle wird auch als SQL-Datensatz bezeichnet. Überlegen wir, wie der Datensatz während der Ausführung dieser Abfrage gebildet wird. Der Server bestimmt nach Erhalt der Abfrage, auf welche Tabellen er sich bezieht, und findet sie dann Finden Sie heraus, welche Teilmenge der Datensätze aus diesen Tabellen in das Abfrageergebnis einbezogen werden muss. Als nächstes liest der Server jeden Datensatz, der den Abfrageergebnissen entspricht, wählt daraus die erforderlichen Felder aus (in unserem Fall ID und NAME) und sendet sie an den Client. Dann wird der Vorgang noch einmal wiederholt – und so weiter für jeden ausgewählten Datensatz.
Dieser ganze Exkurs ist notwendig, damit der liebe Leser versteht, dass alle SQL-Datensätze Zeile für Zeile generiert werden, auch in gespeicherten Prozeduren! Und der Hauptunterschied zwischen Abrufprozeduren und ausführbaren Prozeduren besteht darin, dass erstere darauf ausgelegt sind, viele Zeilen zurückzugeben, während letztere darauf ausgelegt sind, nur eine zurückzugeben. Deshalb werden sie unterschiedlich verwendet: Die Select-Prozedur wird mit dem SELECT-Befehl aufgerufen, der von der Prozedur „verlangt“, alle Datensätze aufzugeben, die sie zurückgeben kann. Die ausführbare Prozedur wird mit EXECUTE PROCEDURE aufgerufen, wodurch nur eine Zeile aus dem HP „entfernt“ und der Rest ignoriert wird (auch wenn sie vorhanden sind!).
Schauen wir uns zur Verdeutlichung ein Beispiel für ein Stichprobenverfahren an. Zur Vergebung erstellen wir eine gespeicherte Prozedur, die genau wie eine SELECT ID, NAME FROM Table_Example-Abfrage funktioniert, das heißt, sie wählt einfach die Felder ID und NAME aus der gesamten Tabelle aus. Hier ist dieses Beispiel:

PROZEDUR ERSTELLEN Simple_Select_SP
KEHRT ZURÜCK (
procID INTEGER,
procNAME VARCHAR(80))
ALS
BEGINNEN
FÜR
SELECT ID, NAME FROM table_example
INTO:procID, :procNAME
TUN
BEGINNEN
AUSSETZEN;
ENDE
ENDE

Schauen wir uns die Schritte dieses Verfahrens namens Simple_Select_SP an. Wie Sie sehen, hat es keine Eingabeparameter und zwei Ausgabeparameter – ID und NAME. Das Interessanteste ist natürlich der Inhalt des Verfahrens. Hier wird das FOR SELECT-Konstrukt verwendet:

FÜR
SELECT ID, NAME FROM table_example
INTO:procID, :procNAME
TUN
BEGINNEN

/*etwas mit den Variablen procID und procName machen*/

ENDE

Dieser Code bedeutet Folgendes: Geben Sie für jede aus der Tabelle Table_example ausgewählte Zeile die ausgewählten Werte in die Variablen procID und procName ein und machen Sie dann etwas mit diesen Variablen.
Sie könnten ein überraschtes Gesicht machen und fragen: „Variablen? Welche anderen Variablen? 9“ Es ist irgendwie die Überraschung dieses Kapitels, dass wir Variablen in gespeicherten Prozeduren verwenden können. In der HP-Sprache können Sie sowohl Ihre eigenen lokalen Variablen innerhalb einer Prozedur deklarieren als auch Eingabe- und Ausgabeparameter als Variablen verwenden.
Um eine lokale Variable in einer gespeicherten Prozedur zu deklarieren, müssen Sie ihre Beschreibung nach dem Schlüsselwort AS und vor dem ersten Wort BEGIN platzieren. Die Beschreibung einer lokalen Variablen sieht folgendermaßen aus:

Variable deklarieren ;

Um beispielsweise eine ganzzahlige lokale Variable Mylnt zu deklarieren, würden Sie die folgende Deklaration zwischen AS und BEGIN einfügen

DECLARE VARIABLE Mylnt INTEGER;

Die Variablen in unserem Beispiel beginnen mit einem Doppelpunkt. Dies geschieht, weil auf sie innerhalb des SQL-Befehls FOR SELECT zugegriffen wird. Um also zwischen Feldern in Tabellen, die in SELECT verwendet werden, und Variablen zu unterscheiden, muss letzteren ein Doppelpunkt vorangestellt werden. Schließlich können Variablen genau den gleichen Namen haben wie Felder in Tabellen!
Der Doppelpunkt vor einem Variablennamen sollte jedoch nur innerhalb von SQL-Abfragen verwendet werden. Außerhalb von Texten wird auf eine Variable ohne Doppelpunkt verwiesen, zum Beispiel:

procName="Ein Name";

Aber kehren wir zum Hauptteil unseres Verfahrens zurück. Die FOR SELECT-Klausel gibt Daten nicht in Form einer Tabelle zurück – einem Datensatz, sondern jeweils zeilenweise. Jedes zurückgegebene Feld muss in einer eigenen Variablen platziert werden: ID => procID, NAME => procName. Im DO-Teil werden diese Variablen an den Client gesendet, der die Prozedur mit dem SUSPEND-Befehl aufgerufen hat
Daher durchläuft der Befehl FOR SELECT...DO in einer Schleife die im SELECT-Teil des Befehls ausgewählten Datensätze. Im Schleifenkörper, der durch den DO-Teil gebildet wird, wird der nächste generierte Datensatz mit dem SUSPEND-Befehl an den Client übertragen.
Das Auswahlverfahren ist also darauf ausgelegt, eine oder mehrere Zeilen zurückzugeben, für die im HP-Körper eine Schleife organisiert ist, die die resultierenden variablen Parameter füllt. Und am Ende des Schleifenkörpers steht immer ein SUSPEND-Befehl, der die nächste Datenzeile an den Client zurückgibt.

Schleifen und Verzweigungsanweisungen

Zusätzlich zum Befehl FOR SELECT...DO, der eine Schleife durch die Datensätze einer Auswahl organisiert, gibt es eine andere Art von Schleife – WHILE...DO, mit der Sie eine Schleife basierend auf der Überprüfung beliebiger Bedingungen organisieren können. Hier ist ein Beispiel dafür, wie HP die WHILE..DO-Schleife verwendet. Diese Prozedur gibt die Quadrate ganzer Zahlen von 0 bis 99 zurück:

PROZEDJRE QUAD ERSTELLEN
RÜCKGABE (QUADRAT INTEGER)
ALS
DECLARE VARIABLE I INTEGER;
BEGINNEN
ich = 1;
Während ich<100) DO
BEGINNEN
QUADRAT= I*I;
I=I+1;
AUSSETZEN;
ENDE
ENDE

Als Ergebnis der Ausführung der SELECT FROM QUAD-Abfrage erhalten wir eine Tabelle mit einer QUADRAT-Spalte, die die Quadrate der ganzen Zahlen von 1 bis 99 enthält
Zusätzlich zur Iteration über die Ergebnisse eines SQL-Beispiels und einer klassischen Schleife verwendet die Sprache der gespeicherten Prozeduren den Operator IF...THEN..ELSE, mit dem Sie Verzweigungen abhängig von der Ausführung beliebiger Bedingungen organisieren können. Seine Syntax ist ähnlich zu den meisten Verzweigungsoperatoren in höheren Programmiersprachen wie Pascal und C.
Schauen wir uns ein komplexeres Beispiel einer gespeicherten Prozedur an, die Folgendes tut.

1. Berechnet den Durchschnittspreis in der Tabelle Table_example (siehe Kapitel „Tabellen Primärschlüssel und Generatoren“)
2. Anschließend wird für jeden Eintrag in der Tabelle die folgende Prüfung durchgeführt: Wenn der vorhandene Preis (PRICE) größer als der Durchschnittspreis ist, wird ein Preis festgelegt, der dem Durchschnittspreis zuzüglich eines festgelegten festen Prozentsatzes entspricht
3. Wenn der vorhandene Preis kleiner oder gleich dem Durchschnittspreis ist, wird ein Preis festgelegt, der dem vorherigen Preis entspricht, plus der Hälfte der Differenz zwischen dem vorherigen und dem Durchschnittspreis.
4. Gibt alle geänderten Zeilen in der Tabelle zurück.

Definieren wir zunächst den Namen des HP sowie die Eingabe- und Ausgabeparameter. All dies wird in den Header der gespeicherten Prozedur geschrieben.

VERFAHREN ERSTELLEN Preise erhöhen (
Prozentuale Erhöhung (DOPPELTE PRÄZISION)
RETURNS (ID INTEGER, NAME VARCHAR(SO), new_price DOUBLE
PRÄZISION AS

Die Prozedur heißt CreatingePrices und hat einen Eingabeparameter Peiceni21nciease vom Typ DOUBLE PRECISION und drei Ausgabeparameter – ID, NAME und new_pnce. Beachten Sie, dass die ersten beiden Ausgabeparameter dieselben Namen haben wie die Felder in der Tabelle Table_example, mit der wir arbeiten werden. Dies ist durch die Regeln der Sprache der gespeicherten Prozeduren zulässig.
Jetzt müssen wir eine lokale Variable deklarieren, die zum Speichern des Durchschnittswerts verwendet wird. Die Deklaration sieht folgendermaßen aus:

DECLARE VARIABLE avg_price DOUBLE PRECISION;

Kommen wir nun zum Hauptteil der gespeicherten Prozedur. Öffnen Sie den Hauptteil des HP Schlüsselwort BEGIN.
Zuerst müssen wir den ersten Schritt unseres Algorithmus ausführen – den Durchschnittspreis berechnen. Dazu verwenden wir den folgenden Abfragetyp:

SELECT AVG(Price_l)
FROM Table_Example
INTO:avg_price,-

Diese Abfrage verwendet die AVG-Aggregatfunktion, die den Durchschnitt des Feldes PRICE_1 unter den ausgewählten Abfragezeilen zurückgibt – in unserem Fall den Durchschnitt von PRICE_1 über die gesamte Tabelle Table_example. Der von der Anfrage zurückgegebene Wert wird in die Variable avg_price eingefügt. Beachten Sie, dass der Variablen avg_pnce ein Doppelpunkt vorangestellt ist, um sie von den in der Anfrage verwendeten Feldern zu unterscheiden.
Die Besonderheit dieser Abfrage besteht darin, dass sie immer genau einen einzigen Datensatz zurückgibt. Solche Abfragen werden Singleton-Abfragen genannt. Und nur solche Auswahlen können in gespeicherten Prozeduren verwendet werden. Wenn eine Abfrage mehr als eine Zeile zurückgibt, muss sie als FOR SELECT...DO-Konstrukt formatiert werden, das eine Schleife zur Verarbeitung jeder zurückgegebenen Zeile organisiert
Wir haben also den Durchschnittspreis erhalten. Jetzt müssen Sie die gesamte Tabelle durchgehen, den Preiswert in jedem Eintrag mit dem Durchschnittspreis vergleichen und entsprechende Maßnahmen ergreifen
Von Anfang an organisieren wir die Suche nach jedem Datensatz aus der Tabelle Table_example

FÜR
WÄHLEN SIE ID, NAME, PREIS_1
FROM Table_Example
INTO:ID, :NAME, :new_price
TUN
BEGINNEN
/*_hier beschreiben wir jeden Eintrag*/
ENDE

Wenn diese Konstruktion ausgeführt wird, werden Daten Zeile für Zeile aus der Tabelle Table_example extrahiert und die Feldwerte in jeder Zeile werden den Variablen ID, NAME und new_pnce zugewiesen. Sie erinnern sich natürlich daran, dass diese Variablen als Ausgabeparameter deklariert sind, aber Sie müssen sich keine Sorgen machen, dass die ausgewählten Daten als Ergebnisse zurückgegeben werden: Die Tatsache, dass den Ausgabeparametern etwas zugewiesen wird, bedeutet nicht, dass der Client den HP aufruft wird diese Werte sofort erhalten! Parameter werden nur übergeben, wenn der SUSPEND-Befehl ausgeführt wird, und davor können wir die Ausgabeparameter als gewöhnliche Variablen verwenden – in unserem Beispiel machen wir genau das mit dem Parameter new_price.
Innerhalb des Körpers der BEGIN... END-Schleife können wir also die Werte jeder Zeile verarbeiten. Wie Sie sich erinnern, müssen wir herausfinden, wie sich der aktuelle Preis im Vergleich zum Durchschnitt verhält, und entsprechende Maßnahmen ergreifen. Dieses Vergleichsverfahren implementieren wir mit der IF-Anweisung:

IF (new_price > avg_price) THEN /*wenn der bestehende Preis größer als der Durchschnittspreis ist*/
BEGINNEN
/*dann legen wir einen neuen Preis fest, der dem Durchschnittspreis plus einem festen Prozentsatz entspricht */
new_price = (avg_price + avg_price*(Percent2Increase/100));
UPDATE Table_example
SET PRICE_1 = :new_price
WHERE ID = :ID;
ENDE
ANDERS
BEGINNEN
/* Wenn der bestehende Preis kleiner oder gleich dem Durchschnittspreis ist, dann legen Sie einen Preis fest, der dem vorherigen Preis entspricht, plus der Hälfte der Differenz zwischen dem vorherigen und dem Durchschnittspreis */
new_price = (new_pnce + ((avg_pnce new_price)/2)) ;
UPDATE Table_example
SET PRICE_1 = :new_price
WHERE ID = .ID;
ENDE

Wie Sie sehen, ist das Ergebnis ein ziemlich großes IF-Konstrukt, das ohne die in den /**/-Symbolen eingeschlossenen Kommentare schwer zu verstehen wäre.
Um den Preis entsprechend der berechneten Differenz zu ändern, verwenden wir die UPDATE-Anweisung, die es uns ermöglicht, vorhandene Datensätze zu ändern – einen oder mehrere. Um eindeutig anzugeben, in welchem ​​Datensatz der Preis geändert werden muss, verwenden wir das Primärschlüsselfeld in der WHERE-Bedingung und vergleichen es mit dem Wert der Variablen, die den ID-Wert für den aktuellen Datensatz speichert: ID=:ID. Beachten Sie, dass der ID-Variablen ein Doppelpunkt vorangestellt ist.
Nach der Ausführung des IF...THEN...ELSE-Konstrukts enthalten die Variablen ID, NAME und new_price Daten, die wir an den Client zurückgeben müssen, der die Prozedur aufgerufen hat. Dazu müssen Sie nach IF den Befehl SUSPEND einfügen, der die Daten an den Ort sendet, von dem der HP aufgerufen wurde. Während der Übertragung wird der Vorgang angehalten, und wenn ein neuer Datensatz vom HP benötigt wird, wird er unterbrochen wird erneut fortgesetzt – und zwar so lange, bis FOR SELECT...DO nicht alle Datensätze in seiner Abfrage durchläuft.
Es ist zu beachten, dass es zusätzlich zum SUSPEND-Befehl, der nur die gespeicherte Prozedur anhält, einen EXIT-Befehl gibt, der die gespeicherte Prozedur nach Übergabe der Zeichenfolge beendet. Allerdings wird der EXIT-Befehl recht selten verwendet, da er hauptsächlich dazu benötigt wird, die Schleife zu unterbrechen, wenn eine Bedingung erreicht ist
Wenn die Prozedur jedoch mit einer SELECT-Anweisung aufgerufen und mit EXIT abgeschlossen wurde, wird die zuletzt abgerufene Zeile nicht zurückgegeben. Das heißt, wenn Sie den Vorgang unterbrechen und trotzdem diese Zeichenfolge erhalten müssen, müssen Sie die Sequenz verwenden

AUSSETZEN;
AUSFAHRT;

Der Hauptzweck von EXIT besteht darin, Singleton-Datensätze und zurückgegebene Parameter durch Aufruf von EXECUTE PROCEDURE zu empfangen. In diesem Fall werden die Werte der Ausgabeparameter gesetzt, der SQL-Datensatz daraus jedoch nicht generiert und die Ausführung der Prozedur beendet.
Schreiben wir den gesamten Text unserer gespeicherten Prozedur auf, damit wir ihre Logik auf einen Blick erfassen können:

VERFAHREN ERSTELLEN Preise erhöhen (
Percent2Increase (DOUBLE PRECISION)
RETURNS (ID INTEGER, NAME VARCHAR(80),
new_price DOUBLE PRECISION) AS
DECLARE VARIABLE avg_price DOUBLE PRECISION;
BEGINNEN
SELECT AVG(Price_l)
FROM Table_Example
INTO:avg_price;
FÜR
WÄHLEN SIE ID, NAME, PREIS_1
FROM Table_Example
INTO:ID, :NAME, :new_price
TUN
BEGINNEN
/*jeden Datensatz hier verarbeiten*/
IF (new_pnce > avg_price) THEN /*wenn der bestehende Preis größer als der Durchschnittspreis ist*/
BEGINNEN
/*einen neuen Preis festlegen, der dem Durchschnittspreis plus einem festen Prozentsatz entspricht */
new_price = (avg_price + avg_price*(Percent2lncrease/100));
UPDATE Table_example
SET PRICE_1 = :new_price
WHERE ID = :ID;
ENDE
ANDERS
BEGINNEN
/* Wenn der vorhandene Preis kleiner oder gleich dem Durchschnittspreis ist, wird ein Preis festgelegt, der dem vorherigen Preis plus der Hälfte der Differenz zwischen dem vorherigen und dem Durchschnittspreis entspricht */
new_price = (new_price + ((avg_price - new_price)/2));
UPDATE Table_example
SET PRICE_1 = :new_price
WHERE ID = :ID;
ENDE
AUSSETZEN;
ENDE
ENDE

Dieses Beispiel für eine gespeicherte Prozedur veranschaulicht die Verwendung grundlegender Sprachkonstrukte und Trigger für gespeicherte Prozeduren. Als Nächstes schauen wir uns Möglichkeiten an, mit gespeicherten Prozeduren einige häufig auftretende Probleme zu lösen.

Rekursive gespeicherte Prozeduren

Gespeicherte InterBase-Prozeduren können rekursiv sein. Das bedeutet, dass sich eine gespeicherte Prozedur selbst aufrufen kann. Bis zu 1000 Verschachtelungsebenen gespeicherter Prozeduren sind zulässig, wir müssen jedoch bedenken, dass die freien Ressourcen auf dem Server möglicherweise erschöpft sind, bevor die maximale Verschachtelung von HP erreicht ist.
Eine häufige Verwendung gespeicherter Prozeduren ist die Verarbeitung von in einer Datenbank gespeicherten Baumstrukturen. Bäume werden häufig in der Produktzusammensetzung, im Lager, im Personalbereich und in anderen gängigen Anwendungen verwendet.
Schauen wir uns ein Beispiel einer gespeicherten Prozedur an, die alle Produkte eines bestimmten Typs ab einer bestimmten Verschachtelungsebene auswählt.
Stellen wir uns die folgende Problemstellung vor: Wir haben ein Warenverzeichnis mit einer hierarchischen Struktur der folgenden Art:

Waren
- Haushaltsgeräte
- Kühlschränke
- Dreikammer
- Doppelkammer
- Einzelkammer
- Waschmaschinen
- Vertikal
- Frontal
- Klassisch
- Eng
- Computertechnologie
....

Diese Struktur des Produktkategorieverzeichnisses kann unterschiedlich tiefe Verzweigungen aufweisen. und auch mit der Zeit zunehmen. Unsere Aufgabe besteht darin, die Auswahl aller finiten Elemente aus dem Verzeichnis durch „Erweitern des vollständigen Namens“ sicherzustellen, beginnend mit einem beliebigen Knoten. Wenn wir beispielsweise den Knoten „Waschmaschinen“ auswählen, müssen wir die folgenden Kategorien erhalten:

Waschmaschinen – Vertikal
Waschmaschinen - Front Classic
Waschmaschinen – Vorderseite schmal

Definieren wir die Tabellenstruktur zum Speichern von Produktverzeichnisinformationen. Wir verwenden ein vereinfachtes Schema, um den Baum in einer Tabelle zu organisieren:

TABELLE GoodsTree ERSTELLEN
(ID_GOOD INTEGER NICHT NULL,
ID_PARENT_GOOD INTEGER,
GOOD_NAME VARCHAR(80),
Einschränkung pkGooci-Primärschlüssel (ID_GOOD));

Wir erstellen eine Tabelle GoodsTree, in der es nur drei Felder gibt: ID_GOOD – die intelligente Kennung der Kategorie, ID_PARENT_GOOD – die Kennung der Muttergesellschaft für diese Kategorie und GOOD_NAME – der Name der Kategorie. Um die Integrität der Daten in dieser Tabelle sicherzustellen, werden wir dieser Tabelle eine Fremdschlüsseleinschränkung auferlegen:

ALTER TABLE GoodsTree
EINSCHRÄNKUNG FK_goodstree HINZUFÜGEN
AUSLÄNDISCHER SCHLÜSSEL (ID_PARENT_GOOD)
REFERENZEN GOODSTPEE (ID__GOOD)

Die Tabelle verweist auf sich selbst und dieser Fremdschlüssel verfolgt dies. sodass die Tabelle keine Verweise auf nicht vorhandene übergeordnete Elemente enthält und außerdem Versuche verhindert werden, Produktkategorien mit untergeordneten Elementen zu löschen.
Tragen wir folgende Daten in unsere Tabelle ein:

ID_GOOD 1 2 3 4 5 6 7 8 9 10 11 12

ID_PARENT_GOOD 0 1 1 2 2 4 4 4 5 5 10 10

GUTER NAME WAREN Haushaltsgeräte Computer und Komponenten Kühlschränke Waschmaschinen Dreikammer Doppelkammer Einzelkammer Vertikal Frontal Eng Klassisch

Nachdem wir nun einen Ort zum Speichern der Daten haben, können wir mit der Erstellung einer gespeicherten Prozedur beginnen, die alle „endgültigen“ Produktkategorien in einer „erweiterten“ Form ausgibt – zum Beispiel für die Kategorie „Dreikammer“ die vollständige Kategorie Der Name wäre „Haushaltsgeräte-Kühlschränke“ mit drei Kammern.
Gespeicherte Prozeduren, die Baumstrukturen verarbeiten, haben ihre eigene Terminologie. Jedes Element des Baums wird als Knoten bezeichnet. und die Beziehung zwischen Knoten, die aufeinander verweisen, wird als Eltern-Kind-Beziehung bezeichnet. Knoten, die sich ganz am Ende des Baums befinden und keine untergeordneten Knoten haben, werden „Blätter“ genannt.
Für diese gespeicherte Prozedur ist der Eingabeparameter die Kategorie-ID, von der aus wir den Drilldown starten müssen. Die gespeicherte Prozedur sieht folgendermaßen aus:

PROZEDURE GETFULLNAME ERSTELLEN (ID_GOOD2SHOW INTEGER)
RETURNS (FULL_GOODS_NAME VARCHAR(1000),
ID_CHILD_GOOD INTEGER)
ALS
DECLARE VARIABLE CURR_CHILD_NAME VARCHAR(80);
BEGINNEN
/*0organisieren Sie die äußere FOR SELECT-Schleife entsprechend den unmittelbaren Nachkommen des Produkts mit ID_GOOD=ID_GOOD2SHOW */
FOR SELECT gtl.id_good, gtl.good_name
VON GoodsTree gtl
WHERE gtl.id_parent_good=:ID_good2show
INTO:ID_CHILD_GOOD, :full_goods_name
TUN
BEGINNEN
/"Überprüfen Sie mit der EXISTS-Funktion, die TRUE zurückgibt, wenn die Abfrage in Klammern mindestens eine Zeile zurückgibt. Wenn der gefundene Knoten mit ID_PARENT_GOOD = ID_CHILD_GOOD keine Kinder hat, dann ist er ein „Blatt“ des Baums und wird in die Ergebnisse einbezogen */
WENN (NICHT EXISTIERT(
WÄHLEN Sie * AUS GoodsTree aus
WO GoodsTree.id_parent_good=:id_child_good))
DANN
BEGINNEN
/* Übergeben Sie das „Blatt“ des Baums an die Ergebnisse */
AUSSETZEN;
ENDE
ANDERS
/* Für Knoten, die Kinder haben*/
BEGINNEN
/*den Namen des übergeordneten Knotens in einer temporären Variablen speichern */
CURR_CHILD_NAME=full_goods_name;
/* diese Prozedur rekursiv ausführen */
FÜR
SELECT ID_CHILD_GOOD, vollständiger_Warenname
VON GETFULLNAME (:ID_CHILD_GOOD)
INTO:ID_CHILD_GOOD, :full_goods_name
Beginnen Sie
/*Füge den Namen des übergeordneten Knotens zum gefundenen untergeordneten Namen hinzu, indem du die Z|| verwendest */
full_goods_name=CURR_CHILD_NAME| " " | full_goods_name,-
AUSSETZEN; /* den vollständigen Namen des Produkts zurückgeben*/
ENDE
ENDE
ENDE
ENDE

Wenn wir diese Prozedur mit dem Eingabeparameter ID_GOOD2SHOW= 1 ausführen, erhalten wir Folgendes:

Wie Sie sehen können, haben wir mithilfe einer rekursiven gespeicherten Prozedur den gesamten Kategoriebaum durchgegangen und den vollständigen Namen der „Blatt“-Kategorien angezeigt, die sich ganz an den Spitzen der Zweige befinden.

Abschluss

Damit ist unsere Betrachtung der Hauptmerkmale der Sprache für gespeicherte Prozeduren abgeschlossen. Natürlich ist es unmöglich, die Entwicklung gespeicherter Prozeduren in nur einem Kapitel vollständig zu beherrschen, aber wir haben hier versucht, die Grundkonzepte gespeicherter Prozeduren vorzustellen und zu erklären. Die beschriebenen Designs und Techniken zum Entwerfen von HP können in den meisten Datenbankanwendungen angewendet werden
Einige der wichtigen Probleme im Zusammenhang mit der Entwicklung gespeicherter Prozeduren werden im nächsten Kapitel behandelt – „Erweiterte Funktionen der InterBase-Sprache für gespeicherte Prozeduren“, das sich mit der Ausnahmebehandlung, der Lösung von Fehlersituationen in gespeicherten Prozeduren und der Arbeit mit Arrays befasst.

In diesem Thema wird beschrieben, wie Sie mithilfe der Transact-SQL CREATE PROCEDURE-Anweisung eine gespeicherte Transact-SQL-Prozedur in SQL Server Management Studio erstellen können.

Bevor Sie beginnen, führen Sie die folgenden Schritte aus. Berechtigungen

Erstellen Sie eine Prozedur mit: SQL Server Management Studio, Transact-SQL

Das Ausführen dieser Anweisung erfordert die CREATE PROCEDURE-Berechtigung für die Datenbank und die ALTER-Berechtigung für das Schema, in dem die Prozedur erstellt wird.

Sie können eine der folgenden Methoden verwenden:

SQL Server Management Studio

Verwenden von SQL Server Management Studio

Erstellen Sie eine Prozedur im Objekt-Explorer

IN Objekt-Explorer Stellen Sie eine Verbindung zu einer Instanz der Datenbank-Engine her und stellen Sie sie bereit.

Falten Sie den Knoten nacheinander auf Datenbank, Datenbank AdventureWorks2012 und Knoten Programmierung.

Klicken Sie mit der rechten Maustaste auf das Element Gespeicherte Prozeduren und auswählen Erstellen Sie eine gespeicherte Prozedur.

Auf der Speisekarte Anfrage Menüpunkt wählen Geben Sie Werte für Vorlagenparameter an.

Im Dialogfeld Festlegen von Werten für Vorlagenparameter Geben Sie die folgenden Werte für die angezeigten Parameter ein.

Parameter	Bedeutung
Autor	Ihr Name
Erstelldatum	Heutiges Datum
Beschreibung	Gibt Daten über Mitarbeiter zurück.
Prozedurname
@Param1	@Familienname, Nachname
@Datatype_For_Param1	nvarchar(50)
Default_Value_For_Param1	NULL
@Param2	@Vorname
@Datatype_For_Param2	nvarchar(50)
Default_Value_For_Param2	NULL

1. Drück den Knopf OK.

   IN Abfrageeditor Ersetzen Sie die SELECT-Anweisung durch die folgende Anweisung:

   Um die Syntax zu überprüfen, wählen Sie Parsing auf der Speisekarte Anfrage. Wenn eine Fehlermeldung zurückgegeben wird, vergleichen Sie die Anweisungen mit den oben genannten und nehmen Sie gegebenenfalls Korrekturen vor.

   Um eine Prozedur zu erstellen, im Menü Anfrage Menüpunkt wählen Ausführen. Die Prozedur wird als Objekt in der Datenbank angelegt.

   Um den Vorgang im Objekt-Explorer anzuzeigen, klicken Sie mit der rechten Maustaste auf das Element Gespeicherte Prozeduren und auswählen Aktualisieren.

   Um eine Prozedur auszuführen, klicken Sie im Objekt-Explorer mit der rechten Maustaste auf den Namen der gespeicherten Prozedur HumanResources.uspGetEmployeesTest und auswählen Ausführen einer gespeicherten Prozedur.

   Im Fenster Ausführen des Verfahrens Geben Sie Margheim als Wert für den Parameter @LastName und Diane als Wert für den Parameter @FirstName ein.

Verwendung von Transact-SQL

Erstellen einer Prozedur im Abfrageeditor

IN Objekt-Explorer Stellen Sie eine Verbindung zu einer Instanz der Datenbank-Engine her.

Auf der Speisekarte Datei Menüpunkt wählen Erstellen Sie eine Anfrage.

Kopieren Sie das folgende Beispiel in das Abfragefenster und klicken Sie auf die Schaltfläche Ausführen. In diesem Beispiel wird dieselbe gespeicherte Prozedur wie zuvor erstellt, jedoch mit einem anderen Prozedurnamen.

VERWENDEN Sie AdventureWorks2012; GEHEN SIE ERSTELLEN VERFAHREN HumanResources .uspGetEmployeesTest2 @LastName nvarchar (50) , @FirstName nvarchar (50) AS SET NOCOUNT ON ; SELECT FirstName, LastName, Department FROM HumanResources.vEmployeeDepartmentHistory WHERE FirstName = @FirstName AND LastName = @LastName AND EndDate IS NULL; GEHEN

Um den Vorgang abzuschließen, kopieren Sie das folgende Beispiel in das Abfragefenster, das Sie erstellen, und klicken Sie auf die Schaltfläche Ausführen. Es werden verschiedene Methoden zum Einstellen von Parameterwerten gezeigt.

Ziel der Arbeit– lernen, gespeicherte Prozeduren auf dem Datenbankserver zu erstellen und zu verwenden.

1. Arbeiten Sie alle Beispiele durch und analysieren Sie die Ergebnisse ihrer Ausführung im SQL Server Management Studio-Dienstprogramm. Überprüfen des Vorhandenseins erstellter Prozeduren in der aktuellen Datenbank.

2. Bearbeitung aller Beispiele und Aufgaben während der Laborarbeit.

3. Erledigung einzelner Aufgaben nach Möglichkeiten.

Erläuterungen zur Ausführung der Arbeiten

Um die Programmierung gespeicherter Prozeduren zu beherrschen, verwenden wir eine Beispieldatenbank namens DB_Books, das in der Laborarbeit Nr. 1 entstand. Achten Sie beim Ausfüllen von Beispielen und Aufgaben auf die Übereinstimmung der Namen der Datenbank, Tabellen und anderer Projektobjekte.

Gespeicherte Prozeduren sind eine Reihe von Befehlen, die aus einer oder mehreren SQL-Anweisungen oder -Funktionen bestehen und in kompilierter Form in einer Datenbank gespeichert werden.

Arten von gespeicherten Prozeduren

Gespeicherte Systemprozeduren sind für die Durchführung verschiedener Verwaltungsaktionen konzipiert. Mit ihrer Hilfe werden nahezu alle Tätigkeiten der Serveradministration durchgeführt. Wir können sagen, dass gespeicherte Systemprozeduren eine Schnittstelle sind, die die Arbeit mit Systemtabellen ermöglicht. Gespeicherte Systemprozeduren haben das Präfix „sp_“, werden in der Systemdatenbank gespeichert und können im Kontext jeder anderen Datenbank aufgerufen werden.

Benutzerdefinierte gespeicherte Prozeduren implementieren bestimmte Aktionen. Gespeicherte Prozeduren sind ein vollwertiges Datenbankobjekt. Dadurch befindet sich jede gespeicherte Prozedur in einer bestimmten Datenbank, wo sie ausgeführt wird.

Temporär gespeicherte Prozeduren sind nur für kurze Zeit vorhanden und werden danach automatisch vom Server zerstört. Sie werden in lokal und global unterteilt. Lokale temporär gespeicherte Prozeduren können nur über die Verbindung aufgerufen werden, in der sie erstellt werden. Wenn Sie eine solche Prozedur erstellen, müssen Sie ihr einen Namen geben, der mit einem einzelnen #-Zeichen beginnt. Wie alle temporären Objekte werden gespeicherte Prozeduren dieses Typs automatisch gelöscht, wenn der Benutzer die Verbindung trennt oder der Server neu gestartet oder gestoppt wird. Globale temporär gespeicherte Prozeduren stehen jeder Verbindung von einem Server zur Verfügung, der über dieselbe Prozedur verfügt. Um es zu definieren, geben Sie ihm einfach einen Namen, der mit den Zeichen ## beginnt. Diese Prozeduren werden gelöscht, wenn der Server neu gestartet oder gestoppt wird oder wenn die Verbindung in dem Kontext, in dem sie erstellt wurden, geschlossen wird.

Gespeicherte Prozeduren erstellen und ändern

Beim Erstellen einer gespeicherten Prozedur müssen die folgenden Probleme gelöst werden: Zugriffsrechte planen. Beachten Sie beim Erstellen einer gespeicherten Prozedur, dass diese über dieselben Zugriffsrechte auf Datenbankobjekte verfügt wie der Benutzer, der sie erstellt hat. Definieren der Parameter einer gespeicherten Prozedur; gespeicherte Prozeduren können Eingabe- und Ausgabeparameter haben; Entwicklung von gespeichertem Prozedurcode. Der Prozedurcode kann eine Folge beliebiger SQL-Befehle enthalten, einschließlich Aufrufen anderer gespeicherter Prozeduren.

Die Syntax des Operators zum Erstellen einer neuen oder Ändern einer vorhandenen gespeicherten Prozedur in MS SQL Server-Notation:

( CREATE | ALTER ) PROC[ EDURE] procedure_name [ ;number] [ ( @parameter_name data_type ) [ VARYING ] [ = DEFAULT ] [ OUTPUT ] ] [ ,... n] [ WITH ( RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION ) ] [ FÜR REPLIKATION] AS sql_statement [ ... n]

Schauen wir uns die Parameter dieses Befehls an.

Mithilfe der Präfixe sp_, #, ## kann die erstellte Prozedur als System- oder temporäre Prozedur definiert werden. Wie Sie der Befehlssyntax entnehmen können, ist es nicht zulässig, den Namen des Besitzers anzugeben, der Eigentümer der erstellten Prozedur sein wird, sowie den Namen der Datenbank, in der sie gespeichert werden soll. Um die von Ihnen erstellte gespeicherte Prozedur in einer bestimmten Datenbank zu platzieren, müssen Sie daher den Befehl CREATE PROCEDURE im Kontext dieser Datenbank ausgeben. Wenn Sie über den Hauptteil einer gespeicherten Prozedur auf Objekte derselben Datenbank zugreifen, können Sie verkürzte Namen verwenden, d. h. ohne Angabe des Datenbanknamens. Wenn Sie auf Objekte zugreifen müssen, die sich in anderen Datenbanken befinden, ist die Angabe des Datenbanknamens obligatorisch.

Um Eingabe- und Ausgabedaten zu übergeben, müssen die Parameternamen in der von Ihnen erstellten gespeicherten Prozedur mit dem @-Zeichen beginnen. Sie können mehrere Parameter in einer einzigen gespeicherten Prozedur angeben, getrennt durch Kommas. Der Rumpf einer Prozedur sollte keine lokalen Variablen verwenden, deren Namen mit den Namen der Parameter dieser Prozedur übereinstimmen. Zur Bestimmung des Datentyps von gespeicherten Prozedurparametern eignet sich jeder SQL-Datentyp, auch der benutzerdefinierte. Der Datentyp CURSOR kann jedoch nur als Ausgabeparameter einer gespeicherten Prozedur verwendet werden, d. h. Angabe des Schlüsselworts OUTPUT.

Das Vorhandensein des Schlüsselworts OUTPUT bedeutet, dass der entsprechende Parameter Daten aus einer gespeicherten Prozedur zurückgeben soll. Dies bedeutet jedoch nicht, dass der Parameter nicht für die Übergabe von Werten an eine gespeicherte Prozedur geeignet ist. Durch die Angabe des Schlüsselworts OUTPUT wird der Server beim Beenden einer gespeicherten Prozedur angewiesen, den aktuellen Parameterwert der lokalen Variablen zuzuweisen, die beim Aufruf der Prozedur als Parameterwert angegeben wurde. Beachten Sie, dass bei Angabe des Schlüsselworts OUTPUT der Wert des entsprechenden Parameters beim Aufruf der Prozedur nur über eine lokale Variable festgelegt werden kann. Alle Ausdrücke oder Konstanten, die für normale Parameter zulässig sind, sind nicht zulässig. Das Schlüsselwort VARYING wird in Verbindung mit dem Parameter OUTPUT verwendet, der vom Typ CURSOR ist. Es gibt an, dass die Ausgabe die Ergebnismenge sein wird.

Das Schlüsselwort DEFAULT stellt den Wert dar, den der entsprechende Parameter standardmäßig annimmt. Daher müssen Sie beim Aufruf einer Prozedur den Wert des entsprechenden Parameters nicht explizit angeben.

Da der Server den Abfrageausführungsplan und den kompilierten Code zwischenspeichert, werden beim nächsten Aufruf der Prozedur die vorgefertigten Werte verwendet. In einigen Fällen ist es jedoch dennoch erforderlich, den Prozedurcode neu zu kompilieren. Durch die Angabe des Schlüsselworts RECOMPILE wird das System angewiesen, bei jedem Aufruf einen Ausführungsplan für die gespeicherte Prozedur zu erstellen.

Der Parameter FOR REPLICATION ist erforderlich, wenn Daten repliziert und die erstellte gespeicherte Prozedur als Artikel in eine Veröffentlichung aufgenommen werden soll. Das Schlüsselwort ENCRYPTION weist den Server an, den Code der gespeicherten Prozedur zu verschlüsseln, was Schutz vor der Verwendung proprietärer Algorithmen bieten kann, die die gespeicherte Prozedur implementieren. Das Schlüsselwort AS wird am Anfang des Hauptteils der gespeicherten Prozedur selbst platziert. Der Prozedurkörper kann fast alle SQL-Befehle verwenden, Transaktionen deklarieren, Sperren setzen und andere gespeicherte Prozeduren aufrufen. Sie können eine gespeicherte Prozedur mit dem RETURN-Befehl beenden.

Entfernen einer gespeicherten Prozedur

DROP PROCEDURE (prozedurname) [,...n]

Ausführen einer gespeicherten Prozedur

Um eine gespeicherte Prozedur auszuführen, verwenden Sie den Befehl: [ [ EXEC [ UTE] procedure_name [ ;number] [ [ @parameter_name= ] ( value | @variable_name) [ OUTPUT ] | [ STANDARD ] ] [ ,... n]

Wenn der Aufruf der gespeicherten Prozedur nicht der einzige Befehl im Stapel ist, ist der Befehl EXECUTE erforderlich. Darüber hinaus ist dieser Befehl erforderlich, um eine Prozedur aus dem Hauptteil einer anderen Prozedur oder eines anderen Triggers aufzurufen.

Die Verwendung des Schlüsselworts OUTPUT beim Aufruf einer Prozedur ist nur für Parameter zulässig, die beim Erstellen der Prozedur mit dem Schlüsselwort OUTPUT deklariert wurden.

Wenn beim Aufruf einer Prozedur das Schlüsselwort DEFAULT für einen Parameter angegeben wird, wird der Standardwert verwendet. Selbstverständlich ist das angegebene Wort DEFAULT nur für diejenigen Parameter zulässig, für die ein Standardwert definiert ist.

Die Syntax des EXECUTE-Befehls zeigt, dass Parameternamen beim Aufruf einer Prozedur weggelassen werden können. Allerdings muss der Benutzer in diesem Fall die Werte für die Parameter in der gleichen Reihenfolge angeben, in der sie beim Erstellen der Prozedur aufgelistet wurden. Sie können einem Parameter keinen Standardwert zuweisen, indem Sie ihn bei der Aufzählung einfach weglassen. Wenn Sie Parameter weglassen möchten, die einen Standardwert haben, reicht es aus, die Parameternamen beim Aufruf der gespeicherten Prozedur explizit anzugeben. Darüber hinaus können Sie auf diese Weise Parameter und deren Werte in beliebiger Reihenfolge auflisten.

Beachten Sie, dass beim Aufruf einer Prozedur entweder Parameternamen mit Werten oder nur Werte ohne Parameternamen angegeben werden. Eine Kombination ist nicht gestattet.

Verwendung von RETURN in einer gespeicherten Prozedur

Ermöglicht Ihnen, den Vorgang zu jedem Zeitpunkt gemäß einer bestimmten Bedingung zu beenden, und ermöglicht Ihnen außerdem, das Ergebnis des Vorgangs als Zahl zu übermitteln, anhand derer Sie die Qualität und Richtigkeit des Vorgangs beurteilen können. Ein Beispiel für die Erstellung einer Prozedur ohne Parameter:

ERSTELLEN SIE PROCEDURE Count_Books AS SELECT COUNT (Code_book) FROM Books GO

Übung 1.

EXEC Count_Books

Überprüfen Sie das Ergebnis.

Ein Beispiel für die Erstellung einer Prozedur mit einem Eingabeparameter:

PROZEDUR ERSTELLEN Count_Books_Pages @Count_pages AS INT AS SELECT COUNT (Code_book) FROM Books WHERE Pages>= @Count_pages GO

Aufgabe 2. Erstellen Sie diese Prozedur im Abschnitt „Gespeicherte Prozeduren“ der DB_Books-Datenbank mit dem SQL Server Management Studio-Dienstprogramm. Führen Sie es mit dem Befehl aus

EXEC Count_Books_Pages 100

Überprüfen Sie das Ergebnis.

Ein Beispiel für die Erstellung einer Prozedur mit Eingabeparametern:

CREATE PROCEDURE Count_Books_Title @Count_pages AS INT , @Title AS CHAR (10 ) AS SELECT COUNT (Code_book) FROM Books WHERE Pages>= @Count_pages AND Title_book LIKE @Title GO

Aufgabe 3. Erstellen Sie diese Prozedur im Abschnitt „Gespeicherte Prozeduren“ der DB_Books-Datenbank mit dem SQL Server Management Studio-Dienstprogramm. Führen Sie es mit dem Befehl aus

EXEC Count_Books_Title 100 , „P%“

Überprüfen Sie das Ergebnis.

Ein Beispiel für die Erstellung einer Prozedur mit Eingabeparametern und einem Ausgabeparameter:

CREATE PROCEDURE Count_Books_Itogo @Count_pages INT , @Title CHAR (10 ) , @Itogo INT OUTPUT AS SELECT @Itogo = COUNT (Code_book) FROM Books WHERE Pages>= @Count_pages AND Title_book LIKE @Title GO

Aufgabe 4. Erstellen Sie diese Prozedur im Abschnitt „Gespeicherte Prozeduren“ der DB_Books-Datenbank mit dem SQL Server Management Studio-Dienstprogramm. Führen Sie es mit dem Befehlssatz aus:

Sql> Deklarieren Sie @q als int EXEC Count_Books_Itogo 100, „P%“, @q-Ausgabe, wählen Sie @q aus

Überprüfen Sie das Ergebnis.

Ein Beispiel für die Erstellung einer Prozedur mit Eingabeparametern und RETURN:

CREATE PROCEDURE checkname @param INT AS IF (SELECT Name_author FROM Authors WHERE Code_author = @param) = „Pushkin A.S.“ ZURÜCK 1 SONST ZURÜCK 2

Aufgabe 5. Erstellen Sie diese Prozedur im Abschnitt „Gespeicherte Prozeduren“ der DB_Books-Datenbank mit dem SQL Server Management Studio-Dienstprogramm. Führen Sie es mit den folgenden Befehlen aus:

DECLARE @return_status INT EXEC @return_status = Prüfname 1 SELECT "Return Status" = @return_status

Ein Beispiel für die Erstellung einer Prozedur ohne Parameter, um den Wert eines Schlüsselfelds in der Tabelle „Käufe“ um das Zweifache zu erhöhen:

CREATE PROC update_proc AS UPDATE Purchases SET Code_purchase = Code_purchase* 2

Aufgabe 6. Erstellen Sie diese Prozedur im Abschnitt „Gespeicherte Prozeduren“ der DB_Books-Datenbank mit dem SQL Server Management Studio-Dienstprogramm. Führen Sie es mit dem Befehl aus

EXEC update_proc

Ein Beispiel für eine Prozedur mit einem Eingabeparameter, um alle Informationen zu einem bestimmten Autor zu erhalten:

CREATE PROC select_author @k CHAR (30 ) AS SELECT * FROM Authors WHERE name_author= @k

Aufgabe 7.

EXEC select_author „Pushkin A.S.“ oder select_author @k= "Pushkin A.S." oder EXEC select_author @k= „Pushkin A.S.“

Ein Beispiel für die Erstellung einer Prozedur mit einem Eingabeparameter und einem Standardwert, um den Wert eines Schlüsselfelds in der Tabelle „Käufe“ um eine angegebene Anzahl zu erhöhen (standardmäßig 2 Mal):

CREATE PROC update_proc @p INT = 2 AS UPDATE Purchases SET Code_purchase = Code_purchase * @p

Die Prozedur gibt keine Daten zurück.

Aufgabe 8. Erstellen Sie diese Prozedur im Abschnitt „Gespeicherte Prozeduren“ der DB_Books-Datenbank mit dem SQL Server Management Studio-Dienstprogramm. Führen Sie es mit den folgenden Befehlen aus:

EXEC update_proc 4 oder EXEC update_proc @p = 4 oder EXEC update_proc --der Standardwert wird verwendet.

Ein Beispiel für die Erstellung einer Prozedur mit Eingabe- und Ausgabeparametern. Erstellen Sie ein Verfahren, um die Anzahl der in einem bestimmten Zeitraum abgeschlossenen Bestellungen zu ermitteln:

CREATE PROC count_purchases @d1 SMALLDATETIME, @d2 SMALLDATETIME, @c INT OUTPUT AS SELECT @c= COUNT (Code_purchase) FROM Purchases WHERE Date_order BETWEEN @d1 AND @d2 SET @c = ISNULL(@c, 0 )

Aufgabe 9. Erstellen Sie diese Prozedur im Abschnitt „Gespeicherte Prozeduren“ der DB_Books-Datenbank mit dem SQL Server Management Studio-Dienstprogramm. Führen Sie es mit den folgenden Befehlen aus:

DECLARE @c2 INT EXEC count_purchases '01.06.2006', '01.07.2006', @c2 OUTPUT SELECT @c2

Optionen für Aufgaben für Laborarbeiten Nr. 4

Allgemeine Bestimmungen. Erstellen Sie im SQL Server Management Studio-Dienstprogramm eine neue Seite für den Code (die Schaltfläche „Abfrage erstellen“). Aktivieren Sie die erstellte DB_Books-Datenbank programmgesteuert mithilfe der Use-Anweisung. Erstellen Sie gespeicherte Prozeduren mithilfe von Anweisungen zum Erstellen von Prozeduren und definieren Sie die Namen der Prozeduren selbst. Jede Prozedur führt eine SQL-Abfrage aus, die im zweiten Labor ausgeführt wurde. Darüber hinaus muss der SQL-Code der Abfragen geändert werden, damit diese die Werte der zur Suche verwendeten Felder übermitteln können.

Zum Beispiel die Ausgangsaufgabe und Anfrage in Laborarbeit Nr. 2:

/*Wählen Sie aus dem Lieferantenverzeichnis (Liefertabelle) die Namen von Unternehmen, Telefonnummern und INN (Felder Name_company, Phone und INN) aus, deren Firmenname (Feld Name_company) „OJSC MIR“ ist.

SELECT Name_company, Telefon, INN FROM Deliveries WHERE Name_company = "OJSC MIR"

*/ –In dieser Arbeit wird folgende Prozedur erstellt:

CREATE PROC select_name_company @comp CHAR (30 ) AS SELECT Name_company, Phone, INN FROM Deliveries WHERE Name_company = @comp

–Um den Vorgang zu starten, verwenden Sie den Befehl:

EXEC select_name_company „JSC MIR“

Aufgabenliste

Erstellen Sie ein neues Programm in SQL Server Management Studio. Aktivieren Sie programmgesteuert die in Laborarbeit Nr. 1 erstellte individuelle Datenbank mithilfe der Use-Anweisung. Erstellen Sie gespeicherte Prozeduren mithilfe von Anweisungen zum Erstellen von Prozeduren und definieren Sie die Namen der Prozeduren selbst. Jede Prozedur führt eine SQL-Abfrage aus, die je nach Optionen in Form separater Aufgaben dargestellt wird.

Variante 1

1. Zeigen Sie eine Liste der Mitarbeiter an, die mindestens ein Kind haben.

2. Zeigen Sie eine Liste der Kinder an, die im angegebenen Zeitraum Geschenke erhalten haben.

3. Zeigen Sie eine Liste der Eltern an, die minderjährige Kinder haben.

4. Zeigen Sie Informationen zu Geschenken an, deren Wert größer als die angegebene Zahl ist, sortiert nach Datum.

Option 2

1. Zeigen Sie eine Liste der Geräte mit dem angegebenen Typ an.

2. Zeigen Sie die Anzahl der reparierten Geräte und die Gesamtkosten der Reparaturen durch den angegebenen Techniker an.

3. Zeigen Sie eine Liste der Gerätebesitzer und die Anzahl ihrer Anfragen an, sortiert nach der Anzahl der Anfragen in absteigender Reihenfolge.

4. Zeigen Sie Informationen über Handwerker an, deren Rang über der angegebenen Zahl liegt oder deren Einstellungsdatum unter dem angegebenen Datum liegt.

Option 3

2. Zeigen Sie eine Liste der Verkaufscodes an, bei denen Blumen für einen Betrag verkauft wurden, der über der angegebenen Anzahl liegt.

3. Zeigen Sie Verkaufsdatum, Menge, Verkäufer und Blume gemäß dem angegebenen Verkaufscode an.

4. Zeigen Sie eine Liste der Blumen und der Sorte für Blumen an, deren Höhe größer als die angegebene Anzahl ist oder die blühen.

Option 4

1. Zeigen Sie eine Liste der Medikamente mit der angegebenen Anwendungsindikation an.

2. Zeigen Sie eine Liste der Liefertermine an, zu denen mehr als die angegebene Anzahl des gleichnamigen Arzneimittels verkauft wurde.

3. Zeigen Sie das Lieferdatum, die Menge, den vollständigen Namen des Managers des Lieferanten und den Namen des Arzneimittels anhand eines Quittungscodes an, der größer als die angegebene Zahl ist.

Option 5

2. Zeigen Sie eine Liste der stillgelegten Geräte aus dem angegebenen Grund an.

3. Geben Sie das Eingangsdatum, den Namen des Geräts, den vollständigen Namen der verantwortlichen Person und das Abschreibungsdatum für Geräte an, die im angegebenen Zeitraum abgeschrieben wurden.

4. Zeigen Sie eine Liste von Geräten mit einem bestimmten Typ oder einem Eingangsdatum an, das über einem bestimmten Wert liegt

Option 6

1. Zeigen Sie eine Liste der Gerichte an, deren Gewicht die angegebene Anzahl übersteigt.

2. Zeigen Sie eine Liste der Produkte an, deren Namen das angegebene Wortfragment enthalten.

3. Zeigen Sie das Volumen des Produkts, den Namen des Gerichts, den Namen des Produkts mit dem Gerichtscode vom angegebenen Anfangswert bis zu einem bestimmten Endwert an.

4. Zeigen Sie die Reihenfolge der Zubereitung eines Gerichts und den Namen eines Gerichts an, dessen Kohlenhydratmenge über einem bestimmten Wert liegt oder dessen Kalorienmenge über einem bestimmten Wert liegt.

Option 7

1. Zeigen Sie eine Liste der Mitarbeiter mit der angegebenen Position an.

3. Zeigen Sie das Registrierungsdatum, den Dokumenttyp, den vollständigen Namen des Registrars und den Namen der Organisation für Dokumente an, die im angegebenen Zeitraum registriert wurden.

4. Zeigen Sie eine Liste registrierter Dokumente mit einem bestimmten Dokumenttyp oder einem Registrierungsdatum an, das über dem angegebenen Wert liegt.

Option 8

1. Zeigen Sie eine Liste der Mitarbeiter mit dem angegebenen Kündigungsgrund an.

3. Zeigen Sie das Registrierungsdatum, den Kündigungsgrund und den vollständigen Namen des Mitarbeiters für Dokumente an, die während des angegebenen Zeitraums registriert wurden.

Option 9

1. Zeigen Sie eine Liste der Mitarbeiter an, die Urlaub der angegebenen Art genommen haben.

2. Zeigen Sie eine Liste von Dokumenten mit einem Registrierungsdatum im angegebenen Zeitraum an.

3. Zeigen Sie das Datum der Registrierung, die Art des Urlaubs und den vollständigen Namen des Mitarbeiters für Dokumente an, die während des angegebenen Zeitraums registriert wurden.

4. Zeigen Sie eine Liste der registrierten Dokumente mit einem Dokumentcode im angegebenen Bereich an.

Option 10

1. Zeigen Sie eine Liste der Mitarbeiter mit der angegebenen Position an.

2. Zeigen Sie eine Liste von Dokumenten an, deren Inhalt das angegebene Wortfragment enthält.

3. Zeigen Sie das Registrierungsdatum, den Dokumenttyp, den vollständigen Namen des Absenders und den Namen der Organisation für Dokumente an, die im angegebenen Zeitraum registriert wurden.

4. Zeigen Sie eine Liste der registrierten Dokumente mit dem angegebenen Dokumenttyp oder mit einem Dokumentcode an, der kleiner als ein bestimmter Wert ist.

Option 11

1. Zeigen Sie eine Liste der Mitarbeiter an, die der angegebenen Position zugeordnet sind.

2. Zeigen Sie eine Liste von Dokumenten mit einem Registrierungsdatum im angegebenen Zeitraum an.

3. Zeigen Sie das Registrierungsdatum, die Position und den vollständigen Namen des Mitarbeiters für Dokumente an, die im angegebenen Zeitraum registriert wurden.

4. Zeigen Sie eine Liste der registrierten Dokumente mit einem Dokumentcode im angegebenen Bereich an.

Option 12

3. Zeigen Sie eine Liste der Personen an, die Ausrüstung gemietet haben, und die Anzahl ihrer Anfragen, sortiert nach der Anzahl der Anfragen in absteigender Reihenfolge.

Option 13

1. Zeigen Sie eine Liste der Geräte mit dem angegebenen Typ an. 2. Zeigen Sie eine Liste der Geräte an, die von einem bestimmten Mitarbeiter abgeschrieben wurden.

3. Zeigen Sie die Menge der stillgelegten Geräte an, gruppiert nach Gerätetyp.

4. Zeigen Sie Informationen zu Mitarbeitern an, deren Einstellungsdatum nach einem bestimmten Datum liegt.

Option 14

1. Drucken Sie eine Liste von Blumen mit der angegebenen Blattart.

2. Zeigen Sie eine Liste der Quittungscodes an, für die Blumen für Beträge über einem bestimmten Wert verkauft wurden.

3. Zeigen Sie das Eingangsdatum, den Betrag, den Namen des Lieferanten und die Farben anhand eines bestimmten Lieferantencodes an.

4. Zeigen Sie eine Liste der Blumen und der Sorte für Blumen an, deren Höhe eine bestimmte Anzahl überschreitet oder die blühen.

Option 15

1. Zeigen Sie eine Liste der Kunden an, die im angegebenen Zeitraum in die Zimmer eingecheckt haben.

2. Zeigen Sie den Gesamtbetrag der Zimmerzahlungen für jeden Kunden an.

3. Zeigen Sie das Ankunftsdatum, die Zimmerkategorie und den vollständigen Namen der im angegebenen Zeitraum registrierten Kunden an.

4. Zeigen Sie eine Liste der registrierten Kunden in Räumen eines bestimmten Typs an.

Option 16

1. Zeigen Sie eine Liste der Geräte mit dem angegebenen Typ an.

2. Zeigen Sie eine Liste der Geräte an, die von einem bestimmten Kunden gemietet wurden.

3. Zeigen Sie eine Liste der Personen an, die Ausrüstung gemietet haben, und die Anzahl ihrer Anfragen, sortiert nach der Anzahl der Anfragen in absteigender Reihenfolge.

4. Zeigen Sie Informationen zu Kunden sortiert nach Adresse an.

Option 17

1. Zeigen Sie eine Liste von Wertgegenständen an, deren Kaufpreis über einem bestimmten Wert liegt oder deren Garantiezeitraum über einer bestimmten Zahl liegt.

2. Zeigen Sie eine Liste der Standorte materieller Vermögenswerte an, deren Namen das angegebene Wort enthalten.

3. Zeigen Sie die Summe der Werte mit einem Code im angegebenen Bereich an.

4. Zeigen Sie eine Liste der finanziell verantwortlichen Personen mit dem Beschäftigungsdatum im angegebenen Bereich an.

Option 18

1. Zeigen Sie eine Liste der von einem bestimmten Techniker durchgeführten Reparaturen an.

2. Zeigen Sie eine Liste der Arbeitsschritte an, die in dem Werk enthalten sind, dessen Titel das angegebene Wort enthält.

3. Zeigen Sie die Summe der Kosten der Reparaturarbeitsschritte für Arbeiten mit einem Code im angegebenen Bereich an.

4. Zeigen Sie eine Liste der Vorarbeiter an, deren Einstellungsdatum im angegebenen Bereich liegt.

Option 19

1. Zeigen Sie eine Liste von Medikamenten mit einer bestimmten Indikation an.

2. Zeigen Sie eine Liste der Belegnummern an, für die mehr als eine bestimmte Anzahl Medikamente verkauft wurden.

3. Geben Sie auf der Quittung das Verkaufsdatum, den Betrag, den Namen des Kassierers und das Medikament mit der angegebenen Nummer an.

4. Zeigen Sie eine Liste von Medikamenten und Maßeinheiten für Medikamente an, deren Menge in der Packung größer als die angegebene Anzahl oder deren Medikamentencode kleiner als ein bestimmter Wert ist.

Option 20

1. Zeigen Sie eine Liste der Mitarbeiter mit der angegebenen Position an.

2. Zeigen Sie eine Liste von Dokumenten an, deren Inhalt das angegebene Wortfragment enthält.

3. Zeigen Sie das Registrierungsdatum, die Art des Dokuments, den vollständigen Namen des Testamentsvollstreckers und die Tatsache der Ausführung für Dokumente an, die im angegebenen Zeitraum registriert wurden.

4. Zeigen Sie eine Liste der registrierten Dokumente mit dem angegebenen Dokumenttyp oder Dokumentcode in einem bestimmten Bereich an.

Gespeicherte SQL-Prozeduren sind ausführbare Programmmodule, die in Form verschiedener Objekte gespeichert werden können. Mit anderen Worten handelt es sich um ein Objekt, das SQL-Anweisungen enthält. Diese gespeicherten Prozeduren können im Anwendungsclient ausgeführt werden, um eine gute Leistung zu erzielen. Darüber hinaus werden solche Objekte häufig aus anderen Skripten oder sogar aus einem anderen Abschnitt aufgerufen.

Einführung

Viele Leute glauben, dass sie verschiedenen Verfahren ähneln (außer MS SQL). Vielleicht stimmt das. Sie haben ähnliche Parameter und können ähnliche Werte erzeugen. Darüber hinaus berühren sie sich in manchen Fällen. Sie werden beispielsweise mit DDL- und DML-Datenbanken sowie Benutzerfunktionen (Codename UDF) kombiniert.

Tatsächlich weisen gespeicherte SQL-Prozeduren zahlreiche Vorteile auf, die sie von ähnlichen Prozessen unterscheiden. Sicherheit, Programmierflexibilität, Produktivität – all das lockt immer mehr Anwender, die mit Datenbanken arbeiten. Den Höhepunkt der Beliebtheit der Verfahren erreichten sie in den Jahren 2005–2010, als ein Programm von Microsoft namens „SQL Server Management Studio“ veröffentlicht wurde. Mit seiner Hilfe ist die Arbeit mit Datenbanken viel einfacher, praktischer und komfortabler geworden. Von Jahr zu Jahr gewann dieses Programm bei Programmierern an Popularität. Heute ist es ein absolut bekanntes Programm, das für Anwender, die mit Datenbanken „kommunizieren“, auf Augenhöhe mit Excel steht.

Wenn eine Prozedur aufgerufen wird, wird sie sofort vom Server selbst verarbeitet, ohne dass unnötige Prozesse oder Benutzereingriffe erforderlich sind. Anschließend können Sie beliebige Löschungen, Ausführungen oder Änderungen durchführen. Für all dies ist der DDL-Operator verantwortlich, der allein die komplexesten Aktionen zur Verarbeitung von Objekten ausführt. Darüber hinaus geschieht dies alles sehr schnell und der Server wird nicht wirklich belastet. Diese Geschwindigkeit und Leistung ermöglichen Ihnen die sehr schnelle Übertragung großer Informationsmengen vom Benutzer zum Server und umgekehrt.

Um diese Technologie für die Arbeit mit Informationen umzusetzen, gibt es mehrere Programmiersprachen. Hierzu zählen beispielsweise PL/SQL von Oracle, PSQL in den Systemen InterBase und Firebird sowie das klassische Microsoft Transact-SQL. Sie alle sind für die Erstellung und Ausführung gespeicherter Prozeduren konzipiert, was es großen Datenbankprozessoren ermöglicht, ihre eigenen Algorithmen zu verwenden. Dies ist auch notwendig, damit diejenigen, die diese Informationen verwalten, alle Objekte vor unbefugtem Zugriff Dritter und damit vor der Erstellung, Änderung oder Löschung bestimmter Daten schützen können.

Produktivität

Diese Datenbankobjekte können auf verschiedene Arten programmiert werden. Dadurch können Benutzer die Art der verwendeten Methode auswählen, die am besten geeignet ist, was Aufwand und Zeit spart. Darüber hinaus wird der Vorgang selbst abgewickelt, wodurch der enorme Zeitaufwand für die Kommunikation zwischen Server und Benutzer vermieden wird. Außerdem kann das Modul jederzeit umprogrammiert und in die gewünschte Richtung verändert werden. Besonders hervorzuheben ist die Geschwindigkeit, mit der die gespeicherte SQL-Prozedur gestartet wird: Dieser Prozess erfolgt schneller als andere ähnliche Prozesse, was ihn bequem und universell macht.

Sicherheit

Diese Art der Informationsverarbeitung unterscheidet sich von ähnlichen Verfahren dadurch, dass sie eine erhöhte Sicherheit gewährleistet. Dies wird dadurch gewährleistet, dass der Zugriff auf Vorgänge durch andere Benutzer vollständig ausgeschlossen werden kann. Dadurch kann der Administrator unabhängig voneinander Vorgänge mit ihnen durchführen, ohne befürchten zu müssen, dass Informationen abgefangen oder unbefugt auf die Datenbank zugegriffen wird.

Datentransfer

Die Beziehung zwischen der gespeicherten SQL-Prozedur und der Clientanwendung besteht in der Verwendung von Parametern und Rückgabewerten. Letztere muss die Daten nicht an die gespeicherte Prozedur übergeben, sondern diese Informationen werden (hauptsächlich auf Wunsch des Benutzers) für SQL verarbeitet. Nachdem die gespeicherte Prozedur ihre Arbeit abgeschlossen hat, sendet sie Datenpakete zurück (aber wiederum optional) an die Anwendung, die sie aufgerufen hat, und verwendet dabei verschiedene Methoden, mit denen entweder die gespeicherte SQL-Prozedur aufgerufen oder zurückgegeben werden kann, zum Beispiel:

Übertragen von Daten mithilfe eines Ausgabetypparameters;

Übergabe von Daten mit dem Return-Operator;

Übergeben von Daten mit dem Auswahloperator.

Lassen Sie uns nun herausfinden, wie dieser Prozess von innen aussieht.

1. Erstellen Sie eine gespeicherte EXEC-Prozedur in SQL

Sie können eine Prozedur in MS SQL (Managment Studio) erstellen. Nachdem die Prozedur erstellt wurde, wird sie im programmierbaren Knoten der Datenbank aufgeführt, in der die Erstellungsprozedur vom Bediener ausgeführt wird. Zur Ausführung verwenden gespeicherte SQL-Prozeduren einen EXEC-Prozess, der den Namen des Objekts selbst enthält.

Wenn Sie eine Prozedur erstellen, erscheint zuerst ihr Name, gefolgt von einem oder mehreren ihr zugewiesenen Parametern. Parameter können optional sein. Nachdem die Parameter, also der Hauptteil der Prozedur, geschrieben wurden, müssen einige notwendige Operationen ausgeführt werden.

Der Punkt ist, dass ein Körper lokale Variablen enthalten kann und diese Variablen auch in Bezug auf Prozeduren lokal sind. Mit anderen Worten: Sie können nur im Hauptteil einer Microsoft SQL Server-Prozedur angezeigt werden. Gespeicherte Prozeduren gelten in diesem Fall als lokal.

Um eine Prozedur zu erstellen, benötigen wir also den Namen der Prozedur und mindestens einen Parameter als Hauptteil der Prozedur. Beachten Sie, dass in diesem Fall eine gute Option darin besteht, eine Prozedur mit dem Schemanamen im Klassifikator zu erstellen und auszuführen.

Der Hauptteil der Prozedur kann beliebiger Art sein, z. B. das Erstellen einer Tabelle, das Einfügen einer oder mehrerer Zeilen einer Tabelle, das Festlegen des Typs und der Art der Datenbank usw. Der Prozedurhauptteil schränkt jedoch die Ausführung bestimmter Vorgänge darin ein. Einige der wichtigen Einschränkungen sind unten aufgeführt:

Der Körper sollte keine andere gespeicherte Prozedur erstellen;

Der Körper sollte keinen falschen Eindruck vom Objekt erwecken;

Der Körper sollte keine Auslöser erzeugen.

2. Festlegen einer Variablen im Hauptteil der Prozedur

Sie können Variablen lokal für den Hauptteil der Prozedur festlegen, sodass sie sich dann ausschließlich im Hauptteil der Prozedur befinden. Es empfiehlt sich, Variablen am Anfang des Hauptteils der gespeicherten Prozedur zu erstellen. Sie können Variablen aber auch an einer beliebigen Stelle im Körper eines bestimmten Objekts festlegen.

Manchmal stellen Sie möglicherweise fest, dass mehrere Variablen in einer Zeile festgelegt sind und die einzelnen Variablenparameter durch ein Komma getrennt sind. Beachten Sie außerdem, dass der Variablen das Präfix @ vorangestellt ist. Im Hauptteil der Prozedur können Sie die Variable an eine beliebige Stelle setzen. Beispielsweise kann die Variable @NAME1 am Ende des Prozedurhauptteils deklariert werden. Um einer deklarierten Variablen einen Wert zuzuweisen, wird ein Satz personenbezogener Daten verwendet. Anders als in der Situation, in der mehr als eine Variable in derselben Zeile deklariert wird, wird in dieser Situation nur ein Satz personenbezogener Daten verwendet.

Benutzer stellen häufig die Frage: „Wie weist man einer Anweisung im Hauptteil einer Prozedur mehrere Werte zu?“ Also. Das ist eine interessante Frage, aber viel einfacher zu lösen, als Sie denken. Antwort: Verwenden von Paaren wie „Select Var = value“. Sie können diese Paare verwenden, indem Sie sie durch ein Komma trennen.

Eine Vielzahl von Beispielen zeigt, wie Menschen eine einfache gespeicherte Prozedur erstellen und ausführen. Eine Prozedur kann jedoch Parameter akzeptieren, sodass der Prozess, der sie aufruft, Werte in der Nähe davon hat (aber nicht immer). Wenn sie zusammenfallen, beginnen entsprechende Prozesse im Körperinneren. Wenn Sie beispielsweise eine Prozedur erstellen, die eine Stadt und Region vom Aufrufer akzeptiert und Daten darüber zurückgibt, wie viele Autoren zu der entsprechenden Stadt und Region gehören. Die Prozedur fragt die Autorentabellen der Datenbank ab, z. B. „Pubs“, um diese Autorenzählung durchzuführen. Um diese Datenbanken zu erhalten, lädt Google beispielsweise das SQL-Skript von der SQL2005-Seite herunter.

Im vorherigen Beispiel benötigt die Prozedur zwei Parameter, die im Englischen üblicherweise @State und @City heißen. Der Datentyp entspricht dem in der Anwendung definierten Typ. Der Hauptteil der Prozedur verfügt über interne Variablen @TotalAuthors, und diese Variable wird verwendet, um die Anzahl der Autoren anzuzeigen. Als nächstes kommt der Abschnitt zur Abfrageauswahl, der alles berechnet. Abschließend wird der berechnete Wert mit der print-Anweisung im Ausgabefenster gedruckt.

So führen Sie eine gespeicherte Prozedur in SQL aus

Es gibt zwei Möglichkeiten, den Vorgang durchzuführen. Der erste Weg zeigt durch die Übergabe von Parametern, wie eine durch Kommas getrennte Liste nach dem Prozedurnamen ausgeführt wird. Nehmen wir an, wir haben zwei Werte (wie im vorherigen Beispiel). Diese Werte werden mithilfe der Prozedurparametervariablen @State und @City erfasst. Bei dieser Methode der Parameterübergabe ist die Reihenfolge wichtig. Diese Methode wird als ordinale Argumentübergabe bezeichnet. Bei der zweiten Methode sind die Parameter bereits direkt zugewiesen, die Reihenfolge spielt in diesem Fall keine Rolle. Diese zweite Methode wird als Übergabe benannter Argumente bezeichnet.

Der Ablauf kann geringfügig vom typischen abweichen. Alles ist wie im vorherigen Beispiel, nur werden hier die Parameter verschoben. Das heißt, @City wird zuerst gespeichert und @State wird neben dem Standardwert gespeichert. Der Standardparameter wird normalerweise separat hervorgehoben. Gespeicherte SQL-Prozeduren werden nur als Parameter übergeben. In diesem Fall ersetzt der Parameter „UT“ sofern vorhanden den Standardwert „CA“. Bei der zweiten Ausführung wird nur ein Argumentwert für den @City-Parameter übergeben und der @State-Parameter nimmt den Standardwert „CA“ an. Erfahrene Programmierer empfehlen, dass alle Variablen standardmäßig am Ende der Parameterliste stehen sollten. Andernfalls ist die Ausführung nicht möglich und Sie müssen mit der Übergabe benannter Argumente arbeiten, was länger und komplexer ist.

4. Gespeicherte SQL Server-Prozeduren: Rückgabemethoden

Es gibt drei wichtige Möglichkeiten, Daten in einer aufgerufenen gespeicherten Prozedur zu senden. Sie sind unten aufgeführt:

Gibt den Wert einer gespeicherten Prozedur zurück;

Parameterausgabe der gespeicherten Prozedur;

Auswahl einer der gespeicherten Prozeduren.

4.1 Rückgabe von Werten aus gespeicherten SQL-Prozeduren

Bei dieser Technik weist eine Prozedur einer lokalen Variablen einen Wert zu und gibt ihn zurück. Eine Prozedur kann auch direkt einen konstanten Wert zurückgeben. Im folgenden Beispiel haben wir eine Prozedur erstellt, die die Gesamtzahl der Autoren zurückgibt. Wenn Sie dieses Verfahren mit den vorherigen vergleichen, können Sie feststellen, dass der Druckwert umgekehrt ist.

Sehen wir uns nun an, wie man eine Prozedur ausführt und ihren Rückgabewert ausgibt. Die Ausführung des Verfahrens erfordert das Setzen einer Variablen und das Drucken, was nach diesem gesamten Vorgang ausgeführt wird. Beachten Sie, dass Sie anstelle einer print-Anweisung auch eine Select-Anweisung verwenden können, z. B. Select @RetValue sowie OutputValue.

4.2 SQL Stored Procedure-Parameterausgabe

Der Antwortwert kann verwendet werden, um eine einzelne Variable zurückzugeben, was wir im vorherigen Beispiel gesehen haben. Mithilfe des Output-Parameters kann eine Prozedur einen oder mehrere Variablenwerte an den Aufrufer senden. Der Ausgabeparameter wird beim Erstellen einer Prozedur genau mit diesem Schlüsselwort „Output“ bezeichnet. Wird ein Parameter als Ausgabeparameter angegeben, muss ihm das Prozedurobjekt einen Wert zuweisen. Gespeicherte SQL-Prozeduren, von denen Beispiele unten zu sehen sind, geben in diesem Fall zusammenfassende Informationen zurück.

In unserem Beispiel gibt es zwei Ausgabenamen: @TotalAuthors und @TotalNoContract. Sie sind in der Parameterliste aufgeführt. Diese Variablen weisen Werte innerhalb des Prozedurkörpers zu. Wenn wir Ausgabeparameter verwenden, kann der Aufrufer den im Hauptteil der Prozedur festgelegten Wert sehen.

Außerdem werden im vorherigen Szenario zwei Variablen deklariert, um die Werte anzuzeigen, die die gespeicherten Prozeduren von MS SQL Server im Ausgabeparameter festgelegt haben. Anschließend wird das Verfahren durchgeführt, indem der Normalwert des Parameters „CA“ bereitgestellt wird. Die folgenden Parameter sind Ausgabeparameter und daher werden die deklarierten Variablen in der angegebenen Reihenfolge übergeben. Beachten Sie, dass bei der Übergabe von Variablen auch das Schlüsselwort „output“ hier festgelegt wird. Nach erfolgreichem Abschluss des Vorgangs werden die von den Ausgabeparametern zurückgegebenen Werte im Meldungsfeld angezeigt.

4.3 Auswahl einer der gespeicherten SQL-Prozeduren

Diese Technik wird verwendet, um eine Reihe von Werten als Datentabelle (RecordSet) an die aufrufende gespeicherte Prozedur zurückzugeben. In diesem Beispiel fragt die gespeicherte SQL-Prozedur mit @AuthID-Parametern die Tabelle „Autoren“ ab, indem sie die mithilfe dieses @AuthId-Parameters zurückgegebenen Datensätze filtert. Die Select-Anweisung entscheidet, was an den Aufrufer der gespeicherten Prozedur zurückgegeben werden soll. Wenn die gespeicherte Prozedur ausgeführt wird, wird die AuthId zurückgegeben. Dieses Verfahren hier liefert immer nur einen Datensatz oder gar keinen. Eine gespeicherte Prozedur unterliegt jedoch keinen Einschränkungen hinsichtlich der Rückgabe von mehr als einem Datensatz. Es ist nicht ungewöhnlich, Beispiele zu sehen, bei denen Daten mithilfe ausgewählter Parameter zurückgegeben werden, die berechnete Variablen umfassen, indem mehrere Summen bereitgestellt werden.

Abschließend

Eine gespeicherte Prozedur ist ein ziemlich ernstes Programmmodul, das zurückgibt oder übergibt und dank der Clientanwendung auch die erforderlichen Variablen festlegt. Da die gespeicherte Prozedur selbst auf dem Server ausgeführt wird, können große Mengen an Datenaustausch zwischen dem Server und der Clientanwendung (für einige Berechnungen) vermieden werden. Dadurch können Sie die Belastung der SQL-Server reduzieren, was natürlich deren Besitzern zugute kommt. Einer der Untertypen sind gespeicherte T-SQL-Prozeduren, deren Untersuchung jedoch für diejenigen erforderlich ist, die beeindruckende Datenbanken erstellen. Es gibt auch eine große, sogar riesige Anzahl von Nuancen, die beim Studium gespeicherter Prozeduren nützlich sein können, aber dies ist eher für diejenigen erforderlich, die planen, sich mit der Programmierung zu befassen, auch beruflich.

Das Konzept der gespeicherten Prozeduren wird definiert. Bietet Beispiele für das Erstellen, Ändern und Verwenden gespeicherter Prozeduren mit Parametern. Die Definition der Eingabe- und Ausgabeparameter wird angegeben. Es werden Beispiele für das Erstellen und Aufrufen gespeicherter Prozeduren bereitgestellt.

Das Konzept einer gespeicherten Prozedur

Gespeicherte Prozeduren sind Gruppen miteinander verbundener SQL-Anweisungen, deren Verwendung die Arbeit des Programmierers einfacher und flexibler macht gespeicherte Prozedur ist oft viel einfacher als eine Folge einzelner SQL-Anweisungen. Gespeicherte Prozeduren sind eine Reihe von Befehlen, die aus einer oder mehreren SQL-Anweisungen oder -Funktionen bestehen und in kompilierter Form in einer Datenbank gespeichert werden. Ausführung in der Datenbank Gespeicherte Prozeduren Anstelle einzelner SQL-Anweisungen hat der Benutzer folgende Vorteile:

• die notwendigen Operatoren sind bereits in der Datenbank enthalten;
• Sie haben alle die Bühne passiert Parsing und liegen im ausführbaren Format vor; Vor Ausführen einer gespeicherten Prozedur SQL Server generiert dafür einen Ausführungsplan, führt dessen Optimierung und Kompilierung durch;
• Gespeicherte Prozeduren Unterstützung Modulare Programmierung, da Sie damit große Aufgaben in unabhängige, kleinere und einfacher zu verwaltende Teile aufteilen können;
• Gespeicherte Prozeduren kann andere verursachen Gespeicherte Prozeduren und Funktionen;
• Gespeicherte Prozeduren kann von anderen Arten von Anwendungsprogrammen aufgerufen werden;
• allgemein, Gespeicherte Prozeduren schneller ausführen als eine Folge einzelner Anweisungen;
• Gespeicherte Prozeduren einfacher zu verwenden: Sie können aus Dutzenden oder Hunderten von Befehlen bestehen, aber um sie auszuführen, müssen Sie nur den Namen des gewünschten Befehls angeben gespeicherte Prozedur. Dadurch können Sie die Größe der vom Client an den Server gesendeten Anfrage und damit die Belastung des Netzwerks reduzieren.

Das Speichern von Vorgängen am selben Ort, an dem sie ausgeführt werden, reduziert die über das Netzwerk übertragene Datenmenge und verbessert die Gesamtsystemleistung. Anwendung Gespeicherte Prozeduren vereinfacht die Wartung von Softwaresystemen und deren Änderungen. Typischerweise werden alle Integritätsbeschränkungen in Form von Regeln und Datenverarbeitungsalgorithmen auf dem Datenbankserver implementiert und stehen der Endanwendung als Satz zur Verfügung Gespeicherte Prozeduren, die die Datenverarbeitungsschnittstelle darstellen. Zur Gewährleistung der Datenintegrität sowie aus Sicherheitsgründen erhält die Anwendung in der Regel keinen direkten Zugriff auf die Daten – die gesamte Arbeit mit ihr erfolgt über einen Aufruf bestimmter Gespeicherte Prozeduren.

Dieser Ansatz macht es sehr einfach, Datenverarbeitungsalgorithmen zu ändern, die sofort allen Netzwerkbenutzern zur Verfügung stehen, und bietet die Möglichkeit, das System zu erweitern, ohne Änderungen an der Anwendung selbst vorzunehmen: einfach ändern gespeicherte Prozedur auf dem Datenbankserver. Der Entwickler muss die Anwendung nicht neu kompilieren, Kopien davon erstellen oder Benutzer anweisen, mit der neuen Version zu arbeiten. Benutzer bemerken möglicherweise nicht einmal, dass Änderungen am System vorgenommen wurden.

Gespeicherte Prozeduren existieren unabhängig von Tabellen oder anderen Datenbankobjekten. Sie werden vom Client-Programm aufgerufen, ein anderes gespeicherte Prozedur oder Auslöser. Der Entwickler kann die Zugriffsrechte verwalten gespeicherte Prozedur, seine Ausführung erlauben oder verbieten. Code ändern gespeicherte Prozedur nur durch seinen Eigentümer oder ein Mitglied einer festen Datenbankrolle zulässig. Bei Bedarf können Sie das Eigentum daran von einem Benutzer auf einen anderen übertragen.

Gespeicherte Prozeduren in der MS SQL Server-Umgebung

Bei der Arbeit mit SQL Server können Benutzer eigene Prozeduren erstellen, die bestimmte Aktionen implementieren. Gespeicherte Prozeduren sind vollwertige Datenbankobjekte und werden daher jeweils in einer bestimmten Datenbank gespeichert. Direktwahl gespeicherte Prozedur ist nur möglich, wenn sie im Kontext der Datenbank durchgeführt wird, in der sich die Prozedur befindet.

Arten von gespeicherten Prozeduren

SQL Server hat mehrere Typen Gespeicherte Prozeduren.

• System Gespeicherte Prozeduren Entwickelt, um verschiedene Verwaltungsaktionen durchzuführen. Mit ihrer Hilfe werden nahezu alle Tätigkeiten der Serveradministration durchgeführt. Wir können das systemisch sagen Gespeicherte Prozeduren sind eine Schnittstelle, die die Arbeit mit Systemtabellen ermöglicht, bei der es sich letztendlich um das Ändern, Hinzufügen, Löschen und Abrufen von Daten aus Systemtabellen von Benutzer- und Systemdatenbanken handelt. System Gespeicherte Prozeduren haben das Präfix sp_, werden in der Systemdatenbank gespeichert und können im Kontext jeder anderen Datenbank aufgerufen werden.
• Brauch Gespeicherte Prozeduren bestimmte Maßnahmen umsetzen. Gespeicherte Prozeduren– ein vollwertiges Datenbankobjekt. Infolgedessen jeder gespeicherte Prozedur befindet sich in einer bestimmten Datenbank, in der es ausgeführt wird.
• Vorübergehend Gespeicherte Prozeduren bestehen nur eine Weile und werden danach automatisch vom Server zerstört. Sie werden in lokal und global unterteilt. Lokal vorübergehend Gespeicherte Prozeduren können nur aus der Verbindung aufgerufen werden, in der sie erstellt wurden. Wenn Sie eine solche Prozedur erstellen, müssen Sie ihr einen Namen geben, der mit einem einzelnen #-Zeichen beginnt. Wie alle temporären Objekte Gespeicherte Prozeduren Dateien dieses Typs werden automatisch gelöscht, wenn der Benutzer die Verbindung trennt oder der Server neu gestartet oder gestoppt wird. Global temporär Gespeicherte Prozeduren stehen für alle Verbindungen von einem Server zur Verfügung, der über das gleiche Verfahren verfügt. Um es zu definieren, geben Sie ihm einfach einen Namen, der mit den Zeichen ## beginnt. Diese Prozeduren werden gelöscht, wenn der Server neu gestartet oder gestoppt wird oder wenn die Verbindung in dem Kontext, in dem sie erstellt wurden, geschlossen wird.

Erstellen, ändern und löschen Sie gespeicherte Prozeduren

Schaffung gespeicherte Prozedur beinhaltet die Lösung folgender Probleme:

• Bestimmen der Art der erstellten gespeicherte Prozedur: temporär oder benutzerdefiniert. Darüber hinaus können Sie Ihr eigenes System erstellen gespeicherte Prozedur, geben Sie ihm einen Namen mit dem Präfix sp_ und platzieren Sie ihn in der Systemdatenbank. Dieses Verfahren ist im Kontext jeder lokalen Serverdatenbank verfügbar;
• Zutrittsrechte planen. Beim Erstellen gespeicherte Prozedur Es ist zu berücksichtigen, dass es die gleichen Zugriffsrechte auf Datenbankobjekte hat wie der Benutzer, der es erstellt hat.
• Definition gespeicherte Prozedurparameter. Ähnlich den Prozeduren, die in den meisten Programmiersprachen enthalten sind, Gespeicherte Prozeduren kann Eingabe- und Ausgabeparameter haben;
• Code-Entwicklung gespeicherte Prozedur. Der Prozedurcode kann eine Folge beliebiger SQL-Befehle enthalten, einschließlich Aufrufen anderer Gespeicherte Prozeduren.

Erstellen Sie ein neues und ändern Sie ein vorhandenes gespeicherte Prozedur erledigt mit folgendem Befehl:

<определение_процедуры>::= (CREATE | ALTER ) PROC procedure_name [;number] [(@parameter_name data_type ) [=default] ][,...n] AS sql_operator [...n]

Schauen wir uns die Parameter dieses Befehls an.

Mit den Präfixen sp_ ​​, # , ## kann die erstellte Prozedur als System- oder temporäre Prozedur definiert werden. Wie Sie der Befehlssyntax entnehmen können, ist es nicht zulässig, den Namen des Besitzers anzugeben, der Eigentümer der erstellten Prozedur sein wird, sowie den Namen der Datenbank, in der sie gespeichert werden soll. Also, um das Geschaffene zu platzieren gespeicherte Prozedur In einer bestimmten Datenbank müssen Sie den Befehl CREATE PROCEDURE im Kontext dieser Datenbank ausgeben. Beim Abwenden vom Körper gespeicherte Prozedur Für Objekte derselben Datenbank können verkürzte Namen verwendet werden, d. h. ohne Angabe des Datenbanknamens. Wenn Sie auf Objekte zugreifen müssen, die sich in anderen Datenbanken befinden, ist die Angabe des Datenbanknamens obligatorisch.

Die Zahl im Namen ist eine Identifikationsnummer gespeicherte Prozedur, wodurch es in einer Gruppe von Prozeduren eindeutig identifiziert wird. Zur Vereinfachung der Verwaltung sind die Verfahren logischerweise vom gleichen Typ Gespeicherte Prozeduren können gruppiert werden, indem ihnen der gleiche Name, aber unterschiedliche Identifikationsnummern gegeben werden.

Um Eingabe- und Ausgabedaten in das erstellte zu übertragen gespeicherte Prozedur Es können Parameter verwendet werden, deren Namen, ebenso wie die Namen lokaler Variablen, mit dem @-Symbol beginnen müssen. Eins gespeicherte Prozedur Sie können mehrere Parameter durch Kommas getrennt angeben. Der Rumpf einer Prozedur sollte keine lokalen Variablen verwenden, deren Namen mit den Namen der Parameter dieser Prozedur übereinstimmen.

Um den Datentyp zu bestimmen, der dem entspricht Parameter einer gespeicherten Prozedur sind alle SQL-Datentypen geeignet, auch benutzerdefinierte. Der Datentyp CURSOR kann jedoch nur als verwendet werden Ausgabeparameter gespeicherte Prozedur, d.h. Angabe des Schlüsselworts OUTPUT.

Das Vorhandensein des Schlüsselworts OUTPUT bedeutet, dass der entsprechende Parameter Daten zurückgeben soll gespeicherte Prozedur. Dies bedeutet jedoch nicht, dass der Parameter nicht für die Übergabe von Werten geeignet ist gespeicherte Prozedur. Durch Angabe des Schlüsselworts OUTPUT wird der Server angewiesen, den Vorgang zu beenden gespeicherte Prozedur Weisen Sie den aktuellen Wert des Parameters der lokalen Variablen zu, die beim Aufruf der Prozedur als Wert des Parameters angegeben wurde. Beachten Sie, dass bei Angabe des Schlüsselworts OUTPUT der Wert des entsprechenden Parameters beim Aufruf der Prozedur nur über eine lokale Variable festgelegt werden kann. Alle Ausdrücke oder Konstanten, die für reguläre Parameter zulässig sind, sind nicht zulässig.

Das Schlüsselwort VARYING wird in Verbindung mit dem Parameter OUTPUT verwendet, der vom Typ CURSOR ist. Es bestimmt das Ausgabeparameter Es wird eine Ergebnismenge geben.

Das Schlüsselwort DEFAULT stellt den entsprechenden Wert dar Standardparameter. Daher müssen Sie beim Aufruf einer Prozedur den Wert des entsprechenden Parameters nicht explizit angeben.

Da der Server den Abfrageausführungsplan und den kompilierten Code zwischenspeichert, werden beim nächsten Aufruf der Prozedur die vorgefertigten Werte verwendet. In einigen Fällen ist es jedoch dennoch erforderlich, den Prozedurcode neu zu kompilieren. Durch die Angabe des Schlüsselworts RECOMPILE wird das System angewiesen, einen Ausführungsplan zu erstellen gespeicherte Prozedur jedes Mal, wenn sie anruft.

Der Parameter FOR REPLICATION ist erforderlich, wenn Daten repliziert und die erstellten Daten aktiviert werden gespeicherte Prozedur als Artikel zur Veröffentlichung.

Das Schlüsselwort ENCRYPTION weist den Server an, den Code zu verschlüsseln gespeicherte Prozedur, was Schutz vor der Verwendung proprietärer Algorithmen bieten kann, die die Arbeit implementieren gespeicherte Prozedur.

Das Schlüsselwort AS wird am Anfang des Hauptteils selbst platziert gespeicherte Prozedur, d.h. eine Reihe von SQL-Befehlen, mit deren Hilfe diese oder jene Aktion ausgeführt wird. Im Rumpf der Prozedur können nahezu alle SQL-Befehle verwendet, Transaktionen deklariert, Sperren gesetzt und weitere aufgerufen werden. Gespeicherte Prozeduren. Ausstieg aus gespeicherte Prozedur kann mit dem RETURN-Befehl erfolgen.

Entfernen einer gespeicherten Prozedur ausgeführt durch den Befehl:

DROP PROCEDURE (prozedurname) [,...n]

Ausführen einer gespeicherten Prozedur

Für eine gespeicherte Prozedur ausführen Der verwendete Befehl lautet:

[[ EXEC [ UTE] procedure_name [;number] [[@parameter_name=](value | @variable_name) |][,...n]

Wenn der Anruf gespeicherte Prozedur nicht der einzige Befehl im Paket ist, ist das Vorhandensein des EXECUTE-Befehls erforderlich. Darüber hinaus ist dieser Befehl erforderlich, um eine Prozedur aus dem Hauptteil einer anderen Prozedur oder eines anderen Triggers aufzurufen.

Die Verwendung des Schlüsselworts OUTPUT beim Aufruf einer Prozedur ist nur für Parameter zulässig, die wann deklariert wurden Erstellen einer Prozedur mit dem Schlüsselwort OUTPUT.

Wenn beim Aufruf einer Prozedur das Schlüsselwort DEFAULT für einen Parameter angegeben wird, wird es verwendet Standardwert. Selbstverständlich ist das angegebene Wort DEFAULT nur für die Parameter zulässig, für die es definiert ist Standardwert.

Die Syntax des EXECUTE-Befehls zeigt, dass Parameternamen beim Aufruf einer Prozedur weggelassen werden können. Allerdings muss der Benutzer in diesem Fall die Werte für die Parameter in der gleichen Reihenfolge angeben, in der sie wann aufgelistet wurden Erstellen einer Prozedur. Dem Parameter zuweisen Standardwert, Sie können es bei der Auflistung nicht einfach überspringen. Wenn Sie die Parameter weglassen möchten, für die es definiert ist Standardwert, reicht es aus, beim Aufruf die Parameternamen explizit anzugeben gespeicherte Prozedur. Darüber hinaus können Sie auf diese Weise Parameter und deren Werte in beliebiger Reihenfolge auflisten.

Beachten Sie, dass beim Aufruf einer Prozedur entweder Parameternamen mit Werten oder nur Werte ohne Parameternamen angegeben werden. Eine Kombination ist nicht gestattet.

Beispiel 12.1. Prozedur ohne Parameter. Entwickeln Sie ein Verfahren zur Ermittlung der Namen und Kosten der von Ivanov gekauften Waren.

CREATE PROC my_proc1 AS SELECT Product.Name, Product.Price*Transaction.Quantity AS Cost, Customer.Last Name FROM Customer INNER JOIN (Product INNER JOIN Transaction ON Product.ProductCode=Transaction.ProductCode) ON Customer.CustomerCode=Transaction.CustomerCode WHERE Kunde .Nachname='Ivanov' Beispiel 12.1. Verfahren zur Erlangung der Namen und Werte der von Ivanov gekauften Waren.

Für Zugriff auf das Verfahren Sie können die Befehle verwenden:

EXEC my_proc1 oder my_proc1

Die Prozedur gibt einen Datensatz zurück.

Beispiel 12.2. Prozedur ohne Parameter. Erstellen Sie ein Verfahren, um den Preis erstklassiger Waren um 10 % zu senken.

Für Zugriff auf das Verfahren Sie können die Befehle verwenden:

EXEC my_proc2 oder my_proc2

Die Prozedur gibt keine Daten zurück.

Beispiel 12.3. Prozedur mit Eingabeparameter. Erstellen Sie ein Verfahren, um die Namen und Preise der von einem bestimmten Kunden gekauften Artikel zu ermitteln.

CREATE PROC my_proc3 @k VARCHAR(20) AS SELECT Product.Name, Product.Price*Transaction.Quantity AS Cost, Customer.Last Name FROM Customer INNER JOIN (Product INNER JOIN Deal ON Product.ProductCode=Transaction.ProductCode) ON Customer. CustomerCode =Transaction.ClientCode WHERE Client.LastName=@k Beispiel 12.3. Ein Verfahren zum Ermitteln der Namen und Preise der von einem bestimmten Kunden gekauften Artikel.

Für Zugriff auf das Verfahren Sie können die Befehle verwenden:

EXEC my_proc3 „Ivanov“ oder my_proc3 @k="Ivanov"

Beispiel 12.4.. Erstellen Sie ein Verfahren, um den Preis eines Produkts eines bestimmten Typs entsprechend dem angegebenen Prozentsatz zu senken.

Für Zugriff auf das Verfahren Sie können die Befehle verwenden:

EXEC my_proc4 „Waffles“,0.05 oder EXEC my_proc4 @t="Waffles", @p=0.05

Beispiel 12.5. Verfahren mit Eingabeparametern und Standardwerte. Erstellen Sie ein Verfahren, um den Preis eines Produkts eines bestimmten Typs entsprechend dem angegebenen Prozentsatz zu senken.

CREATE PROC my_proc5 @t VARCHAR(20)=’Candy`, @p FLOAT=0.1 AS UPDATE Product SET Price=Price*(1-@p) WHERE Type=@t Beispiel 12.5. Verfahren mit Eingabeparametern und Standardwerten. Erstellen Sie ein Verfahren, um den Preis eines Produkts eines bestimmten Typs entsprechend dem angegebenen Prozentsatz zu senken.

Für Zugriff auf das Verfahren Sie können die Befehle verwenden:

EXEC my_proc5 „Waffles“,0.05 oder EXEC my_proc5 @t="Waffles", @p=0.05 oder EXEC my_proc5 @p=0.05

In diesem Fall wird der Preis für Süßigkeiten reduziert (der Typwert wird beim Aufruf der Prozedur nicht angegeben und standardmäßig verwendet).

Im letzteren Fall werden beide Parameter (sowohl Typ als auch Prozentsatz) beim Aufruf der Prozedur nicht angegeben; ihre Werte werden standardmäßig übernommen.

Beispiel 12.6. Verfahren mit Ein- und Ausgabeparametern. Erstellen Sie ein Verfahren zur Ermittlung der Gesamtkosten der in einem bestimmten Monat verkauften Waren.

CREATE PROC my_proc6 @m INT, @s FLOAT OUTPUT AS SELECT @s=Sum(Product.Price*Transaction.Quantity) FROM Product INNER JOIN Transaction ON Product.ProductCode=Transaction.ProductCode GROUP BY Month(Transaction.Date) HAVING Month( Transaction.Date)=@m Beispiel 12.6. Verfahren mit Ein- und Ausgabeparametern. Erstellen Sie ein Verfahren zur Ermittlung der Gesamtkosten der in einem bestimmten Monat verkauften Waren.

Für Zugriff auf das Verfahren Sie können die Befehle verwenden:

DECLARE @st FLOAT EXEC my_proc6 1,@st OUTPUT SELECT @st

Mit diesem Befehlsblock können Sie die Kosten der im Januar verkauften Waren ermitteln ( Eingabeparameter Monat wird als 1) angegeben.

Erstellen Sie ein Verfahren, um die Gesamtmenge der von dem Unternehmen gekauften Waren zu ermitteln, in dem ein bestimmter Mitarbeiter arbeitet.

Zunächst entwickeln wir ein Verfahren zur Ermittlung des Unternehmens, in dem der Mitarbeiter arbeitet.

Beispiel 12.7. Verwendung verschachtelte Prozeduren. Erstellen Sie ein Verfahren, um die Gesamtmenge der von dem Unternehmen gekauften Waren zu ermitteln, in dem ein bestimmter Mitarbeiter arbeitet.

Anschließend erstellen wir ein Verfahren, das die Gesamtmenge der von dem Unternehmen, an dem wir interessiert sind, gekauften Waren berechnet.

CREATE PROC my_proc8 @fam VARCHAR(20), @kol INT OUTPUT AS DECLARE @firm VARCHAR(20) EXEC my_proc7 @fam,@firm OUTPUT SELECT @kol=Sum(Transaction.Quantity) FROM Client INNER JOIN Transaction ON Client.ClientCode= Transaction.ClientCode GROUP BY Client.Firm HAVING Client.Company=@firm Beispiel 12.7. Erstellen Sie ein Verfahren, um die Gesamtmenge der von dem Unternehmen gekauften Waren zu ermitteln, in dem ein bestimmter Mitarbeiter arbeitet.

Die Prozedur wird mit dem Befehl aufgerufen:

DECLARE @k INT EXEC my_proc8 ‚Ivanov‘,@k OUTPUT SELECT @k