Oracle PL/SQL Tutorial: SQL
Einbettung von SQL in PL/SQL

Prinzip

Da PL/SQL die prozedurale Erweiterung von SQL darstellt, können in PL/SQL fast alle Möglichkeiten von SQL benutzt werden. Alle SQL- DML - Befehle (select, insert, update, delete) sind direkt in PL/SQL verfügbar. Dabei dienen PL/SQL - Variablen dem Datenaustausch zwischen SQL und PL/SQL. Weiterhin ist die Verwendung der Befehle zur Transaktionssteuerung (commit, rollback ...) möglich.

DDL - Befehle müssen über dynamisches SQL durch Verwendung der Standard Package DBMS_SQL realisiert werden.

Alle SELECT-Befehle müssen eine INTO-Klausel enthalten, die definiert, welche Variablen das Ergebnis aufnehmen sollen.

Weitere Details sind auch in dem Buch Oracle PL/SQL Programmierung zu finden.

Beispiel:

declare

  v_bezeichnung einheit.BEZEICHNUNG%TYPE;

begin

  select bezeichnung into v_bezeichnung
    from einheit
    where einheit_kurz = 'm';

     ...

  insert into einheit (einheit_kurz, bezeichnung)
    values ('l', 'Liter');

  ...

  update auftrag set
    hinweis = hinweis || 'Neuer Artikel ' || v_bezeichnung;

end;

Cursor

Definition 

Cursor sind Datenstrukturen im Arbeitsspeicher, die für die Abarbeitung von SQL - Befehlen benötigt werden. Für jeden SQL - Befehl wird automatisch ein entsprechender Speicherbereich allokiert (impliziter Cursor). Für SELECT - Befehle, die mehrere Zeilen liefern, muß ein expliziter Cursor verwendet werden.

Beispiel:

declare

  v_summe        number := 0;

  v_pos_preis    number;

  cursor c_auftrag_pos is
    select anzahl*preis from auftrag_pos;

begin

  OPEN c_auftrag_pos;

  loop

    FETCH c_auftrag_pos into v_pos_preis;

    exit when c_auftrag_pos%NOTFOUND;

    v_summe := v_summe + v_pos_preis;

  end loop;

  CLOSE c_auftrag_pos;

end;

Cursor - Parameter

Beispiel:

declare

  v_summe       number := 0;

  v_pos_preis   number;

  cursor c_auftrag_pos
    (p_auftrag_nr in auftrag_pos.auftrag_nr%TYPE) is
    select anzahl*preis
      from auftrag_pos
      where auftrag_nr = p_auftrag_nr;

begin

  OPEN c_auftrag_pos (4711);

  loop

    FETCH c_auftrag_pos into v_pos_preis;

    exit when c_auftrag_pos%NOTFOUND;

    v_summe := v_summe + v_pos_preis;

  end loop;

  CLOSE c_auftrag_pos;

end;

Cursor - Strukturvariablen

Beispiel:

declare

  v_summe          number := 0;

  v_auftrag_pos    auftrag_pos%ROWTYPE;

  cursor c_auftrag_pos
    (p_auftrag_nr in auftrag_pos.auftrag_nr%TYPE) is
    select *
      from auftrag_pos
      where auftrag_nr = p_auftrag_nr;

begin

  OPEN c_auftrag_pos (4711);

  loop

    FETCH c_auftrag_pos into v_auftrag_pos;

    exit when c_auftrag_pos%NOTFOUND;

    v_summe := v_summe + v_auftrag_pos.anzahl *
                          v_auftrag_pos.preis;

  end loop;

  CLOSE c_auftrag_pos;

end;

Cursor - Attribute 

Jeder Cursor hat vier Statusattribute, auf die mit cursor%ATTRIBUT zugegriffen wird.

Bei impliziten Cursorn erfolgt der Zugriff mit SQL%ATTRIBUT. %OPEN steht bei impliziten Cursorn nicht zur Verfügung.

Um den aktuellen Datensatz eines expliziten Cursors mit einem UPDATE-Befehl zu ändern, kann man die CURRENT OF - Klausel in der WHERE-Bedingung verwenden. Beispiel siehe unten.

%FOUND

• Gibt an, ob der letzte FETCH - Befehl einen Satz gefunden hat => TRUE
• Vor dem ersten Fetch NULL

%NOTFOUND

• Gibt an, ob der letzte FETCH - Befehl einen Satz gefunden hat => FALSE
• Vor dem ersten Fetch NULL

%ROWCOUNT

• Liefert die Anzahl der mit FETCH gelesenen Zeilen
• Vor dem ersten FETCH auf 0

%ISOPEN

• Gibt an, ob ein Cursor geöffnet ist

CURRENT OF

declare

  v_auftrag    auftrag%ROWTYPE;

  cursor c_auftrag
    (p_auftrag_nr in auftrag_pos.auftrag_nr%TYPE) is
    select *
      from auftrag
      where auftrag_nr = p_auftrag_nr;
      for update of status;

begin

  OPEN c_auftrag (4711);

  loop

    FETCH c_auftrag into v_auftrag;

    exit when c_auftrag_pos%NOTFOUND;

    if v_auftrag.status = 'A' then

      bearbeite_auftrag (v_auftrag);

      update auftrag set status = 'E'
        where current of c_auftrag;

    end if;

  end loop;

  CLOSE c_auftrag;

end;

Cursor - FOR - Schleifen 

Durch eine Cursor - FOR - Schleife wird ein Cursor automatisch geöffnet, alle Sätze der Ergebnismenge des zugehörigen SELECT - Befehls gelesen und der Cursor wieder geschlossen. Die Befehle OPEN, FETCH und CLOSE sind dann nicht notwendig.

Die Schleifenvariable hat den Recordtyp des Cursors.

Beispiel 1:

declare

  v_summe    number := 0;

  cursor c_auftrag_pos
    (p_auftrag_nr in auftrag_pos.auftrag_nr%TYPE) is
    select *
      from auftrag_pos
      where auftrag_nr = p_auftrag_nr;

begin

  for v_auftrag_pos in c_auftrag_pos(4711) loop

    v_summe := v_summe + v_auftrag_pos.anzahl *
                         v_auftrag_pos.preis;

  end loop;

end;

Beispiel 2:

declare

  v_summe number := 0;

begin

  for v_auftrag_pos in (select * from auftrag_pos) loop

    v_summe := v_summe + v_auftrag_pos.anzahl *
                         v_auftrag_pos.preis;

  end loop;

end;