Logo Oracle Deutschland   Application Express Community
10 SQL Tipps (nicht nur) für APEX Entwickler
Erscheinungsmonat APEX-Version Datenbankversion
Januar 2015 alle ab 10.2

Mit SQL arbeitet man als APEX Entwickler jeden Tag. Sei es die SQL-Abfrage als Datenquelle in einem Bericht, Diagramm oder Kalender oder sei es ein DML-Kommando in einem PL/SQL Prozess: SQL ist überall. Und auch SQL hat sich in den mehr als 30 Jahren, seit es die Oracle-Datenbank gibt, enorm weiterentwickelt. Alle Möglichkeiten im Kopf zu behalten ist gar nicht so einfach - daher haben wir für Sie 10 SQL-Tipps zusammengestellt. Schauen Sie mal hinein - vielleicht ist etwas dabei, was Sie noch nicht kannten ...

Tipp #1: Zeilen ohne Tabelle generieren

Mit einem SQL SELECT lesen Sie normalerweise die Zeilen einer Tabelle. Doch was ist, wenn Sie keine Tabelle haben, aber dennoch eine Anzahl Zeilen brauchen? Nützlich könnte das sein, wenn Sie eine Datumsliste generieren möchten. Sie müssen nicht eigens eine Tabelle erzeugen und Zeilen speichern; auch das explizite Anlegen einer Table-Function ist nicht nötig. Nein, es geht viel einfacher. 

select 'Zeile '||level
from dual
connect by level <= 15;

'ZEILE'||LEVEL
-----------------------------------------
Zeile 1
Zeile 2
Zeile 3
:
Zeile 14
Zeile 15

15 Zeilen ausgewählt.

Eine Datumsliste, bspw. mit den nächsten 10 Montagen sieht dann so aus. 

select next_day(sysdate, 'MONDAY') + (level - 1) * 7 DATUM_MONTAG
from dual 
connect by level <= 10;

DATUM_MONTAG
-----------------------------------------
22.12.2014
29.12.2014
05.01.2015
12.01.2015
:
09.02.2015
16.02.2015
23.02.2015

10 Zeilen ausgewählt.

Zurück zum Anfang

Tipp #2: Joins müssen nicht "=" sein. Tage zwischen zwei Datum finden

Zumindest bis zum Erscheinen von APEX 5.0 bleibt es ein Thema, mit dem man sich bei der Nutzung des APEX-Kalenders auseinandersetzen muss. Angenommen, in der Tabelle gibt es zwei Datumsspalten (BEGINN und ENDE) und man möchte damit eine APEX-Kalenderregion erzeugen. Diese sieht aber nur eine Datumsspalte vor - wie bildet man also das mehrtägige Ereignis ab? 

create table ereignisse (
  ereignis       varchar2(200),
  datum_beginn   date,
  datum_ende     date
);

insert into ereignisse values ('APEX für Kenner', DATE'2015-01-15', DATE'2015-01-17');
insert into ereignisse values ('Oracle12c',       DATE'2015-01-16', DATE'2015-01-23');
 
commit;

Ganz einfach: Wir nutzen den Tipp #1 und erzeugen eine Inline-View mit allen Tagen eines Monats (oder Jahres). Diese wird dann mit der Tabelle gejoint. Dabei wird aber kein EQUAL-Join verwendet, sondern einer mit BETWEEN. Zusätzlich wird noch auf die Arbeitstage Montag bis Freitag eingegrenzt. 

with days_month as (
  select DATE'2015-01-01' + (level - 1) as day
  from dual 
  connect by level <= 90
)
select 
  e.ereignis,
  d.day tag
from days_month d, ereignisse e
where d.day between e.datum_beginn and e.datum_ende
and to_char(d.day, 'FMDAY','nls_date_language=''ENGLISH''') in ('MONDAY','TUESDAY','WEDNESDAY','THURSDAY','FRIDAY')
/

EREIGNIS             TAG
-------------------- -------------------
APEX für Kenner      15.01.2015
APEX für Kenner      16.01.2015
Oracle12c            16.01.2015
Oracle12c            19.01.2015
Oracle12c            20.01.2015
Oracle12c            21.01.2015
Oracle12c            22.01.2015
Oracle12c            23.01.2015

Ohne das Anlegen einer Funktion oder Table-Function können Sie so auch mehrtägige Ereignisse in einem APEX-Kalender darstellen,

Zurück zum Anfang

Tipp #3: Arbeiten mit Monaten - aber richtig

Dass man eine Anzahl Monate zu einem Datum addieren muss, kommt immer wieder vor. Diese so einfache Aufgabe hat aber ihre Tücken, wie das folgende Beispiel zeigt. Zunächst zum ganz einfachen Fall: Wir addieren einen Monat zum 10.01.2015 - nach SQL Standard. 

select DATE'2015-01-10' + INTERVAL '1' MONTH from dual;

DATE'2015-01-10'+IN
-------------------
10.02.2015 00:00:00

So weit - so gut. Versuchen Sie das nun mal mit dem 30. Januar. 

select DATE'2015-01-30' + INTERVAL '1' MONTH from dual;

                        *
FEHLER in Zeile 1:
ORA-01839: Datum für angegebenen Monat nicht gültig

SQL hat versucht, den 30. Februar zu generieren - das ist in der "Endkontrolle" dann aber doch aufgeflogen. Besser ist es, in diesen Fällen mit der guten alten Oracle-SQL-Funktion ADD_MONTHS zu arbeiten ... 

select add_months(DATE'2015-01-10', 1) from dual;

ADD_MONTHS(DATE'201
-------------------
10.02.2015 00:00:00

select add_months(DATE'2015-01-30', 1) from dual;

ADD_MONTHS(DATE'201
-------------------
28.02.2015 00:00:00

select add_months(DATE'2015-01-30', -1) from dual;

ADD_MONTHS(DATE'201
-------------------
30.12.2014 00:00:00

Zurück zum Anfang

Tipp #4: Einen wirklich eindeutigen Wert generieren

Die SQL-Funktion SYS_GUID() tut das für Sie - wie folgt. 

select sys_guid() from dual;

SYS_GUID()
--------------------------------
0A54251C4ACF6D56E053E2F4A50A6F6C

SYS_GUID liefert Ihnen einen Wert vom Typ RAW(16) zurück; also eine 16 Byte lange Folge, zu der die Datenbank garantiert, dass diese global eindeutig ist. Bevor Sie nun jedoch alle Ihre Primärschlüssel von Sequences auf SYS_GUID umstellen, beachten Sie bitte, dass ein SYS_GUID immer 16 Byte braucht, während ein NUMBER mit wesentlich weniger auskommt. Außerdem ist ein NUMBER-Schlüssel im Zweifel vom Menschen lesbar - was während der Anwendungsentwicklung von Vorteil sein kann.

Zurück zum Anfang

Tipp #5: Analytische Funktionen nutzen

Zum Thema Analytische SQL Funktionen gibt es bereits zahlreiche Blog Postings und auch einen Community-Tipp. Analytische Funktionen sind aber nicht nur interessant, wenn es um Durchschnitte, Statistik oder Datenanalysen geht. Auch so einfache Dinge wie der "Zugriff auf die nächste oder vorherige Zeile einer Ergebnismenge" können mit analytischen Funktionen elegant gelöst werden. So die Frage, wie groß - in der Tabelle EMP - der Gehaltsabstand zum jeweils nächsthöheren Gehalt ist. 

select deptno, ename, sal, lead(sal, 1) over (partition by deptno order by sal asc) - sal abstand 
from emp
order by deptno, sal;

    DEPTNO ENAME             SAL    ABSTAND
---------- ---------- ---------- ----------
        10 MILLER           1300       1150
        10 CLARK            2450       2550
        10 KING             5000
        20 SMITH             800        300
        20 ADAMS            1100       1875
        20 JONES            2975         25
        20 SCOTT            3000          0
        20 FORD             3000
        30 JAMES             950        300
        30 WARD             1250          0
        30 MARTIN           1250        350
        30 ALLEN            1600

Zurück zum Anfang

Tipp #6: Nutzen Sie das Data Dictonary

Nutzen Sie die Möglichkeiten der Datenbank so weit es geht aus. Das bedeutet im einzelnen ...

  • Bilden Sie referentielle Integrität mit Fremdschlüsselbeziehungen ab
  • Nutzen Sie Check-Constraints für die fachliche Datenintegrität
  • Setzen Sie Eindeutigkeit mit einem Unique-Constraint durch - verlassen Sie sich nicht auf die Anwendung
  • Nutzen Sie das COMMENT-Kommando, um zusätzliche Informationen im Data-Dictionary zu hinterlegen
  • Nutzen Sie Hilfetext in APEX-Anwendungen
  • :

Dies hat zwei Aspekte: Zunächst geht es natürlich um die Funktionalität - der Check-Constraint auf einer Tabelle stellt absolut sicher, dass nur gültige Daten eingegeben werden; unabhängig von der Anwendung. Zum zweiten lässt sich dies aber sehr gut zum automatischen Erzeugen einer Dokumentation nutzen - die folgende Abbildung wurde mit nichts als einem APEX-Bericht generiert.

Simple APEX-Anwendung zur Dokumentation von Tabellen und deren Spalten

Simple APEX-Anwendung zur Dokumentation von Tabellen und deren Spalten

Das Berichts-SQL ist denkbar einfach ... 

select c.column_id, c.column_name, c.data_type, cc.comments 
from all_tab_columns c, all_col_comments cc
where c.table_name = cc.table_name and c.column_name = cc.column_name
and c.table_name = :P2_TABLE_NAME
order by c.column_id

Wenn Sie sowohl das Dictionary der Datenbank als auch das APEX-Dictionary konsequent nutzen, ist das Bereitstellen einer stets aktuellen und vollständigen Dokumentation überhaupt kein Problem mehr.

Zurück zum Anfang

Tipp #7: Views und DML

In der Oracle-Datenbank ist jede View grundsätzlich updatefähig - das können Sie leicht selbst feststellen; erzeugen Sie wie folgt eine View und machen Sie ein SQL UPDATE auf dieselbe. 

create or replace view emp_dept10 as
select * from emp where deptno = 10;

select * from emp_dept10;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09.06.1981 00:00:00       2450                    10
      7839 KING       PRESIDENT            17.11.1981 00:00:00       5000                    10
      7934 MILLER     CLERK           7782 23.01.1982 00:00:00       1300                    10

update emp_dept10 set deptno=20 where empno=7782;

1 Zeile wurde aktualisiert.

select * from emp_dept10;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17.11.1981 00:00:00       5000                    10
      7934 MILLER     CLERK           7782 23.01.1982 00:00:00       1300                    10

Das geht nur mit eher einfachen Views - sobald die Definition komplexer wird, schafft Oracle es nicht mehr, ein auf die View abgesetztes DML-Kommando auf die Basistabellen zurückzuführen - und vielfach ist dies sogar unmöglich. In diesen Fällen kann man eine View mit einem INSTEAD-OF Trigger selbst updatefähig machen - das bedeutet jedoch Programmierarbeit.

Oft ist jedoch auch das oben gezeigte Verhalten nicht erwünscht - schließlich mutet es seltsam an, wenn eine Zeile durch ein SQL UPDATE auf einmal aus der View verschwindet. Es gibt daher zusätzlich Schlüsselworte, mit denen das Update-Verhalten einer View gesteuert werden kann.

  • WITH CHECK OPTION:
    Legt fest, dass durch DML veränderte oder neu eingefügte Zeilen zur View-Definition passen müssen. 
    create or replace view emp_dept10 as
select * from emp where deptno = 10
WITH CHECK OPTION;

select * from emp_dept10;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09.06.1981 00:00:00       2450                    10
      7839 KING       PRESIDENT            17.11.1981 00:00:00       5000                    10
      7934 MILLER     CLERK           7782 23.01.1982 00:00:00       1300                    10

update emp_dept10 set deptno=20 where empno=7782;

       *
FEHLER in Zeile 1:
ORA-01402: Verletzung der WHERE-Klausel einer View WITH CHECK OPTION
  • WITH READ ONLY:
    Legt fest, dass diese View nicht updatefähig sein soll. 
    create or replace view emp_dept10 as
select * from emp where deptno = 10
WITH READ ONLY;

select * from emp_dept10;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09.06.1981 00:00:00       2450                    10
      7839 KING       PRESIDENT            17.11.1981 00:00:00       5000                    10
      7934 MILLER     CLERK           7782 23.01.1982 00:00:00       1300                    10

update emp_dept10 set deptno=20 where empno=7782;

       *
FEHLER in Zeile 1:
ORA-42399: DML-Vorgang kann auf schreibgeschützter View nicht ausgeführt werden.

Zurück zum Anfang

Tipp #8: Benutzerfehler mit Flashback Query beheben

Bereits seit der Version 9 der Oracle-Datenbank kann Flashback Query genutzt werden, um frühere Versionen einer Tabellenzeile abzurufen. Die Daten der früheren Versionen werden aus dem UNDO-Tablespace geholt, wo sie ohnehin vorgehalten, um die normale Lesekonsistenz sicherzustellen (bekanntlich zeigt Oracle, wenn eine Tabellenzeile verändert und noch kein COMMIT abgesetzt wurde, die letzte Version vor dieser Änderung an).

Die Informationen im UNDO-Tablespace werden zyklisch überschrieben; bei "normalen" Produktionsdatenbanken können Sie davon ausgehen, dass Informationen zu den letzten 5 Minuten bereitstehen; auf stark belasteten System mit nur knapp bemessenem UNDO-Tablespace vielleicht noch nicht mal das.

Sie können Flashback Query einfach mit der AS OF-Klausel wie folgt nutzen. 

select * from emp where deptno = 10;

EMPNO ENAME      JOB         MGR HIREDATE              SAL  COMM DEPTNO
----- ---------- --------- ----- ------------------- ----- ----- ------
 7782 CLARK      MANAGER    7839 09.06.1981 00:00:00  2450           10
 7839 KING       PRESIDENT       17.11.1981 00:00:00  5000           10
 7934 MILLER     CLERK      7782 23.01.1982 00:00:00  1300           10

delete from emp where deptno = 10;

3 Zeilen gelöscht.

commit;

Transaktion mit COMMIT abgeschlossen.

select * from emp 
as of timestamp systimestamp - interval '1' MINUTE
where deptno = 10;

EMPNO ENAME      JOB         MGR HIREDATE              SAL  COMM DEPTNO
----- ---------- --------- ----- ------------------- ----- ----- ------
 7782 CLARK      MANAGER    7839 09.06.1981 00:00:00  2450           10
 7839 KING       PRESIDENT       17.11.1981 00:00:00  5000           10
 7934 MILLER     CLERK      7782 23.01.1982 00:00:00  1300           10

3 Zeilen ausgewählt.

APEX nutzt Flashback Query an vielen Stellen - die folgenden Abbildungen zeigen einige Beispiele.

Flashback Query in APEX: Interaktive Berichte

Flashback Query in APEX: Interaktive Berichte

Flashback Query in APEX: Export

Flashback Query in APEX: Export

Mit Flashback Query können Sie Ihre Endanwender in die Lage versetzen, etwaige Fehler (so sie schnell bemerkt werden) selbst zu korrigieren.

Zurück zum Anfang

Tipp #9: VARCHAR Quote Syntax

Bekanntlich wird in der Datenbank das einfache Anführungszeichen (') zur Begrenzung von VARCHAR-Literalen verwendet. Wenn man ein solches als Teil des Textes haben möchte, muss man es doppeln. 

select 'Der Ku''damm ist in Berlin' as TEXT from dual;

TEXT
-------------------------
Der Ku'damm ist in Berlin

Insbesondere, wenn man mit einer SQL-Anweisung oder in einer PL/SQL-Prozedur wiederum Code generiert, kann dies aber schnell unübersichtlich werden ... 

select 'select ''Der Ku''''damm ist in Berlin'' as TEXT from dual;' as SQL from dual;

SQL
------------------------------------------------------
select 'Der Ku''damm ist in Berlin' as TEXT from dual;

Die alternative Quote-Syntax kann hier weiterhelfen. Sie wird mit q' eingeleitet, dann kommt ein frei wählbares Begrenzungszeichen (welches möglichst nicht im Text vorkommen sollte). Abgeschlossen wird der String mit dem gewählten Begrenzungszeichen und dem einfachen Anführungszeichen. Nimmt man den "Lattenzaun" (#) als Begrenzungszeichen, dann sieht das so aus. 

select q'#Der Ku'damm ist in Berlin#' as TEXT from dual;

TEXT
-------------------------
Der Ku'damm ist in Berlin

Man kann auch unterschiedliche Delimiter kombinieren ... 

select q'§select q'#Der Ku'damm ist in Berlin#' as TEXT from dual;§' as SQL from dual;

SQL
--------------------------------------------------------
select q'#Der Ku'damm ist in Berlin#' as TEXT from dual;

Die Les- und Wartbarkeit von Code kann allein damit massiv erhöht werden.

Zurück zum Anfang

Tipp #10: Bleiben Sie Up-to-Date

Die Fähigkeiten der Datenbank, was SQL und PL/SQL angeht, erweitern sich ständig. Neue Funktionen werden mittlerweile nicht nur mit den Major-Releases wie 11.2.0.1 oder 12.1.0.1, sondern auch mit Patchsets wie 11.2.0.2, 11.2.0.3 oder 12.1.0.2 eingeführt. Eins der wichtigsten Handbücher der Oracle-Dokumentation ist daher der New Features Guide, der alle neuen Funktionen enthält und ständig aktualisiert wird. Auch enthält jedes einzelne Handbuch (wie bspw. die SQL Language Reference) ein Kapitel New Features.

Bleiben Sie daher am Ball - am besten schaut man regelmäßig in die New Features Handbücher hinein; dann sind Sie immer Up-to-Date und kennen sich mit den Möglichkeiten der Datenbank aus.

Zurück zur Community-Seite