Präsentation "Fortgeschrittene SQL

Fortgeschrittene SQL-Techniken
für APEX-Formulare und Reports
Andreas Wismann
WHEN OTHERS
Beratung | Projektmanagement | Coaching
rund um Oracle Application Express
… rund um Application Express
Beratung | Projektmanagement | Coaching
Andreas Wismann
[email protected]
+49 176 7800 3109
"Kleinigkeiten"
1. Quote Char Syntax
2. WITH
3. UNION vs. UNION ALL
Quote Char Syntax
l_sql :=
'insert into MY_TBL values(''123'')';
• Quote Char Syntax q'[…]'
erspart das Escapen (Verdoppeln) von Anführungszeichen:
l_sql :=
l_sql :=
l_sql :=
l_sql :=
l_sql :=
…
q'[insert
q'{insert
q'<insert
q'\insert
q'#insert
into
into
into
into
into
MY_TBL
MY_TBL
MY_TBL
MY_TBL
MY_TBL
values('123')]';
values('123')}';
values('123')>';
values('123')\';
values('123')#';
9i
WITH
• Subqueries zu Beginn des SQL-Statements
definieren
• Entwirrt komplexes SQL
• Optimizer Hint
/*+ materialize */ kann die Leistung steigern
9i
WITH
WITH
query_1 AS (SELECT … FROM … WHERE … GROUP BY …),
query_2 AS (SELECT … FROM … WHERE … GROUP BY …),
query_3 AS (SELECT … FROM … WHERE … GROUP BY …)
SELECT …
FROM query_1, query_2, query_3
WHERE …
9i
WITH
query_1 AS (SELECT
query_2 AS (SELECT
query_3 AS (SELECT
SELECT …
FROM my_table
JOIN query_1 ON …
JOIN query_2 ON …
JOIN query_3 ON …
WHERE query_1.spalte
WITH
… FROM … WHERE … GROUP BY …),
… FROM … WHERE … GROUP BY …),
… FROM … WHERE … GROUP BY …)
… = …
12c
WITH
WITH
FUNCTION add_number (num1 NUMBER, num2 NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN num1 + num2;
END;
SELECT add_number(1, 2)
FROM dual;
UNION
A B C
SELECT … FROM … WHERE …
UNION
SELECT … FROM … WHERE …
A B C D
C D
SELECT … FROM … WHERE …
UNION ALL
SELECT … FROM … WHERE …
A B C C D
• UNION ALL verzichtet auf die Verdichtung und liefert alle Zeilen
• UNION ALL ist dadurch performanter
SQL Best Practices in APEX
• Views anstatt Tabellen verwenden
• Daten und Formatierung trennen (SQL, HTML)
HTML in SQL
• Trennung von Logik und Präsentation …
SELECT ''
|| empno || '' AS empno
, ''
|| ename || '' AS ename
, dname
FROM emp
NATURAL JOIN dept
WHERE dname in ('ACCOUNTING', 'SALES')
HTML in SQL
• View für die Selektion der richtigen Daten
CREATE OR REPLACE FORCE VIEW
P250_emp_view
SELECT empno
, ename
, dname
FROM emp
NATURAL JOIN dept
WHERE dname in ('ACCOUNTING', 'SALES')
HTML in SQL
• View für die Formatierungen
CREATE OR REPLACE FORCE VIEW
P250_emp_view_f
SELECT ''
|| empno || '' AS empno
, ''
|| ename || '' AS ename
, dname
FROM P250_emp_view
Im APEX-Reportfenster:
SELECT *
FROM P250_emp_view_f
HTML in SQL
• Spätere Änderungen nicht ausgeschlossen …
CREATE OR REPLACE FORCE VIEW
P250_emp_view_f
SELECT empno
, ename
, CASE dname
WHEN 'SALES'
THEN ''
|| dname || ''
ELSE dname
END AS dname
FROM P250_emp_view
Views anstatt Tabellen
• Generelles Ziel im APEX-SQL-Fenster:
SELECT *
FROM my_view
• Durchgehende View-Schicht für Reports, LOVs, …
• Namens-Konvention!
 Statuskontrolle, Wartbarkeit, Testbarkeit
Formulare über Prozeduren
Beispieltabelle
CREATE TABLE umsaetze
region
NUMBER NOT
jahr
NUMBER NOT
monat
NUMBER NOT
umsatz
NUMBER,
(
NULL,
NULL,
NULL,
PRIMARY KEY (region, jahr, monat)
)
Table API
• SQL Workshop > Utilities > Methods on Tables
• erzeugt Package mit DML-Routinen
Table API
API erzeugen
Table API
• Eigener "Dispatcher" mit IN OUT - Parametern
PROCEDURE IO_UMSAETZE
(
p_aktion
IN VARCHAR2
-- GET, INS, UPD, DEL
,p_region
IN OUT NUMBER
,p_jahr
IN OUT NUMBER
,p_monat
IN OUT NUMBER
,p_umsatz
IN OUT NUMBER
,p_MD5
IN OUT VARCHAR2
)
Table API
Demo
CASE upper(p_aktion)
WHEN
WHEN
WHEN
WHEN
'GET'
'INS'
'UPD'
'DEL'
ELSE NULL;
END CASE;
THEN
THEN
THEN
THEN
GET_UMSAETZE
INS_UMSAETZE
UPD_UMSAETZE
DEL_UMSAETZE
(…);
(…);
(…);
(…);
Hierarchische Abfragen
•
•
•
•
Wie zählt man in SQL bis zehn?
CONNECT BY
Beispiel: umfangreiche Seitennavigation
Typisch: ID   PARENT_ID
CONNECT BY
SELECT
LEVEL AS menu_level
,SYS_CONNECT_BY_PATH(n.navigation_id, ',') AS parents
,n.aktiv
,n.navigation_id
,n.tab_titel
,n.breadcrumb_titel
,n.clear_cache
FROM app_navigation n
WHERE …
CONNECT BY PRIOR n.navigation_id = n.navigation_parent_id
START WITH n.navigation_id = 0
ORDER SIBLINGS BY n.position;
Hierarchische Abfragen
• Beispiel: Terminkalender
• treibende Tabelle: TERMINE
• woher nehmen wir die "leeren" Slots?
+/- 50 Jahres-Kalender
Kalender-Report
CREATE OR REPLACE FORCE VIEW
termine_view
AS
WITH kalender AS (
SELECT TRUNC(SYSDATE) + LEVEL - 1 - 50 * 365 AS datum
FROM DUAL
CONNECT BY LEVEL < 100 * 365
)
SELECT
,
,
FROM
LEFT
ON
kalender.datum
termine.termin_id
termine.titel
kalender
-- sämtliche "freie" Slots
OUTER JOIN termine
-- nur die vorhandenen Einträge
kalender.datum = termine.datum;
Formulare über Views
• View anstatt Tabelle als Datenquelle
• Über "einfache" Views kein Problem
• Verbundene Tabellen verlangen etwas mehr …
Instead-Of-Trigger
• Trigger übernehmen die Aktionen
– INSERT
– UPDATE
– DELETE
• APEX-Wizards für Formulare
können 1:1 verwendet werden
INSERT-Trigger
CREATE OR REPLACE TRIGGER ii_termine_view
INSTEAD OF INSERT ON termine_view
FOR EACH ROW
BEGIN
INSERT INTO termine (termin_id, datum, titel)
VALUES (nvl(:NEW.termin_id, termine_seq.nextval),
:NEW.datum, :NEW.titel);
END;
UPDATE-Trigger
CREATE OR REPLACE TRIGGER iu_termine_view
INSTEAD OF UPDATE ON termine_view
FOR EACH ROW
BEGIN
UPDATE termine
SET termin_id = :NEW.termin_id -- PK
, datum
= :NEW.datum
, titel
= :NEW.titel
WHERE termin_id = :OLD.termin_id;
END;
DELETE-Trigger
CREATE OR REPLACE TRIGGER id_termine_view
INSTEAD OF DELETE ON termine_view
FOR EACH ROW
BEGIN
DELETE FROM termine
WHERE termin_id = :OLD.termin_id;
END;
Formular über View
• Primary Key als Grundlage verwenden
Formular über View
Formular über View
• Bei "komplexen" Views,
nach UPDATE oder DELETE:
Formular über View
• Application Item steuert das Locking-Verhalten:
http://www.inside-oracle-apex.com/tag/ora-02014/
Formular über View
• ORA-02014 verhindern:
http://www.inside-oracle-apex.com/tag/ora-02014/
Pivotieren von Zeilen
Pivotieren von Zeilen
• Zeilen in Spalten auflösen, "Kreuztabelle"
• Beispiel: Monatsumsätze über Region
Pivotieren durch Aggregation mit
DECODE
SELECT region
, SUM (DECODE(monat, 1, umsatz)) JAN
, SUM (DECODE(monat, 2, umsatz)) FEB
, SUM (DECODE(monat, 3, umsatz)) MAR
, SUM (DECODE(monat, 4, umsatz)) APR
, SUM (DECODE(monat, 5, umsatz)) MAI
, SUM (DECODE(monat, 6, umsatz)) JUN -- usw.
FROM umsaetze
WHERE jahr = 2013
GROUP BY region
11g
PIVOT-Klausel
SELECT *
FROM umsaetze
PIVOT (
SUM(umsatz) FOR monat IN (
1 JAN
, 2 FEB
, 3 MAR
, 4 APR
, 5 MAI
, 6 JUN ) -- usw.
)
WHERE jahr = 2013
• Automatische
Gruppierung
verbleibender
Spalten ( * )
• Interne Umsetzung
in "klassisches" SQL
mit GROUP BY
• aber: optimierter
Ausführungsplan!
11g
PIVOT-Klausel
• Problem: Anzahl Spalten muss bekannt sein
(wie immer in SQL)
• also kein "dynamisches" Pivotieren möglich
• Lösung 1:
– SQL-Statement mit PL/SQL erzeugen
– in APEX als Reportquelle nutzen
Beispiel: PIVOT generieren lassen
FUNCTION pivot_umsaetze (
p_region IN umsaetze.region%TYPE
,p_jahr
IN umsaetze.jahr%TYPE
,p_monat IN umsaetze.monat%TYPE )
RETURN VARCHAR2
IS
Report
l_select VARCHAR2(10000);
l_where VARCHAR2 (1000);
c_monate CONSTANT VARCHAR2(100)
:= '01 JAN, 02 FEB, 03 MAR, 04 APR, 05 MAI, 06 JUN, '
|| '07 JUL, 08 AUG, 09 SEP, 10 OKT, 11 NOV, 12 DEZ';
BEGIN
l_select := 'SELECT region, jahr, '
|| TRANSLATE(SUBSTR(c_monate, 1, (NVL(p_monat, 12)-1)*8 +6), '0123456789 ', ' ')
|| ' FROM umsaetze';
l_where := CASE WHEN p_region IS NULL THEN NULL ELSE ' AND region = ' || p_region END;
l_where :=
l_where || CASE WHEN p_jahr
IS NULL THEN NULL ELSE ' AND jahr = '
|| p_jahr
END;
IF l_where IS NOT NULL THEN
l_where := ' WHERE' || SUBSTR (l_where, 5);
END IF;
RETURN l_select
|| ' PIVOT (SUM(umsatz) FOR monat IN (' || c_monate || '))'
|| l_where;
END;
Generische Spalten im Report
Funktion für Spaltennamen
Funktion für Spaltennamen
FUNCTION pivot_umsaetze_cols RETURN VARCHAR2
IS
BEGIN
RETURN 'Region:Jahr:JAN:FEB:MAR:APR:MAI:JUN:JUL:AUG:SEP:OKT:NOV:DEZ';
END;
Pivotierter Report
11g
PIVOT-Klausel
• Lösung 2:
– PIVOT XML verwenden
– Nebeneffekt: Formatierung möglich
Pivotieren mit XML
SELECT *
FROM umsaetze
PIVOT XML (
SUM(umsatz) sum_umsatz
FOR monat IN (
SELECT DISTINCT monat
FROM umsaetze)
)
Pivotieren mit XML
<PivotSet>
<item>
<column name = "MONAT">1</column>
<column name = "SUM_UMSATZ">420355</column>
</item>
…
</PivotSet>
9i
Pipelined Table Functions
• PL/SQL-Funktionen als Datenquelle für SQL nutzen
("FROM")
• Daten werden kontinuierlich ausgegeben ("PIPE")
• Registrierter Datentyp als Rückgabe
9i
Pipelined Table Functions
CREATE TYPE integer_array AS TABLE OF INTEGER;
CREATE FUNCTION min_max (
"min" IN INTEGER, "max" IN INTEGER
)
RETURN integer_array
PIPELINED
AS
BEGIN
FOR i IN "min".."max" LOOP
PIPE ROW(i);
END LOOP;
RETURN;
END;
9i
Pipelined Table Functions
SELECT COLUMN_VALUE
FROM TABLE (min_max(1, 10));
9i
Pipelined Table Functions
• Kapselung von PL/SQL-Programmlogik
• z.B. verschachtelte Funktionsaufrufe
• komplexe Berechnungen mit PL/SQL klarer zu
programmieren als mit SQL
• validierbare Parameter
• kontinuierliche Ausgabe ("Staging")
10g
SQL MODEL Clause
• Zellen, Bezüge, Formeln
• "Spreadsheet"-ähnliche Berechnungen
10g
SQL MODEL Clause
SELECT region, jahr, monat, TRUNC(umsatz)
FROM umsaetze
WHERE jahr = 2013
MODEL
RETURN UPDATED ROWS
PARTITION BY (region)
DIMENSION BY (jahr, monat)
MEASURES (umsatz)
RULES (umsatz[2014, 1] = umsatz[2013, 1] *1.2)
10g
SQL MODEL Clause
SELECT …
MODEL … PARTITION BY … DIMENSION BY … MEASURES …
RULES (umsatz[2014,
-------------------------------------FOR monat IN (SELECT DISTINCT monat
FROM umsaetze
WHERE jahr = 2013)
-------------------------------------] = umsatz[2013, CV()] *1.2)
ORDER BY region, jahr, monat
"Kleinigkeiten"
1.
2.
3.
4.
LISTAGG
ORDER BY mit SELECT-Statement
COALESCE anstatt NVL
MERGE
11g R2
LISTAGG
-- Alphabetische Auflistung aller Anwendungen
-- pro Workspace
SELECT workspace_display_name
workspace,
LISTAGG (application_name, ', ')
WITHIN GROUP
(ORDER BY application_id)
apps
FROM apex_applications
GROUP BY workspace_display_name
-- Achtung: LISTAGG unterstützt max. 4000 Byte!
ORDER BY mit SELECT-Statement
SELECT *
FROM EMP e
ORDER BY (
SELECT yesterday
FROM performance p -- wer war der Fleißigste
WHERE e.empno = p.empno
)
9i
COALESCE anstatt NVL
SELECT NVL( 1, 1/0 )
FROM dual
SELECT COALESCE ( 1, 1/0 )
FROM dual
-- ORA-01476: Division durch 0
-- 1
• NVL wertet stets beide Argumente aus
• COALESCE kennt den "Short Circuit":
– wenn Argument 1 NOT NULL,
dann Verzicht auf Berechnung von Argument 2
• COALESCE akzeptiert mehr als 2 Parameter
COALESCE anstatt NVL
• Performance-Gewinn …
– in Schleifen
– mit v( '…' )-Syntax in APEX:
SELECT COALESCE ( v('P1_TEL1'), v('P1_TEL2') ) FROM dual
• Weniger Kontext-Switches zwischen
SQL und PL/SQL
COALESCE
• Sonderfall: Sequences
SELECT COALESCE ( 1, my_seq.nextval ) FROM dual
• wird .nextval erwähnt, dann wird die
Sequence stets erhöht (kein Short-Circuit).
9i R2
MERGE
• Insert oder Update in einem Statement
• nützlich im Zusammenspiel mit
APEX_COLLECTIONS
9i R2
MERGE
MERGE INTO umsaetze
USING (SELECT * FROM apex_collections …)
ON (region = n001 AND jahr = n002 AND monat = n003)
WHEN MATCHED THEN UPDATE
SET umsatz = n004
WHEN NOT MATCHED THEN INSERT
(region, jahr, monat, umsatz)
VALUES (n001, n002, n003, n004);
Zusammenfassung
• Trennung von Daten und Formatierung
 Aufgaben mit Views entkoppeln
• Formulare über Prozeduren
 TABLE API generieren lassen
• Hierarchische Abfragen als Datenlieferanten
 CONNECT BY
• Formulare über Views
 INSTEAD OF Trigger verwenden
• Dynamisches SQL in Reports
 "Function Returning SQL" nutzen
• Datenquelle über sehr komplexe Sachverhalte
 Pipelined Function schreiben
Buchempfehlung
• Sanjay Mishra,
Alan Baulieu
• Buch & Kindle
• 494 Seiten
• Kurze Einführung
• Ausführliche
Beispiele
Buchempfehlung
• Jürgen Sieben
• 877 Seiten, 2013
• Umfangreiche
Einführung und
Vertiefung
• Viele
Praxisbeispiele
• Locker und sehr
gut verständlich
… rund um Application Express
Beratung | Projektmanagement | Coaching
Andreas Wismann
[email protected]
+49 176 7800 3109