Logik und SQL

Heute geht's um die Verwendung von Kenntnissen aus der Aussagenlogik und wie man diese im Rahmen von SQL anwenden kann. Als Beispiel dient die Forderung, dass ein Analyst ein Gehalt von mindestens 2500 erhalten muss.

Diese Forderung kann man wie folgt formulieren:

WENN ( JOB = 'ANALYST' ) DANN ( SAL >= 2500 )

Das entspricht der Implikation in der Aussagenlogik:

( JOB = 'ANALYST' ) -> ( SAL >= 2500 )

Die Implementierung umfasst die Definition eines Check-Constraints für die Tabelle EMP. Zwar existiert in SQL der CASE-Operator, jedoch kann dieser bei der Spezifikation der Bedindung nicht verwendet werden; dies gilt auch für die Verwendung von PL/SQL. Man kann die Forderung aber mithilfe der Operatoren NOT und OR angeben, welche beide von SQL unterstützt werden. Die folgende Warheitstabelle zeigt die Gültigkeit von:

A → B ≡ ¬A ∨ B

A	B	A → B	¬ A ∨ B
T	T	T	T
T	F	F	F
F	T	T	T
F	F	T	T

Man kann erkennen, dass der Warheitswerteverlauf der dritten Spalte mit dem der vierten Spalte übereinstimmt. Das macht man sich nun zu Nutze, um die Bedingung umzuformen:

NOT ( JOB = 'ANALYST' ) OR ( SAL >= 2500 )

Der erste Teil kann wie folgt vereinfacht werden:

( JOB != 'ANALYST' ) OR ( SAL >= 2500 )

Diese Formulierung kann man nun im Rahmen des Check-Constraints verwenden:

alter table emp
add constraint c_emp_analyst_sal
check ( job != 'ANALYST' or sal >= 2500 );

Schon dieses einfache Beispiel zeigt, dass grundlegende Kenntnisse aus dem Bereich der Logik von Vorteil sind. Natürlich gibt es dazu noch weitere interessante Themen, wie zum Beispiel die systematische Herleitung von SQL Abfragen durch die Verwendung von Prädikatenlogik; aber dazu mehr in einem anderen Post.

Locking mithilfe von DBMS_LOCK

Heute geht's um die Verwendung von DBMS_LOCK zum Sicherstellen von Integritätsbedingungen. Zur Demonstration wollen wir sicherstellen, dass pro Abteilung nur maximal ein Manager existiert. Wie bereit im vergangenen Post wird ein Compound Trigger verwendet:

create trigger check_manager_c_iu for insert or update of job, deptno on emp 
when (new.job = 'MANAGER' or old.deptno != new.deptno)
compound trigger

 type departments_nt is table of dept.deptno%type;
 departments_tab departments_nt := departments_nt();
 
 before each row is
 begin
  departments_tab.extend;
  departments_tab( departments_tab.last ) := :new.deptno;
 end before each row;
 
 after statement is
  v_manager_count integer := 0;
 begin
  departments_tab := set( departments_tab );
  for i in 1 .. departments_tab.count loop
   select 
    count(*)
   into 
    v_manager_count
   from 
    emp 
   where 
    job = 'MANAGER' and deptno = departments_tab(i);
   if ( v_manager_count > 1 ) then
    raise_application_error
    (
     -20010, 
     'No more than one manager allowed in department ' || departments_tab(i) || '.'
    );
   end if;
  end loop;
 end after statement;

end check_manager_c_iu;
/

Der BEFORE EACH ROW Teil speichert die Abteilungen in einer Nested Table, während der AFTER STATEMENT Teil die eigentliche Überprüfung vornimmt. Jedoch besteht immer noch die Möglichkeit, dass die Bedinung verletzt wird.

Zur Demonstration dienen zwei Sessions (T1, T2), welche die folgenden Operationen ausführen:

t₀ T1: INSERT MANAGER 'X' IN DEPARTMENT 40
t₁ T2: INSERT MANAGER 'Y' IN DEPARTMENT 40
t₂ T1: COMMIT;
t₃ T2: COMMIT;

Anmerkung: Wir nehmen an, dass Abteilung 40 vor dem Zeitpunkt t₀ keinen Manager besitzt.

Der INSERT-Befehl zum Zeitpunkt t₀ funktioniert, da noch kein Manager für Abteilung 40 existiert. Jedoch geht auch der zweite INSERT-Befehl von T2 durch, da T1 noch kein COMMIT ausgeführt hat. Nach Zeitpunkt t₃ existieren somit zwei Manager in Abteilung 40; eine Verletzung der geforderten Bedingung.

Eine mögliche Lösung für dieses Problem stellt DBMS_LOCK in Form der Prozedur ALLOCATE_UNIQUE und der Funktion REQUEST bereit. ALLOCATE_UNIQUE ordnet einem Namen eine eindeutige Nummer zu und gibt diese zurück. Die eigentliche Anfrage für diesen benannten Lock wird durch die Funktion REQUEST ausgeführt. Da die Prozedur ALLOCATE_UNIQUE einen impliziten COMMIT durchführt ist eine autonome Transaktion erforderlich.

Das nachfolgende Code Listing enthält eine Funktion, welche ALLOCATE_UNIQUE in einer anderen Transaktion aufruft und den Lock Handle zurückliefert. Die Prozedur REQUEST_LOCK stellt dann die Anfrage für den Lock im exclusive mode.

create function allocate_unique(p_lockname_in in varchar2) return varchar2 
is
 pragma autonomous_transaction;
 v_lockhandle varchar2(128);
begin
 dbms_lock.allocate_unique(
  upper(p_lockname_in),
  v_lockhandle,
  60*10
 );
 return v_lockhandle;
end;
/

create procedure request_lock(p_lockname_in in varchar2)
is
 v_lockhandle varchar2(128);
 v_return_code number;
begin
 v_lockhandle := allocate_unique(p_lockname_in);
 v_return_code := dbms_lock.request(
  lockhandle => v_lockhandle,
  lockmode => dbms_lock.x_mode,
  timeout => 10,
  release_on_commit => true
 );
 if pl_return not in (0,4) then
  raise_application_error
  (
   -20010,
   'Unable to get the requested lock ' || p_lockname_in || '.');
 end if;
end;
/

Eine Möglichkeit besteht nun darin, die Prozedur REQUEST_LOCK im AFTER STATEMENT Teil des Triggers aufzurufen; der Lock trägt den Namen CHECK_MANAGER.

...
 after statement is
  v_manager_count integer := 0;
 begin
  request_lock('CHECK_MANAGER');
  ...
 end after statement;
...

Damit ist das Problem zwar gelöst, jedoch kann das Locking noch granularer gestaltet werden. Dazu wird ein Lock pro Abteilung definiert, wie das folgende Listing zeigt.

...
 after statement is
  v_manager_count integer := 0;
 begin
  departments_tab := set( departments_tab );
  for i in 1 .. departments_tab.count loop
   request_lock( 'CHECK_MANAGER_DEPT_' ||  departments_tab(i) );
   ...
  end loop;
 end after statement;
...

Das ganze soll nun mithilfe von zwei Sessions getestet werden.

T1:

insert into emp (empno, ename, job, mgr, hiredate, sal, deptno)
values (7974, 'CLARKSON', 'MANAGER', 7839, sysdate, 3750, 40);
-- 1 rows inserted

T2:

insert into emp (empno, ename, job, mgr, hiredate, sal, deptno)
values (7975, 'JOHNSON', 'MANAGER', 7839, sysdate, 3500, 40);
-- WAIT (maximal 10 Sekunden, wie in der Prozedur REQUEST_LOCK angegeben)

T1:

commit; -- weniger als 10 Sekunden nach dem INSERT

T2:

--SQL-Fehler: ORA-20010: No more than one manager allowed in department 40.
--ORA-06512: in "EXAMPLE.CHECK_MANAGER_C_IU", Zeile 27
--ORA-04088: Fehler bei der Ausführung von Trigger 'EXAMPLE.CHECK_MANAGER_C_IU'

Das Beispiel und die Verwendung von DBMS_LOCK ist angelehnt an das Buch "Applied Mathematics for Database Professionals" von Lex de Haan und Toon Koppelaars. Dort wird das Ganze noch detaillierter und mit zahrleichen Beispielen samt der notwendigen Theorie behandelt.

Compound Trigger im Falle von Mutating Table

Heute geht's um die Verwendung von Compund Triggern. Ein Compound Trigger fasst die verschiedenen Ereignisse (BEFORE STATEMENT, BEFORE EACH ROW...) in einem Block zusammen. Zudem enthält ein Compound Trigger einen Abschnitt zur Deklaration von Variablen der dann von allen Ereignissen verwendet werden kann. Genau dieser Abschnitt eigent sich dann für die Behandlung von Mutating Table Problemen. Zur Demonstration soll in der Tabelle EMP sichergestellt werden, dass die Summe der Gehält ein Budget der jeweiligen Abteilung nicht überschreitet.

Zunächst wird dazu die Tabelle DEPT um die Spalte SALBUDGET ergänzt:

alter table dept add salbudget integer;

Dieser Post verwendet die folgenden Werte für die Spalte SALBUDGET:

select
 deptno, salbudget
from
 dept;

DEPTNO         SALBUDGET     
-------------- --------------
10             13000         
20             15000         
30             11000         
40             10000

Der Compound Trigger soll nun sicherstellen, dass das Budget der Abteilung nicht überschritten wird. Dazu werden die betroffenen Abteilungen im BEFORE EACH ROW Teil in einer Nested Table gespeichert, um diese anschließend im AFTER STATEMENT Teil zu überprüfen.

create trigger check_salbudget_c_iu for insert or update of sal, deptno on emp

compound trigger

 type dept_nt is table of dept.deptno%type;
 v_dept_tab dept_nt := dept_nt();
 
 before each row is
 begin
  if 
  (
   inserting or (updating and (:new.sal > :old.sal or :new.deptno != :old.deptno))
  ) 
  then
   v_dept_tab.extend;
   v_dept_tab(v_dept_tab.last) := :new.deptno;
  end if;
 end before each row;
 
 after statement is
  v_difference integer;
 begin
  v_dept_tab := set(v_dept_tab);
  for i in 1 .. v_dept_tab.count loop
   select
    (select sum(sal) from emp where deptno = v_dept_tab(i)) 
     -
    (select salbudget from dept where deptno = v_dept_tab(i))
   into
    v_difference
   from
    dual;
   if (v_difference > 0) then
    raise_application_error
    (
     -20010,
     'Budget exceeded for department ' || v_dept_tab(i) || '.'
    ); 
   end if;
  end loop;
 end after statement;
 
end check_salbudget_c_iu;

Anmerkung: Die Funktion SET in Zeile 23 stellt sicher, dass eine Abteilung nicht mehrfach überprüft werden muss indem doppelte Einträge aus der Nested Table entfernt werden. Hinzu kommt, dass ein UPDATE nur dann zu einer Überschreitung des Budgets führen kann, wenn das Gehalt erhöht wird order sich die Abteilung ändert; dies wird in Zeile 12 überpüft.

Nun sollen zum Test die Gehälter aller Personen in Abteilung 30 um 20% erhöht werden:

update 
 emp
set 
 sal = sal * 1.20
where 
 deptno = 30;

Die Summe der Gehälter in Abteilung 30 beträgt nach dieser Veränderung 11040; somit eine Überschreitung des Budgets von 11000. Man erhält die entsprechende Fehlermeldung:

SQL-Fehler: ORA-20010: Budget exceeded for department 30.
ORA-06512: in "EXAMPLE.CHECK_SALBUDGET_C_U", Zeile 26
ORA-04088: Fehler bei der Ausführung von Trigger 'EXAMPLE.CHECK_SALBUDGET_C_U

Ein Compound Trigger bietet also im Wesentlichen die Funktionalität, die man vorher (vor Oracle 11g) mithilfe von Package Variablen implementieren musste.

Berechnung der Potenzmenge mithilfe von POWERMULTISET

Heute geht's um die Berechnung der Potenzmenge mithilfe der vorhandenen Table Function POWERMULTISET. Als Potenzmenge bezeichnet man die Menge aller Teilmengen einer gegebenen Grundmenge. Die Potenzmenge einer endlichen Menge mit n Elementen enthält 2ⁿ Elemente. Nehmen wir als Beispiel die Menge M = { 1, 2, 3 }. Da es sich um eine endliche Menge mit 3 Elementen handelt, enthält die Potenzmenge 2³ = 8 Elemente, die da wären:

1.  {   }
2.  { 1 }
3.  { 2 }
4.  { 3 }
5.  { 1, 2 }
6.  { 1, 3 }
7.  { 2, 3 }
8.  { 1, 2, 3 }

Anmerkung: Eine Menge enthält keine Ordnung, sodass z.B. { 1, 2 } = { 2, 1 } ist.

Die Table Function POWERMULTISET erwartet als Argument eine Nested Table. Dazu erstellen wir zunächst den entsprechenden Typ:

create type num_nt as table of integer;
/

Jetzt kann die Funktion aufgerufen werden:

select 
 column_value 
from 
 table(powermultiset(num_nt(1,2,3)))
order by 
 cardinality(column_value);

Man erhält nun die folgenden sieben Zeilen:

COLUMN_VALUE
----------------------------
EXAMPLE.NUM_NT('1')
EXAMPLE.NUM_NT('2')
EXAMPLE.NUM_NT('3')
EXAMPLE.NUM_NT('1','2')
EXAMPLE.NUM_NT('1','3')
EXAMPLE.NUM_NT('2','3')
EXAMPLE.NUM_NT('1','2','3')

Man erkennt, dass eine Teilmenge fehlt: die leere Menge; somit verhält sich die Implementierung leider nicht ganz so wie in der Mathematik.

Neben der Table Function POWERMULTISET gibt es auch die Table Function POWERMULTISET_BY_CARDINALITY, die alle Teilmengen zu einer gegebenen Kardinalität zurückgibt. Man erhält alle zwei-elementigen Teilmengen mithilfe der folgenden Abfrage:

select
 column_value
from 
 table(powermultiset_by_cardinality(num_nt(1,2,3),2));

Man erhält die folgenden drei Zeilen:

COLUMN_VALUE
----------------------------
EXAMPLE.NUM_NT('1','2')
EXAMPLE.NUM_NT('1','3')
EXAMPLE.NUM_NT('2','3')

Also wiedermal ein nettes Feature, welches man mitunter bei einigen Problemen verwenden kann.

SQL*Loader und die Zeichensatzkonvertierung mit gVim

Heute wollen wir eine, mit latin1 kodierte, Datei mithilfe von SQL*Loader in einer Datenbank, die mit UTF-8 arbeitet, bereitstellen. Die Datei mit dem Namen mitarbeiter.data enthält die folgenden Zeilen:

ID,VORNAME,NACHNAME
1,Karl,Müller
2,Céline,Dupont
3,Henry,Weiß

Dazu bietet gVim eine einfache Möglichkeit an, die latin1 kodierte Datei UTF-8 kompatibel zu speichern. Nach dem Öffnen der Datei kann man sich diese zum einen in einer anderen Kodierung anzeigen lassen und zum anderen in einer anderen Kodierung abspeichern.

Um sich die aktuelle Kodierung, auf der die Darstellung beruht, anzeigen zu lassen genügt der folgende Befehl:

:set enc

Öffnet man die Datei mit gVim erhält man in diesem Beispiel:

Zum einen kann man sich nun die Datei UTF-8 kodiert anzeigen lassen; das geht mit dem Befehl:

:set enc=utf8

Anmerkung: dies ändert nur die Anzeige. Man erhält dann:

Nun speichern wir die Datei UTF-8 kodiert mit dem Befehl:

:w ++enc=utf8

Kommen wir nun zu SQL*Loader und dem entsprechendem Control File:

OPTIONS(SKIP = 1, DIRECT = TRUE)
LOAD DATA
INFILE 'mitarbeiter.data'
BADFILE 'mitarbeiter.bad'
DISCARDFILE 'mitarbeiter.dsc'
TRUNCATE
INTO TABLE mitarbeiter
FIELDS TERMINATED BY ","
(
 id integer external,
 vorname char,
 nachname char
)

Nachdem man in der Konsole die Umgebungsvariable NLS_LANG angepasst hat erhält man den folgenden Output:

D:\Oracle\blog_posting>set NLS_LANG = GERMAN_GERMANY.UTF8

D:\Oracle\blog_posting>sqlldr user@tns control=mitarbeiter.ctl
Kennwort:

SQL*Loader: Release 11.2.0.1.0 - Production on Sa Aug 13 21:44:18 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Ladevorgang beendet

Jetzt betrachten wir den Inhalt der Tabelle mitarbeiter und erhalten:

Anmerkung: die Umgebungsvariable NLS_LANG wurde zuvor auf GERMAN_GERMANY.WE8PC850 gesetzt.

Damit sind wir am Ende vom Post angelangt. Insbesondere die interaktive Ansicht der Datei in verschiedenen Kodierungen erscheint mir ein besonders nützliches Feature von gVim, das mir bis dato unbekannt war.

Multiple Regression mit UTL_NLA

Heute geht's erneut um die multiple Regression, die in diesem Beispiel mithilfe von UTL_NLA durchgeführt wird. UTL_NLA stellt dazu BLAS (Basic Linear Algebra Subroutines) und LAPACK (Linear Algebra PACKage) bereit.

Wie im vorherigen Post zu diesem Thema, werden die folgenden Daten verwendet:

y = Nachgefragte Menge in 1000 Stück
x₁ = Werbeausgaben in 100.000 Euro für Printmedien
x₂ = Werbeausgaben in 100.000 Euro für Fernsehen
x₃ = Preis pro Mengeneinheit in 100 Euro

y	x1	x2	x3
500	1	1	20
800	3	1	20
1500	3	3	18
2500	6	4	15
3200	6	6	12

Man erhält die Koeffizienten der Regressionsfunktion in Form des Vektors b durch:

Die Matrix X im Beispiel:

Der Vektor y im Beispiel:

Die Berechnung des Vektors b umfasst also die Bestimmung der Inversen, die Multiplikation von Matrizen und die Multiplikation einer Matrix mit einem Vektor. All diese Operationen lassen sich mithilfe von UTL_NLA durchführen. Für die Multiplikation von Matrizen wird BLAS_GEMM verwendet, für die Multiplikation einer Matrix mit einem Vektor wird BLAS_GEMV verwendet und für die Lösung des Gleichungssystems kommt LAPACK_GESV zum Einsatz.

Die Werte für die Matrizen werden entweder Zeilen- oder Spaltenweise angeben, je nachdem für welches Argument man sich für den Parameter PACK entscheidet. Im folgenden Beispiel wurde als Argument für den Parameter PACK 'R' gewählt. Die Deklaration stellt sich dann wie folgt dar:

v_matrix_xt UTL_NLA_ARRAY_DBL := UTL_NLA_ARRAY_DBL(  1 , 1 , 1 , 1 , 1, 
                                                     1 , 3 , 3 , 6 , 6, 
                                                     1 , 1 , 3 , 4 , 6,
                                                     20, 20, 18, 15, 12 );
 
v_matrix_x UTL_NLA_ARRAY_DBL := UTL_NLA_ARRAY_DBL(   1 , 1 , 1 , 20,
                                                     1 , 3 , 1 , 20,
                                                     1 , 3 , 3 , 18,
                                                     1 , 6 , 4 , 15, 
                                                     1 , 6 , 6 , 12 ); 

Der Aufruf der Prozedur UTL_NLA.BLAS_GEMM ermittelt nun die Matrix X^TX.

UTL_NLA.BLAS_GEMM(
   transa => 'N',
   transb => 'N',
   m      => 4,
   n      => 4,
   k      => 5,
   alpha  => 1,
   a      => v_matrix_xt,
   lda    => 5,
   b      => v_matrix_x,
   ldb    => 4,
   beta   => 0,
   c      => v_matrix_xtx,
   ldc    => 4,
   pack   => 'R');

Nun wird der Vektor X^Ty mithilfe von UTL_NLA.BLAS_GEMV bestimmt:

UTL_NLA.BLAS_GEMV(
   trans  => 'N',
   m      => 4,
   n      => 5,
   alpha  => 1,
   a      => v_matrix_xt,
   lda    => 5,
   x      => v_vector_y,
   incx   => 1,
   beta   => 0,
   y      => v_vector_xty,
   incy   => 1,
   pack   => 'R');

Im finalen Schritt, zur Bestimmung der Koeffizienten, ist das folgende Gleichungssystem zu lösen:

X^TXb=X^Ty

Diese Aufgabe erledigt die Prozedur UTL_NLA.LAPACK_GESV, wobei sich das Ergebnis nach dem Aufruf in der Variablen befindet, die als Argument für den Parameter b übergeben wurde.

UTL_NLA.LAPACK_GESV(
   n      => 4,
   nrhs   => 1,
   a      => v_matrix_xtx,
   lda    => 4,
   ipiv   => v_matrix_p,
   b      => v_vector_xty,
   ldb    => 1,
   info   => v_result,
   pack   => 'R');

Die Ausgabe der Koeffizienten ergibt:

for i in 1..v_vector_xty.count loop
 dbms_output.put_line('b' || (i-1) || ' = ' || to_number(v_vector_xty(i)));
end loop;

b0 = 1440.8163265303169
b1 = 168.36734693877926
b2 = 266.32653061226313
b3 = -69.38775510202751

Die Regressionsfunktion ergibt sich somit zu:

y = 1440,8163 + 168,3673∙x₁ + 266,3265∙x₂ - 69,3878∙x₃

Abschließend sei noch erwähnt, dass eine Matrix bzw. ein Vektor vom Typ UTL_NLA_ARRAY_DBL bzw. UTL_NLA_ARRAY_FLT maximal 1.000.000 Elemente enthalten kann.

Natürlich befinden sich die Daten meist in einer Tabelle und man will die Matrix nicht manuell angeben. Vielmehr möchte man die Elemente durch SQL und PL/SQL gewinnen und in einer Variablen vom entsprechenden Typ speichern.

Aber das ist was für einen anderen Post...

BINARY_DOUBLE und die implizite Typ-Konvertierung

Heute geht's um die Verwendung des Datentyps BINARY_DOUBLE bzw. BINARY_FLOAT und die Vermeidung von impliziter Typ-Konvertierung.

Als Beispiel dient das Wallis-Produkt zur Berechnung der Kreiszahl Pi. Die Berechnung erfolgt durch den folgenden anonymen PL/SQL-Block:

declare
 v_pi binary_double := 1.0;
begin
 for i in 1 .. 1000000 loop
  v_pi := v_pi * (1.0 + (1.0 / (4.0 * i * i - 1.0)));
 end loop;
 v_pi := v_pi * 2.0;
 dbms_output.put_line(v_pi);
end;
/
3,1415918681921307E+000

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:05.07

Die Berechnung dauert ungefähr fünf Sekunden und gibt die Zahl Pi bis auf fünf Stellen genau an. Doch an welcher Stelle befindet sich nun das Optimierungspotenzial?

Literale vom Typ BINARY_DOUBLE bzw. BINARY_FLOAT enthalten den Buchstaben d (D) bzw. f (F). Ansonsten handelt es sich um Literale vom Typ NUMBER, die im vorherigen PL/SQL-Block zur Typ-Konvertierung geführt haben.

Der angepasste PL/SQL-Block sieht dann wie folgt aus:

declare
 v_pi binary_double := 1.0d;
begin
 for i in 1 .. 1000000 loop
  v_pi := v_pi * (1.0d + (1.0d / (4.0d * i * i - 1.0d)));
 end loop;
 v_pi := v_pi * 2.0d;
 dbms_output.put_line(v_pi);
end;
/
3,1415918681921489E+000

PL/SQL-Prozedur erfolgreich abgeschlossen.

Abgelaufen: 00:00:00.46

Jetzt reduziert sich die Dauer der Berechnung deutlich auf unter eine Sekunde.

Also sollte man bei der Verwendung von BINARY_DOUBLE bzw. BINARY_FLOAT stets auf die korrekte Angabe von Literalen achten.

Verwendung von PL/SQL Subtypes

Heute geht's um die Verwendung von Subtypen in PL/SQL.

Dazu ein einfaches Beispiel:

declare
 subtype name_st is varchar2(30);
 v1 name_st;
 v2 name_st;
 v3 name_st;
 .
 .
 .
 vn name_st;
begin
 ...
end;

Der Subtyp NAME_ST schränkt den Typ VARCHAR2 auf eine Länge von 30 ein. Nun kann dieser zur Deklaration von Variablen verwendet werden. Zusätzlich dazu kann ein Subtyp um einen NOT NULL Constraint erweitert werden (in diesem Fall sind bei der Deklaration Werte vom entsprechenden Typ anzugeben):

declare
 subtype name_st is varchar2(30) not null;
 v1 name_st := '1';
 v2 name_st := '2';
 v3 name_st := '3';
 .
 .
 .
 vn name_st := 'n';
begin
 ...
end;

Ein Subtyp vom Typ BINARY_INTEGER bzw. PLS_INTEGER kann zudem eine Angabe zum zulässigen Bereich enthalten.

declare
 subtype percentage_st is binary_integer range 0 .. 100;
 v_percentage percentage_st;
begin
 ...
end;

Auch hier kann optional ein NOT NULL Constraint angegeben werden.

Leider funktioniert die Angabe des Bereichs nicht für die Typen BINARY_FLOAT und BINARY_DOUBLE. Siehe dazu auch das Oracle White Paper PL/SQL conditional compilation vom Oktober 2005, welches auf Seite 49 in Fußnote 58 eine solche Erweiterung vorschlägt.

Besonders hilfreich erscheint mir die Verwendung von Subtypen im Kontext von Packages. Zum einen spart man sich mitunter einige Zeilen PL/SQL Code durch die Angabe eines zulässigen Bereichs und zum anderen kann der Subtyp an mehreren Stellen im Package verwendet werden. Als Typ für die Deklaration einer Variablen, wie als Typ für einen Parameter von Funktionen oder Prozeduren.

Mehr dazu findet man in der Dokumentation.

Korrekte Implementierung der Aggregatsfunktion SUM

Heute geht es um die bekannte Aggregatsfunktion SUM und deren nicht ganz optimales Verhalten. Damit meine ich im Wesentlichen die Rückgabe der Funktion, im Falle einer leeren Tabelle.

Betrachten wir dazu das folgende Beispiel:

create table empty_tab
(
 val number
);

select sum(val) sum_val from empty_tab;

Das Ergebnis ist eine Tabelle, die eine Spalte mit dem Namen SUM_VAL enthält deren "Wert" NULL ist; in der Session wurde zuvor set null ? ausgeführt.

SUM_VAL               
-----------------------
?

Und genau an dieser Stelle befindet sich der Fehler in der Implementierung, denn korrekt wäre die Rückgabe von 0. Eine Summe ohne Summanden (die leere Summe) ergibt in der Mathematik 0.

Natürlich kann man z.B. die Funktion COALESCE verwenden, um das gewünschte Ergebnis zu erhalten.

select coalesce(sum(val),0) sum_val from empty_tab;

SUM_VAL               
-----------------------
0

Aber glücklicherweise kann man mithilfe von User-Defined-Aggregates eine eigene Funktion, die ich RSUM nenne, implementieren, die ein korrektes Verhalten zeigt.

Dazu zunächst der Objekt-Typ:

create or replace type sum_t as object
(
  v_sum number,

  static function ODCIAggregateInitialize(
   sctx IN OUT sum_t) return number,

  member function ODCIAggregateIterate(
   self IN OUT sum_t, 
   value IN number) return number,

  member function ODCIAggregateTerminate(
   self IN sum_t, 
   returnValue OUT number, 
   flags IN number) return number,

  member function ODCIAggregateMerge(
   self IN OUT sum_t, 
   ctx2 IN sum_t) return number
);

Die Variable v_sum dient der Speicherung der Summe. Der nun folgende Rumpf des Objek-Typs enthält dann die korrekte Initialisierung mit 0 (siehe Zeile 6 des folgenden Listings).

create or replace type body sum_t is

  static function ODCIAggregateInitialize(
   sctx IN OUT sum_t) return number is
  begin
   sctx := sum_t(0);
   return ODCIConst.Success;
  end;
  
  member function ODCIAggregateIterate(
   self IN OUT sum_t, 
   value IN number) return number is
  begin
   self.v_sum := self.v_sum + value;
   return ODCIConst.Success;
  end;
  
  member function ODCIAggregateTerminate(
   self IN sum_t, 
   returnValue OUT number, 
   flags IN number) return number is
  begin
   returnValue := self.v_sum;
   return ODCIConst.Success;
  end;
  
  member function ODCIAggregateMerge(
   self IN OUT sum_t, 
   ctx2 IN sum_t) return number is
  begin
   self.v_sum := self.v_sum + ctx2.v_sum;
   return ODCIConst.Success;
  end;

end;

Jetzt fehlt nur noch die Funktion, welche den Objekt-Typ verwendet.

create or replace function rsum (input number) return number
deterministic parallel_enable aggregate using sum_t;

Eine Ausführung der Funktion RSUM auf einer leeren Tabelle liefert dann das korrekte Ergebnis 0 zurück. Anmerkung: Da die Methode ODCIAggregateIterate nur für Nicht-NULL's aufgerufen wird, gilt dies auch für eine Tabelle, die in der entsprechenden Spalte nur NULL's enthält.

select rsum(val) rsum_val from empty_tab;                 

RSUM_VAL               
-----------------------
0

Ähnlich verhält es sich übrigens bei der Aggregatsfunktion AVG zur Berechnung des arithmetischen Mittels. Wendet man die Funktion auf eine leere Tabelle an erhält man als Rückgabe NULL. Eine Fehlermeldung der Art "divisor is equal to zero" erschien mir in diesem Falle schlüssiger.

Multiple Regression mit OLS_REGRESSION

Zur Demonstration dienen die folgenden Daten:

y = Nachgefragte Menge in 1000 Stück
x₁ = Werbeausgaben in 100.000 Euro für Printmedien
x₂ = Werbeausgaben in 100.000 Euro für Fernsehen
x₃ = Preis pro Mengeneinheit in 100 Euro

y	x1	x2	x3
500	1	1	20
800	3	1	20
1500	3	3	18
2500	6	4	15
3200	6	6	12

Man möchte mithilfe der multiple Regression überprüfen, ob und welche Abhängigkeiten zwischen dem Absatz und den Werbeausgaben für Printmedien, den Werbeausgaben für Fernsehen sowie dem Preis pro Mengeneinheit bestehen.

Um OLS_REGRESSION verwenden zu können ist ein Skript zu starten, das sich nach der Installation der Examples im Verzeichnis $ORACLE_HOME/plsql/demo befindet und den Namen olstype.sql trägt. Nun wird OLS_REGRESSION dazu verwendet, die oben genannte Fragestellung zu beantworten.

Die Daten befinden sich in einer Tabelle mit dem Namen REGRESSION_SAMPLE:

NACHFRAGE   WERBUNG_PRINT  WERBUNG_TV   PREIS
----------- -------------- ------------ ------
500         1              1            20
800         3              1            20
1500        3              3            18
2500        6              4            15
3200        6              6            12

Zunächst wird ein View erstellt, der eine Spalte vom Typ OLS_REGRESSION enthält. Beim Aufruf des Konstruktors sind die folgenden Argumente für die Parameter zu übergeben:

mean_y:

Arithmetisches Mittel der abhängigen Variable; in diesem Fall die Nachfrage.

Argument: avg(nachfrage)

variance_y:

Varianz der abhängigen Variable; in diesem Fall die Nachfrage. Es spielt dabei keine Rolle, ob man zur Berechnung der Varianz die Funktion VAR_POP oder VAR_SAMP verwendet. Jedoch ist zu beachten, dass man sich für eine der Funktionen entscheidet und diese dann in allen Argumenten verwendet. Bei der Funktion VAR_SAMP handelt es sich um die Stichprobenvarianz, welche mit (n-1) als Divisor gebildet wurde, während die Funktion VAR_POP n als Divisor verwendet.

Argument: var_pop(nachfrage)

MV:

Mittelwerte der unabhängigen Variablen in Form eines Vektors.

Argument:

utl_nla_array_dbl(
 avg(werbung_print),
 avg(werbung_tv),
 avg(preis))

VCM:

Varianz-Kovarianz-Matrix. Aufgrund der Symmetrie der Matrix erfolgt nur die Angabe der Elemente im oberen Dreieck.

Allgemeine Form der Varianz-Kovarianz-Matrix:



Da COV(X,X)=VAR(X) kann für alle Elemente der Hauptdiagonalen die Funktion für die Varianz verwendet werden. Zudem ist zu beachten, dass die Angabe Zeilenweise erfolgt. Im Beispiel stellt sich die gesamte Matrix wie folgt dar:



Ersetzt man auf der Hauptdiagonale noch die Kovarianz durch die Varianz erhält man:



Die Angabe erfolgt nun Zeilenweise in der Form:

VAR(X₁),COV(X₁,X₂),COV(X₁,X₃),VAR(X₂),COV(X₂,X₃),VAR(X₃)

Argument:

utl_nla_array_dbl(
 var_pop(werbung_print),
 covar_pop(werbung_print,werbung_tv),
 covar_pop(werbung_print,preis),
 var_pop(werbung_tv),
 covar_pop(werbung_tv,preis),
 var_pop(preis))

CV:

Kovarianz-Vektor der unabhängigen und abhängigen Variablen in Form eines Vektors:

Argument:

utl_nla_array_dbl(
 covar_pop(nachfrage,werbung_print),
 covar_pop(nachfrage,werbung_tv),
 covar_pop(nachfrage,preis))

Der View stellt sich dann wie folgt dar:

create or replace view ols_view as
select
 count(*) n,
 ols_regression
 (
  avg(nachfrage),
  var_pop(nachfrage),
  utl_nla_array_dbl
  (
   avg(werbung_print),
   avg(werbung_tv),
   avg(preis)
  ),
  utl_nla_array_dbl
  (
   var_pop(werbung_print),
   covar_pop(werbung_print,werbung_tv),
   covar_pop(werbung_print,preis),
   var_pop(werbung_tv),
   covar_pop(werbung_tv,preis),
   var_pop(preis)
  ),
  utl_nla_array_dbl
  (
   covar_pop(nachfrage,werbung_print),
   covar_pop(nachfrage,werbung_tv),
   covar_pop(nachfrage,preis)
  )
 ) ols_object
from
 regression_sample;

Als zusätzliche Spalte wurde die Anzahl der Zeilen aufgenommen, welche nachher zur Berechnung des korrigierten Bestimmtheitsmaßes verwendet wird. Aufbauend auf dieser View lassen sich die Koeffizienten der Regressionsfunktion und das Bestimmtheitsmaß ermitteln:

select
 o.ols_object.getEquation(3) funktionsgleichung,
 round(o.ols_object.getCoefficient(0),3) b0,
 round(o.ols_object.getCoefficient(1),3) b1,
 round(o.ols_object.getCoefficient(2),3) b2,
 round(o.ols_object.getCoefficient(3),3) b3,
 round(o.ols_object.getCorrelation(),6) r,
 round(power(o.ols_object.getCorrelation(),2),6) r_quadrat,
 round((1-((1-power(o.ols_object.getCorrelation(),2))*((o.n-1)/(o.n-1-o.ols_object.betacount)))),3) korrigiertes_r_quadrat
from
 ols_view o;

      B0       B1       B2       B3        R       R2      K_R2
-------- -------- -------- -------- -------- -------- ---------
1440,816  168,367  266,327  -69,388  ,999842  ,999685      ,999

Zusätzlich zu den Koeffizienten kann es von Interesse sein, die standardisierten Koeffizienten zu ermitteln. Diese geben Auskunft über den Einfluss der Variablen auf die Zielgröße. Man erhält die so genannten Beta-Faktoren oder Z-Scores durch folgende Abfrage:

with sd_query as
(
 select
  stddev(werbung_print) sd_x1,
  stddev(werbung_tv) sd_x2,
  stddev(preis) sd_x3,
  stddev(nachfrage) sd_y
 from
  regression_sample
)
select
 round((o.ols_object.getCoefficient(1)*sd.sd_x1/sd.sd_y),6) beta_x1,
 round((o.ols_object.getCoefficient(2)*sd.sd_x2/sd.sd_y),6) beta_x2,
 round((o.ols_object.getCoefficient(3)*sd.sd_x3/sd.sd_y),6) beta_x3
from
 ols_view o, sd_query sd;

BETA_X1                BETA_X2                BETA_X3
---------------------- ---------------------- ----------------------
0.320754               0.496462               -0.211222

Man stellt nun zum Beispiel fest, dass die Variable für TV-Werbung mehr als doppelt so viel Einfluss auf die Nachfrage besitzt, wie die Variable für den Preis.

Will man nun die Regressionsfunktion nutzen, um die Nachfrage zu prognostizieren kann man die Methode PREDICT verwenden. Ein Argument vom TYP UTL_NLA_ARRAY_DBL enthält dabei die Werte für die Regressionsfunktion; in diesem Beispiel genau drei Stück für die Variablen Werbung-Print, Werbung-TV und Preis. Die folgende Abfrage vergleicht die tatsächliche Nachfrage mit dem Wert der Regressionfunktion.

select
 nachfrage,
 o.ols_object.predict(utl_nla_array_dbl(werbung_print,werbung_tv,preis)) prediction
from
 ols_view o, regression_sample;

NACHFRAGE              PREDICTION
---------------------- ----------------------
500                    487.7551020409137
800                    824.48979591844341
1500                   1495.918367346951072
2500                   2475.51020408157385
3200                   3216.32653061211845

Will man nun die Nachfrage prognostizieren, wenn die Werbeausgaben für Printmedien 400.000€, die Werbeausgaben für TV 350.000€ und der Preis pro Mengeneinheit 1.600€ beträgt erhält man:

select o.ols_object.predict(utl_nla_array_dbl(4,3.5,16)) prediction
from ols_view o;

PREDICTION
----------------------
1936.22448979589853

Dijkstra-Algorithmus mit PL/SQL

Der Algorithmus von Edsger W. Dijkstra dient der Ermittlung der kürzesten Pfade ausgehend von einem Startknoten zu allen anderen Knoten in einem Graphen.

Die Darstellung des Graphen erfolgt in Form einer einfachen Tabelle mit den Spalten ID, SOURCE, DESTINATION und DISTANCE.

Als Beispiel dient der folgende Graph:



Die zugehörige Tabelle stellt sich dann wie folgt dar:

        ID     SOURCE DESTINATION DISTANCE
---------- ---------- ----------- --------
         1          1           2        4
         1          1           3        6
         1          1           4        8
         1          2           5        7
         1          2           3        1
         1          3           4        2
         1          3           5        5
         1          3           6        4
         1          4           6        5
         1          5           7        6
         1          6           5        1
         1          6           7        8

Auf dieser Grundlage sollen jetzt die kürzesten Wege ausgehend von Knoten 1 gefunden werden, wenngleich von jedem Knoten begonnen werden kann.

Der Aufruf der PL/SQL-Table-Function geht so:

select * from table(dijkstra(1,1)) order by vertex;

    VERTEX DISTANCE PREDECESSOR PATH
---------- -------- ----------- -----------------------
         1        0             1
         2        4           1 1 -> 2
         3        5           2 1 -> 2 -> 3
         4        7           3 1 -> 2 -> 3 -> 4
         5       10           3 1 -> 2 -> 3 -> 5
         6        9           3 1 -> 2 -> 3 -> 6
         7       16           5 1 -> 2 -> 3 -> 5 -> 7

Die Spalte DISTANCE gibt die minimale Distanz zum jeweiligen Knoten an, die Spalte PREDECESSOR enthält den direkten Vorgänger und die Spalte PATH enthält die Knoten, welche auf dem kürzesten Weg zurückgelegt wurden.

Nach dieser kurzen Demonstration der Funktionsweise hier der zugehörige Code:

-- Tabelle

drop table graph;

create table graph
(
 id integer,
 source integer,
 destination integer,
 distance number(38,2) not null
);

alter table graph
add constraint pk_graph
primary key (id, source, destination);

alter table graph
add constraint c_graph_source
check (source > 0);

alter table graph
add constraint c_graph_destination
check (destination > 0);

alter table graph
add constraint c_graph_distance
check (distance >= 0);

-- Daten

insert into graph(id,source,destination,distance)
values(1,1,2,4);
insert into graph(id,source,destination,distance)
values(1,1,3,6);
insert into graph(id,source,destination,distance)
values(1,1,4,8);
insert into graph(id,source,destination,distance)
values(1,2,5,7);
insert into graph(id,source,destination,distance)
values(1,2,3,1);
insert into graph(id,source,destination,distance)
values(1,3,4,2);
insert into graph(id,source,destination,distance)
values(1,3,5,5);
insert into graph(id,source,destination,distance)
values(1,3,6,4);
insert into graph(id,source,destination,distance)
values(1,4,6,5);
insert into graph(id,source,destination,distance)
values(1,5,7,6);
insert into graph(id,source,destination,distance)
values(1,6,5,1);
insert into graph(id,source,destination,distance)
values(1,6,7,8);
commit;

-- Type

drop type dijkstra_tab;

drop type dijkstra_t;

create type dijkstra_t as object
(
 vertex integer,
 distance binary_double,
 predecessor integer,
 path varchar2(4000)
);
/

create type dijkstra_tab as table of dijkstra_t;
/

-- Table function

create or replace
function dijkstra(p_graph_in in binary_integer, p_vertex_in in binary_integer) return dijkstra_tab pipelined
is
 
 graph_not_found exception;
 vertex_not_found exception;
 
 type unchecked_tab is table of binary_integer index by binary_integer;
 type predecessor_tab is table of binary_integer index by binary_integer;
 type distance_tab is table of binary_double index by binary_integer;
 
 cursor init_cur is
  select source vertex
  from graph
  where id = p_graph_in
   union
  select destination vertex
  from graph
  where id = p_graph_in;

 cursor distance_cur(pc_vertex_in in binary_integer) is
  select destination, distance
  from graph
  where id = p_graph_in and source = pc_vertex_in;
 
 i binary_integer;
 v_dummy varchar(10);
 v_unchecked unchecked_tab;
 v_predecessor predecessor_tab;
 v_distance distance_tab;
 v_minimum binary_integer;
 v_alternative binary_double;
 v_path varchar2(4000);
 
begin

 begin
  select 'TRUE' into v_dummy
  from dual
  where exists
  (
   select *
   from graph
   where id = p_graph_in
  );
 exception
  when no_data_found then
   raise graph_not_found;
 end;
 
 begin
  select 'TRUE' into v_dummy
  from dual
  where exists
  (
   select *
   from graph
   where id = p_graph_in and (source = p_vertex_in or destination = p_vertex_in)
  );
 exception
  when no_data_found then
   raise vertex_not_found;
 end;
 
 begin
  for init_rec in init_cur loop
   v_unchecked(init_rec.vertex) :=  null;
   v_predecessor(init_rec.vertex) := null;
   v_distance(init_rec.vertex) := binary_double_infinity;
  end loop;
  v_distance(p_vertex_in) := 0;
 end;

 begin
  while (v_unchecked.count > 0) loop
   v_minimum := null;
   i := v_unchecked.first;
   while (i is not null) loop
    if (v_minimum is null) then
     v_minimum := i;
    else
     if (v_distance(i) < v_distance(v_minimum)) then
      v_minimum := i;
     end if;
    end if;
    i := v_unchecked.next(i);
   end loop;
   v_unchecked.delete(v_minimum);
   for distance_rec in distance_cur(v_minimum) loop
    if (v_unchecked.exists(distance_rec.destination)) then
     v_alternative := v_distance(v_minimum) + distance_rec.distance;
     if (v_alternative < v_distance(distance_rec.destination)) then
      v_distance(distance_rec.destination) := v_alternative;
      v_predecessor(distance_rec.destination) := v_minimum;
     end if;
    end if;
   end loop;
   if (v_distance(v_minimum) = binary_double_infinity) then
    v_path := '';
   else
    v_path := v_minimum;
   end if;
   i := v_predecessor(v_minimum);
   while (i is not null) loop
    v_path :=  i || ' -> ' || v_path;
    i := v_predecessor(i);
   end loop;
   pipe row( dijkstra_t (
    v_minimum, 
    v_distance(v_minimum), 
    v_predecessor(v_minimum), 
    v_path ) );
  end loop;
 end;

exception
 when graph_not_found then
  raise_application_error(-20010, 'DIJKSTRA: The graph was not found.');
 when vertex_not_found then
  raise_application_error(-20011, 'DIJKSTRA: The vertex to start the algorithm was not found.');
 when others then
  raise_application_error(-20012, 'DIJKSTRA: Unexpected error: ' || substr(1,200,SQLERRM)); 
end;