Prozeduren in Transact-SQL

Prozeduren:
CREATE PROCEDURE [ Eigentümer.]Prozedurname ( [ Parameter, ... ] )
{ [ RESULT ( Ergebnisspalte, ... ) ]
[ ON EXCEPTION RESUME ]
AS SQL-Anweisung
| AT Speicherort
| EXTERNAL NAME Bibliotheksaufruf
| [ DYNAMIC RESULT SETS Ganzzahlausdruck ]
[ EXTERNAL NAME Java-Aufruf LANGUAGE JAVA ]
}
oder
CREATE PROCEDURE [ Eigentümer.]Prozedurname ( [ Parameter, ... ] )
AS SQL-Anweisung
Parameter:
Parametermodus Parametername Datentyp DEFAULT Ausdruck ]
| SQLCODE
| SQLSTATE
Parametermodus:
IN | OUT | INOUT
Ergebnisspalte:
Spaltenname Datentyp
Bibliotheksaufruf:
'[Betriebssystem:][email protected]; ...'
Betriebssystem:
Windows95 | WindowsNT | NetWare | UNIX
Java-Aufruf:
'[Paketname.]Klassenname.Methodenname Methodensignatur'
Methodensignatur:
( [ Felddeskriptor, ... ] ) Rückgabedeskriptor
Felddeskriptor:
Z | B | S | I | J | F | D | C | V | [Deskriptor | LKlassenname;
Rückgabedeskriptor:
Felddeskriptor
CREATE PROCEDURE-Klausel Parameternamen müssen den Regeln für andere
Datenbank-Bezeichner, wie z.B. Spaltennamen, entsprechen. Sie müssen einen gültigen SQLDatentyp haben, und es muss ihnen eines der folgenden Schlüsselwörter vorangehen: IN,
OUT oder 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, welche der Prozedur einen Wert zur
Verfügung stellt, und der von der Prozedur ein neuer Wert gegeben werden kann.
Wenn Prozeduren mit der CALL-Anweisung ausgeführt werden, brauchen nicht alle
Parameter angegeben zu werden. Wenn in der CREATE PROCEDURE-Anweisung ein
Standardwert bereitgestellt wird, wird einem fehlenden Parameter der Standardwert
zugeordnet. Falls in der CALL-Anweisung kein Argument angegeben und kein Standardwert
gesetzt ist, wird ein Fehler ausgegeben.
SQLSTATE und SQLCODE sind spezielle Parameter, die den SQLSTATE- oder
SQLCODE-Wert ausgeben, wenn die Prozedur endet (es handelt sich um OUT-Parameter).
Unabhängig davon, ob die Parameter SQLSTATE und SQLCODE angegeben werden oder
nicht, können die Spezialwerte SQLSTATE und SQLCODE immer sofort nach einem
Prozeduraufruf überprüft werden, um den Rückgabestatus der Prozedur zu testen.
Die Werte der Spezialwerte SQLSTATE und SQLCODE werden durch die nächste SQLAnweisung geändert. Indem SQLSTATE oder SQLCODE als Prozedurargumente
bereitgestellt werden, kann die Rückmeldung in einer Variablen gespeichert werden.
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 CALLAnweisung beschrieben wird.
Einige Prozeduren können 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 unter anderen nur eine Spalte
zurück.
CREATE PROCEDURE names( IN formal char(1))
BEGIN
IF formal = 'n' THEN
SELECT emp_fname
FROM employee
ELSE
SELECT emp_lname,emp_fname
FROM employee
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 mit einem DESCRIBE versehen,
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:
Variable Ergebnismengen-Prozeduren können von ODBC-Anwendungen
verwendet werden. Die richtige Beschreibung der Ergebnismengen wird vom ODBCTreiber vorgenommen.
 Open Client-Anwendungen:
Variable Ergebnismengen-Prozeduren 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 ClientAnwendungen die Ergebnismenge noch einmal beschreiben, nachdem der Cursor geöffnet
wurde.
Um mehrfache Ergebnismengen verarbeiten zu können, muss ODBC den aktuell ausgeführten
Cursor beschreiben, und nicht die von der Prozedur definierte Ergebnismenge. Deshalb
bezeichnet ODBC die Spaltennamen nicht immer so, wie sie in der RESULT-Klausel der
gespeicherten Prozedur definiert sind. Verwenden Sie Spaltenaliasnamen in der SELECTAnweisung, die die Ergebnismenge erzeugt, um dieses Problem zu vermeiden.
ON EXCEPTION RESUME-Klausel Diese Klausel aktiviert Transact-SQL - wie eine
Fehlerbehandlung, die innerhalb einer Prozedur in 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
 CALL
 SELECT @variable =
 EXECUTE
 CASE
 SIGNAL
 LOOP
 RESIGNAL
 LEAVE
 DECLARE
 CONTINUE
 SET VARIABLE
Einen expliziten Fehlerbehandlungscode sollten Sie nicht mit der Klausel ON EXCEPTION
RESUME verwenden.
EXTERNAL NAME-Klausel Eine Funktion, welche die EXTERNAL NAME-Klausel
verwendet, dient als Behälter für den Aufruf einer Funktion in einer externen Bibliothek. Eine
gespeicherte Prozedur, die EXTERNAL NAME verwendet, kann keine anderen Klauseln
haben, die der Parameterliste folgen.
AT Standort-Klausel Erstellt eine gespeicherte Proxyprozedur 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.
Die folgende Anweisung erstellt z.B. eine Proxy-Prozedur (remotewho), die die Prozedur
dbo.sp_who in der Master-Datenbank auf dem Server bostonase aufruft:
CREATE PROCEDURE remotewho ()
AT 'bostonase.master.dbo.sp_who
Entfernte Prozeduren können nur bis zu 254 Zeichen in Ausgabevariablen zurückgeben.
DYNAMIC RESULT SETS-Klausel Diese Klausel ist bei Prozeduren hilfreich, die
Behälter für Javamethoden sind. Wenn die Klausel DYNAMIC RESULT SETS nicht
angegeben ist, wird angenommen, dass von der Methode keine Ergebnismengen
zurückgegeben werden.
EXTERNAL NAME LANGUAGE JAVA-Klausel Eine Prozedur, die EXTERNAL
NAME mit einer LANGUAGE JAVA-Klausel verwendet, ist ein Behälter für eine JavaMethode.
Wenn die Anzahl der Parameter geringer ist als die in der Methodensignatur angegebene
Zahl, muss der Unterschied gleich der Zahl sein, die in DYNAMIC RESULT SETS
angegeben wurde. Jeder Parameter in der Methodensignatur, der nicht in der Parameterliste
für die Prozedur enthalten ist, muss die Methodensignatur [Ljava/SQL/ResultSet; haben.
Eine Javamethodensignatur ist die kompakte Zeichendarstellung der Typen der Parameter und
der Typen der Ergebniswerte.
Der Felddeskriptor und der Rückgabedeskriptor haben folgende Bedeutung:
Feldtyp
B
C
D
F
I
J
Lklassenname;
S
V
Z
[
Javadatentyp
byte
char
double
float
int
long
Eine Instanz der Klasse Klassenname. Die Klasse muss voll qualifiziert
sein und alle Punkte im Namen müssen durch das Zeichen / ersetzt
werden. Beispiel: java/lang/String
short
void
Boolescher Ausdruck
Verwenden Sie jeweils eine für jede Felddimension.
Zum Beispiel:
double some_method(
boolean a,
int b,
java.math.BigDecimal c,
byte [][] d,
java.SQL.ResultSet[] rs ) {
}
Dies hätte folgende Signatur:
'(ZILjava/math/BigDecimal;[[B[Ljava/SQL/ResultSet;)D'
Einsatzbereich:
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 CALLAnweisung aufgerufen.
Berechtigungen:
RESSOURCE-Berechtigung ist erforderlich.
Sie müssen über DBA-Berechtigung für externe Prozeduren oder zum Erstellen einer
Prozedur für einen anderen Benutzer verfügen.
Nebenwirkungen:
Automatisches Festschreiben
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 "DBA"."product"
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
Die folgende Prozedur verwendet einen Cursor oder Schleifen ü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 company_name, CAST(
sum(sales_order_items.quantity *
product.unit_price) AS INTEGER) VALUE
FROM customer
LEFT OUTER JOIN sales_order
LEFT OUTER JOIN sales_order_items
LEFT OUTER JOIN product
GROUP BY company_name;
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
Prozeduren in Transact-SQL [T-SQL]
Die folgende Teilmenge der Transact-SQL CREATE PROCEDURE-Anweisung wird in
Adaptive Server Anywhere unterstützt.
CREATE PROCEDURE [Eigentümer.]Prozedurname
[ [ ( ] @Parametername Datentyp [ = Standardwert ] [ OUTPUT ], ... [ ) ] ]
[ WITH RECOMPILE ] AS Anweisungsliste
Im Folgenden werden Unterschiede zwischen Transact-SQL- und Adaptive Server AnywhereAnweisungen (Watcom SQL) aufgelistet, um denjenigen zu helfen, die in beiden Dialekten
schreiben.





Variablennamen mit Präfix @: Das Zeichen "@" bezeichnet einen Transact-SQLVariablennamen. Eine Watcom SQL-Variable kann jedoch jeder gültige Bezeichner sein,
und der Präfix @ ist optional.
Eingabe- und Ausgabeparameter: In Watcom SQL werden Prozedurparameter als IN,
OUT oder INOUT angegeben, während in Transact-SQL Prozedurparameter
standardmäßig INPUT-Parameter sind oder als OUTPUT angegeben werden können. Die
Parameter, welche in Adaptive Server Anywhere als INOUT oder als OUT deklariert
werden, müssen in Transact-SQL mit OUTPUT deklariert werden.
Standardwerte für Parameter: In Watcom SQL werden Prozedurparameter mit dem
Schlüsselwort DEFAULT Standardwerte zugewiesen. In Transact-SQL wird ein
Gleichheitszeichen (=) verwendet, um den Standardwert bereitzustellen.
Hauptteil einer Prozedur Der Hauptteil einer Transact-SQL-Prozedur besteht aus einer
Liste von Transact-SQL-Anweisungen, denen das Schlüsselwort AS als Präfix vorausgeht.
Der Hauptteil einer Watcom SQL-Prozedur ist eine zusammengesetzte Anweisungen, die
von den beiden Schlüsselwörtern BEGIN und END umschlossen wird.
Rückgabe von Ergebnismengen: Watcom SQL verwendet eine RESULT-Klausel, um
zurückgegebene Ergebnismengen anzugeben. In Transact-SQL-Prozeduren werden die
Spaltennamen oder Aliasnamen der ersten Abfrage der aufrufenden Umgebung
zurückgegeben.
Die folgende Transact-SQL-Prozedur veranschaulicht, wie Ergebnismengen von
gespeicherten Prozeduren in Transact-SQL zurückgegeben werden:
CREATE PROCEDURE showdept @deptname varchar(30)
AS
SELECT employee.emp_lname, employee.emp_fname
FROM department, employee
WHERE department.dept_name = @deptname
AND department.dept_id = employee.dept_id
Im Folgenden wird die entsprechende Watcom SQL-Prozedur aufgeführt:
CREATE PROCEDURE showdept(in deptname
varchar(30) )
RESULT ( lastname char(20), firstname char(20))
ON EXCEPTION RESUME
BEGIN
SELECT employee.emp_lname, employee.emp_fname
FROM department, employee
WHERE department.dept_name = deptname
AND department.dept_id = employee.dept_id
END
Berechtigungen:
RESSOURCE-Berechtigung ist erforderlich.
Nebenwirkungen:
Automatisches Festschreiben
Wenn die optionale Transact-SQL-Klausel WITH RECOMPILE angegeben wird, wird sie
ignoriert. Adaptive Server Anywhere rekompiliert Prozeduren, wenn sie beim Start der
Datenbank zum ersten Mal ausgeführt werden, und speichert die kompilierte Prozedur, bis die
Datenbank angehalten wird.
Prozedurgruppen werden nicht unterstützt.
Funktionen:
CREATE FUNCTION [ Eigentümer.]Funktionsname ( [ Parameter, ... ] )
RETURNS Datentyp
{ EXTERNAL NAME Bibliotheksaufruf
| EXTERNAL NAME Java-Aufruf LANGUAGE JAVA
| [ ON EXCEPTION RESUME ]
}
SQL-Anweisung
Parameter:
IN Parametername Datentyp
Bibliotheksaufruf:
'[Betriebssystem:][email protected]; ...'
Betriebssystem:
Windows95 | WindowsNT | NetWare | UNIX
Java-Aufruf:
'[Paketname.]Klassenname.Methodenname Methodensignatur'
Methodensignatur:
( [ Felddeskriptor, ... ] ) Rückgabedeskriptor
Felddeskriptor:
Z | B | S | I | J | F | D | C | V | [Deskriptor | LKlassenname;
Rückgabedeskriptor:
Felddeskriptor
CREATE FUNCTION-Klausel Parameternamen müssen den Regeln für Datenbank-Bezeichner
folgen. Sie müssen einen gültigen SQL-Datentyp haben, und das Schlüsselwort IN muss ihnen
vorangestellt sein, was bedeutet, dass das Argument ein Ausdruck ist, welcher der Funktion einen
Wert zur Verfügung stellt.
EXTERNAL NAME-Klausel Eine Funktion, welche die EXTERNAL NAME-Klausel verwendet,
dient als Behälter für den Aufruf einer Funktion in einer externen Bibliothek. Eine Funktion, die
EXTERNAL NAME verwendet, kann keine anderen Klauseln haben, die der RETURNS-Klausel
folgen.
EXTERNAL NAME LANGUAGE JAVA-Klausel Eine Funktion, die EXTERNAL NAME mit
einer LANGUAGE JAVA-Klausel verwendet, ist ein Behälter für eine Java-Methode.
ON EXCEPTION RESUME-Klausel
(siehe: Prozeduren!)
.
Transact-SQL-ähnliche Fehlerbehandlung verwenden.
Beispiel:
CREATE FUNCTION VollerName (
Vorname CHAR(30),
Nachname CHAR(30) )
RETURNS CHAR(61)
BEGIN
DECLARE Name CHAR(62);
SET Name = Vorname || ' ' || Nachname;
RETURN (Name);
END
Anwendungen:
SELECT VollerName ('Sabine','Sauer')as Name
Name
Sabine Sauer
SELECT VollerName (Name, Vornamen) as Person
FROM Spieler
WHERE Verb_nr is NULL
Person
Wiegand GWS
Bischof D
Kohl C
Mueller P
prozedurale Anweisungen:
BEGIN ….. END
 fasst mehrere SQL-Anweisungen zu einem Block zusammen,
 hat keine eigene Wirkung.
DECLARE @Variablenname Datentyp [, …]
 Variable(n) vereinbaren,
 Datentyp kann System- oder nutzerdefinierter Datentyp sein.
WHILE logischer_Ausdruck SQL_Anweisung
 Wenn logischer_Ausdruck wahr (true) ist, wird die SQL_Anweisung in einer Schleife ausgeführt.
 Schleifenende: logischer_Ausdruck ist nicht mehr wahr (false),
BREAK-Anweisung,
RETURN-Anweisung,
GOTO-Anweisung.
BREAK
 WHILE-Schleife wird verlassen.
 Fortsetzung mit erster Anweisung nach der WHILE-Schleife
CONTINUE
 kehrt zurück zum Beginn der WHILE-Schleife,
 logischer_Ausdruck der WHILE-Schleife wird erneut geprüft.
CASE WHEN logischer_Ausdruck THEN Ausdruck
[WHEN logischer_Ausdruck THEN Ausdruck [, …]]
[ELSE Ausdruck] END
oder
CASE Ausdruck WHEN Ausdruck THEN Ausdruck
[WHEN Ausdruck THEN Ausdruck [, …]]
[ELSE Ausdruck] END
 CASE kann anstelle als Anweisung auch als Ausdruck verwendet werden: überall dort, wo ein
Ausdruck erlaubt ist.
 Innerhalb einer SQL-Anweisung kann, abhängig vom Wert eines Ausdrucks, der Wert eines
anderen Ausdrucks als Ergebnis verwendet werden.
 Die Datentypen der resultierenden Ausdrücke müssen kompatibel sein.
 Wenigstens einer der resultierenden Ausdrücke muss verschieden von NULL sein.
 Eine weggelassene ELSE-Klausel ist gleichbedeutend mit ELSE NULL
NULLIF ( Ausdruck_1, Ausdruck_2 )
 Ergebnis ist NULL, wenn beide Ausdrücke gleich sind,
 anderenfalls: Ausdruck_1
 Wenigstens einer der Ausdrücke muss verschieden von NULL sein.
 Die Datentypen der Ausdrücke müssen kompatibel sein.
WAITFOR DELAY 'hh:mm:ss'
oder
WAITFOR TIME 'hh:mm:ss'
oder
WAITFOR { ERROREXIT | PROCESSEXIT }
 wartet entsprechend der angegebenen Zeit oder auf das Eintreten der angegebenen Ereignisses.
 DELAY und TIME: maximal 24 Stunden Wartezeit,
 ERROREXIT: wartet bis ein Prozess anormal abbricht,
 PROCESSEXIT: wartet bis ein Prozess normal endet anormal abbricht.
RAISERROR Fehlernummer [{ 'Formatvorgabe' | @Variable }] [ , Werteliste]
 bricht mit der angegebenen Fehlernummer ab
 Fehlernummern bei System-Fehlermeldungen: 17000 … 19999
 Fehlernummern bei nutzerdefinierten Fehlermeldungen: >=20000
 Beispiel:
SP_ADDMESSAGE 22222, 'Das ist eine %1! Warnung.';
RAISERROR 22222, 'ernste'
bewirkt die Fehlermeldung: 'Das ist eine ernste Warnung.'
RETURN [ [ ( ] Festkommaausdruck [ ) ] ]
 Prozedur wird verlassen
 Fortsetzung mit der nächsten Anweisung nach dem Aufrufpunkt der Prozedur
[ { EXEC | EXECUTE } ] [ @Rueckkehrstatus = ] Prozedurname
{ [ [ @Parametername = ] Wert | @Variable [ OUTPUT ] ] } [, …]
 ruft Prozedur zur Ausführung auf.
 falls Prozedurname mit SP_ beginnt, wird gesucht in: lokaler DB, SYBSYSTEMPROCS,
Datenbank MASTER (in dieser Reihenfolge).
[ { EXEC | EXECUTE } ] @Zeichenkettenvariable
 diese Variante ist in SQLAnywhere nicht anwendbar!
 Variable enthält einen Prozedurnamen
 bewirkt Aufruf der in der Variablen angegebenen Prozedur
 Zeichenkette kann durch Verketten von Variablen und Konstanten gebildet werden
 Beispiel:
DECLARE @zk varchar(100);
SELECT @zk = 'SP_HELP';
EXEC @zk 'Spieler'
 Beispiel:
EXEC ( 'SELECT * FROM ' + @Tabellenname )