Einführung in die PL/SQL-Programmierung

Grundsätzlich kann man einen PL/SQL-Block in drei Abschnitte unterteilen.

1. Deklarationsabschnitt
   Als erstes erfolgt die Deklaration von Variablen, Konstanten etc.
2. Ausführbarer Abschnitt
   Als zweites werden die entsprechenden Befehle in den ausführbaren Abschnitt eingetragen.
3. Exceptionsabschnitt
   Als letztes haben Sie einen Abschnitt mit Exceptions. Hier wird eine Ausnahmebehandlung durchgeführt.

Wir wollen die drei Abschnitte anhand eines kleinen Beispiels verdeutlichen. Die Syntax der hier verwendeten Befehle und Kontrollstrukturen wird in den weiteren Kapiteln genauer beschrieben.

Anonymer Block

-- Deklarationsabschnitt
declare
   v_zahl1 number(5) := 5;
   v_zahl2 number(5) DEFAULT 5;

-- Ausführbarer Abschnitt & Exception	
begin
   select count(*) into anzahl from scott.emp; 
   dbms_output.put_line(anzahl);
   -- Exception-Abschnitt
   Exception
     when too_many_rows then
     dbms_output.put_line('Sie haben zu viele Werte.');
       when others then
     dbms_output.put_line('unbekannte Ausnahmeverletzung.');
end;

Nach begin folgt der ausführbare Teil des Blockes. Hier wird zuerst die Ausgabe per dbms_output aktiviert.

Es gibt viele Datensätze in der Tabelle scott.emp. Somit wird die Exception too_many_rows aufgerufen. Diese Exception wird immer dann aufgerufen, wenn mit Hilfe einer SELECT-Anweisung mehr als ein Wert in eine Variable geladen werden soll. Nach dieser Exception wird der Block beendet. Es gibt neben der Exception too_many_rows natürlich noch weitere Exceptions, die später behandelt werden.

Sie können gern dieses kleine Programm testen. Speichern Sie die Datei unter beispielsweise ~/test/t1.sql ab und rufen Sie dieses Programm unter sqlplus mit dem Befehl start ~/test/t1.sql auf.

Variablendeklaration

Neben den üblichen Variablen gibt es bei PL/SQL noch weitere Datentypen, die beim Erstellen eines Blockes benutzt werden können.

Boolean		Boolsche Variable mit den Zuständen TRUE oder FALSE.
Binary_integer		Zahlen von -2147483647 bis +2147483647.
natural		Zahlen von 0 bis +2147483647.
positive		Zahlen von 1 bis +2147483647.
%Type		Zuweisung von Datentypen äquivalent zum Datentyp
		einer Spalte.
%rowtype		Zusammengesetzter Datentyp, äquivalent zu einer
		Tabellenzeile.

Deklaration einer Variable mit %type

Mit dem Datentype %type können Sie einer Variablen den gleichen Datentyp zuweisen, den eine bestehende Tabellenspalte besitzt. Dies ist hilfreich, wenn bestimmte Variablen mit Werten aus einer Tabelle versehen werden sollen. Sie müssen sich in diesem Fall keine Gedanken mehr um den Datentyp dieser Variablen machen. Sie nehmen einfach für diese Variable den Datentyp, den die Spalte, deren Wert in die Variable geladen werden soll, besitzt.

Variable Tabellenname.Spalte\%type

Deklaration einer Variable mit %rowtype

Mit dem Datentype %rowtype können Sie in einer Variablen alle Spalten einer Tabelle speichern und diese anschließend abrufen. Die entsprechenden Spalteninhalte sind mit Variablenname.Spaltenname abrufbar.

Variable Tabellenname\%rowtype

IF ... THEN ... ELSE

Oracle PL/SQL bietet die Möglichkeit, Bedingungen zu formulieren und in Abhängigkeit des Wahrheitsgehaltes dieser Bedingungen einen Block auszuführen oder nicht auszuführen.

Folgendes Beispiel soll den Aufbau einer IF THEN ELSE-Anweisung darstellen: Durch IF anzahl <= 10 then ... wird überprüft, ob die Variable anzahl kleiner oder gleich 10 ist. Wenn diese Bedingung wahr ist, so wird der Block ausgeführt, der sich dem THEN befindet. In unserem Beispiel ist das die Anweisung dbms_output.put_line('Wenig'). Diese Anweisung gibt lediglich die Zeichenkette Wenig auf dem Bildschirm aus.

Ist die Bedingung 'anzahl kleiner oder gleich 10' falsch, so geht Oracle zum nächsten ELSIF (Achtung: Nicht ELSEIF!!). Durch ELSIF anzahl <= 20 then ... wird überprüft, ob die Variable anzahl kleiner oder gleich 20 ist. Wenn diese Bedingung wahr ist, so wird der Block ausgeführt, der sich dem THEN befindet.

In unserem Beispiel ist das die Anweisung dbms_output.put_line('Mittel'). Diese Anweisung gibt lediglich die Zeichenkette Mittel auf dem Bildschirm aus. Ist die Bedingung 'anzahl kleiner oder gleich 20' falsch, so führt Oracle den Block aus, der sich nach dem ELSE befindet. In unserem Falle also dbms_output.put_line('viel')

IF Bedingung THEN
  Anweisungen;
ELSIF Bedingung THEN
  Anweisungen;
ELSIF Bedingung THEN
  Anweisungen;
  ...
ELSE
  Anweisungen;
END IF;

If anzahl < 100 then
	dbms_output.put_line('Wenig');
Elsif anzahl between 100 and 200 then 	
      dbms_output.put_line('Mittel'); 	
Else
 	dbms_output.put_line('Viel'); 	
End If;

LOOP ... END LOOP

Eine LOOP ... END LOOP-Schleife ohne EXIT-Kriterium ist eine Endlosschleife. Die Anweisungen werden unendlich wiederholt. Da Endlosschleifen in der Praxis eher selten benötigt werden, wird ein EXIT-Kriterium eingesetzt.

Loop
  exit when Bedingung;
End Loop;

Im folgenden Beispiel wird als EXIT-Kriterium keine IF-Bedingung benutzt, sondern eine EXIT WHEN-Anweisung. Wenn die Variable Zaehler größer als 10 ist, so wird die LOOP ... END LOOP-Schleife verlassen. Es wird zur Anweisung hinter dem END LOOP gesprungen und dort wird ganz normal fortgefahren.

-- Vorher muss natürlich a deklariert worden sein.
Loop
      	exit when a=10;
      	a:=a+1;
      	dbms_output.put_line(a);
End Loop;

WHILE LOOP ... END LOOP

Anders als bei der normalen LOOP ... END LOOP-Anweisung ist das EXIT-Kriterium bei der WHILE LOOP ... END LOOP-Anweisung bereits über das WHILE integriert. Die Schleife wird so lange durchlaufen, wie die Variable Zaehler kleiner oder gleich 10 ist. So bald die Variable Zaehler größer als 10 ist, wird die Schleife beendet und es wird mit der Anweisung hinter END LOOP fortgefahren.

WHILE Bedingung LOOP
  Anweisungen;
END LOOP;

-- Vorher muss natürlich a deklariert worden sein:
While a<>11 Loop
   dbms_output.put_line(a);
   a:=a+1;
End Loop;

FOR ... IN .. LOOP ... END LOOP

Durch eine FOR LOOP ... END LOOP-Schleife wird ein Block n-mal durchlaufen. Die Anzahl der Durchläufe wird durch die Grenzwerte hinter IN definiert. Im folgenden Beispiel wird die Schleife demnach 11 mal durchlaufen. Begonnen wird bei 0, anschließend wird hochgezählt und wenn die Variable zaehler den Wert 10 erreicht hat, wird die Schleife beendet.

FOR Zählvariable IN untere_Grenze .. obere_Grenze LOOP
  Anweisungen;
END LOOP;

-- x muss nicht deklariert werden:
For x In 1 .. 10 Loop
    dbms_output.put_line(x);
End Loop;

Erstellen einer Prozedur

CREATE PROCEDURE name [ ( parameter [, parameter ] ) ] 
IS
   -- Deklarationsteil
   BEGIN
   -- Programmteil
   EXCEPTION
   -- Ausnahmebehandlung
   END;
;

set serveroutput on;
create or replace procedure p2(zae_beg int,zae_end int) 
is
anzahl number(5) := 1;
a int:=1;
  begin
    for x in zae_beg .. zae_end loop
      select count(*) into anzahl from sys.products where supplierid=x; 
      dbms_output.put_line(anzahl);
    end loop;
  Exception
    when too_many_rows then
      dbms_output.put_line('Sie haben zu viele Werte');
      when others then
        dbms_output.put_line('unbekannte Ausnahmeverletzung');
  end;
/
show errors;
-- Aufrufen mit exec p2(1,10)

Tipps und Tricks

Dynamisches Erstellen von SQL-Anweisungen (Für DDL etc.)

EXECUTE IMMEDIATE 'CREATE TABLE X(A DATE)';

Fehlerbehandlung

begin
  	select bezeichnung into v_bezeichnung
    from einheit
    where einheit_kurz = 'm';
  	if v_bezeichnung <> 'Meter' then
    raise EINHEIT_FEHLER;
  	end if;
	exception when EINHEIT_FEHLER then
  	...
end;

Beispiele

Erstellen eines Cursors (Beispiel 1)

DECLARE
emp_id CHAR(9);
FNAME VARCHAR(12);
LNAME VARCHAR(20);
CURSOR CUR1 IS
SELECT employeeid, firstname, lastname
FROM employees ORDER BY employeeid;
BEGIN
    OPEN CUR1;
    FETCH CUR1 INTO emp_id, FNAME, LNAME;
    WHILE (CUR1%FOUND) LOOP
    FETCH CUR1 INTO emp_id, FNAME, LNAME;
    END LOOP;
CLOSE CUR1;
END;

Erstellen eines Cursors (Beispiel 2)

SET SERVEROUTPUT ON 
DECLARE CURSOR video_cursor 
IS 
  SELECT title, mpaa_rating FROM video 
    WHERE hit_status = 1 
    ORDER BY title; 
  BEGIN 
    FOR video_rec 
      IN video_cursor 
      LOOP 
        DBMS_OUTPUT.PUT_LINE (video_rec.title || ',' || video_rec.mpaa_rating); 
      END LOOP; 
END; 
/

Übungen 09

Übungen siehe Seite .