Eine Stored Procedure ist ein Softwaremodul, das von einem Client, einer anderen Prozedur, Funktion, ausführbaren Block oder Trigger aufgerufen werden kann.Gespeicherte Prozeduren, gespeicherte Funktionen, ausführbare Blöcke und Trigger werden in prozeduralem SQL (PSQL) geschrieben.Die meisten SQL-Anweisungen sind auch in PSQL verfügbar, manchmal mit einigen Einschränkungen oder Erweiterungen, bemerkenswerte Einschränkungen sind DDL- und Transaktionssteuerungsanweisungen.

Gespeicherte Prozeduren können viele Eingabe- und Ausgabeparameter haben.

`CREATE PROCEDURE`

Verwendet für

Erstellen einer neuen gespeicherten Prozedur

Verfügbar in

DSQL, ESQL

Syntax

CREATE PROCEDURE procname [ ( [ <in_params> ] ) ]
  [RETURNS (<out_params>)]
  {<psql_procedure> | <external-module-body>}

<in_params> ::= <inparam> [, <inparam> ...]

<inparam> ::= <param_decl> [{= | DEFAULT} <value>]

<out_params> ::= <outparam> [, <outparam> ...]

<outparam> ::= <param_decl>

<value> ::= {<literal> | NULL | <context_var>}

<param_decl> ::= paramname <domain_or_non_array_type> [NOT NULL]
  [COLLATE collation]

<type> ::=
    <datatype>
  | [TYPE OF] domain
  | TYPE OF COLUMN rel.col

<domain_or_non_array_type> ::=
  !! Siehe auch Syntax für Skalardatentypen !!

<psql_procedure> ::=
  [SQL SECURITY {INVOKER | DEFINER}]
  <psql-module-body>

<psql-module-body> ::=
  !! Siehe auch Syntax des Modulrumpfes !!

<external-module-body> ::=
  !! Siehe auch Syntax des Modulrumpfes !!

Table 1. `CREATE PROCEDURE`-Anweisungsparameter
Parameter	Beschreibung
procname	Name der gespeicherten Prozedur.Die maximale Länge beträgt 63 Zeichen.Muss unter allen Tabellen-, Ansichts- und Prozedurnamen in der Datenbank eindeutig sein
inparam	Beschreibung der Eingabeparameter
outparam	Beschreibung der Ausgangsparameter
literal	Ein Literalwert, der mit dem Datentyp des Parameters zuweisungskompatibel ist
context_var	Jede Kontextvariable, deren Typ mit dem Datentyp des Parameters kompatibel ist
paramname	Der Name eines Eingabe- oder Ausgabeparameters der Prozedur.Er kann aus bis zu 63 Zeichen bestehen.Der Name des Parameters muss unter den Eingabe- und Ausgabeparametern der Prozedur und ihrer lokalen Variablen eindeutig sein
collation	Sortierreihenfolge

Die Anweisung CREATE PROCEDURE erstellt eine neue gespeicherte Prozedur.Der Name der Prozedur muss unter den Namen aller gespeicherten Prozeduren, Tabellen und Ansichten in der Datenbank eindeutig sein.

CREATE PROCEDURE ist eine zusammengesetzte Anweisung, bestehend aus einem Header und einem Body.Der Header gibt den Namen der Prozedur an und deklariert Eingabeparameter und gegebenenfalls Ausgabeparameter, die von der Prozedur zurückgegeben werden sollen.

Der Prozedurrumpf besteht aus Deklarationen für alle lokalen Variablen und benannten Cursors, die von der Prozedur verwendet werden, gefolgt von einer oder mehreren Anweisungen oder Anweisungsblöcken, die alle in einem äußeren Block eingeschlossen sind, der mit dem Schlüsselwort BEGIN beginnt und mit . endet das Schlüsselwort END.Deklarationen und eingebettete Anweisungen werden mit Semikolons (‘;’) abgeschlossen.

docnext count = 17

Statement-Terminatoren

Einige Editoren für SQL-Anweisungen – insbesondere das Dienstprogramm isql, das mit Firebird geliefert wird, und möglicherweise einige Editoren von Drittanbietern – verwenden eine interne Konvention, die erfordert, dass alle Anweisungen mit einem Semikolon abgeschlossen werden.Dies führt beim Codieren in diesen Umgebungen zu einem Konflikt mit der PSQL-Syntax.Wenn Sie dieses Problem und seine Lösung nicht kennen, lesen Sie bitte die Details im PSQL-Kapitel im Abschnitt Umschalten des Terminators in isql.

Parameter

Jeder Parameter hat einen Datentyp.Der NOT NULL-Constraint kann auch für jeden Parameter angegeben werden, um zu verhindern, dass NULL übergeben oder ihm zugewiesen wird.

Eine Kollatierungssequenz kann für String-Typ-Parameter mit der COLLATE-Klausel angegeben werden.

Eingabeparameter: Eingabeparameter werden als Liste in Klammern nach dem Namen der Funktion angezeigt.Sie werden als Wert an die Prozedur übergeben, sodass Änderungen innerhalb der Prozedur keine Auswirkungen auf die Parameter im Aufrufer haben.Eingabeparameter können Standardwerte haben.Parameter mit angegebenen Standardwerten müssen am Ende der Parameterliste hinzugefügt werden.
Ausgabeparameter: Die optionale RETURNS-Klausel dient zum Angeben einer in Klammern gesetzten Liste von Ausgabeparametern für die gespeicherte Prozedur.

SQL-Sicherheit

Die SQL SECURITY-Klausel gibt den Sicherheitskontext zum Ausführen anderer Routinen oder zum Einfügen in andere Tabellen an.Wenn SQL-Sicherheit nicht angegeben ist, wird der Standardwert der Datenbank zur Laufzeit angewendet.

Die SQL SECURITY-Klausel kann nur für PSQL-Prozeduren angegeben werden und gilt nicht für Prozeduren, die in einem Paket definiert sind.

Siehe auch SQL-Sicherheit im Kapitel Sicherheit.

Variablen-, Cursor- und Sub-Routine-Deklarationen

Der optionale Deklarationsabschnitt, der sich am Anfang des Hauptteils der Prozedurdefinition befindet, definiert Variablen (einschließlich Cursors) und Unterroutinen lokal für die Prozedur.Lokale Variablendeklarationen folgen den gleichen Regeln wie Parameter bezüglich der Angabe des Datentyps.Weitere Informationen finden Sie im PSQL-Kapitel für DECLARE VARIABLE, ` DECLARE CURSOR`, DECLARE FUNCTION und DECLARE PROCEDURE.

Externe UDR-Prozeduren

Eine gespeicherte Prozedur kann sich auch in einem externen Modul befinden.In diesem Fall spezifiziert CREATE PROCEDURE anstelle eines Prozedurrumpfs die Position der Prozedur im externen Modul mit der EXTERNAL-Klausel.Die optionale NAME-Klausel spezifiziert den Namen des externen Moduls, den Namen der Prozedur innerhalb des Moduls und – optional – benutzerdefinierte Informationen.Die erforderliche ENGINE-Klausel gibt den Namen der UDR-Engine an, die die Kommunikation zwischen Firebird und dem externen Modul handhabt.Die optionale AS-Klausel akzeptiert ein String-Literal “body”, das von der Engine oder dem Modul für verschiedene Zwecke verwendet werden kann.

Wer kann ein Verfahren erstellen

Die CREATE PROCEDURE-Anweisung kann ausgeführt werden durch:

Administratoren
Benutzer mit dem Privileg CREATE PROCEDURE

Der Benutzer, der die Anweisung CREATE PROCEDURE ausführt, wird Eigentümer der Tabelle.

Beispiele

Erstellen einer gespeicherten Prozedur, die einen Datensatz in die BREED-Tabelle einfügt und den Code des eingefügten Datensatzes zurückgibt:

CREATE PROCEDURE ADD_BREED (
  NAME D_BREEDNAME, /* Domain attributes are inherited */
  NAME_EN TYPE OF D_BREEDNAME, /* Only the domain type is inherited */
  SHORTNAME TYPE OF COLUMN BREED.SHORTNAME,
    /* The table column type is inherited */
  REMARK VARCHAR(120) CHARACTER SET WIN1251 COLLATE PXW_CYRL,
  CODE_ANIMAL INT NOT NULL DEFAULT 1
)
RETURNS (
  CODE_BREED INT
)
AS
BEGIN
  INSERT INTO BREED (
    CODE_ANIMAL, NAME, NAME_EN, SHORTNAME, REMARK)
  VALUES (
    :CODE_ANIMAL, :NAME, :NAME_EN, :SHORTNAME, :REMARK)
  RETURNING CODE_BREED INTO CODE_BREED;
END

Erstellen einer auswählbaren gespeicherten Prozedur, die Daten für Adressetiketten generiert (aus employee.fdb):

CREATE PROCEDURE mail_label (cust_no INTEGER)
RETURNS (line1 CHAR(40), line2 CHAR(40), line3 CHAR(40),
         line4 CHAR(40), line5 CHAR(40), line6 CHAR(40))
AS
  DECLARE VARIABLE customer VARCHAR(25);
  DECLARE VARIABLE first_name VARCHAR(15);
  DECLARE VARIABLE last_name VARCHAR(20);
  DECLARE VARIABLE addr1 VARCHAR(30);
  DECLARE VARIABLE addr2 VARCHAR(30);
  DECLARE VARIABLE city VARCHAR(25);
  DECLARE VARIABLE state VARCHAR(15);
  DECLARE VARIABLE country VARCHAR(15);
  DECLARE VARIABLE postcode VARCHAR(12);
  DECLARE VARIABLE cnt INTEGER;
BEGIN
  line1 = '';
  line2 = '';
  line3 = '';
  line4 = '';
  line5 = '';
  line6 = '';

  SELECT customer, contact_first, contact_last, address_line1,
    address_line2, city, state_province, country, postal_code
  FROM CUSTOMER
  WHERE cust_no = :cust_no
  INTO :customer, :first_name, :last_name, :addr1, :addr2,
    :city, :state, :country, :postcode;

  IF (customer IS NOT NULL) THEN
    line1 = customer;
  IF (first_name IS NOT NULL) THEN
    line2 = first_name || ' ' || last_name;
  ELSE
    line2 = last_name;
  IF (addr1 IS NOT NULL) THEN
    line3 = addr1;
  IF (addr2 IS NOT NULL) THEN
    line4 = addr2;

  IF (country = 'USA') THEN
  BEGIN
    IF (city IS NOT NULL) THEN
  	  line5 = city || ', ' || state || '  ' || postcode;
  	ELSE
      line5 = state || '  ' || postcode;
  END
  ELSE
  BEGIN
    IF (city IS NOT NULL) THEN
  	  line5 = city || ', ' || state;
  	ELSE
      line5 = state;
    line6 = country || '    ' || postcode;
  END

  SUSPEND; -- die Anweisung, die eine Ausgabezeile an den Puffer sendet
           -- und die Prozedur "selektierbar" macht
END

With DEFINER set for procedure p, user US needs only the EXECUTE privilege on p.If it were set for INVOKER, either the user or the procedure would also need the INSERT privilege on table t.

set term ^;
create procedure p (i integer) SQL SECURITY DEFINER
as
begin
  insert into t values (:i);
end^
set term ;^

grant execute on procedure p to user us;
commit;

connect 'localhost:/tmp/17.fdb' user us password 'pas';
execute procedure p(1);

Siehe auch

[fblangref40-ddl-proc-crtoralter-de], [fblangref40-ddl-proc-alter-de], [fblangref40-ddl-proc-recreate-de], [fblangref40-ddl-proc-drop-de]

`ALTER PROCEDURE`

Verwendet für

Ändern einer vorhandenen gespeicherten Prozedur

Verfügbar in

DSQL, ESQL

Syntax

ALTER PROCEDURE procname [ ( [ <in_params> ] ) ]
  [RETURNS (<out_params>)]
  <module-body>

!! Vgl. auch die Syntax CREATE PROCEDURE für weitere Regeln !!

Die ALTER PROCEDURE-Anweisung ermöglicht die folgenden Änderungen an einer Stored-Procedure-Definition:

der Satz und die Eigenschaften der Eingabe- und Ausgabeparameter
lokale Variablen
Code im Hauptteil der gespeicherten Prozedur

Nachdem ALTER PROCEDURE ausgeführt wurde, bleiben bestehende Privilegien intakt und Abhängigkeiten werden nicht beeinflusst.

Wenn Sie eine Prozedur ändern, ohne die SQL SECURITY-Klausel anzugeben, wird die SQL-Sicherheitseigenschaft entfernt, wenn sie derzeit für diese Prozedur festgelegt ist.Dies bedeutet, dass das Verhalten auf den Datenbankstandard zurückgesetzt wird.

Caution

Achten Sie darauf, die Anzahl und den Typ der Eingabe- und Ausgabeparameter in gespeicherten Prozeduren zu ändern.Vorhandener Anwendungscode und Prozeduren und Trigger, die ihn aufrufen, könnten ungültig werden, da die neue Beschreibung der Parameter nicht mit dem alten Aufrufformat kompatibel ist.Informationen zur Behebung einer solchen Situation finden Sie im Artikel Das RDB$VALID_BLR-Feld im Anhang.

Wer kann ein Verfahren ändern

Die Anweisung ALTER PROCEDURE kann ausgeführt werden durch:

Administratoren
Der Besitzer der gespeicherten Prozedur
Benutzer mit der Berechtigung ALTER ANY PROCEDURE

`ALTER PROCEDURE`-Beispiel

Ändern der gespeicherten Prozedur GET_EMP_PROJ.

ALTER PROCEDURE GET_EMP_PROJ (
  EMP_NO SMALLINT)
RETURNS (
  PROJ_ID VARCHAR(20))
AS
BEGIN
  FOR SELECT
      PROJ_ID
    FROM
      EMPLOYEE_PROJECT
    WHERE
      EMP_NO = :emp_no
    INTO :proj_id
  DO
    SUSPEND;
END

Siehe auch

[fblangref40-ddl-proc-create-de], [fblangref40-ddl-proc-crtoralter-de], [fblangref40-ddl-proc-recreate-de], [fblangref40-ddl-proc-drop-de]

`CREATE OR ALTER PROCEDURE`

Verwendet für

Erstellen einer neuen gespeicherten Prozedur oder Ändern einer vorhandenen Prozedur

Verfügbar in

DSQL

Syntax

CREATE OR ALTER PROCEDURE procname [ ( [ <in_params> ] ) ]
  [RETURNS (<out_params>)]
  {<psql_procedure> | <external-module-body>}

!! Vgl. auch die Syntax CREATE PROCEDURE für weitere Regeln !!

Die Anweisung "CREATE OR ALTER PROCEDURE" erstellt eine neue gespeicherte Prozedur oder ändert eine vorhandene.Wenn die gespeicherte Prozedur nicht existiert, wird sie durch transparentes Aufrufen einer CREATE PROCEDURE-Anweisung erstellt.Wenn die Prozedur bereits existiert, wird sie geändert und kompiliert, ohne ihre bestehenden Privilegien und Abhängigkeiten zu beeinträchtigen.

`CREATE OR ALTER PROCEDURE`-Beispiel

Erstellen oder Ändern der Prozedur GET_EMP_PROJ.

CREATE OR ALTER PROCEDURE GET_EMP_PROJ (
    EMP_NO SMALLINT)
RETURNS (
    PROJ_ID VARCHAR(20))
AS
BEGIN
  FOR SELECT
      PROJ_ID
    FROM
      EMPLOYEE_PROJECT
    WHERE
      EMP_NO = :emp_no
    INTO :proj_id
  DO
    SUSPEND;
END

Siehe auch

[fblangref40-ddl-proc-create-de], [fblangref40-ddl-proc-alter-de], [fblangref40-ddl-proc-recreate-de]

`DROP PROCEDURE`

Verwendet für

Löschen einer gespeicherten Prozedur

Verfügbar in

DSQL, ESQL

Syntax

DROP PROCEDURE procname

Table 1. `DROP PROCEDURE`-Anweisungsparameter
Parameter	Beschreibung
procname	Name einer vorhandenen gespeicherten Prozedur

Die Anweisung DROP PROCEDURE löscht eine vorhandene gespeicherte Prozedur.Wenn die gespeicherte Prozedur Abhängigkeiten aufweist, schlägt der Versuch, sie zu löschen, fehl und der entsprechende Fehler wird ausgegeben.

Wer kann ein Verfahren abbrechen

Die Anweisung ALTER PROCEDURE kann ausgeführt werden durch:

Administratoren
Der Besitzer der gespeicherten Prozedur
Benutzer mit dem Privileg DROP ANY PROCEDURE

`DROP PROCEDURE`-Beispiel

Löschen der gespeicherten Prozedur GET_EMP_PROJ.

DROP PROCEDURE GET_EMP_PROJ;

Siehe auch

[fblangref40-ddl-proc-create-de], [fblangref40-ddl-proc-recreate-de]

`RECREATE PROCEDURE`

Verwendet für

Erstellen einer neuen gespeicherten Prozedur oder Neuerstellen einer vorhandenen Prozedur

Verfügbar in

DSQL

Syntax

RECREATE PROCEDURE procname [ ( [ <in_params> ] ) ]
  [RETURNS (<out_params>)]
  {<psql_procedure> | <external-module-body>}

!! Vgl. auch die Syntax CREATE PROCEDURE für weitere Regeln !!

Die Anweisung 'RECREATE PROCEDURE' erstellt eine neue gespeicherte Prozedur oder erstellt eine vorhandene neu.Wenn es bereits eine Prozedur mit diesem Namen gibt, versucht die Engine, diese zu löschen und eine neue zu erstellen.Das Neuerstellen einer vorhandenen Prozedur schlägt bei der COMMIT-Anforderung fehl, wenn die Prozedur Abhängigkeiten hat.

Warning

Beachten Sie, dass Abhängigkeitsfehler erst in der COMMIT-Phase dieser Operation erkannt werden.

Nachdem eine Prozedur erfolgreich neu erstellt wurde, werden die Berechtigungen zum Ausführen der gespeicherten Prozedur und die Berechtigungen der gespeicherten Prozedur selbst gelöscht.

`RECREATE PROCEDURE`-Beispiel

Erstellen der neuen gespeicherten Prozedur GET_EMP_PROJ oder Neuerstellen der vorhandenen gespeicherten Prozedur GET_EMP_PROJ.

RECREATE PROCEDURE GET_EMP_PROJ (
  EMP_NO SMALLINT)
RETURNS (
  PROJ_ID VARCHAR(20))
AS
BEGIN
  FOR SELECT
      PROJ_ID
    FROM
      EMPLOYEE_PROJECT
    WHERE
      EMP_NO = :emp_no
    INTO :proj_id
  DO
    SUSPEND;
END

Siehe auch

[fblangref40-ddl-proc-create-de], [fblangref40-ddl-proc-drop-de], [fblangref40-ddl-proc-crtoralter-de]

PROCEDURE

PROCEDURE

CREATE PROCEDURE

docnext count = 17

Statement-Terminatoren

Parameter

SQL-Sicherheit

Variablen-, Cursor- und Sub-Routine-Deklarationen

Externe UDR-Prozeduren

Wer kann ein Verfahren erstellen

Beispiele

ALTER PROCEDURE

Wer kann ein Verfahren ändern

ALTER PROCEDURE-Beispiel

CREATE OR ALTER PROCEDURE

CREATE OR ALTER PROCEDURE-Beispiel

DROP PROCEDURE

Wer kann ein Verfahren abbrechen

DROP PROCEDURE-Beispiel

RECREATE PROCEDURE

RECREATE PROCEDURE-Beispiel

`PROCEDURE`

`CREATE PROCEDURE`

`ALTER PROCEDURE`

`ALTER PROCEDURE`-Beispiel

`CREATE OR ALTER PROCEDURE`

`CREATE OR ALTER PROCEDURE`-Beispiel

`DROP PROCEDURE`

`DROP PROCEDURE`-Beispiel

`RECREATE PROCEDURE`

`RECREATE PROCEDURE`-Beispiel