Objekttyp als Spaltentyp verwenden

Heute geht's um die Verwendung von Objekttypen als Spaltentyp in einer Tabelle. Damit besteht die Möglichkeit einen Constraint an einen Typ statt an eine Tabelle zu binden. Das macht insbesondere dann Sinn, wenn der gleiche Typ mit den gleichen Restriktionen in mehreren Tabellen verwendet wird; im Normalfall ist der Constraint pro Tabelle zu erstellen, in der dieser Typ verwendet wird. Als Beispiel dient ein Typ für die Angabe von Stückzahlen, der wie folgt als Objekttyp erstellt wird:

create type qty as object
(
 q integer,
 constructor function qty(q in integer) return self as result,
 member function the_q return integer,
 pragma restrict_references(the_q, wnds)
);
/

Dabei gilt die Restriktion, dass sich die Stückzahl zwischen 0 und 5000 (jeweils inklusive) befinden muss. Dieser Sachverhalt wird im Rahmen des Konstruktors überprüft und gegebenenfalls eine Exception ausgelöst.

create type body qty is
 
 constructor function qty(q in integer) return self as result is
 begin
  if (q >= 0 and q <= 5000) then
   self.q := q;
   return;
  else
   raise_application_error(-20010, 'This is not a valid value for type QTY.');
  end if;
 end;

 member function the_q return integer is
 begin
  return self.q;
 end;
 
end;
/

Die zusätzliche Funktion THE_Q kann später dazu verwendet werden, die Stückzahl als skalaren Typ zu erhalten; im Beispiel einen Wert vom Typ integer. Nun kann dieser Objekttyp als Spaltentyp verwendet werden, wie das folgende Beispiel zeigt:

create table warehouse
(
 product_id integer,
 available qty,
 constraint pk_warehouse primary key (product_id)
);

Bei jedem INSERT-Befehl wird dann der Konstruktor aufgerufen, welcher die notwendige Überprüfungen vornimmt und mitunter eine Exception auslöst; auch bei einem UPDATE-Befehl erfolgt eine enstprechende Überprüfung.

insert into warehouse (product_id, available)
values (1, qty(100));

-- 1 rows inserted

insert into warehouse (product_id, available)
values (2, qty(1000));

-- 1 rows inserted

insert into warehouse (product_id, available)
values (3, qty(10000));

-- SQL-Fehler: ORA-20010: This is not a valid value for type QTY.

Bei Abfragen kann man dann auf die Funktion THE_Q zurückgreifen, um die Stückzahl als integer zu erhalten.

select 
 w.product_id, 
 w.available.the_q() qty_int
from 
 warehouse w;

Natürlich kann der erstellte Typ in mehreren Tabellen verwendet werden. Somit besteht die Möglichkeit einen Constraint pro Typ statt pro Tabelle, in der dieser Typ verwendet wird, zu definieren.

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.