Die Stored Procedures

In diesem Video werden wir eine Stored Procedure erzeugen, die uns den BMI-Wert zu einer Körpergröße und einem Körpergewicht ausgibt. Dieses Programm hatten wir bereits als anonymes PL/SQL-Proramm geschrieben. Deshalb werde ich jetzt ganz einfach das bereits geschriebene, und zwar greife ich jetzt etwas zurück, auf kap03_bmi_case, und zwar öffnen wir diese Datei kap03_bmi_case und werden die unter einem anderen Namen dann abspeichern. Also schreibe ich jetzt: edit kap03_bmi_case, öffne diese Datei, und speichere sie mal unter einem anderen Namen ab, und zwar unter kap04_bmi_berechnen. Gleichzeitig werde ich das Programm auch noch ein wenig verkleinern, um uns nicht in unwichtigen Details zu verlieren. Beispielsweise werde ich für die Ausgabe des BMI-Wertes nur noch ein einzelnes SEARCHED CASE STATEMENT stehen lassen, das soll für dieses Beispiel dann ausreichen. Also, an dieser Stelle füge ich dann einen Konkatenations-Operator hinzu, und einen Doppelpunkt, und das werde ich jetzt alles entfernen, das brauchen wir nicht mehr, und ich lasse stehen: CASE WHEN bmi > BMI_MAX und dann soll hinter dieser Abfrage soll dann der Text "Uebergewicht" direkt ausgegeben werden, oder zurückgeliefert werden. Deswegen kopiere ich "Uebergewicht", und füge es hier oben ein, genauso mache ich das mit dem Untergewicht, das soll dann auch direkt zurückgeliefert werden, und im ELSE-Zweig nehme ich dann das Normalgewicht, und setze es dort ein. So. Den Rest kann ich entfernen, und schon bin ich damit fertig. Wir beginnen also mit der Programmierung unserer Stored Procedure indem wir den Kopf des SQL-Skriptes abändern. Hier werden wir jetzt folgendes schreiben: Ich entferne das Schlüsselwort DECLARE, denn das wird ja nur eingesetzt, wenn das PL/SQL-Programm anonym ist, und stattdessen schreib ich jetzt hin: CREATE PROCEDURE Jetzt muss ich der Procedure einen Namen geben, bmi Unterstrich berechnen denn unter diesem Namen wird die Procedure dann in der Datenbank abgespeichert werden. Hinter dem Namen füge ich das Schlüsselwort AS hin, man kann an der Stelle auch statt AS auch IS schreiben, das ist historisch bedingt, ich lasse es jetzt hier bei AS Und schon sind wir fertig, und haben aus dem anonymen PL/SQL-Programm ein benanntes PL/SQL-Programm erstellt, und zwar eine Procedure. Ich werde nun noch zwei weitere Schlüsselwörter hinzufügen, nämlich hier oben: hinter CREATE schreibe ich noch OR und REPLACE. Beim ersten Erzeugen der Procedure hätte das Schlüsselwort CREATE ausgereicht. da wir das Skript aber öfter aufrufen möchten, eignet sich die Variante CREATE OR REPLACE besser, denn hierdurch wird eine gleichbenannte Procedure, so sie bereits vorhanden ist, einfach ersetzt. Bei einer Procedure haben Sie die Möglichkeit, Übergabeparameter zu definieren. Und zwar geschieht das hier, hinter dem Namen hier können Sie also eine öffnende und eine schließende Klammer hinzufügen, und hier drin, hier können dann Übergabeparameter definiert werden. Ich werde nun zwei Übergabeparameter definieren, über die wir die Körpergröße und das Körpergewicht festlegen können, beziehungsweise der Procedure übergeben können. Da wir der Procedure die Körpergröße und das Körpergewicht übergeben möchten, nenne ich die beiden Übergabeparameter groesse und gewicht. Ich schreibe also: groesse und gewicht hier hinein, und muss nun noch dafür sorgen, dass hier der richtige Datentyp angegeben ist. Als Datentyp setze ich NUMBER für Größe und NUMBER für das Gewicht. Beachten Sie, dass ich bei diesen beiden Datentypen der Übergabeparameter darauf verzichte, eine Vorkomma- oder Nachkommastelle anzugeben. Genauso wäre es bei einer Zeichenkette. Auch dort würde ich die Anzahl der Zeichen gar nicht angeben. Die Deklaration der Datentypen ist also anders als hier im Deklarationsabschnitt. Ich habe nun die beiden Übergabeparameter definiert, deswegen brauche ich jetzt auch diese beiden Variablen nicht mehr, die ich im Deklarationsabschnitt definiert hatte, und bin mit meiner Stored Procedure fertig. Die speichere ich jetzt ab. schließe Notepad++, und starte das SQL-Skript, damit die Procedure in der Datenbank abgespeichert wird. Also, START kap04 Unterstrich bmi Unterstrich berechnen Wie Sie sehen, wird mir nun der enthaltene PL/SQL-Block nicht mehr unmittelbar durch die PL/SQL-Engine ausgeführt. Stattdessen wird der PL/SQL-Block als Stored Procedure in der Oracle-Database unter dem Namen bmi_berechnen abgelegt. Um die Stored Procedure anschließend auszuführen, benötige ich ein weiteres Schlüsselwort, und das nennt sich: EXECUTE Also, so. Man kann EXECUTE auch abkürzen mit EXEC Ich wähle jetzt die lange Variante und schreibe dahinter den Namen der Procedure bmi berechnen und, jetzt muss ich auch noch die Übergabeparameter setzen und zwar schreibe ich dort die Zahl 1,78 und 69,5 hinein. Wie Sie sehen, gibt die Stored Procedure nun den BMI für die von mir vorgegebenen Werte aus. Das SQL-Skript könnten wir jetzt löschen, da sich die Oracle-Database das Programm ja bereits gemerkt hat. Der darin enthaltene Quelltext ist auch noch vorhanden. Sie können ihn mit folgender Anweisung wieder hervorholen: Und zwar können Sie schreiben: selecttext from dba Unterstrich source selecttext from where name ist gleich und hier muss dann der Name rein bmi Unterstrich berechnen Semikolon. Und dann sehen Sie das Programm, das wir vorhin geschrieben haben. In diesem Video habe ich Ihnen gezeigt, wie Sie eine Stored Procedure programmieren. Dabei haben wir die Stored Procedure so entworfen, dass sie zwei Übergabeparameter entgegen nimmt.