Struktur von SQL-Prozeduren

SQL-Prozeduren bestehen aus mehreren Logikabschnitten und die SQL-Prozedurentwicklung erfordert die Implementierung dieser Abschnitte in einem strukturierten Format.

Das Format ist recht einfach und einfach zu befolgen und soll das Design und die Semantik von Routinen vereinfachen.

Der Kern einer SQL-Prozedur ist eine zusammengesetzte Anweisung. Zusammengesetzte Anweisungen werden durch die Schlüsselwörter BEGIN und END begrenzt. Diese Anweisungen können ATOMIC oder NOT ATOMIC sein. Standardmäßig sind sie NICHT ATOMAR.

In einer zusammengesetzten Anweisung können mehrere optionale SQL PL-Objekte deklariert und mit SQL-Anweisungen referenziert werden. Das folgende Diagramm veranschaulicht das strukturierte Format einer zusammengesetzten Anweisung in SQL-Prozeduren:

  label:  BEGIN 
    Variable declarations
    Condition declarations
    Cursor declarations
    Condition handler declarations
    Assignment, flow of control, SQL statements and other compound statements
  END label

Das Diagramm zeigt, dass SQL-Prozeduren aus einer oder mehreren optional atomaren zusammengesetzten Anweisungen (oder Blöcken) bestehen können und dass diese Blöcke in einer einzigen SQL-Prozedur verschachtelt oder seriell eingeführt werden können. In jedem dieser atomaren Blöcke gibt es eine vorgeschriebene Reihenfolge für die optionalen Variablen-, Bedingungs-und Handlerdeklarationen. Diese müssen der Einführung von prozeduraler Logik vorausgehen, die mit SQL-Steueranweisungen und anderen SQL-Anweisungen und Cursordeklarationen implementiert wird. Cursor können überall mit der Gruppe von SQL-Anweisungen deklariert werden, die im Hauptteil der SQL-Prozedur enthalten sind.

Zur Verdeutlichung des Steuerungsflusses können atomare Blöcke von SQL-Prozeduren wie viele der darin enthaltenen SQL-Steueranweisungen beschriftet werden. Dies erleichtert die Genauigkeit bei der Referenzierung von Variablen und der Übertragung von Steueranweisungsreferenzen.

Das folgende Beispiel zeigt eine SQL-Prozedur, die jedes der oben aufgelisteten Elemente veranschaulicht:
   CREATE PROCEDURE DEL_INV_FOR_PROD (IN prod INT, OUT err_buffer VARCHAR(128))
   LANGUAGE SQL
   DYNAMIC RESULT SETS 1
   BEGIN
   
     DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
     DECLARE SQLCODE integer DEFAULT 0;
     DECLARE NO_TABLE CONDITION FOR SQLSTATE '42704';
     DECLARE cur1 CURSOR WITH RETURN TO CALLER  
                     FOR SELECT * FROM Inv;

     A: BEGIN ATOMIC
          DECLARE EXIT HANDLER FOR NO_TABLE
            BEGIN
               SET ERR_BUFFER='Table Inv does not exist';
            END;

          SET err_buffer = '';

          IF (prod < 200)
            DELETE FROM Inv WHERE product = prod;
          ELSE IF (prod < 400)
            UPDATE Inv SET quantity = 0 WHERE product = prod;
          ELSE
            UPDATE Inv SET quantity = NULL WHERE product = prod; 
          END IF;
        END A;

      B:  OPEN cur1;

   END

NOT ATOMIC-Compound-Anweisungen in SQL-Prozeduren

Das vorherige Beispiel veranschaulicht eine Compound-Anweisung NOT ATOMIC und ist der Standardtyp, der in SQL-Prozeduren verwendet wird. Wenn innerhalb der zusammengesetzten Anweisung eine nicht behandelte Fehlerbedingung auftritt, werden alle Arbeiten, die vor dem Fehler abgeschlossen wurden, nicht rückgängig gemacht, aber auch nicht festgeschrieben. Die Gruppe von Anweisungen kann nur zurückgesetzt werden, wenn die Arbeitseinheit explizit mit ROLLBACK oder ROLLBACK TO SAVEPOINT-Anweisungen zurückgesetzt wird. Sie können die Anweisung COMMIT auch verwenden, um erfolgreiche Anweisungen festzuschreiben, wenn dies sinnvoll ist.

Das folgende Beispiel zeigt eine SQL-Prozedur mit einer Compound-Anweisung NOT ATOMIC:
  CREATE PROCEDURE not_atomic_proc ()   
  LANGUAGE SQL   
  SPECIFIC not_atomic_proc
   nap:  BEGIN NOT ATOMIC            

   INSERT INTO c1_sched (class_code, day)
     VALUES ('R11:TAA', 1);           
  
   SIGNAL SQLSTATE '70000';   
   
   INSERT INTO c1_sched (class_code, day)               
     VALUES ('R22:TBB', 1);    

  END nap

Wenn die Anweisung SIGNAL ausgeführt wird, löst sie explizit einen Fehler aus, der nicht behandelt wird. Die Prozedur kehrt unmittelbar danach zurück. Nachdem die Prozedur zurückgegeben wurde, obwohl ein Fehler aufgetreten ist, wurde die erste Anweisung INSERT erfolgreich ausgeführt und eine Zeile in die Tabelle c1_sched eingefügt. Die Prozedur hat die Zeileneinfügung weder festgeschrieben noch rückgängig gemacht, und dies bleibt für die vollständige Arbeitseinheit, in der die SQL-Prozedur aufgerufen wurde, zu tun.

ATOMIC-Compound-Anweisungen in SQL-Prozeduren

Wie der Name vermuten lässt, können ATOMIC-Compound-Anweisungen als singuläres Ganzes betrachtet werden. Wenn darin nicht behandelte Fehlerbedingungen auftreten, werden alle Anweisungen, die bis zu diesem Punkt ausgeführt wurden, ebenfalls als fehlgeschlagen betrachtet und daher rückgängig gemacht.

Atomare zusammengesetzte Anweisungen können nicht in anderen ATOMIC-Compound-Anweisungen verschachtelt werden.

Sie können die Anweisung SAVEPOINT, die Anweisung COMMIT oder die Anweisung ROLLBACK nicht innerhalb einer ATOMIC-Compound-Anweisung verwenden. Diese werden nur in nicht atomaren Compound-Anweisungen in SQL-Prozeduren unterstützt.

Das folgende Beispiel zeigt eine SQL-Prozedur mit einer ATOMIC-Compound-Anweisung:
  CREATE PROCEDURE atomic_proc ()
  LANGUAGE SQL  
  SPECIFIC atomic_proc  
 
  ap:  BEGIN ATOMIC            

     INSERT INTO c1_sched (class_code, day)     
       VALUES ('R33:TCC', 1);           
 
     SIGNAL SQLSTATE '70000';       
   
     INSERT INTO c1_sched (class_code, day)               
       VALUES ('R44:TDD', 1);    

  END ap

Wenn die Anweisung SIGNAL ausgeführt wird, löst sie explizit einen Fehler aus, der nicht behandelt wird. Die Prozedur kehrt unmittelbar danach zurück. Die erste Anweisung INSERT wird zurückgesetzt, obwohl die erfolgreiche Ausführung zu einer Tabelle ohne eingefügte Zeilen für diese Prozedur führt.

Bezeichnungen und zusammengesetzte Anweisungen für SQL-Prozeduren

Bezeichnungen können optional verwendet werden, um jede ausführbare Anweisung in einer SQL-Prozedur zu benennen, einschließlich Compound-Anweisungen und Schleifen. Durch Verweise auf Bezeichnungen in anderen Anweisungen können Sie den Ausführungsablauf zwingen, aus einer zusammengesetzten Anweisung oder Schleife zu springen oder zusätzlich zum Anfang einer zusammengesetzten Anweisung oder Schleife zu springen. Die Anweisungen GOTO, ITERATE und LEAVE können auf Bezeichnungen verweisen.

Optional können Sie einen entsprechenden Kennsatz für das Ende einer zusammengesetzten Anweisung angeben. Wenn ein Endkennsatz angegeben wird, muss er mit dem am Anfang verwendeten Kennsatz identisch sein.

Jeder Kennsatz muss im Hauptteil einer SQL-Prozedur eindeutig sein.

Bezeichnungen können auch verwendet werden, um Mehrdeutigkeit zu vermeiden, wenn eine Variable mit demselben Namen in mehreren zusammengesetzten Anweisungen deklariert wurde, wenn die gespeicherte Prozedur verwendet wird. Eine Bezeichnung kann verwendet werden, um den Namen einer SQL-Variablen zu qualifizieren.