Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 (Deutsch) » SQL Anywhere Server - SQL-Referenzhandbuch » Verwendung von SQL » SQL-Anweisungen » SQL-Anweisungen (A-D)

 

CREATE PROCEDURE-Anweisung

Mit dieser Anweisung erstellen Sie eine neue benutzerdefinierte SQL-Prozedur in einer Datenbank. Hinweise zur Erstellung von Schnittstellen zu externen Prozeduren finden Sie unter CREATE PROCEDURE-Anweisung (externe Prozeduren). Hinweise zur Erstellung von Webdienstprozeduren finden Sie unter CREATE PROCEDURE-Anweisung (Webdienste).

Syntax
CREATE [ OR REPLACE | TEMPORARY ] PROCEDURE [ Eigentümer.]Prozedurname 
( [ Parameter, ... ] )
[ RESULT ( Ergebnisspalte, ... ) | NO RESULT SET ]
[ SQL SECURITY { INVOKER | DEFINER } ]
[ ON EXCEPTION RESUME ]
Zusammengesetzte_Anweisung | AT Standort_Zeichenfolge 
Parameter :
ParametermodusParametername Datentyp [ DEFAULT Ausdruck ]
| SQLCODE
| SQLSTATE
Parametermodus : IN 
| OUT 
| INOUT
Ergebnisspalte : Spaltenname Datentyp
Parameter
  • CREATE PROCEDURE   Sie können permanente oder temporäre (TEMPORARY) gespeicherte Prozeduren erstellen. PROC kann als Synonym für PROCEDURE verwendet werden.

    Parameternamen müssen den Regeln für andere Datenbankbezeichner, wie z.B. Spaltennamen, entsprechen. Es muss sich dabei um einen gültigen SQL-Datentyp handeln. Eine Liste der gültigen Datentypen finden Sie unter SQL-Datentypen.

    Parameter können eines der Schlüsselwörter IN, OUT oder INOUT als Präfix haben. Wenn Sie keinen dieser Werte angeben, sind die Parameter standardmäßig INOUT. Die Schlüsselwörter haben die folgenden Bedeutungen:

    • IN   Dieser Parameter ist ein Ausdruck, welcher der Prozedur einen Wert zur Verfügung stellt.

    • OUT   Dieser Parameter ist eine Variable, die von der Prozedur einen Wert erhalten kann.

    • INOUT   Dieser Parameter ist eine Variable, die einen Wert für die Prozedur bereitstellt, und die von der Prozedur einen neuen Wert erhalten kann.

    Wenn Prozeduren mit der CALL-Anweisung ausgeführt werden, müssen nicht alle Parameter angegeben werden. Wenn in der CREATE PROCEDURE-Anweisung ein Standardwert bereitgestellt wird, werden den fehlenden Parametern die Standardwerten zugeordnet. Falls in der CALL-Anweisung kein Argument angegeben und kein Standardwert gesetzt ist, wird ein Fehler ausgegeben.

    SQLSTATE und SQLCODE sind spezielle OUT-Parameter, die den SQLSTATE- oder SQLCODE-Wert ausgeben, wenn die Prozedur beendet wird. Die Spezialwerte SQLSTATE und SQLCODE können sofort geprüft werden, nachdem ein Prozeduraufruf abgeschlossen wurde, um den Rückgabestatus der Prozedur zu testen.

    Die Spezialwerte SQLSTATE und SQLCODE werden durch die nächste SQL-Anweisung geändert. Indem SQLSTATE oder SQLCODE als Prozedurargumente bereitgestellt werden, kann die Rückmeldung in einer Variablen gespeichert werden.

    Wenn Sie CREATE OR REPLACE PROCEDURE angeben, wird eine neue Prozedur erstellt oder eine bestehende Prozedur mit demselben Namen ersetzt. Diese Klausel ändert die Definition der Prozedur, lässt aber bestehende Berechtigungen unberührt. Sie können die OR REPLACE-Klausel nicht mit temporären Prozeduren verwenden. Außerdem wird ein Fehler zurückgegeben, wenn die zu ersetzende Prozedur bereits verwendet wird.Die Angabe von CREATE TEMPORARY PROCEDURE bedeutet, dass die gespeicherte Prozedur lediglich für die Verbindung sichtbar ist, die sie erstellt hat, und dass sie automatisch gelöscht wird, wenn die Verbindung beendet wird. Temporäre gespeicherte Prozeduren können auch explizit gelöscht werden. Sie können kein ALTER, GRANT oder REVOKE für sie ausführen, und im Gegensatz zu anderen gespeicherten Prozeduren werden temporäre gespeicherte Prozeduren nicht im Katalog oder Transaktionslog aufgezeichnet.

    Temporäre Prozeduren werden mit den Berechtigungen ihres Erstellers (aktueller Benutzer) oder eines angegebenen Eigentümers ausgeführt. Sie können unter folgenden Voraussetzungen einen Eigentümer für eine temporäre Prozedur festlegen:

    • Wenn die temporäre Prozedur in einer permanent gespeicherten Prozedur erstellt wird

    • Wenn der Eigentümer der temporären Prozedur mit dem der permanenten Prozedur identisch ist

    Um den Eigentümer einer temporären Prozedur zu löschen, müssen Sie erst die temporäre Prozedur löschen.

    Temporäre gespeicherte Prozeduren können erstellt und gelöscht werden, wenn sie mit einer schreibgeschützten Datenbank verbunden sind, und sie dürfen keine externen Prozeduren sein.

    Die folgende temporäre Prozedur z.B. löscht die Tabelle namens CustRank, falls sie existiert. In diesem Beispiel nimmt die Prozedur an, dass der Tabellenname eindeutig ist und vom Prozedurersteller ohne Angabe eines Tabelleneigentümers referenziert werden kann:

    CREATE TEMPORARY PROCEDURE drop_table( IN @TableName char(128) )
    BEGIN
        IF EXISTS  ( SELECT * FROM SYS.SYSTAB WHERE table_name = @TableName ) THEN
       EXECUTE IMMEDIATE 'DROP TABLE "' || @TableName || '"';
       MESSAGE 'Table "' || @TableName || '" dropped' to client;
        END IF;
    END;
    CALL drop_table( 'CustRank' );

  • RESULT-Klausel   Die RESULT-Klausel deklariert die Anzahl und den Typ der Spalten in der Ergebnismenge. Mit der Liste in Klammern nach dem Schlüsselwort RESULT werden die Namen und Typen der Ergebnisspalten festgelegt. Diese Angaben werden von Embedded SQL DESCRIBE oder von ODBC SQLDescribeCol zurückgegeben, wenn eine CALL-Anweisung beschrieben wird. Eine Liste der Datentypen finden Sie unter SQL-Datentypen.

    Weitere Informationen zum Zurückgeben von Ergebnismengen aus Prozeduren finden Sie unter Ergebnisse aus Prozeduren zurückgeben.

    Einige Prozeduren können mehr als eine Ergebnismenge mit unterschiedlicher Spaltenanzahl zurückgeben, je nachdem, wie sie ausgeführt werden. Die folgende Prozedur gibt beispielsweise unter bestimmten Bedingungen zwei Spalten und in anderen Fällen nur eine Spalte zurück.

    CREATE PROCEDURE names( IN formal char(1))
    BEGIN
       IF formal = 'n' THEN
          SELECT GivenName
          FROM Employees
       ELSE
          SELECT Surname, GivenName
          FROM Employees
       END IF
    END;

    Prozeduren mit variablen Ergebnismengen müssen ohne eine RESULT-Klausel oder in Transact-SQL geschrieben werden. Ihre Verwendung ist den folgenden Einschränkungen unterworfen:

    • Embedded SQL   Sie müssen den Prozeduraufruf mithilfe einer DESCRIBE-Anweisung beschreiben, nachdem der Cursor für die Ergebnismenge geöffnet wurde, aber bevor Zeilen zurückgegeben werden, damit die richtige Form der Ergebnismenge bezogen wird. Die Klausel CURSOR Cursorname in der DESCRIBE-Anweisung ist erforderlich.

    • ODBC, OLE DB, ADO.NET   Variable Ergebnismengenprozeduren können von Anwendungen verwendet werden, die diese Schnittstellen benutzen. Die richtige Beschreibung der Ergebnismengen wird vom Treiber oder Provider vorgenommen.

    • Open Client-Anwendungen   Variable Ergebnismengenprozeduren können von Open Client-Anwendungen verwendet werden.

    Wenn Ihre Prozedur nur eine Ergebnismenge zurückgibt, sollten Sie eine RESULT-Klausel verwenden. Das Vorhandensein dieser Klausel verhindert, dass ODBC- und Open Client-Anwendungen die Ergebnismenge noch einmal beschreiben, nachdem der Cursor geöffnet wurde.

    Um mehrere Ergebnismengen verarbeiten zu können, muss ODBC den aktuell ausgeführten Cursor beschreiben, und nicht die definierte Ergebnismenge der Prozedur. Deshalb bezeichnet ODBC die Spaltennamen nicht immer so, wie sie in der RESULT-Klausel der gespeicherten Prozedur definiert sind. Um dieses Problem zu vermeiden, verwenden Sie Spaltenaliasnamen in der SELECT-Anweisung, die die Ergebnismenge erzeugt.

  • NO RESULT SET-Klausel   Deklariert, dass von dieser Prozedur keine Ergebnismenge zurückgegeben wird. Das ist nützlich, wenn eine externe Umgebung wissen muss, dass eine Prozedur keine Ergebnismenge zurückgibt.

  • SQL SECURITY-Klausel   Die SQL SECURITY-Klausel legt fest, ob die Prozedur als INVOKER (der Benutzer, der die Prozedur aufruft) oder als DEFINER (der Benutzer, dem die Prozedur gehört) aufgrufen wird. Standardwert ist DEFINER.

    Wenn SQL SECURITY INVOKER angegeben ist, wird mehr Speicher verwendet, weil für jeden Benutzer, der die Prozedur aufruft, ein Vermerk erfolgen muss. Außerdem erfolgt bei SQL SECURITY INVOKER auch eine Namensauflösung als Aufrufer. Sie sollten daher mit Umsicht vorgehen und alle Objektnamen (Tabellen, Prozeduren, etc.) mit ihrem richtigen Eigentümer qualifizieren. Beispiel: Der Benutzer user1 erstellt die folgende Prozedur:

    CREATE PROCEDURE user1.myProcedure()
       RESULT( columnA INT )
       SQL SECURITY INVOKER
       BEGIN
         SELECT columnA FROM table1;
       END;

    Wenn der Benutzer user2 versucht, diese Prozedur auszuführen und eine Tabelle user2.table1 nicht existiert, kommt es zu einem Tabellensuchfehler. Wenn eine Tabelle user2.table1 existiert, wird diese Tabelle anstelle der beabsichtigten user1.table1 verwendet. Um dies zu verhindern, qualifizieren Sie die Tabellenreferenz in der Anweisung (user1.table1 anstelle von table1).

  • ON EXCEPTION RESUME-Klausel   Diese Klausel aktiviert eine Transact-SQL-ähnliche Fehlerbehandlung, die innerhalb einer Prozedur mit Watcom SQL-Syntax verwendet wird.

    Wenn Sie ON EXCEPTION RESUME verwenden, übernimmt die Prozedur eine Aktion, die von der Einstellung der Option on_tsql_error abhängt. Wenn on_tsql_error auf Conditional (Standardwert) gesetzt ist, wird die Ausführung fortgesetzt, falls die nächste Anweisung den Fehler behebt. Andernfalls wird die Ausführung beendet.

    Anweisungen für die Fehlerbehandlung enthalten folgende Elemente:

    • IF
    • SELECT @variable =
    • CASE
    • LOOP
    • LEAVE
    • CONTINUE
    • CALL
    • EXECUTE
    • SIGNAL
    • RESIGNAL
    • DECLARE
    • SET VARIABLE

    Einen expliziten Fehlerbehandlungscode sollten Sie nicht mit der Klausel ON EXCEPTION RESUME verwenden.

    Weitere Hinweise finden Sie unter on_tsql_error-Option [Kompatibilität].

  • AT Standort_Zeichenfolge-Klausel   Erstellt eine gespeicherte Proxy-Prozedur in der aktuellen Datenbank für eine entfernte Prozedur, die durch Speicherort angegeben ist. Die AT-Klausel unterstützt das Semikolon (;) als Feldbegrenzer in Speicherort. Wenn kein Semikolon vorhanden ist, gilt der Punkt als Feldbegrenzer. Damit können Dateinamen oder Erweiterungen in den Datenbank- und Eigentümerfeldern verwendet werden.

    Entfernte Prozeduren akzeptieren Eingabeparameter mit einer Länge von bis zu 254 Byte und geben bis zu 254 Zeichen in Ausgabevariablen zurück.

    Wenn die entfernte Prozedur eine Ergebnismenge zurückgeben kann, selbst wenn sie dies nicht in allen Fällen tut, muss die lokale Prozedurdefinition eine RESULT-Klausel enthalten.

    Informationen zu Fremdservern finden Sie unter CREATE SERVER-Anweisung. Hinweise zum Verwenden von entfernten Prozeduren finden Sie unter Entfernte Prozeduraufrufe (RPCs) verwenden.

Bemerkungen

Die CREATE PROCEDURE-Anweisung erstellt eine Prozedur in der Datenbank. Benutzer mit DBA-Berechtigung können Prozeduren für andere Benutzer erstellen, indem Sie einen Eigentümer angeben. Eine Prozedur wird mit einer CALL-Anweisung aufgerufen.

Wenn eine gespeicherte Prozedur eine Ergebnismenge zurückgibt, kann sie nicht zusätzlich Ausgabeparameter setzen oder einen Rückgabewert zurückgeben.

Bei der Referenzierung einer temporären Tabelle durch mehrere Prozeduren kann ein Problem entstehen, wenn die Definitionen der temporären Tabelle nicht konsistent sind und Anweisungen, die die Tabelle referenzieren, im Cache abgelegt sind. Weitere Hinweise finden Sie unter Temporäre Tabellen innerhalb von Prozeduren referenzieren.

Berechtigungen

Sie müssen über RESOURCE-Datenbankberechtigungen verfügen, es sei denn, Sie erstellen eine temporäre Prozedur.

Sie müssen über DBA-Berechtigungen für externe Prozeduren oder zum Erstellen einer Prozedur für einen anderen Benutzer verfügen.

Nebenwirkungen

Automatisches Festschreiben (Autocommit).

Siehe auch
Standards und Kompatibilität
  • SQL/2003   Persistent Stored Module-Funktion. Die Syntaxerweiterungen für Java-Ergebnismengen werden wie in der optionalen J621-Funktion angegeben verwendet.

Beispiele

Die folgende Prozedur verwendet eine CASE-Anweisung, um die Ergebnisse einer Abfrage zu klassifizieren.

CREATE PROCEDURE ProductType (IN product_ID INT, OUT type CHAR(10))
BEGIN
   DECLARE prod_name CHAR(20);
   SELECT name INTO prod_name FROM Products
   WHERE ID = product_ID;
   CASE prod_name
   WHEN 'Tee Shirt' THEN
      SET type = 'Shirt'
   WHEN 'Sweatshirt' THEN
      SET type = 'Shirt'
   WHEN 'Baseball Cap' THEN
      SET type = 'Hat'
   WHEN 'Visor' THEN
      SET type = 'Hat'
   WHEN 'Shorts' THEN
      SET type = 'Shorts'
   ELSE
      SET type = 'UNKNOWN'
   END CASE;
END;

Im folgenden Beispiel wird die Prozedur ProductType ersetzt, die im vorherigen Beispiel erstellt wurde. Nach dem Ersetzen der Prozedur werden die Parameter für Tee Shirt und Sweatshirt aktualisiert:

CREATE OR REPLACE PROCEDURE ProductType (IN product_ID INT, OUT type CHAR(10))
BEGIN
   DECLARE prod_name CHAR(20);
   SELECT name INTO prod_name FROM Products
   WHERE ID = product_ID;
   CASE prod_name
   WHEN 'Tee Shirt' THEN
      SET type = 'T Shirt'
   WHEN 'Sweatshirt' THEN
      SET type = 'Long Sleeve Shirt'
   WHEN 'Baseball Cap' THEN
      SET type = 'Hat'
   WHEN 'Visor' THEN
      SET type = 'Hat'
   WHEN 'Shorts' THEN
      SET type = 'Shorts'
   ELSE
      SET type = 'UNKNOWN'
   END CASE;
END;

Die folgende Prozedur verwendet einen Cursor und eine Schleife über die Zeilen des Cursors, um einen Einzelwert zurückzugeben.

CREATE PROCEDURE TopCustomer (OUT TopCompany CHAR(35), OUT TopValue INT)
BEGIN
   DECLARE err_notfound EXCEPTION
   FOR SQLSTATE '02000';
   DECLARE curThisCust CURSOR FOR
      SELECT CompanyName,
          CAST(SUM(SalesOrderItems.Quantity *
          Products.UnitPrice) AS INTEGER) VALUE
      FROM Customers
      LEFT OUTER JOIN SalesOrders
      LEFT OUTER JOIN SalesOrderItems
      LEFT OUTER JOIN Products
      GROUP BY CompanyName;
   DECLARE ThisValue INT;
   DECLARE ThisCompany CHAR(35);
   SET TopValue = 0;
   OPEN curThisCust;
   CustomerLoop:
   LOOP
      FETCH NEXT curThisCust
      INTO ThisCompany, ThisValue;
      IF SQLSTATE = err_notfound THEN
         LEAVE CustomerLoop;
      END IF;
      IF ThisValue > TopValue THEN
         SET TopValue = ThisValue;
         SET TopCompany = ThisCompany;
         END IF;
   END LOOP CustomerLoop;
   CLOSE curThisCust;
END;