PL/SQL/ Druckversion

Vor zu " Schleifen "

Das Buch PL-SQL ist zurzeit in Überarbeitung! Änderungen auf dieser Seite bitte nur in Absprache mit mir.

Allgemeines

Ein Cursor ist eine Art Zeilenvariable oder -zeiger, mit dem die Ergebnismenge einer SELECT-Anweisung zeilenweise (mit Hilfe der FETCH-Anweisung oder per CURSOR-FOR-LOOP) durchschritten wird.

Syntax

 cursor [NAME] is
 select [SPALTEN,*] from [TABELLE] 
 where  [BEDINGUNG] ....;

Cursor Schleifen

LOOP
FETCH [CURSOR] INTO [TYPE];
.....
EXIT WHEN [BEDINGUNG]
END LOOP;

FOR [i] IN [CURSOR]
LOOP
.....
EXIT;

Schleifen

Das Buch PL-SQL ist zurzeit in Überarbeitung! Änderungen an dieser Seite nur in Absprache mit mir.

Wie in jeder Sprache unterstützt auch PL/SQL Schleifen. Um richtig in die Programmierung einzusteigen, wollen wir hier zuallererst zwischen kopf- und fußgesteuerten Schleifen unterscheiden.

Loop --- Until
While
For - Schleifen

Beispiele LOOP --- UNTIL:

(PL/SQL kennt keine REPEAT/UNTIL Konstruktion, stattdessen kann mit Hilfe der EXIT-Anweisung eine Schleife an beliebiger Stelle abgebrochen werden.)

Declare
  x NUMBER := 1;
Begin
  Loop
     DBMS_OUTPUT.put_line( x );
     X := X + 1;
     EXIT WHEN x > 10;
  End Loop;
End;

Die Schleife wird solange ausgeführt, bis die Abbruchbedingung erfüllt ist. Die EXIT Bedingung kann an jeder beliebigen Stelle innerhalb der Schleife verwendet werden. Es ist zu beachten, daß bei unkorrekter Verwendung eine Endlosschleife ausgeführt wird. Wird die EXIT Bedingung am Beginn der Schleife verwendet, entspricht es einer WHILE Schleife.

Lesbarer ist allerdings die 'echte' WHILE Schleife:

Declare
  x NUMBER := 1;
Begin
  While X < 11 Loop
     DBMS_OUTPUT.put_line( x );
     X := X + 1;
  End Loop;
End;

Hier ein Beispiel für eine einfache FOR Schleife:

Declare
  -- Indexvariablen müssen nicht deklariert werden;
  -- bei X handelt es sich immer um eine ganze Zahl.
Begin
  For X IN 1 .. 10 Loop
    DBMS_OUTPUT.put_line( x );
  End Loop;
End;

Die Schleife wird genau 10 mal ausgeführt.

Cursor

Zurück zu " Schleifen " |

Vor zu " Exception-Handling"

Das Buch PL-SQL wird zurzeit überarbeitet. Änderungen an dieser Seite bitte nur in Absprache mit mir.

Cursor sind Zeiger

Cursor ist englisch und bedeutet so viel wie "Zeiger". Im allgemeinen Gebrauch mit dem Computer hat das Wort vor allem zwei Bedeutungen: Einerseits bezeichnet man den Mauszeiger als Cursor und zum anderen den Strich, welcher die Eingabestelle bei Eingebeprogrammen (überall, wo man etwas schreiben kann) markiert.

Im Zusammenhang mit PL/SQL hat er noch eine dritte Bedeutung. Laut Wikipedia steht der Begriff hier eventuell als Abkürzung für Current Set of Records. Das bedeutet, er ist ein temporärer Bereich im Arbeitsspeicher, welcher Informationen über eine spezielle PL/SQL-Anweisung und ihre Ausgabe-Daten speichert und diese auch manipulieren kann. (Als Anweisung bezeichnet man einen Bereich zwischen zwei Semikola.) Wird ein Cursor in der Anweisung wieder geschlossen, gehen auch die in ihm enthaltenen Daten verloren.

In PL/SQL unterscheidet man, je nach Unterscheidungkriterium, insgesamt vier verschiedene Cursor: Das sind einerseits die statischen bzw. dynamischen und andererseits die expliziten bzw. impliziten Cursor. Die Anweisungen eines dynamischen Cursors werden erst zur Laufzeit festgelegt und können für jeden gültigen SQL-Anweisungstyp genutzt werden. Implementiert werden sie über EXECUTE IMMEDIATE-Anweisungen. Ein statischer Cursor hingegen wird bereits zur Kompilierzeit festgelegt und nur für DML-Anweisungen (also SELECT, INSERT, UPDATE, DELETE, MERGE oder SELECT FOR UPDATE) verwendet. Auftauchen können sie in zweierlei Formen: als explizit deklarierte oder implizit eingebettete Cursor.

Implizierter Cursor

Implizite Cursor werden weder deklariert noch per Befehl geöffnet, bearbeitet oder geschlossen. Sie tauchen erst an der Stelle im Ausführungsteil auf, an der sie benötigt werden, und werden danach wieder geschlossen, wodurch die in ihnen enthaltenen Daten wieder gelöscht werden.

Hier ein Beispiel eines impliziten Cursors, mit dem ein einziger Datensatz gelesen werden kann:

Beispiel in einem anonymen Block:

declare
    v_user_id users.id%TYPE;
    v_username users.username%type;
begin
    -- implicit cursor
    SELECT id, username
    INTO v_user_id, v_username
    FROM users
    WHERE username='test';
    
    dbms_output.PUT_LINE('----------------------------');
    dbms_output.PUT_LINE('User-ID: '||v_user_id);
    dbms_output.PUT_LINE('Username: '||v_username);
    dbms_output.PUT_LINE('----------------------------');
end;

Wenn man dabei ein SELECT-Statement angibt, das mehr als einen Satz als Ergebnis hat, wird eine Fehlermeldung ausgegeben, da nicht klar ist, welcher der gefundenen Datensätze in die Variable geschrieben werden soll.

Für SELECT-Statements, die mehr als einen Ergebnis-Satz liefern, kann die folgende Konstruktion verwendet werden:

begin
    -- implicit cursor mit FOR
    FOR rec_users in ( SELECT id, username, firstname, lastname FROM users ) LOOP
        dbms_output.PUT_LINE('User-ID: '||rec_users.id);
        dbms_output.PUT_LINE('Username: '||rec_users.username);
        dbms_output.PUT_LINE('Firstname: '||rec_users.firstname);
        dbms_output.PUT_LINE('Lastname: '||rec_users.lastname);
        dbms_output.PUT_LINE('----------------------------');
    END LOOP;
end;

Expliziter Cursor

Explizite Cursor sind, wie der Name bereits erahnen lässt, im Deklarationsabschnitt deklariert. Dies kann ohne Parameter, mit Argumenten als Parameterliste oder auch mit einer RETURN-Klausel geschehen. Im Ausführungs- bzw. Exceptionabschnitt operiert man mit ihnen mithilfe der Befehle OPEN, FETCH und CLOSE zum Öffnen, Auslesen und Schließen. Dabei sind explizite Cursor die einzigen, die auch mehrzeilige Ausgaben machen können.

Hier ein Beispiel dazu in einem anonymen Block:

declare  
    CURSOR cur_users IS
        SELECT id, username, firstname, lastname
        FROM users;
    rec_users cur_users%ROWTYPE;
begin
    -- explicit cursor
    OPEN cur_users;
    LOOP
        FETCH cur_users INTO rec_users;
        EXIT WHEN cur_users%NOTFOUND;
    
        dbms_output.PUT_LINE('User-ID: '||rec_users.id);
        dbms_output.PUT_LINE('Username: '||rec_users.username);
        dbms_output.PUT_LINE('Firstname: '||rec_users.firstname);
        dbms_output.PUT_LINE('Lastname: '||rec_users.lastname);
        dbms_output.PUT_LINE('----------------------------');
    END LOOP;
end;

Statusvariablen für Cursor

In einer Cursor-FOR-Schleife kann man die Anzahl der bereits gelesenen Sätze mit der Statusvariablen ROWCOUNT abfragen. Diese Variable ist jedoch nur im Inneren der Schleife gültig. Wenn man sie außerhalb der Schleife verwendet, dann wird ein Syntaxfehler ausgegeben.

Beispiel:

SET SERVEROUTPUT ON
DECLARE
CURSOR c IS
SELECT table_name 
FROM all_tables
WHERE ROWNUM <= 10
;
BEGIN
dbms_output.put_line('start');
-- gibt Fehler:
-- dbms_output.put_line('vor for i cursor rowcount=' || to_char(c%rowcount));
FOR i IN c LOOP
   dbms_output.put_line('cursor rowcount=' || to_char(c%rowcount));
END LOOP;
-- gibt Fehler:
-- dbms_output.put_line('nach end loop cursor rowcount=' || to_char(c%rowcount));
dbms_output.put_line('fertig');
END;
/
SHOW ERRORS

Das Programm hat folgende Ausgabe:

start
cursor rowcount=1
cursor rowcount=2
cursor rowcount=3
cursor rowcount=4
cursor rowcount=5
cursor rowcount=6
cursor rowcount=7
cursor rowcount=8
cursor rowcount=9
cursor rowcount=10
fertig
PL/SQL procedure successfully completed.
No errors.

Rowcount bei einem Cursor ausgeben

Die auskommentierten Zeilen ergeben eine Fehlermeldung, wenn sie ausgeführt werden sollen.

set serveroutput on
declare
cursor c is
select table_name 
from all_tables
where rownum <= 1
;
name varchar2(100);
begin
dbms_output.put_line('start');
--dbms_output.put_line('c%found=' || case when c%found then 'TRUE' else 'FALSE' end);
--dbms_output.put_line('c%notfound=' || case when c%notfound then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%isopen=' || case when c%isopen then 'TRUE' else 'FALSE' end);
--dbms_output.put_line('c%rowcount=' || to_char(c%rowcount));
open c;
dbms_output.put_line('open c');
dbms_output.put_line('c%found=' || case when c%found then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%notfound=' || case when c%notfound then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%isopen=' || case when c%isopen then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%rowcount=' || to_char(c%rowcount));
fetch c into name;
dbms_output.put_line('fetch c');
dbms_output.put_line('c%found=' || case when c%found then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%notfound=' || case when c%notfound then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%isopen=' || case when c%isopen then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%rowcount=' || to_char(c%rowcount));
fetch c into name;
dbms_output.put_line('fetch c');
dbms_output.put_line('c%found=' || case when c%found then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%notfound=' || case when c%notfound then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%isopen=' || case when c%isopen then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%rowcount=' || to_char(c%rowcount));
fetch c into name;
dbms_output.put_line('fetch c');
dbms_output.put_line('c%found=' || case when c%found then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%notfound=' || case when c%notfound then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%isopen=' || case when c%isopen then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%rowcount=' || to_char(c%rowcount));
close c;
dbms_output.put_line('close c');
--dbms_output.put_line('c%found=' || case when c%found then 'TRUE' else 'FALSE' end);
--dbms_output.put_line('c%notfound=' || case when c%notfound then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%isopen=' || case when c%isopen then 'TRUE' else 'FALSE' end);
--dbms_output.put_line('c%rowcount=' || to_char(c%rowcount));
dbms_output.put_line('fertig');
end;
/
show errors

Das Programm hat folgende Ausgabe:

start
c%isopen=FALSE
open c
c%found=FALSE
c%notfound=FALSE
c%isopen=TRUE
c%rowcount=0
fetch c
c%found=TRUE
c%notfound=FALSE
c%isopen=TRUE
c%rowcount=1
fetch c
c%found=FALSE
c%notfound=TRUE
c%isopen=TRUE
c%rowcount=1
fetch c
c%found=FALSE
c%notfound=TRUE
c%isopen=TRUE
c%rowcount=1
close c
c%isopen=FALSE
fertig
PL/SQL procedure successfully completed.
No errors.

Exception-Handling

Zurück zu " Cursor " |

Vor zu " Prozedur "

Das Buch PL-SQL wird zurzeit überarbeitet! Änderungen an dieser Seite bitte nur in Absprache mit mir.

Vordefinierte Exceptions

Eine Auswahl an Exceptions. Eine vollständige Liste steht unter ^[1] zur Verfügung.

Name	Fehlercode	Beschreibung	Vorhanden ab
CURSOR_ALREADY_OPEN	ORA-06511	Cursor ist bereits geöffnet	Oracle 7
DUP_VAL_ON_INDEX	ORA-00001	INSERT oder UPDATE eines Wertes, der bereits vorhanden ist, in einer als UNIQUE deklarierten Spalte	Oracle 7
INVALID_CURSOR	ORA-01001	Der Cursor ist nicht deklariert (existiert nicht) oder wurde nicht geöffnet	Oracle 7
INVALID_NUMBER	ORA-01722	Fehler bei impliziter Typkonvertierung, z.B. String enthält keinen numerischen Wert wird aber in NUMBER Spalte/Variable eingefügt bzw. übergeben	Oracle 7
LOGIN_DENIED	ORA-01017	Benutzername oder Passwort falsch	Oracle 7
NO_DATA_FOUND	ORA-01403	SELECT liefert kein Ergebnis: keine Daten wurden durch einen impliziten Cursor gefunden oder ein Index in einem Assoziativen Array existiert nicht	Oracle 7
NOT_LOGGED_ON	ORA-01012	Keine Verbindung zur Datenbank	Oracle 7
PROGRAM_ERROR	ORA-06501	Interner PL/SQL - Fehler	Oracle 7
ROWTYPE_MISMATCH	ORA-06504	Strukturvariablen inkompatibel	Oracle 7
STORAGE_ERROR	ORA-06500	Speicherprobleme	Oracle 7
TIMEOUT_ON_RESOURCE	ORA-00051	Datenbank-Sperre	Oracle 7
TOO_MANY_ROWS	ORA-01422	SELECT liefert mehr als eine Zeile	Oracle 7
VALUE_ERROR	ORA-06502	Fehler beim Arbeiten mit "Werten" in Variablen/Tabellen/Prozeduren, z.B. zu große Werte, Verletzung von Constraints, Fehler bei Typkonvertierungen (außer INVALID_NUMBER - Exception)	Oracle 7
ZERO_DIVIDE	ORA-01476	bei Division durch 0	Oracle 7
ACCESS_INTO_NULL	ORA-06530	Zuweisung an ein nicht initialisiertes Objekt	Oracle 8
ACCESS_INTO_NULL	ORA-06531	Zugriff auf eine nicht initialisierte Collection (TABLE Variable oder VARRAY)	Oracle 8
SELF_IS_NULL	ORA-030625	Methode eines nicht initialisierten Objekts wird aufgerufen	Oracle 8
SUBSCRIPT_BEYOND_COUNT	ORA-06533	Index für Zugriff auf eine Collection (TABLE Variable oder VARRAY) ist nicht vorhanden	Oracle 8
SUBSCRIPT_OUTSIDE_LIMIT	ORA-06532	ungültiger Index bei Zugriff auf eine Collection (TABLE Variable oder VARRAY)	Oracle 8
SYS_INVALID_ROWID	ORA-01410	Zuweisung eines inkompatiblen/nicht konvertierbaren Datentyps zu einem ROWID-Element	Oracle 8
CASE_NOT_FOUND	ORA-06592	In einem CASE konnte kein WHEN ausgewählt werden und ELSE existiert nicht	Oracle 9i

Exceptions gelten immer für den Block in dem sie deklariert sind. Da innerhalb eines Blocks auch mehrere Exceptions auftreten können, ist es möglich, diese hintereinander abzufragen. Mit "others" werden alle bis dahin unbehandelten Exceptions abgefangen.

Beispiel:

  declare
  ..
  begin
  ..
  exception
  when ZERO_DIVIDE then
  ... -- Fehler behandeln
  when others then
  ... -- Fehler behandeln
  end;

mit RAISE können Exceptions erzeugt werden oder auch aus dem aktuellen Block an den umschließenden Block weitergeleitet werden.

  declare
  ..
  begin
  ..
    declare
      lv_zahl NUMBER (10);
    begin
      select into lv_zahl 99 from dual;
      IF lv_zahl = 99
        RAISE INVALID_NUMBER
      END IF;
    exception
      when INVALID_NUMBER
         DBMS_OUTPUT.PUT_LINE("99 gilt nicht!");
         RAISE;
    end;
  ..
  exception
    when INVALID_NUMBER
      DBMS_OUTPUT.PUTLINE (sqlcode||sqlerrm);
  end;

Es wird im inneren Block die Exception abgefangen, mit einer Meldung behandelt und danach an den äußeren Block weitergeleitet. Dort wird durch sqlcode und sqlerrm sowohl die Exception-Nummer als auch der Fehlertext der vordefinierten Exception ausgegeben.

Referenzen

↑ http://download.oracle.com/docs/cd/E11882_01/server.112/e10880/toc.htm

Variablen

Zurück zu " Anonyme Blöcke" |

ORACLE Built-in Datentypen

Text

Bezeichnung	Beschreibung	Deklarationsbeispiel	Anwendungsgebiet	Kommentar
CHAR(size)	Speicherung von Texten fester Länge	CHAR(8)	Codes oder Abkürzungen gleicher Länge	Maximale Größe 32767 Bytes. Default und Minimum sind 1 Byte. Beim Speichern wird der übergebene String mit Leerzeichen bis zur deklarierten Länge aufgefüllt.
NCHAR(size)	Speicherung von Texten fester Länge	NCHAR(8)	Codes oder Abkürzungen gleicher Länge	Ähnlich CHAR in Unicode, maximal 32767/2 bei AL16UTF16 encoding, und 32767/3 bei UTF8.
VARCHAR2(size)	Speicherung von Texten variabler Länge	VARCHAR2(200)	Zeichenketten bis maximal 32767 Byte	Häufig verwendeter Datentyp für Texte - Basisdatentyp. Bei der Deklaration muss eine Längenangabe erfolgen, dies ist die maximale Größe. Bei Vergleichsoperationen wird nur der tatsächlich gespeicherte String verglichen.
NVARCHAR2(size)	Speicherung von Texten variabler Länge	NVARCHAR2(200)	Zeichenketten bis maximal 32767 Byte	Ähnlich VARCHAR2 in Unicode, maximal 32767/2 bei AL16UTF16 encoding, und 32767/3 bei UTF8.
LONG	Speicherung von langen Texten	LONG	Texte bis zu 4 GB oder 2³¹ -1 Bytes.	LONG sollte nicht mehr verwendet werden. Oracle empfiehlt statt dessen LOB zu verwenden, da LONG möglicherweise in Zukunft nicht mehr supported wird.
CLOB	Speicherung von langen Texten	CLOB	Texte bis zu (4 GiBytes - 1)*Blocksize (8, 16, 32, 64 oder 128 TiByte)	Character Large Object speichert lange Texte im Datenbankzeichensatz.
NCLOB	Speicherung von langen Texten	NCLOB	Texte bis zu (4 GiBytes - 1)*Blocksize (8, 16, 32, 64 oder 128 TiByte)	Character Large Object für Unicodezeichen.

Zahlen

Bezeichnung	Beschreibung	Deklarationsbeispiel	Anwendungsgebiet	Kommentar
NUMBER(p,s)	Speicherung von Zahlen mit fest definierter Genauigkeit(p) und Anzahl der Nachkommastellen(s)	NUMBER(22,2)	Währung	Sehr häufig verwendeter Datentyp für alle Zahlenformate - Basisdatentyp. 1 <= p <= 38, -84 <= s <= 127. Wertebereich 1.0 x 10^-130 bis 9.9...9 x 10¹²⁵. Integer können mit NUMBER(p) definiert werden, Fließkommazahlen mit NUMBER.

Datum

Bezeichnung	Beschreibung	Deklarationsbeispiel	Anwendungsgebiet	Kommentar
DATE	Speicherung von Datum und Zeit	DATE	Datum und Zeit (in Sekundengenauigkeit)	Erlaubtes Intervall von 1.1.4712 v.Chr. 31.12.9999 n.Chr..
TIMESTAMP (fractional_seconds_precision)	Zeitstempel	TIMESTAMP(6)	z.B. Audit des Erstellungszeitpunkts eines Datensatzes	Datum mit Tag, Uhrzeit und den spezifizierten Sekundenbruchteilen (0-9)
TIMESTAMP (fractional_seconds_precision) WITH TIME ZONE	Zeitstempel	TIMESTAMP(6)		wie TIMESTAMP mit Abweichung der Zeitzone
TIMESTAMP (fractional_seconds_precision) WITH LOCAL TIME ZONE	Zeitstempel	TIMESTAMP(6)		wie vorhergehendes mit folgenden Ausnahmen: Beim Speichern werden die Daten auf die Zeitzone der Datenbank normalisiert, beim Auslesen auf die Zeitzone des Anwenders.
INTERVAL YEAR (year_precision) TO MONTH	Zeitintervall	INTERVAL YEAR (2) TO MONTH		Zeitintervall in Jahren und Monaten, wobei year_precision die Zahl der Stellen im Jahresfeld des Datums ist (0-9).
INTERVAL DAY (day_precision) TO SECOND (fractional_seconds_precision)	Zeitintervall	INTERVAL DAY (2) TO SECOND (5)		Zeitintervall in Tagen, Stunden, Minuten und Sekunden, wobei day_precision die Maximale Zahl der Ziffern bei den Tagen angibt (0-9). fractional_seconds_precision ist der Sekundenbruchteil (0-9).

Binärdaten

Bezeichnung	Beschreibung	Deklarationsbeispiel	Anwendungsgebiet	Kommentar
RAW (size)	Binärdaten	RAW(256)		Binärdaten mit spezifizierter Länge, maximal 2000 Bytes.
LONG RAW	Binärdaten	LONG RAW		Binärdaten mit variabler Länge. Maximale Größe 2 GB. Oracle empfiehlt statt LONG RAW besser BLOB zu verwenden, da die Verarbeitung weniger Restriktionen unterliegt.
BLOB	Binärdaten	BLOB	z.B. in der Datenbank gespeicherte Worddateien	Maximale Größe bis zu (4 GiBytes - 1)*Blocksize (8, 16, 32, 64 oder 128 TiByte)
BFILE	Verweis auf eine Binärdatei	BFILE	z.B. Verweis auf externe Videodateien	Verweis auf eine Binärdatei, die Außerhalb der Datenbank abgelegt ist. Maximale Größe 8 Terabyte

Sonstige

Bezeichnung	Beschreibung	Deklarationsbeispiel	Anwendungsgebiet	Kommentar
ROWID	Eindeutige Adresse einer Zeile in einer Tabelle	ROWID	z.B. temporäre Kennung eines Datensatzes ohne eindeutige ID	Nicht portable, interne Adresse für einen Datensatz; erhält man beim SELECT durch Angabe der Pseudospalte ROWID.
UROWID [(size)]	Logische Adresse einer Zeile in einer index-organized table.	UROWID		Maximale Größe 4000 Bytes.

PL/SQL Datentypen

Neben den Built-in Datentypen, die auch für die Definition von Tabellen benutzt werden können, können in der Sprache PL/SQL weitere Datentypen verwendet werden.

Zahlen

Bezeichnung	Beschreibung	Deklarationsbeispiel	Anwendungsgebiet	Kommentar
PLS_INTEGER	Ganzzahlen mit einer Größe von -2.147.483.647 bis +2.147.483.647	PLS_INTEGER[()]	Normale Zahlen, Iterationen	Sehr häufig verwendeter Datentyp für Rechenoperationen (Rechnen mit Ganzzahlen), seit Version 10g identisch mit BINARY_INTEGER

Hinzu kommen noch Datentypen, die der Benutzer selbst definieren kann. Hierzu gehören Objekte, die wie in der objektorientierten Programmierung eigene Methoden und Attribute haben, Arrays und Collections.

Datentyp über Spalte festlegen

Variablenname Tabellenname.Spaltenname%type;

Definiert eine Variable des Typs der angegebenen Spalte.

Datentyp über Tabelle festlegen

Variablenname Tabellenname%rowtype;

Definiert eine Variable des Typs der angegebenen Tabelle.

Beispiel :

   CURSOR cursor_name IS
       SELECT *
       FROM tabelle;
   
   variable tabelle%rowtype;
   
   ...
   
   FOR i IN cursor_name LOOP
   
       variable := i;
       andere_variable := variable.SPALTENNAME;
   
   END LOOP;

Benutzerdefinierte Datentypen

Benutzerdefinierte Datentypen werden mit Hilfe von "type datentyp is record(feld1 typ1 :=xyz, feld2 typ2 :=xyz, ..., feldn typn :=xyz);" definiert.

declare

type t_adresse is record(

hausname adresse.hausname%type,

strasse adresse.strasse%type,

hausnummer adresse.hausnummer%type,

postleitzahl adresse.postleitzahl%type);

v_adresse t_adresse;

begin

select hausname,strasse, hausnummer, postleitzahl into v_adresse from adresse where rownum = 1;

end;

Das Beispielprogramm definiert einen eigenen Datentyp mit Namen t_adresse welcher die Felder hausname, strasse, hausnummer und postleitzahl enthält.

Mit diesem Datentyp wird eine Variable v_adresse definiert, welche mit einem Datensatz aus der Tabelle adresse gefüllt wird.

Mittels Punktnotation kann auf die Attribute zurückgegriffen werden "v_adresse.hausname := 'Nollenburgerweg 115';"

DML-Befehle

|

Vor zu " Bedingungen"

Im Anweisungsteil eines PL/SQL-Programms können DML-Befehle ausgeführt werden.

SELECT

Mit dem Select-Befehl werden Daten aus einer Tabelle oder View gelesen. Die Ergebnisse werden in PL/SQL-Variablen gespeichert. Dabei sind grundsätzlich zwei Fälle zu unterscheiden:

1. Es wird ein Select-Befehl verwendet, der entweder keinen oder maximal einen Satz als Ergebnis liefern kann.

2. Ein Select-Befehl kann auch mehrere Sätze als Ergebnis liefern.

Select-Befehl, der maximal einen Satz liefern kann

Select-Befehle, die maximal einen Datensatz lesen, sind meistens Zugriffe über den Primärschlüssel einer Tabelle. Dabei kann man entweder für jede Spalte des Ergebnissatzes eine einzelne Variable angeben oder eine Struktur angeben, in die die gelesenen Werte eingetragen werden.

Beispiel für die Verwendung einzelner Variablen:

Für jede Spalte, die durch das Select-Statement gelesen wird, muss eine Variable bereitgestellt werden.

SET SERVEROUTPUT ON
DECLARE 
nachname varchar2(100);
beruf varchar2(100);
gehalt number;
BEGIN
   SELECT ename, job, sal
   INTO nachname, beruf, gehalt
   FROM scott.emp
   WHERE empno = 7566
   ;
   dbms_output.put_line('Nachname=' || nachname);
   dbms_output.put_line('Beruf=' || beruf);
   dbms_output.put_line('Gehalt=' || to_char(gehalt));
END;
/
SHOW ERRORS

Das Programm gibt folgendes Ergebnis aus:

Nachname=JONES
Beruf=MANAGER
Gehalt=2975

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> SHOW ERRORS
Keine Fehler.

Beispiel für die Verwendung einer Struktur:

SET SERVEROUTPUT ON
DECLARE 
emp_rec scott.emp%ROWTYPE;
BEGIN
   SELECT *
   INTO emp_rec
   FROM scott.emp
   WHERE empno = 7566
   ;
   dbms_output.put_line('Ename=' || emp_rec.ename);
   dbms_output.put_line('Job=' || emp_rec.job);
   dbms_output.put_line('Sal=' || to_char(emp_rec.sal));
END;
/
SHOW ERRORS

Das Programm gibt folgendes Ergebnis aus:

Ename=JONES
Job=MANAGER
Sal=2975

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> show errors
Keine Fehler.

Wenn kein Satz gefunden werden kann, dann wird eine Fehlermeldung ausgegeben.

Select-Befehl, der mehrere Sätze liefern kann

Wenn ein Select-Befehl verwendet wird, der auch mehr als einen einzigen Satz lesen kann, dann gibt es mehrere Möglichkeiten.

1. kann man mit einem Cursor arbeiten. Siehe Kapitel Cursor.

2. man kann auch alle Sätze in eine PL/SQL-Struktur einlesen

Beispiel für einen Bulk-Select

...

INSERT, UPDATE, DELETE

Angenommen, es existiert eine Tabelle t_test mit folgender Definition:

CREATE TABLE t_test (i NUMBER);

Dann kann man die Daten, die in dieser Tabelle gespeichert werden, mit dem folgenden PL/SQL-Programm verändern:

BEGIN
INSERT INTO t_test VALUES(1);
INSERT INTO t_test VALUES(2);
UPDATE t_test SET i = 3;
DELETE FROM t_test;
COMMIT;
END;
/
SHOW ERRORS

Durch den Befehl COMMIT werden die Änderungen endgültig und die geänderten Sätze können auch von anderen Sessions gelesen oder weiter verändert werden.

Man hat auch die Möglichkeit, mit dem Befehl ROLLBACK die letzten Änderungen wieder zurückzunehmen. Das geht aber nur dann, wenn die Änderungen noch nicht mit COMMIT freigegeben wurden.

Vordefinierte Statusvariablen

Es kann sein, dass ein DML-Befehl keinen Satz findet der gelesen, geändert oder gelöscht werden kann. Um sich zu vergewissern, ob ein Satz gefunden wurde, gibt es die vordefinierte Variable sql%rowcount.

SET SERVEROUTPUT ON
BEGIN
dbms_output.put_line('start');
INSERT INTO t_test values(1);
dbms_output.put_line('insert rowcount=' || to_char(sql%rowcount));
INSERT INTO t_test values(1);
dbms_output.put_line('insert rowcount=' || to_char(sql%rowcount));
UPDATE t_test SET i = 3;
dbms_output.put_line('update rowcount=' || to_char(sql%rowcount));
DELETE FROM t_test;
dbms_output.put_line('delete rowcount=' || to_char(sql%rowcount));
COMMIT;
dbms_output.put_line('fertig');
END;
/
SHOW ERRORS

Das Programm ergibt folgende Ausgabe

start
insert rowcount=1
insert rowcount=1
update rowcount=2
delete rowcount=2
fertig
PL/SQL procedure successfully completed.
No errors.

Schleifen

Das Buch PL-SQL ist zurzeit in Überarbeitung! Änderungen an dieser Seite nur in Absprache mit mir.

Wie in jeder Sprache unterstützt auch PL/SQL Schleifen. Um richtig in die Programmierung einzusteigen, wollen wir hier zuallererst zwischen kopf- und fußgesteuerten Schleifen unterscheiden.

Loop --- Until
While
For - Schleifen

Beispiele LOOP --- UNTIL:

(PL/SQL kennt keine REPEAT/UNTIL Konstruktion, stattdessen kann mit Hilfe der EXIT-Anweisung eine Schleife an beliebiger Stelle abgebrochen werden.)

Declare
  x NUMBER := 1;
Begin
  Loop
     DBMS_OUTPUT.put_line( x );
     X := X + 1;
     EXIT WHEN x > 10;
  End Loop;
End;

Die Schleife wird solange ausgeführt, bis die Abbruchbedingung erfüllt ist. Die EXIT Bedingung kann an jeder beliebigen Stelle innerhalb der Schleife verwendet werden. Es ist zu beachten, daß bei unkorrekter Verwendung eine Endlosschleife ausgeführt wird. Wird die EXIT Bedingung am Beginn der Schleife verwendet, entspricht es einer WHILE Schleife.

Lesbarer ist allerdings die 'echte' WHILE Schleife:

Declare
  x NUMBER := 1;
Begin
  While X < 11 Loop
     DBMS_OUTPUT.put_line( x );
     X := X + 1;
  End Loop;
End;

Hier ein Beispiel für eine einfache FOR Schleife:

Declare
  -- Indexvariablen müssen nicht deklariert werden;
  -- bei X handelt es sich immer um eine ganze Zahl.
Begin
  For X IN 1 .. 10 Loop
    DBMS_OUTPUT.put_line( x );
  End Loop;
End;

Die Schleife wird genau 10 mal ausgeführt.

Cursor

Zurück zu " Schleifen " |

Vor zu " Exception-Handling"

Das Buch PL-SQL wird zurzeit überarbeitet. Änderungen an dieser Seite bitte nur in Absprache mit mir.

Cursor sind Zeiger

Cursor ist englisch und bedeutet so viel wie "Zeiger". Im allgemeinen Gebrauch mit dem Computer hat das Wort vor allem zwei Bedeutungen: Einerseits bezeichnet man den Mauszeiger als Cursor und zum anderen den Strich, welcher die Eingabestelle bei Eingebeprogrammen (überall, wo man etwas schreiben kann) markiert.

Im Zusammenhang mit PL/SQL hat er noch eine dritte Bedeutung. Laut Wikipedia steht der Begriff hier eventuell als Abkürzung für Current Set of Records. Das bedeutet, er ist ein temporärer Bereich im Arbeitsspeicher, welcher Informationen über eine spezielle PL/SQL-Anweisung und ihre Ausgabe-Daten speichert und diese auch manipulieren kann. (Als Anweisung bezeichnet man einen Bereich zwischen zwei Semikola.) Wird ein Cursor in der Anweisung wieder geschlossen, gehen auch die in ihm enthaltenen Daten verloren.

In PL/SQL unterscheidet man, je nach Unterscheidungkriterium, insgesamt vier verschiedene Cursor: Das sind einerseits die statischen bzw. dynamischen und andererseits die expliziten bzw. impliziten Cursor. Die Anweisungen eines dynamischen Cursors werden erst zur Laufzeit festgelegt und können für jeden gültigen SQL-Anweisungstyp genutzt werden. Implementiert werden sie über EXECUTE IMMEDIATE-Anweisungen. Ein statischer Cursor hingegen wird bereits zur Kompilierzeit festgelegt und nur für DML-Anweisungen (also SELECT, INSERT, UPDATE, DELETE, MERGE oder SELECT FOR UPDATE) verwendet. Auftauchen können sie in zweierlei Formen: als explizit deklarierte oder implizit eingebettete Cursor.

Implizierter Cursor

Implizite Cursor werden weder deklariert noch per Befehl geöffnet, bearbeitet oder geschlossen. Sie tauchen erst an der Stelle im Ausführungsteil auf, an der sie benötigt werden, und werden danach wieder geschlossen, wodurch die in ihnen enthaltenen Daten wieder gelöscht werden.

Hier ein Beispiel eines impliziten Cursors, mit dem ein einziger Datensatz gelesen werden kann:

Beispiel in einem anonymen Block:

declare
    v_user_id users.id%TYPE;
    v_username users.username%type;
begin
    -- implicit cursor
    SELECT id, username
    INTO v_user_id, v_username
    FROM users
    WHERE username='test';
    
    dbms_output.PUT_LINE('----------------------------');
    dbms_output.PUT_LINE('User-ID: '||v_user_id);
    dbms_output.PUT_LINE('Username: '||v_username);
    dbms_output.PUT_LINE('----------------------------');
end;

Wenn man dabei ein SELECT-Statement angibt, das mehr als einen Satz als Ergebnis hat, wird eine Fehlermeldung ausgegeben, da nicht klar ist, welcher der gefundenen Datensätze in die Variable geschrieben werden soll.

Für SELECT-Statements, die mehr als einen Ergebnis-Satz liefern, kann die folgende Konstruktion verwendet werden:

begin
    -- implicit cursor mit FOR
    FOR rec_users in ( SELECT id, username, firstname, lastname FROM users ) LOOP
        dbms_output.PUT_LINE('User-ID: '||rec_users.id);
        dbms_output.PUT_LINE('Username: '||rec_users.username);
        dbms_output.PUT_LINE('Firstname: '||rec_users.firstname);
        dbms_output.PUT_LINE('Lastname: '||rec_users.lastname);
        dbms_output.PUT_LINE('----------------------------');
    END LOOP;
end;

Expliziter Cursor

Explizite Cursor sind, wie der Name bereits erahnen lässt, im Deklarationsabschnitt deklariert. Dies kann ohne Parameter, mit Argumenten als Parameterliste oder auch mit einer RETURN-Klausel geschehen. Im Ausführungs- bzw. Exceptionabschnitt operiert man mit ihnen mithilfe der Befehle OPEN, FETCH und CLOSE zum Öffnen, Auslesen und Schließen. Dabei sind explizite Cursor die einzigen, die auch mehrzeilige Ausgaben machen können.

Hier ein Beispiel dazu in einem anonymen Block:

declare  
    CURSOR cur_users IS
        SELECT id, username, firstname, lastname
        FROM users;
    rec_users cur_users%ROWTYPE;
begin
    -- explicit cursor
    OPEN cur_users;
    LOOP
        FETCH cur_users INTO rec_users;
        EXIT WHEN cur_users%NOTFOUND;
    
        dbms_output.PUT_LINE('User-ID: '||rec_users.id);
        dbms_output.PUT_LINE('Username: '||rec_users.username);
        dbms_output.PUT_LINE('Firstname: '||rec_users.firstname);
        dbms_output.PUT_LINE('Lastname: '||rec_users.lastname);
        dbms_output.PUT_LINE('----------------------------');
    END LOOP;
end;

Statusvariablen für Cursor

In einer Cursor-FOR-Schleife kann man die Anzahl der bereits gelesenen Sätze mit der Statusvariablen ROWCOUNT abfragen. Diese Variable ist jedoch nur im Inneren der Schleife gültig. Wenn man sie außerhalb der Schleife verwendet, dann wird ein Syntaxfehler ausgegeben.

Beispiel:

SET SERVEROUTPUT ON
DECLARE
CURSOR c IS
SELECT table_name 
FROM all_tables
WHERE ROWNUM <= 10
;
BEGIN
dbms_output.put_line('start');
-- gibt Fehler:
-- dbms_output.put_line('vor for i cursor rowcount=' || to_char(c%rowcount));
FOR i IN c LOOP
   dbms_output.put_line('cursor rowcount=' || to_char(c%rowcount));
END LOOP;
-- gibt Fehler:
-- dbms_output.put_line('nach end loop cursor rowcount=' || to_char(c%rowcount));
dbms_output.put_line('fertig');
END;
/
SHOW ERRORS

Das Programm hat folgende Ausgabe:

start
cursor rowcount=1
cursor rowcount=2
cursor rowcount=3
cursor rowcount=4
cursor rowcount=5
cursor rowcount=6
cursor rowcount=7
cursor rowcount=8
cursor rowcount=9
cursor rowcount=10
fertig
PL/SQL procedure successfully completed.
No errors.

Rowcount bei einem Cursor ausgeben

Die auskommentierten Zeilen ergeben eine Fehlermeldung, wenn sie ausgeführt werden sollen.

set serveroutput on
declare
cursor c is
select table_name 
from all_tables
where rownum <= 1
;
name varchar2(100);
begin
dbms_output.put_line('start');
--dbms_output.put_line('c%found=' || case when c%found then 'TRUE' else 'FALSE' end);
--dbms_output.put_line('c%notfound=' || case when c%notfound then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%isopen=' || case when c%isopen then 'TRUE' else 'FALSE' end);
--dbms_output.put_line('c%rowcount=' || to_char(c%rowcount));
open c;
dbms_output.put_line('open c');
dbms_output.put_line('c%found=' || case when c%found then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%notfound=' || case when c%notfound then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%isopen=' || case when c%isopen then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%rowcount=' || to_char(c%rowcount));
fetch c into name;
dbms_output.put_line('fetch c');
dbms_output.put_line('c%found=' || case when c%found then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%notfound=' || case when c%notfound then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%isopen=' || case when c%isopen then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%rowcount=' || to_char(c%rowcount));
fetch c into name;
dbms_output.put_line('fetch c');
dbms_output.put_line('c%found=' || case when c%found then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%notfound=' || case when c%notfound then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%isopen=' || case when c%isopen then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%rowcount=' || to_char(c%rowcount));
fetch c into name;
dbms_output.put_line('fetch c');
dbms_output.put_line('c%found=' || case when c%found then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%notfound=' || case when c%notfound then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%isopen=' || case when c%isopen then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%rowcount=' || to_char(c%rowcount));
close c;
dbms_output.put_line('close c');
--dbms_output.put_line('c%found=' || case when c%found then 'TRUE' else 'FALSE' end);
--dbms_output.put_line('c%notfound=' || case when c%notfound then 'TRUE' else 'FALSE' end);
dbms_output.put_line('c%isopen=' || case when c%isopen then 'TRUE' else 'FALSE' end);
--dbms_output.put_line('c%rowcount=' || to_char(c%rowcount));
dbms_output.put_line('fertig');
end;
/
show errors

Das Programm hat folgende Ausgabe:

start
c%isopen=FALSE
open c
c%found=FALSE
c%notfound=FALSE
c%isopen=TRUE
c%rowcount=0
fetch c
c%found=TRUE
c%notfound=FALSE
c%isopen=TRUE
c%rowcount=1
fetch c
c%found=FALSE
c%notfound=TRUE
c%isopen=TRUE
c%rowcount=1
fetch c
c%found=FALSE
c%notfound=TRUE
c%isopen=TRUE
c%rowcount=1
close c
c%isopen=FALSE
fertig
PL/SQL procedure successfully completed.
No errors.

Exception-Handling

Zurück zu " Cursor " |

Vor zu " Prozedur "

Das Buch PL-SQL wird zurzeit überarbeitet! Änderungen an dieser Seite bitte nur in Absprache mit mir.

Vordefinierte Exceptions

Eine Auswahl an Exceptions. Eine vollständige Liste steht unter ^[1] zur Verfügung.

Name	Fehlercode	Beschreibung	Vorhanden ab
CURSOR_ALREADY_OPEN	ORA-06511	Cursor ist bereits geöffnet	Oracle 7
DUP_VAL_ON_INDEX	ORA-00001	INSERT oder UPDATE eines Wertes, der bereits vorhanden ist, in einer als UNIQUE deklarierten Spalte	Oracle 7
INVALID_CURSOR	ORA-01001	Der Cursor ist nicht deklariert (existiert nicht) oder wurde nicht geöffnet	Oracle 7
INVALID_NUMBER	ORA-01722	Fehler bei impliziter Typkonvertierung, z.B. String enthält keinen numerischen Wert wird aber in NUMBER Spalte/Variable eingefügt bzw. übergeben	Oracle 7
LOGIN_DENIED	ORA-01017	Benutzername oder Passwort falsch	Oracle 7
NO_DATA_FOUND	ORA-01403	SELECT liefert kein Ergebnis: keine Daten wurden durch einen impliziten Cursor gefunden oder ein Index in einem Assoziativen Array existiert nicht	Oracle 7
NOT_LOGGED_ON	ORA-01012	Keine Verbindung zur Datenbank	Oracle 7
PROGRAM_ERROR	ORA-06501	Interner PL/SQL - Fehler	Oracle 7
ROWTYPE_MISMATCH	ORA-06504	Strukturvariablen inkompatibel	Oracle 7
STORAGE_ERROR	ORA-06500	Speicherprobleme	Oracle 7
TIMEOUT_ON_RESOURCE	ORA-00051	Datenbank-Sperre	Oracle 7
TOO_MANY_ROWS	ORA-01422	SELECT liefert mehr als eine Zeile	Oracle 7
VALUE_ERROR	ORA-06502	Fehler beim Arbeiten mit "Werten" in Variablen/Tabellen/Prozeduren, z.B. zu große Werte, Verletzung von Constraints, Fehler bei Typkonvertierungen (außer INVALID_NUMBER - Exception)	Oracle 7
ZERO_DIVIDE	ORA-01476	bei Division durch 0	Oracle 7
ACCESS_INTO_NULL	ORA-06530	Zuweisung an ein nicht initialisiertes Objekt	Oracle 8
ACCESS_INTO_NULL	ORA-06531	Zugriff auf eine nicht initialisierte Collection (TABLE Variable oder VARRAY)	Oracle 8
SELF_IS_NULL	ORA-030625	Methode eines nicht initialisierten Objekts wird aufgerufen	Oracle 8
SUBSCRIPT_BEYOND_COUNT	ORA-06533	Index für Zugriff auf eine Collection (TABLE Variable oder VARRAY) ist nicht vorhanden	Oracle 8
SUBSCRIPT_OUTSIDE_LIMIT	ORA-06532	ungültiger Index bei Zugriff auf eine Collection (TABLE Variable oder VARRAY)	Oracle 8
SYS_INVALID_ROWID	ORA-01410	Zuweisung eines inkompatiblen/nicht konvertierbaren Datentyps zu einem ROWID-Element	Oracle 8
CASE_NOT_FOUND	ORA-06592	In einem CASE konnte kein WHEN ausgewählt werden und ELSE existiert nicht	Oracle 9i

Exceptions gelten immer für den Block in dem sie deklariert sind. Da innerhalb eines Blocks auch mehrere Exceptions auftreten können, ist es möglich, diese hintereinander abzufragen. Mit "others" werden alle bis dahin unbehandelten Exceptions abgefangen.

Beispiel:

  declare
  ..
  begin
  ..
  exception
  when ZERO_DIVIDE then
  ... -- Fehler behandeln
  when others then
  ... -- Fehler behandeln
  end;

mit RAISE können Exceptions erzeugt werden oder auch aus dem aktuellen Block an den umschließenden Block weitergeleitet werden.

  declare
  ..
  begin
  ..
    declare
      lv_zahl NUMBER (10);
    begin
      select into lv_zahl 99 from dual;
      IF lv_zahl = 99
        RAISE INVALID_NUMBER
      END IF;
    exception
      when INVALID_NUMBER
         DBMS_OUTPUT.PUT_LINE("99 gilt nicht!");
         RAISE;
    end;
  ..
  exception
    when INVALID_NUMBER
      DBMS_OUTPUT.PUTLINE (sqlcode||sqlerrm);
  end;

Es wird im inneren Block die Exception abgefangen, mit einer Meldung behandelt und danach an den äußeren Block weitergeleitet. Dort wird durch sqlcode und sqlerrm sowohl die Exception-Nummer als auch der Fehlertext der vordefinierten Exception ausgegeben.

Referenzen

↑ http://download.oracle.com/docs/cd/E11882_01/server.112/e10880/toc.htm

Prozedur

Das Buch PL-SQL wird zurzeit überarbeitet! Änderungen an dieser Seite bitte nur in Absprache mit mir.

Einstieg

Prozeduren sind nützlich, um regelmäßig wiederkehrende Arbeitsabläufe zu automatisieren z.B. um Installationsarbeiten durchzuführen.

Als Einstieg soll eine Prozedur dienen, die nur aus zwei Befehlen besteht.

CREATE OR REPLACE PROCEDURE spins IS
BEGIN
   INSERT INTO tdept (deptno, deptname, mgrno, admrdept)
   VALUES ('A00', 'SPIFFY COMPUTER DIV.', '000010', 'A00');
   INSERT INTO tdept (deptno, deptname, mgrno, admrdept)
   VALUES ('B01', 'PLANNING            ', '000020', 'A00');
END;
/

Die Procedur wird im SQLPLUS aufgerufen mit dem Befehl:

EXECUTE spins;

Innerhalb eines PL/SQL-Scripts wird die Procedur nur durch Angabe ihres Namens aufgefufen.

BEGIN
   spins;
END;
/

Parameterübergabe

Wenn man der Prozedur Parameter übergeben will, dann wird unterschieden in:

IN: Die aufrufende Umgebung übergibt einen Wert an die Prozedur. Dieser Parameter kann innerhalb der Verarbeitung nicht verändert werden. IN ist der default Parameter-Modus
OUT: Die aufrufende Umgebung übergibt eine Variable an die Prozedur, die innerhalb der Prozedur als nicht initialisiert betrachtet wird. Wenn innerhalb der Verarbeitung diesem Parameter ein Wert zugewiesen wird, dann wird dieser an die aufrufende Umgebung zurückgegeben.
IN OUT: Die Aufrufende Umgebung übergibt eine Variable an die Prozedur. Diese kann innerhalb der Prozedur verwendet werden und auch geändert werden. Der geänderte Wert wird der aufrufenden Umgebung mitgeteilt.

Einfache Prozedur, die einen Parameter übergeben bekommt. Dieser Parameter wird durch den Befehl put_line wieder ausgegeben. Um die Ausgabe zu aktivieren, muss erst der SQL*Plus - Befehl 'set serveroutput on' ausgeführt werden.

set serveroutput on
CREATE OR REPLACE PROCEDURE myfirstproc(v IN VARCHAR2)
IS
BEGIN
   dbms_output.put_line(v);
END;
/

Es ist auch möglich, Werte von Prozeduren verändern zu lassen:

CREATE OR REPLACE PROCEDURE mysecondproc(param1 IN OUT NUMBER)
IS
BEGIN
   Param1 := Param1 + 42;
END;
/

Wird dieses zweite Beispiel verwendet, liefert der entsprechende Test:

set serveroutput on
DECLARE
  x NUMBER := 13;
BEGIN
   DBMS_OUTPUT.PUT_LINE( x );
   mysecondproc( x );
   DBMS_OUTPUT.PUT_LINE( x );
END;
/

wie zu erwarten, werden die Werte 13 und 55 ausgegeben.

Funktion

Das Buch PL-SQL wird zurzeit überarbeitet! Änderungen an dieser Seite bitte nur in Absprache mit mir.

Neben den vielen Funktionen, die die SQL-Sprache schon bietet, kann man eigene Funktionen definieren. Funktionen haben immer einen oder mehrere Input-Parameter und genau einen Ergebniswert.

Hier ein ganz einfaches Beispiel einer Funktion, die einen String als Input erwartet und einen String als Ergebnis liefert. Die Verarbeitung der Funktion besteht darin, den Input-Parameter um ein 'x' zu erweitern.

CREATE OR REPLACE FUNCTION myfunction (in_parm VARCHAR2) RETURN VARCHAR2 IS
BEGIN
   RETURN in_parm || 'x';
END;
/

Diese Funktion kann man nun genauso verwenden wie die anderen bereits vordefinierten Funktionen. Man muss nur darauf achten, dass man die richtige Anzahl an Parametern angibt und die passenden Datentypen wählt. Beispielaufruf:

SELECT loc, myfunction(loc) 
FROM scott.dept;

Ergebnis:

LOC           MYFUNCTION(LOC)
------------- ---------------
NEW YORK      NEW YORKx
DALLAS        DALLASx
CHICAGO       CHICAGOx
BOSTON        BOSTONx

In dem Anweisungsteil der Funktion können beliebig komplexe PL/SQL-Anweisungen angegeben werden. Ein Beispiel für eine etwas komplexere Funktion:

CREATE OR REPLACE FUNCTION anzma (abteilung VARCHAR2)
RETURN INTEGER IS
h_anzahl INTEGER;
BEGIN
   SELECT COUNT(*) INTO h_anzahl
   FROM ben01.templ
   WHERE workdept = abteilung;
   RETURN h_anzahl;
END;
/

Funktionen kann man gut testen, indem man sie in einem SELECT einsetzt, der aus der Tabelle dual liest. Diese Tabelle enthält genau einen Satz mit einer Spalte. Eigentlich ist man aber an diesem Satz gar nicht interessiert, sondern man will durch diesen SELECT nur bewirken, dass die Funktion genau einmal aufgerufen wird. Aufrufbeispiel:

SELECT anzma('A00') 
FROM dual;

Ergebnis:

ANZMA('A00')
------------
           3

Hinweise:

Die 1. Funktion ist deterministisch d.h. wenn man die Funktion zwei mal mit dem selben Eingabe-Parameter aufruft, dann liefert sie immer den selben Output.
Die 2. Funktion ist nicht-deterministisch d.h. ihr Ergebnis ist auch von anderen Informationen abhängig, als nur den übergebenen Parametern. Wenn jemand die Sätze in der Tabelle ben01.templ ändert, dann kann die Funktion anzma bei gleichem Eingabe-Parameter ein anderes Ergebnis liefern.

Einführung1

Zurück zu " PL/SQL Einleitung " |

Vor zu " Anonymer Block "

Mit der Oracle Funktion DBMS_OUTPUT.PUT_LINE können Zeichenketten ausgegeben werden, die nicht das Ergebnis einer SQL-Abfrage sind. Allerdings dürfen die Zeichenketten nicht länger als 255 Zeichen sein, sonst kommt es zu einem Abbruch. Wir werden noch eine Funktion vorstellen, die auch längere Zeichenketten ausgeben kann, aber vorerst müssen wir mit 255 Zeichen vorlieb nehmen. In der Oracle 10g Version sollte die Funktion auch mehr Zeichen ausgeben können. Als Einstieg wollen wir nun in SQL*PLUS unsere erste Ausgabe erhalten.

Vorher nicht vergessen in SQL*PLUS auf

SET SERVEROUTPUT ON

zu stellen. Das ist einer der häufigsten Dinge, die vergessen werden und einen beim Testen zur Verzweiflung bringen.

In PL*SQL würde unser Beispiel 'Hello World' ungefähr so aussehen

Listing: HelloWorld.SQL

BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello World') ;
END;

Das war doch gar nicht so schwer, oder?

Vorgehensweise in SQL*PLUS

Es empfiehlt sich die Anlage einer Datei z.B. mit dem Namen helloworld.plsql:

  create or replace PROCEDURE helloworld is
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello World');
  END;
  /

Ein Aufruf von sqlplus nach dem Schema:

  sqlplus user/password @helloworld.plsql

kompiliert die Prozedur. Eventuelle Fehlermeldungen können in SQL*PLUS mit

  SQL> show err;

eingesehen werden.

Der Aufruf der Prozedur kann dann mit

  SQL> set serverout on;
  SQL> begin helloworld; end;
  SQL> /

erfolgen.

Literatur

|