Damit konnten sie sich Selects über ein User-Interface bequem zusammenklicken, Teilergebnisse pivotieren und die Ausgaben anschließend in Excel übertragen.
Da die alte Lizenz auslief, das Tool inzwischen wesentlich teurer und umfangreicher geworden war und sich auch die Bedienung verändert hatte, suchten sie nach einer Ersatzmöglichkeit.
Eine Oracle-Datenbank kann fast alles (außer Kaffee kochen) und so ließ ich mir die Abfragen schicken und versuchte selber mein Glück.
Unser Produktionsschema (hier abstrahiert und vereinfacht) enthält Tabellen für die Aufträge (orders), die Geräte (DEVICES), die Tester-Stationen (TEST_STATION), die Gerätetests (DEVICE_TESTS), die Resultate der einzelnen Messungen (RESULTS), die Parameter, die gemessen wurden (PARAMETERS), und die Verpackungsdaten (PACKAGING)
(siehe Abbildung rechts).
Insgesamt sollten ca. 15 verschiedene Berichte für verschiedene Produkttypen erstellt werden, die als Eingabeparameter entweder einzelne Auftragsnummern oder Geräteseriennummern akzeptierten.
Die Ergebnisse sollten als csv-Datei abrufbar sein, um sie mit Excel weiterverarbeiten zu können.
Zusätzlich zu diversen Test- und Verpackungsdetails der Geräte wünschten sich die Kolleginnen die Messergebnisse von zwischen 5 und 30 Messparametern in pivotierter Form.
Die über das Tool generierten Abfragen sahen eher abschreckend aus
Hier ein (vereinfachtes) Beispiel:
SELECT "DEVICES"."SERIAL_NO", "ORDERS"."ORDER_NO", "RESULTS"."PASS", "PARAMETERS"."PARAMETER_NAME", "PARAMETERS"."UNITY", "DEVICE_TESTS"."TEST_DATE", "DEVICE_TESTS"."PASS", "TEST_STATION"."DESCRIPTION", "PACKAGING"."STATUS", "PRODUCT_TYPES"."DESCRIPTION", "RESULTS"."VALUE"
FROM ((("PROD"."PACKAGING" "PACKAGING" INNER JOIN (((("PROD"."RESULTS" "RESULTS" INNER JOIN "PROD"."DEVICE_TESTS" "DEVICE_TESTS" ON "RESULTS"."DEV_TEST_ID"="DEVICE_TESTS"."ID") INNER JOIN "PROD"."PARAMETERS" "PARAMETERS" ON "RESULTS"."PARAMETER_ID"="PARAMETERS"."ID") INNER JOIN "PROD"."DEVICES" "DEVICES" ON "DEVICE_TESTS"."DEVICE_ID"="DEVICES"."ID") INNER JOIN "PROD"."TEST_STATION" "TEST_STATION" ON "DEVICE_TESTS"."TEST_STATION_ID"="TEST_STATION"."ID") ON "PACKAGING"."DEVICE_ID"="DEVICES"."ID") INNER JOIN "PROD"."ORDERS" "ORDERS" ON "DEVICES"."ORDER_ID"="ORDERS"."ID") INNER JOIN "PROD"."PRODUCT_TYPES" "PRODUCT_TYPES" ON "DEVICES"."PROD_TYPE_ID"="PRODUCT_TYPES"."ID")
WHERE "ORDERS"."ORDER_NO"='<auftragsnummer>' AND "TEST_STATION"."DESCRIPTION"<>'STATION3' AND ("PARAMETERS"."PARAMETER_NAME"='PAR1' OR "PARAMETERS"."PARAMETER_NAME"='PAR2' OR "PARAMETERS"."PARAMETER_NAME"='PAR3' OR "PARAMETERS"."PARAMETER_NAME"='PAR4' OR "PARAMETERS"."PARAMETER_NAME"='PAR5' OR "PARAMETERS"."PARAMETER_NAME"='PAR6' OR "PARAMETERS"."PARAMETER_NAME"='PAR7' OR "PARAMETERS"."PARAMETER_NAME"='PAR8' OR "PARAMETERS"."PARAMETER_NAME"='PAR9' OR "PARAMETERS"."PARAMETER_NAME"='PAR10' .....)
ORDER BY "DEVICES"."SERIAL_NO", "DEVICE_TESTS"."TEST_DATE";
Nach Löschen der unzähligen Anführungszeichen und Klammern und ein paar Umstellungen war der Select aber schon lesbarer:
SELECT d.serial_no,
o.order_no,
p.parameter_name,
p.unity,
dt.test_date,
dt.pass test_result,
r.pass meas_result,
t.description test_station,
pl.status packing_status,
pt.description prod_type,
r.value
FROM prod.packaging pl JOIN prod.devices d
ON pl.device_id = d.id
JOIN prod.orders o
ON d.order_id = orders.id
JOIN prod.product_types pt
ON d.prod_type_id = pt.id
JOIN prod.device_tests dt
ON dt.device_id = d.id
JOIN prod.results r
ON r.dev_test_id = dt.id
JOIN prod.parameters p
ON r.parameter_id = p.id
JOIN prod.test_station t
ON dt.test_station_id = t.id
WHERE o.order_no = '?'
AND t.description <> 'STATION3'
AND ( p.parameter_name = 'PAR1'
OR p.parameter_name = 'PAR2'
OR p.parameter_name = 'PAR3'
OR p.parameter_name = 'PAR4'
OR p.parameter_name = 'PAR5'
OR p.parameter_name = 'PAR6'
OR p.parameter_name = 'PAR7'
OR p.parameter_name = 'PAR8'
OR p.parameter_name = 'PAR9'
OR p.parameter_name = 'PAR10'
.....)
ORDER BY devices.serial_no, device_tests.test_date;
Pivotiert werden sollten die Messergebnisse für die einzelnen Parameter.
In der Tabelle RESULTS standen sie neben der ID der jeweiligen Parameter, z.B.
ID DEV_TEST_ID PARAMETER_ID VALUE
----- -------------------- ----------------------------------
1 2 150 1.512784
2 2 210 9910.76
3 2 90 FLAG1
Gewünscht war eine Folge von Parameternamen mit dem Messergebnis darunter:
DEV_TEST_ID PAR1 PAR2 PAR3
-------------------- ----------- ---------- ---------
2 1.512784 9910.76 FLAG1
Vor der Version 11g brauchte man für die Pivotierung ja noch einige Verrenkungen, hier ein Beispiel für die unverwüstliche EMP-Tabelle:
SELECT SUM(CASE TO_CHAR(hiredate, 'yyyy') WHEN '1980' THEN 1 ELSE 0 END) "1980",
SUM(CASE TO_CHAR(hiredate, 'yyyy') WHEN '1981' THEN 1 ELSE 0 END) "1981",
SUM(CASE TO_CHAR(hiredate, 'yyyy') WHEN '1982' THEN 1 ELSE 0 END) "1982",
SUM(CASE TO_CHAR(hiredate, 'yyyy') WHEN '1987' THEN 1 ELSE 0 END) "1987"
FROM emp;
1980 1981 1982 1987
--------- --------- --------- ---------
1 10 1 2
Seit 11g gibt es die PIVOT-Klausel, die hier einiges an Schreibarbeit erspart.
SELECT * FROM (SELECT TO_CHAR(hiredate, 'yyyy') year FROM emp)
PIVOT (COUNT(*) FOR year IN (1980,1981,1982,1987));
Ich musste also den alten tool-generierten Select mit einer Pivotierung ergänzen, am besten etwas tunen und damit eine Art View basteln, die nach Eingabe einer Auftragsnummer die Ergebnisse auflistet.
Genau dafür gibt es Table Functions, also Funktionen, die diverse Parameter übernehmen und als Ergebnis eine Tabelle ausgeben.
Dafür braucht man als erstes einen Objekttypen. Damit alle Reportfunktionen den gleichen Typen nutzen können, umfasst er alle Felder, die in den Abfragen vorkommen können, z.B.
CREATE OR REPLACE TYPE select_obj_type AS OBJECT(
serial_no VARCHAR2(9),
status NUMBER(1,0),
order_no VARCHAR2(30),
box_no VARCHAR2(30),
package_no NUMBER(5,0),
packing_date DATE,
test_date TIMESTAMP(3),
test_type VARCHAR2(60),
test_pass NUMBER(1,0),
test_station VARCHAR2(70),
meas_pass NUMBER(1,0),
order_start TIMESTAMP(2),
order_finished TIMESTAMP(2),
product_type VARCHAR2(60),
article_no NUMBER(5,0),
Par1 NUMBER,
Par2 NUMBER,
Par3 NUMBER,
Par4 VARCHAR2(200),
Par5 NUMBER,
.....
Par30 NUMBER);
/
Auf Basis des Objekttyps erstellt man eine Nested Table.
CREATE OR REPLACE TYPE select_nt_type AS TABLE OF select_obj_type;
/
Und die Funktion sieht dann so aus:
CREATE OR REPLACE FUNCTION fnc_get_devices_per_order (p_order_no IN VARCHAR2)
-- PIPELINED besagt, dass der Inhalt der Nested Table Zeile für Zeile zurückgegeben wird
RETURN select_nt_type PIPELINED
AS
-- Variable für die NT
select_nt select_nt_type;
l_dt_min prod.device_tests.id%TYPE;
l_dt_max prod.device_tests.id%TYPE;
l_order_id prod.orders.id%TYPE;
BEGIN
SELECT id INTO l_order_id
FROM prod.orders
WHERE order_no = p_order_no;
-- kleinste und größte Test-ID bestimmen, dann geht's danach schneller
SELECT MAX(id), MIN(id) INTO l_dt_max, l_dt_min
FROM prod.device_tests dt
WHERE EXISTS (SELECT 1 FROM prod.devices d
WHERE dt.device_id = d.id
AND d.order_id = l_order_id);
-- hier kommt die Abfrage von oben mit Anpassungen
FOR rec IN (SELECT d.serial_no, p_order_no, r.pass meas_pass, pl.status,
dt.test_date,dt.test_type, r.par1, r.par4, r.par7,
r.par11,r.par15, r.par18, r.par22, r.par25, r.par27, r.par30
FROM (SELECT serial_no, id FROM prod.devices
WHERE order_id = l_order_id) d
JOIN (SELECT device_id, status FROM prod.packaging) pl
ON pl.device_id = d.id
JOIN (SELECT id, device_id, test_date, test_type, test_station_id
FROM prod.device_tests
WHERE id BETWEEN l_dt_min AND l_dt_max
AND test_station_id IN (12,15,77,22,9,13,72)) dt
ON dt.device_id = d.id
JOIN prod.test_station t
ON dt.test_station_id = t.id
-- hier werden die Messwerte pivotiert
JOIN (SELECT *
FROM (SELECT dev_test_id, parameter_id, value, pass
FROM prod.results
WHERE parameter_id IN (110, 410, 710, 110, 150,
180, 220, 250, 270, 300)
AND dev_test_id BETWEEN l_dt_min AND l_dt_max)
PIVOT (MAX(value)
FOR parameter_id IN (110 AS par1,
410 AS par4,
710 AS par7,
110 AS par11,
150 AS par15,
180 AS par18,
220 AS par22,
250 AS par25,
270 AS par27,
300 AS par30))) r
ON r.dev_test_id = dt.id
ORDER BY d.serial_no) LOOP
-- und hier die Nested Table zeilenweise über den Objekttypen gefüllt
PIPE ROW(select_obj_type(rec.serial_no, rec.status, p_order_no, NULL, NULL,
NULL, rec.test_date, rec.test_type, NULL, NULL,
rec.meas_pass, NULL, NULL, NULL, NULL, rec.par1,
NULL, NULL, rec.par4, NULL, NULL, rec.par7, NULL,
NULL, NULL, rec.par11, NULL, NULL, NULL, rec.par15,
NULL, NULL, rec.par18, NULL, NULL, NULL, rec.par22,
NULL, NULL, rec.par25, NULL, rec.par27, NULL, NULL,
rec.par30));
END LOOP;
EXCEPTION
WHEN OTHERS THEN
prod.pkg_err.log_error(
p_errm => REGEXP_SUBSTR(SQLERRM, '.*$',1,1,'m'),
p_proc => 'fnc_get_devices_per_order',
p_parameters => 'Order_no: '||p_order_no);
END;
/
Der QA-User bekommt das Select-Recht an der Funktion und kann sie dann folgendermaßen aufrufen:
SELECT d.serial_n, p_order_no, r.pass meas_pass, pl.status, dt.test_date, r.par1,
r.par4, r.par7, r.par11,r.par15, r.par18, r.par22, r.par25, r.par27, r.par30
FROM TABLE(fnc_get_devices_per_order('9S117022023'));
Da Pipelined Table Functions die Ergebnisse schon ausgeben, bevor die Nested Table komplett gefüllt ist, und relativ speicherschonend arbeiten, ging die Reporterstellung sehr zügig, auch dank der verbesserten Abfrage.
Da meine Kolleginnen die Funktionen im SQL-Developer aufrufen und sich dann selber als csv-File abspeichern, brauchte ich hier keinen Export oder eine Mailing-Funktionalität einzubauen. Das wäre aber mit den altbewährten Packages UTL_FILE und/oder UTL_HTTP kein Problem gewesen.
Fazit:
Eine Oracle-Datenbank hat auch in der einfachsten Ausgabe soviel "unter der Haube", dass man für die meisten Alltagsaufgaben keine externe Tools braucht, wenn man sich mit PL/SQL und SQL auskennt und die vordefinierten Oracle Packages nutzt.
Es wird dann nicht nur billiger, sondern meist auch schneller.
Und es macht echt Spaß!
Dr. Elke Fritsch
DOAG Themenverantwortliche Tuning
