Firebird Documentation IndexFirebird 2.5 SprachreferenzStatements der Data Definition (DDL) → PROCEDURE
Firebird Home Firebird Home Zurück: TRIGGERFirebird Documentation IndexNach oben: Statements der Data Definition (DDL)Weiter: EXTERNAL FUNCTION

PROCEDURE

Inhaltsverzeichnis

CREATE PROCEDURE
ALTER PROCEDURE
CREATE OR ALTER PROCEDURE
DROP PROCEDURE
RECREATE PROCEDURE

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

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

CREATE PROCEDURE

Benutzt für: Erstellen einer neuen gespeicherten Prozedur

Verfügbar in: DSQL, ESQL

Syntax: 

CREATE PROCEDURE procname
[(<inparam> [, <inparam> ...])]
[RETURNS (<outparam> [, <outparam> ...])]
AS
[<declarations>]
BEGIN
[<PSQL_statements>]
END

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

	<outparam> ::= <param_decl>

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

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

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

<datatype> ::=
    {SMALLINT | INT[EGER] | BIGINT}
  | {FLOAT | DOUBLE PRECISION}
  | {DATE | TIME | TIMESTAMP}
  | {DECIMAL | NUMERIC} [(precision [, scale])]
  | {CHAR | CHARACTER | CHARACTER VARYING | VARCHAR} [(size)]
    [CHARACTER SET charset]
  | {NCHAR | NATIONAL CHARACTER | NATIONAL CHAR} [VARYING]
    [(size)]
  | BLOB [SUB_TYPE {subtype_num | subtype_name}]
    [SEGMENT SIZE seglen] [CHARACTER SET charset]
  | BLOB [(seglen [, subtype_num])]

<declarations> ::=
  {<declare_var> | <declare_cursor>};
    [{<declare_var> | <declare_cursor>}; …]
        

Tabelle 5.24. CREATE PROCEDURE Statement-Parameter

Parameter Beschreibung
procname Der Name der gespeicherten Prozedur besteht aus bis zu 31 Zeichen. Muss für alle Tabellen-, View- und Prozedurnamen in der Datenbank eindeutig sein
inparam Beschreibung der Eingabeparameter
outparam Beschreibung der Ausgangsparameter
declarations Abschnitt zum Deklarieren von lokalen Variablen und benannten Cursorn
declare_var Lokale Variablendeklaration
declare_cursor Benannte Cursor-Deklaration
PSQL_statements Prozedurale SQL-Anweisungen
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. Dieser kann aus bis zu 31 Zeichen bestehen. Der Name des Parameters muss unter den Eingabe- und Ausgabeparametern der Prozedur und ihren lokalen Variablen eindeutig sein
datatype SQL-Datentyp
collation Sortierfolge
domain Domain-Name
rel Tabellen- oder View-Name
col Spaltenname einer Tabelle oder View
precision Die Gesamtanzahl der signifikanten Stellen, die der Parameter halten kann (1..18)
scale Die Anzahl der Stellen nach dem Dezimalpunkt (0..precision)
size Die maximale Größe eines Zeichenfolgentypparameters oder einer Variablen in Zeichen
charset Zeichensatz eines String-Typ-Parameters oder einer Variablen
subtype_num Subtyp-Nummer eines BLOB
subtype_name Mnemonischer Name eines BLOB-Subtyps
seglen Segmentgröße (max. 65535)


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 ggf. die Ausgabeparameter, die von der Prozedur zurückgegeben werden sollen.

Der Prozedurhauptteil 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 endet mit dem Schlüsselwort END endet. Deklarationen und eingebettete Anweisungen werden mit Semikolon (;) abgeschlossen.

Statement-Terminatoren

Einige SQL-Anweisungseditoren — insbesondere das mit Firebird mitgelieferte Dienstprogramm isql und möglicherweise einige Editoren von Drittanbietern — verwenden eine interne Konvention, die erfordert, dass alle Anweisungen mit einem Semikolon abgeschlossen werden. Dies führt bei der Codierung in diesen Umgebungen zu einem Konflikt mit der PSQL-Syntax. Wenn Sie mit diesem Problem und seiner Lösung nicht vertraut sind, lesen Sie bitte die Details im Kapitel PSQL im Abschnitt Umschalten des Terminators in isql.

Parameter

Jeder Parameter hat einen Datentyp, der dafür angegeben ist. Die Einschränkung NOT NULL kann auch für jeden beliebigen Parameter angegeben werden, um zu verhindern, dass NULL übergeben oder zugewiesen wird.

Mit der Klausel COLLATE kann eine Sortierfolge für Parameter vom Typ String festgelegt werden.

Eingabeparameter: 

Eingabeparameter werden nach dem Namen der Prozedur in Klammern angezeigt. Sie werden als Werte an die Prozedur übergeben, d.h. alles, was sie innerhalb der Prozedur ändert, hat keine Auswirkungen auf die Parameter im aufrufenden Programm.

Eingabeparameter können Standardwerte haben. Diejenigen, für die Werte angegeben sind, müssen sich am Ende der Parameterliste befinden.

Ausgabeparameter: 

Die optionale Klausel RETURNS dient zur Angabe einer eingeklammerten Liste von Ausgabeparametern für die gespeicherte Prozedur.

Verwendung von Domains in Deklarationen

Ein Domainname kann als Typ eines Parameters angegeben werden. Der Parameter erbt alle Domainattribute. Wenn ein Standardwert für den Parameter angegeben wird, überschreibt dieser den in der Domänendefinition angegebenen Standardwert.

Wenn die Klausel TYPE OF vor dem Domänennamen hinzugefügt wird, wird nur der Datentyp der Domain verwendet: Alle anderen Attribute der Domain — NOT NULL-Einschränkung, CHECK-Bedingung, Standardwert — werden weder geprüft noch verwendet. Wenn die Domain jedoch aus einem Texttyp besteht, werden immer ihre Zeichensatz und die Sortierreihenfolge verwendet.

Verwendung des Spaltentyps in Deklarationen

Eingabe- und Ausgabeparameter können auch über den Datentyp von Spalten in vorhandenen Tabellen und Ansichten deklariert werden. Die Klausel TYPE OF COLUMN wird dafür verwendet, wobei relationname.columnname als Argument angegeben wird.

Wenn TYPE OF COLUMN verwendet wird, erbt der Parameter nur den Datentyp, bei Zeichenkettentypen den Zeichensatz und die Sortierreihenfolge. Die Constraints und der Standardwert der Spalte werden ignoriert.

Bugwarnung für Versionen vor Firebird 3:

Für Eingabeparameter wird die Sortierung, die mit dem Typ der Spalte geliefert wird, in Vergleichen ignoriert (z. B. Gleichheitstests). Bei lokalen Variablen variiert das Verhalten.

Der Bug wurde für Firebird 3 behoben.

Variablen- und Cursor-Deklarationen

Der optionale Deklarationsabschnitt, der zuletzt im Headerabschnitt der Prozedurdefinition aufgeführt ist, definiert lokale Variablen für die Prozedur und ihre benannten Cursor. Lokale Variablendeklarationen folgen denselben Regeln wie Parameter bezüglich der Spezifikation des Datentyps. Bitte entnehmen Sie Details den Abschnitten PSQL chapter for DECLARE VARIABLE und DECLARE CURSOR.

Auf den Headerabschnitt folgt der Prozedurhauptteil, der aus einer oder mehreren PSQL-Anweisungen besteht, die zwischen den äußeren Schlüsselwörtern BEGIN und END eingeschlossen sind. Mehrere BEGIN ... END-Blöcke von beendeten Anweisungen können in den Prozedurtext eingebettet werden.

Jeder Benutzer, der mit der Datenbank verbunden ist, kann eine neue gespeicherte Prozedur erstellen. Der Benutzer, der eine gespeicherte Prozedur erstellt, wird zu seinem Besitzer.

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 wä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; -- the statement that sends an output row to the buffer
	         -- and makes the procedure "selectable"
END
          

Siehe auch:  CREATE OR ALTER PROCEDURE, ALTER PROCEDURE, RECREATE PROCEDURE, DROP PROCEDURE

ALTER PROCEDURE

Benutzt für: Ändern einer vorhandenen gespeicherten Prozedur

Verfügbar in: DSQL, ESQL

Syntax: 

ALTER PROCEDURE procname
[(<inparam> [, <inparam> ...])]
[RETURNS (<outparam> [, <outparam> ...])]
AS
[<declarations>]
BEGIN
[<PSQL_statements>]
END

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

	<outparam> ::= <param_decl>

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

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

<datatype> ::=
    {SMALLINT | INT[EGER] | BIGINT}
  | {FLOAT | DOUBLE PRECISSION}
  | {DATE | TIME | TIMESTAMP}
  | {DECIMAL | NUMERIC} [(precision [, scale])]
  | {CHAR | CHARACTER | CHARACTER VARYING | VARCHAR} [(size)]
    [CHARACTER SET charset]
  | {NCHAR | NATIONAL CHARACTER | NATIONAL CHAR} [VARYING]
    [(size)]
  | BLOB [SUB_TYPE {subtype_num | subtype_name}]
    [SEGMENT SIZE seglen] [CHARACTER SET charset]
  | BLOB [(seglen [, subtype_num])]

	<declarations> ::= {<declare_var> | <declare_cursor>};
	[{<declare_var> | <declare_cursor>}; …]
        

Tabelle 5.25. ALTER PROCEDURE Statement-Parameter

Parameter Beschreibung
procname Der Name einer existierenden gespeicherten Prozedur
inparam Beschreibung der Eingabeparameter
outparam Beschreibung der Ausgangsparameter
declarations Abschnitt zum Deklarieren von lokalen Variablen und benannten Cursorn
declare_var Lokale Variablendeklaration
declare_cursor Benannte Cursor-Deklaration
PSQL_statements Prozedurale SQL-Anweisungen
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. Dieser kann aus bis zu 31 Zeichen bestehen. Der Name des Parameters muss unter den Eingabe- und Ausgabeparametern der Prozedur und ihren lokalen Variablen eindeutig sein
datatype SQL-Datentyp
collation Sortierfolge
domain Domain-Name
rel Tabellen- oder View-Name
col Spaltenname einer Tabelle oder View
precision Die Gesamtanzahl der signifikanten Stellen, die der Parameter halten kann (1..18)
scale Die Anzahl der Stellen nach dem Dezimalpunkt (0..precision)
size Die maximale Größe eines Zeichenfolgentypparameters oder einer Variablen in Zeichen
charset Zeichensatz eines String-Typ-Parameters oder einer Variablen
subtype_num Subtyp-Nummer eines BLOB
subtype_name Mnemonischer Name eines BLOB-Subtyps
seglen Segmentgröße (max. 65535)


Die Anweisung ALTER PROCEDURE erlaubt folgende Änderungen der Definition für gespeicherte Prozeduren:

  • Satz und die Eigenschaften von Eingangs- und Ausgangsparametern
  • lokale Variablen
  • Code im Hauptteil der gespeicherten Prozedur

Nach der Ausführung von ALTER PROCEDURE bleiben vorhandene Berechtigungen erhalten und Abhängigkeiten werden nicht beeinflusst.

Achtung

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

Der Prozedureigentümer und Administratoren besitzen die Recht zum Ausführen von ALTER PROCEDURE.

Beispiele für ALTER PROCEDURE: Ä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:  CREATE PROCEDURE, CREATE OR ALTER PROCEDURE, RECREATE PROCEDURE, DROP PROCEDURE

CREATE OR ALTER PROCEDURE

Benutzt für: Erstellen einer neuen gespeicherten Prozedur oder Ändern einer vorhandenen

Verfügbar in: DSQL

Syntax: 

CREATE OR ALTER PROCEDURE procname
[(<inparam> [, <inparam> ...])]
[RETURNS (<outparam> [, <outparam> ...])]
AS
[<declarations>]
BEGIN
[<PSQL_statements>]
END
        

Das vollständige Syntaxdetail finden Sie unter CREATE DATABASE .

Die Anweisung CREATE OR ALTER PROCEDURE erstellt eine neue gespeicherte Prozedur oder ändert eine vorhandene Prozedur. Wenn die gespeicherte Prozedur nicht vorhanden ist, wird sie durch das transparente Aufrufen einer Anweisung CREATE PROCEDURE erstellt. Wenn die Prozedur bereits vorhanden ist, wird sie geändert und kompiliert, ohne die vorhandenen Berechtigungen und Abhängigkeiten zu beeinträchtigen.

Beispiel:  Creating or altering the GET_EMP_PROJ procedure.

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:  CREATE PROCEDURE, ALTER PROCEDURE, RECREATE PROCEDURE

DROP PROCEDURE

Benutzt für: Löschen einer gespeicherten Prozedur

Verfügbar in: DSQL, ESQL

Syntax: 

DROP PROCEDURE procname
        

Tabelle 5.26. DROP PROCEDURE Statement-Parameter

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 Löschversuch fehl und der entsprechende Fehler wird ausgelöst.

Der Prozedureigentümer und Administratoren haben die Berechtigung, DROP PROCEDURE zu verwenden.

Beispiel:  Löschen der gespeicherten Prozedur GET_EMP_PROJ

DROP PROCEDURE GET_EMP_PROJ;
          

Siehe auch:  CREATE PROCEDURE, RECREATE PROCEDURE

RECREATE PROCEDURE

Benutzt für: Eine neue gespeicherte Prozedur erstellen oder eine vorhandene wiederherstellen

Verfügbar in: DSQL

Syntax: 

RECREATE PROCEDURE procname
[(<inparam> [, <inparam> ...])]
[RETURNS (<outparam> [, <outparam> ...])]
AS
[<declarations>]
BEGIN
[<PSQL_statements>]
END
        

Das vollständige Syntaxdetail finden Sie unter CREATE PROCEDURE .

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

Warnung

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

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

Beispiel:  Erstellen der neuen gespeicherten Prozedur GET_EMP_PROJ oder Wiederherstellen 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:  CREATE PROCEDURE, DROP PROCEDURE, CREATE OR ALTER PROCEDURE

Zurück: TRIGGERFirebird Documentation IndexNach oben: Statements der Data Definition (DDL)Weiter: EXTERNAL FUNCTION
Firebird Documentation IndexFirebird 2.5 SprachreferenzStatements der Data Definition (DDL) → PROCEDURE