Insbesondere in Geschäftsberichten wird häufig die Darstellung als Kreuztabelle gewünscht. 

Abbildung 1 zeigt den Unterschied zwischen einem normalen Application Express-Bericht und der Darstellung als Kreuztabelle. In der Kreuztabelle werden die Daten im Grunde genommen "gekippt". Wie man einen Application Express-Bericht als Kreuztabelle darstellt, haben Sie bereits in der letzten Ausgabe erfahren. Lesen Sie heute, wie Sie diese Kreuztabelle änderbar machen; der Endanwender kann die Daten in der Tabelle also ändern - die Applikation ändert jedoch die Daten in der (in Abbildung 1 links dargestellten) Original-Tabelle (hier UMSATZ).

Editierbare Berichte bietet Application Express als tabellarische Formulare eigentlich out-of-the-box an. Allerdings erwartet das "normale" tabellarische Formular, dass die Daten in der Form dargestellt werden, wie sie in der Tabelle vorliegen. Mit der Kreuztabelle, welche die Daten ja in "gekippter" Form darestellt, kann es nicht umgehen.

Allerdings kann man das mit ein wenig Programmierung selbst erledigen. Die reine Darstellung der Daten als Kreuztabelle ist ja bereits fertig - das Ergebnis der letzten Ausgabe ist Ausgangspunkt für heute. Als nächste Schritte werden Sie nun ...

• Eingabefelder in der Kreuztabelle darstellen, damit der Endanwender Daten eingeben kann.
• Einen PL/SQL-Prozeß schreiben, der die eingebenen Daten richtig in die Originaltabelle einfügt.
• Eine "Sicherung" einbauen, so dass der Anwender eine Warnung bekommt, wenn ein anderer Nutzer die gleichen Daten zwischenzeitlich geändert hat (Optimistisches Locking)

Navigieren Sie also zu den Berichtsattributen und dort zu den Attributen der einzelnen Spalten der Kreuztabelle (Abbildung 2).

Navigieren Sie nun in die Spaltenattribute der einzelnen Spalten der Kreuztabelle und gehen Sie in den Bereich Tabellarisches Form-Element (Abbildung 3). Wählen Sie für die Spalte PRODUKT, also die ganz linke Spalte der Kreuztabelle den Wert Als Text anzeigen (hat Speicherstatus) bei Anzeigen als aus. Dadurch wird die Spalte wie bisher dargestellt; Sie haben aber im PL/SQL-Prozeß später Zugriff auf die Inhalte.

Wählen Sie für die nachfolgenden Datenspalten den Wert Textfeld bei Anzeigen als aus und geben Sie die Elementbreite nach Ihren Wünschen ein ("10" meint 10 Zeichen). Damit wird das Feld im Bericht editierbar.

Klicken Sie dann auf Änderungen anwenden und verfahren Sie mit den anderen Datenspalten analog. Wenn Sie die Seite danach neu starten, sollte sie in etwa wie in Abbildung 4 aussehen.

Nun fehlen noch Schaltflächen, um die gemachten Änderungen zu speichern. Fügen Sie also eine neue Schaltfläche hinzu. Erzeugen Sie eine Schaltfläche, die in einer Regionsposition angezeigt wird und welche die Seite weiterleitet (Abbildung 5). Das Template und die Position können Sie nach Ihren Vorstellungen aussuchen - nach Klick auf die Schaltfläche soll auf die gleiche Seite (hier: Seite 1) zurückverzweigt werden.

Nun geht's ans Eingemachte: Wenn auf die neue Schaltfläche geklickt wird, sollen die Daten gespeichert werden. Dazu benötigen Sie etwas PL/SQL-Code. Erzeugen Sie also einen neuen PL/SQL-Prozeß, welcher beim Weiterleiten der Seite (onSubmit) durch die soeben erzeugte Schaltfläche APPLY_CHANGES ausgeführt werden soll.

Innerhalb des PL/SQL-Code haben Sie durch die vordefinierten Arrays APEX_APPLICATION.G_F01 bis APEX_APPLICATION.G_F50 Zugriff auf die Elemente eines tabellarischen Formulars - dabei wird auf der linken Seite mit dem Zählen begonnen. Die Spalte PRODUKTE (die benötigen Sie beim späteren UPDATE in der Originaltabelle für die WHERE-Klausel) befinden sich also in APEX_APPLICATION.G_F01 und die fünf Datenspalten sind in den Arrays APEX_APPLICATION.G_F02 bis APEX_APPLICATION.G_F06. Hinterlegen Sie für den PL/SQL-Prozeß also folgenden PL/SQL Code.

declare
  v_datum date;
begin
  -- Wir beginnen mit dem im Element P1_START_DATUM enthaltenen
  -- Startdatum; die erste Datenspalte der Kreuztabelle enthält
  -- eben dieses Datum.
  v_datum := to_date(:P1_START_DATUM, 'DD.MM.YYYY');

  -- Die Schleife läuft über die Elemente des Arrays; das sind
  -- die Zeilen des tabellarischen Formulars. In diesem Beispiel
  -- sind das also die Produkte.
  for i in apex_application.g_f01.first..apex_application.g_f01.last loop
    
    -- MERGE-Anweisung für die fünf Datenspalten; MERGE wird verwendet,
    -- da bei einem leeren Feld gar keine Zeile für dieses Produkt und
    -- Datum vorhanden ist. Wenn also im tabellarischen Formular ein
    -- Wert in ein leeres Feld einträgt, muss das zu einem SQL INSERT
    -- führen, wenn ein Wert geändert wird, muss ein SQL UPDATE ausgelöst
    -- werden. Für die Umsetzung einer solchen Anforderung ist MERGE
    -- gedacht.
    merge into umsatz dest
    using (
      select apex_application.g_f01(i) produkt, v_datum datum, apex_application.g_f02(i) umsatz 
      from dual union all (
       select apex_application.g_f01(i) produkt, v_datum + 1 datum, apex_application.g_f03(i) umsatz
       from dual 
      ) union all (
       select apex_application.g_f01(i) produkt, v_datum + 2 datum, apex_application.g_f04(i) umsatz
       from dual 
      ) union all (
       select apex_application.g_f01(i) produkt, v_datum + 3 datum, apex_application.g_f05(i) umsatz
       from dual 
      ) union all (
       select apex_application.g_f01(i) produkt, v_datum + 4 datum, apex_application.g_f06(i) umsatz
       from dual
      )
    ) src
    on (src.produkt = dest.produkt and src.datum = dest.datum)
    -- Wenn die Tabellenzeile schon da ist ...
    when matched then 
      -- Umsatz ändern ... (Update)
      update set dest.umsatz = src.umsatz 
      -- und wenn der Umsatz NULL (also das Feld leer ist), dann Zeile löschen!
      delete where src.umsatz is null
    when not matched 
      -- Wenn die Tabellenzeile noch nicht da ist, INSERT!
      then insert (produkt, datum, umsatz) 
      values (src.produkt, src.datum, src.umsatz)
      -- aber nur, wenn der Umsatz NOT NULL ist, also das Feld nicht leer war.
      where src.umsatz is not null;
  end loop;
end;

Der PL/SQL Code sieht nur auf den ersten Blick kompliziert aus: Da die Kreuztabelle fünf Datenspalten hat (die eingegebenen Daten also in den Arrays APEX_APPLICATION.G_F02 bis APEX_APPLICATION.G_F06 enthalten sind), muss für jedes dieser Arrays ein MERGE-Statement im Code angegeben werden. Das SQL MERGE ist nötig, da für leere Eingabefelder noch gar keine Zeile in der Originaltabelle existiert - sie muss also eingefügt (INSERT) werden. Für geänderte Werte wird ein UPDATE durchgeführt. Wenn Sie sich die einzelnen MERGE-Kommandos ansehen, erkennen Sie in der USING-Klausel, dass das Datum stets um einen Tag erhöht wird und jeweils das nächste Array angesprochen wird.

Probieren Sie das tabellarische Formular nun aus, indem Sie einige Änderungen vornehmen - man erkennt, dass auch die Daten in der links dargestellten Originaltabelle entsprechend geändert werden.

Fehlt nur noch eines: Wenn ein anderer Nutzer in der Zeit zwischen dem Laden des Formulars und dem Speichern der Änderungen eigene Änderungen an den gleichen Daten durchführt, werden diese durch den PL/SQL-Prozeß einfach überschrieben - das ist jedoch in vielen Fällen nicht gewünscht. Im folgenden werden wir daher das Formular so erweitern, dass es vor dem Speichern der Änderungen feststellt, ob die Daten zwischenzeitlich von einer anderen Stelle aus geändert wurden.

Fügen Sie Ihrer Applikation dazu eine neue Validierung auf Seitenebene hinzu. Die Validierung soll als PL/SQL-Funktion, die booleschen Wert zurückgibt erfolgen - der PL/SQL-Code stellt also fest, ob die Daten zwischenzeitlich geändert wurden und liefert true zurück, wenn alles in Ordnung ist und false, wenn Daten geändert wurden - der Anwender sich die Daten also nochmals ansehen sollte.

Application Express hat beim Laden der Seite bereits MD5-Checksummen für alle Zeilen des tabellarischen Formulars berechnet. Diese befinden sich im Array APEX_APPLICATION.G_FCS. Die Validierung erfolgt nun beim Absenden des Formulars: Die Prüfsummenberechnung muss nochmals durchgeführt und die MD5-Checksummen der einzelnen Zeilen müssen mit denen im Array APEX_APPLICATION.G_FCS verglichen werden. Sind alle Prüfsummen gleich, wurden die Daten zwischenzeitlich nicht geändert, der vorhin erstellte PL/SQL-Prozeß kann loslaufen. Bei Unterschieden soll eine Fehlermeldung angezeigt werden.

Der PL/SQL-Code für die Validierung hängt von der Art und Weise ab, wie Sie die Kreuztabelle erstellt haben. Das folgende Beispiel basiert auf der Version "vor Oracle11g" und der Funktion getPivotSQL(). Diese Funktion wird nun genau wie für die Kreuztabelle aufgerufen; das zurückgegebene SQL wird allerdings nochmal von einem Aufruf auf WWV_FLOW_ITEM.MD5 umschlossen. Das so ermittelte SQL wird mit EXECUTE IMMEDIATE aufgerufen, so dass eine Liste mit Prüfsummen für die Zeilen der Kreuztabelle zurückgegeben wird. Diese wird in der anschließenden Schleife mit den Summen im Array APEX_APPLICATION.G_FCS verglichen; sobald in Unterschied auftritt, wird das Ergebnis der Funktion auf false gesetzt.

declare
  v_cks wwv_flow_global.vc_arr2;
  v_sql varchar2(32767);
  v_ok  boolean:=true;
begin
  v_sql := 'select apex_item.md5(produkt, "VALUE[1]", "VALUE[2]", "VALUE[3]", "VALUE[4]", "VALUE[5]") cks '||
           'from ( ' || 
           getPivotSql(
            'select u.produkt, datum, u.umsatz ' ||
            'from umsatz u '||
            'partition by (produkt) right outer join datum d using (datum) '||
            'where datum between ' ||
            '  to_date('''||:P1_START_DATUM||''', ''DD.MM.YYYY'') and ' ||
            '  to_date('''||:P1_START_DATUM||''', ''DD.MM.YYYY'') + 4',
            'DATUM',
            'DATE',       
            'to_date('''||:P1_START_DATUM||''', ''DD.MM.YYYY'')',
            1,
            'DAY',
            5,      
            'PRODUKT',
            'UMSATZ',
            'SUM' 
           ) ||
           ')';
  execute immediate v_sql bulk collect into v_cks;

  if v_cks.count != apex_application.g_fcs.count then 
    v_ok := false;
  else 
    for i in 1..v_cks.count loop
      if apex_application.g_fcs(i) != v_cks(i) then
        v_ok := false;
      end if;
    end loop;
  end if;
  return v_ok;
end;

Wenn Sie auf einer Oracle11g Datenbank arbeiten und die neue PIVOT-Klausel für Ihre Kreuztabelle verwendet haben, muss auch der PL/SQL-Code anders aussehen. Im Grunde genommen ist es aber dasselbe. Nur das zugrundeliegende SQL für die Kreuztabelle ist ein anderes.

declare
  v_cks wwv_flow_global.vc_arr2;
  v_sql varchar2(32767);
  v_ok  boolean:=true;
begin
  select wwv_flow_item.md5(produkt, tag_1, tag_2, tag_3, tag_4, tag_5) cks 
  bulk collect into v_cks
  from (
    with umsatzdaten as (
      select 
        u.produkt,
        datum,
        u.umsatz
      from umsatz u
      partition by (produkt) right outer join datum d using (datum)
      where datum between to_date(:P1_START_DATUM, 'DD.MM.YYYY') and to_date(:P1_START_DATUM ,'DD.MM.YYYY') + 4
    )
    select 
      produkt,
      tag_1,  
      tag_2,
      tag_3,
      tag_4,
      tag_5
    from umsatzdaten 
    pivot (
      sum(umsatz) for datum in (
        to_date('&P1_START_DATUM.', 'DD.MM.YYYY') as "TAG_1", 
        to_date('&P1_START_DATUM.', 'DD.MM.YYYY') + 1 as "TAG_2",
        to_date('&P1_START_DATUM.', 'DD.MM.YYYY') + 2 as "TAG_3",
        to_date('&P1_START_DATUM.', 'DD.MM.YYYY') + 3 as "TAG_4",
        to_date('&P1_START_DATUM.', 'DD.MM.YYYY') + 4 as "TAG_5"
      )
    )
  );

  if v_cks.count != apex_application.g_fcs.count then 
    v_ok := false;
  else 
    for i in 1..v_cks.count loop
      if apex_application.g_fcs(i) != v_cks(i) then
        v_ok := false;
      end if;
    end loop;
  end if;
  return v_ok;
end;

Probieren Sie es nun aus: Starten Sie die Seite und ändern Sie in der Kreuztabelle sichtbaren Daten der Tabelle UMSATZ dann in SQL*Plus oder im SQL Workshop (Commit nicht vergessen). Nehmen Sie dann im Formular Änderungen vor und senden Sie die Seite ab. Sie sollten dann eine Fehlermeldung bekommen (Abbildung 7)

Abb. 7: Fehlermeldung, wenn die Inhalte der Kreuztabelle bereits von andere Seite geändert wurden

Damit ist die änderbare Kreuztabelle fertig. Sie können Daten mit der Oracle Datenbank und Application Express also nicht nur pivotieren (kippen), sondern diese Tabelle auch noch ändern und die Änderungen direkt auf die Originaltabelle durchreichen.

Zurück zur Community-Seite