Viele meiner Kollegen (um die 90 %) nutzen bei schwierigen SQL Problemen nicht das
SQL Forum, sondern versuchen sich lieber an inperformanten PL/SQL Code.
Die Gründe sind leicht erklärt:
Zeitmangel!
Der kurzfristige Vorteil kann im Nachhinein aber sehr teuer / zeitaufwendig werden. Nämlich dann, wenn die schnelle Lösung mit erhöhten Datenmengen versagt und statt ursprünglich wenigen Sekunden plötzlich Minuten an Zeit verbraucht.
Hierbei möchte ich auf die goldene Regel von Tom Kyte referenzieren:
1.) Nutze solange SQL wie es geht
2.) Wenn SQL nicht mehr reicht, dann nehme PL/SQL
3.) Wenn PL/SQL nicht mehr reicht, dann nehme JAVA oder irgendetwas Anderes was weiterhilft
Meine Anmerkung wäre die folgende zu Punkt 1.)
1.) Nutze solange SQL wie es geht und befrage bei Problemen das Oracle SQL Forum
Wie aber stelle ich eine Frage richtig?
Um auch richtige Hilfe zu erhalten, muss eine entsprechende Vorarbeit Ihrerseits geleistet werden.
Das heißt:
1.) Beschreiben Sie das Problem und fokussieren Sie sich dabei auf das Kernproblem
2.) Hinterlegen Sie Quelldaten und ihre gescheiterten SQL Versuche
3.) Beschreiben Sie das Zielszenario (Ergebnis in Tabellenform)
Sie sollten mit 30 Minuten Vorbereitungszeit rechnen (das erste Mal wird etwas länger dauern).
Info: Achten Sie auch auf den Inhalt der Quelldaten. :)
Sie können sich dabei an meinen Beispielen inspirieren lassen:
Beispiel 1:
Generate some kind of cartesian list
Beispiel 2:
wi (dd.mm.yyyy-dd.mm.yyyy)
Beispiel 3:
Check crossing time periods between rows
Der aufwendigste Teil bleibt immer wieder die Aufbereitung der Quell- und Zieldaten. Ich bin zwar kein Fan der WITH-Klausel aber bei der Hilfe im Forum wird Sie von den Entwicklern immer wieder verwendet. Nun wäre es die einfachste Vorgehensweise die Quelldaten immer mit Hilfe der WITH-Klausel aufbereitet zur Verfügung zu stellen. Denn dann könnten die Entwickler deren Hilfe ich in Anspruch nehme, viel schneller reagieren. Wobei auch so die Reaktionszeit bei durchschnittlich 10 Minuten liegt (siehe Beispiele).
Deshalb habe ich eine Funktionen gebaut die auf Basis einer Tabelle oder View die Quelldaten als WITH-Klausel aufbereitet.
create or replace function get_with_clause(p_table_name in varchar2, p_include_with_clause number default 1, p_rows in number default 10) return clob as
/* Dynamische WITH-Klausel mit SELECT FROM DUAL auf Basis einer vorhandenen Tabelle generieren
Übergabeparameter:
p_table_name = Tabellename
p_include_with_clause = 1 oder 0, wenn 1 dann mit WITH-Klausel ansonsten nur SELECT FROM DUAL
p_rows = Anzahl Zeilen die zurückgegeben werden sollen, wenn NULL, dann alle
*/
l_sql1 varchar2(4000);
l_sql2 clob;
begin
select 'SELECT ' || chr(13)
||
case
when p_include_with_clause = 1 then
'''WITH ' || max(table_name) || ' AS ('' ||' || chr(13)
else
null
end
|| 'REGEXP_REPLACE(DBMS_XMLGEN.CONVERT(XMLAGG(XMLELEMENT(E, ' || chr(13)
|| 'CHR(13) || ''SELECT '' || CHR(13) || '
|| replace(listagg(
case
when data_type = 'NUMBER'
then 'nvl(trim(to_char('||column_name||')),''NULL'') || '' as ' || column_name || ',''|| CHR(13) || '
when data_type = 'VARCHAR2'
then 'decode('||column_name||',NULL,''NULL'',''''''''||'||column_name||'||'''''''') || '' as ' || column_name || ',''|| CHR(13) || '
when data_type = 'DATE'
then 'decode('||column_name||',NULL,''NULL'',''''''''||to_char('||column_name||',''dd.mm.yyyy hh24:mi'')||'''''''') || '' as ' || column_name || ',''|| CHR(13) || '
else
null
end
, ' '
) within group (order by rownum) || 'ZZ',',''|| CHR(13) || ZZ',' '' ||')
|| chr(13)
|| '''FROM DUAL UNION ALL '''
|| chr(13)
|| ')).EXTRACT(''//text()'').GETCLOBVAL(),1)||''XX'','' UNION ALL XX'','''')'
||
case
when p_include_with_clause = 1 then
'|| CHR(13) || '') ' || 'SELECT t1.* FROM ' || max(table_name) || ' t1 ORDER BY 1'''
else
null
end
|| chr(13)
|| 'FROM '
|| max(table_name)
|| chr(13)
|| case when p_rows is null then null else 'WHERE ROWNUM BETWEEN 1 AND ' || trim(to_char(p_rows)) end
into l_sql1
from user_tab_columns
where table_name = p_table_name
and data_type in ('NUMBER','VARCHAR2','DATE');
execute immediate (l_sql1) into l_sql2;
return l_sql2;
end get_with_clause;
Innerhalb der Funktion werden die Spalten der übergebenen Tabelle ausgelesen. Daraus generiere ich dann ein dynamisches SQL das mir die "select * from dual" - je Zeile generiert. Das Ergebnis ist ein CLOB mit einem ausführbaren SQL Code.
Unter 12c sollte es auch ohne Funktionsaufruf abbildbar sein (Grundlagen sie
Blogpost von Carsten), ich konnte es aber wegen fehlen einer 12c Umgebung noch nicht abschließend testen. :(
with function get_with_clause(p_table_name in varchar2, p_include_with_clause number default 1, p_rows in number default 10) return clob as
L_SQL1 VARCHAR2(4000);
L_SQL2 CLOB;
BEGIN
SELECT 'SELECT ' || CHR(13)
||
case
when p_include_with_clause = 1 then
'''WITH ' || MAX(table_name) || ' AS ('' ||' || CHR(13)
else
null
end
|| 'REGEXP_REPLACE(DBMS_XMLGEN.CONVERT(XMLAGG(XMLELEMENT(E, ' || CHR(13)
|| 'CHR(13) || ''SELECT '' || CHR(13) || '
|| replace(listagg(
case
when data_type = 'NUMBER'
then 'nvl(trim(to_char('||column_name||')),''NULL'') || '' as ' || column_name || ',''|| CHR(13) || '
when data_type = 'VARCHAR2'
then 'decode('||column_name||',NULL,''NULL'',''''''''||'||column_name||'||'''''''') || '' as ' || column_name || ',''|| CHR(13) || '
when data_type = 'DATE'
then 'decode('||column_name||',NULL,''NULL'',''''''''||to_char('||column_name||',''dd.mm.yyyy hh24:mi'')||'''''''') || '' as ' || column_name || ',''|| CHR(13) || '
else
null
end
, ' '
) WITHIN GROUP (ORDER BY ROWNUM) || 'ZZ',',''|| CHR(13) || ZZ',' '' ||')
|| CHR(13)
|| '''FROM DUAL UNION ALL '''
|| CHR(13)
|| ')).EXTRACT(''//text()'').GETCLOBVAL(),1)||''XX'','' UNION ALL XX'','''')'
||
case
when p_include_with_clause = 1 then
'|| CHR(13) || '') ' || 'SELECT t1.* FROM ' || MAX(table_name) || ' t1 ORDER BY 1'''
else
null
end
|| CHR(13)
|| 'FROM '
|| MAX(table_name)
|| CHR(13)
|| case when p_rows is null then null else 'WHERE ROWNUM BETWEEN 1 AND ' || trim(to_char(p_rows)) end
INTO L_SQL1
FROM user_tab_columns
WHERE table_name = p_table_name
AND DATA_TYPE IN ('NUMBER','VARCHAR2','DATE');
EXECUTE IMMEDIATE (L_SQL1) INTO L_SQL2;
RETURN L_SQL2;
end get_with_clause;
select get_with_clause('DEPARTMENT') as result from dual
Ergebnis Beispiel für Tabelle DEPT:
SELECT GET_WITH_CLAUSE( P_TABLE_NAME => 'DEPT', P_INCLUDE_WITH_CLAUSE => 1, P_ROWS => 100 ) as RESULT
FROM DUAL;
WITH DEPT AS (
SELECT
10 as DEPTNO,
'ACCOUNTING' as DNAME,
'NEW YORK' as LOC FROM DUAL UNION ALL
SELECT
20 as DEPTNO,
'RESEARCH' as DNAME,
'DALLAS' as LOC FROM DUAL UNION ALL
SELECT
30 as DEPTNO,
'SALES' as DNAME,
'CHICAGO' as LOC FROM DUAL UNION ALL
SELECT
40 as DEPTNO,
'OPERATIONS' as DNAME,
'BOSTON' as LOC FROM DUAL
) SELECT t1.* FROM DEPT t1 ORDER BY 1
Info:
Die Lösung funktioniert nur mit den Datentypen: NUMBER, VARCHAR2, DATE. Die restlichen Datentypen werden derzeit ignoriert.
