Tutorials

Wählen zwischen SQL und PL/SQL

Eine Übersetzung aus dem Englischen. Original von Rob van Wijk

Auf meinen Beitrag Wahrscheinlichkeiten bei N Würfen eines Würfels berechnen erhielt ich einen Kommentar von Narendra, der sagt:

Ich hoffe, dass du die letzte Aussage “dass es mit einigen Kommentaren gar nicht so schwer wartbar ist“ nicht ernst meinst...:)

Aber ich meinte es ernst. Dennoch, es ist eine Klage, die ich oft höre: nach meiner Präsentation Do more with SQL bekomme ich ähnliche Kommentare, die behaupten, PL/SQL wäre in manchen Fällen einfacher. Und gemäß Iggy Fernandez hat Steven Feuerstein gesagt:

Some people can perform seeming miracles with straight SQL, but the statements can end up looking like pretzels created by someone who is experimenting with hallucinogens
(Manche Leute können mit reinem SQL wahre Wunder vollbringen, aber am Ende können die Statements aussehen wie Brezen, die jemand gemacht hat, der mit Halluzinogenen experimentiert).

Aber ich verstehe es nicht.

Nun, ich kann verstehen, warum das die erste Reaktion sein kann, wenn jemand ein langes undokumentiertes Stück SQL sieht, das einige der neueren Konstrukte enthält. Aber wenn ich länger darüber nachdenke glaube ich, dass das keine faire Reaktion ist und zwar deshalb:

Wenn man mit einem anspruchsvollen Problem konfrontiert wird, tendieren die meisten zu der Sprache, die sie am besten kennen. Ich tue es jedenfalls. Zum Beispiel bin ich viel besser in PL/SQL als in Java. Wenn ich also mit einem schweren Algorithmus konfrontiert werde, werde ich immer PL/SQL verwenden. Und ich wette, ein Java Programmierer argumentiert genau anders herum. Wenn du also sagst, dass reines SQL schwerer zu warten ist als PL/SQL, dann sagst du eigentlich, dass deine PL/SQL Kenntnisse sehr gut sind, aber deine SQL Fähigkeiten, nun, etwas weniger gut. Das ist überhaupt kein Problem, weil du immer noch effektive Anwendungen entwickeln kannst. Aber ich denke, dass nicht die Sprache selbst schuld ist, es sind die Fertigkeiten der Leute, die diese Sprache sprechen.

In produktivem Code finde ich PL/SQL Code häufiger mit Kommentaren dokumentiert als SQL Code. Aber jeder produktive Code, der nicht selbsterklärend ist sollte dokumentiert werden. Warum enthält SQL Code selten Kommentare? Möglicherweise, weil die meisten SQL Statements in Produktion von der einfachen Art SELECT ... FROM ... WHERE sind. Und wenn man gewöhnt ist diese einfachen SQL Statements nicht zu kommentieren, dann werden den schwierigeren automatisch auch die Kommentare fehlen. Wenn du anfängst Kommentare zum SQL Code hinzuzufügen, genauso wie bei deinem PL/SQL Code, dann gibt es einen Grund weniger den SQL Code schwer verständlich zu finden.

In PL/SQL kannst du komplexe Vorgänge in viele einfache auftrennen. Jeder Vorgang wird dann von einer einzelnen Funktion oder Prozedur durchgeführt. Diese Funktionen und Prozeduren haben klare Namen, die den Code selbstdokumentierend machen. Das wird Modularisierung genannt und dir wurde das vielleicht schon in der Schule gelehrt. Komplexes SQL war bisher ein gigantischer Haufen Text ohne diese Möglichkeit, aber seit Oracle 9i stellt uns Oracle die WITH-Clause zur Verfügung (auch bekannt als subquery factoring). Mit dieser Klausel kann man jedem Teilstück des SQL einen sprechenden Namen geben. So ist dieselbe Modularisierung wie in PL/SQL seit 9i auch in SQL möglich

Und deshalb denke ich, dass SQL genauso lesbar ist, lediglich etwas kompakter; und oft schneller auf Grund der fehlenden Kontext-Switches

Ein Beispiel, das den Code von Wahrscheinlichkeiten bei N Würfen eines Würfels berechnen verwendet, wie es in Produktion aussehen könnte. Der Unterschied ist, dass die Ergebnisse der Abfrage in eine Tabelle eingefügt werden. Ich habe beide Varianten "production like" gemacht, indem ich sie dokumentiert habe. Zunächst ein Package, das SQL verwendet:

SQL> create package probabilities_sql
  2  as
  3    --
  4    -- The procedure "calculate" calculates all probabilities with
  5    -- p_number_of_dies throws of a die. The results of the calculation
  6    -- are inserted into the table probabilities.
  7    --
  8    procedure calculate (p_number_of_throws in number)
  9    ;
 10  end probabilities_sql;
 11  /

Package wurde erstellt.

SQL> create or replace package body probabilities_sql
  2  as
  3    procedure calculate (p_number_of_throws in number)
  4    is
  5    begin
  6      insert into probabilities
  7      ( sum_of_dies
  8      , percentage
  9      )
 10      with number_of_die_faces as (select count(*) cnt from die)
 11      , all_probabilities as
 12      ( select sum_value
 13             , prob
 14             , i
 15          from --
 16               -- Generate as many rows as there are possible combinations of the
 17               -- dies. This equals: power(,p_number_of_throws).
 18               -- For example: with a traditional die (6 faces) and 3 throws, there
 19               -- are power(6,3) = 216 rows with a l-value running from 1 until 216.
 20               --
 21               ( select level l
 22                   from number_of_die_faces
 23                connect by level <= power(cnt,p_number_of_throws)
 24               )
 25             , number_of_die_faces
 26         model
 27               --
 28               -- A reference model to be able to quickly lookup the face_value
 29               -- and probability when provided a face_id
 30               --
 31               reference r on (select face_id, face_value, probability from die)
 32                 dimension by (face_id)
 33                 measures (face_value,probability)
 34               main m
 35                 --
 36                 -- Each combination is in a different partition.
 37                 -- Which means it is easy to parallellize if necessary.
 38                 --
 39                 partition by (l rn, cnt)
 40                 dimension by (0 i)
 41                 measures (0 die_face_id, 0 sum_value, 1 prob, l remainder)
 42               --
 43               -- Iterate as many times as there are throws of the die.
 44               --
 45               rules iterate (1000) until (iteration_number+1=p_number_of_throws)
 46               --
 47               -- For each throw of the die, calculate the face_id, remainder, the
 48               -- sum and probability. For the sum and probability, the reference
 49               -- model is used as a lookup. Each iteration overwrites the previous
 50               -- one.
 51               --
 52               ( die_face_id[0] = 1 + mod(remainder[0]-1,cv(cnt))
 53               , remainder[0]   = ceil((remainder[0] - die_face_id[0] + 1) / cv(cnt))
 54               , sum_value[0]   = sum_value[0] + face_value[die_face_id[0]]
 55               , prob[0]        = prob[0] * probability[die_face_id[0]]
 56               )
 57      )
 58      --
 59      -- All probabilities of each possible combination are now calculated.
 60      -- Now, sum them all up per sum of all face_values.
 61      --
 62      select sum_value
 63           , sum(prob)
 64        from all_probabilities
 65       group by sum_value
 66      ;
 67    end calculate;
 68  end probabilities_sql;
 69  /

Package Body wurde erstellt.

Und ein Package, das es mit PL/SQL macht, unter Verwendung der gleichen Idee:

SQL> create or replace package probabilities_plsql
  2  as
  3    --
  4    -- The procedure "calculate" calculates all probabilities with
  5    -- p_number_of_dies throws of a die. The results of the calculation
  6    -- are inserted into the table probabilities.
  7    --
  8    procedure calculate (p_number_of_throws in number)
  9    ;
 10  end probabilities_plsql;
 11  /

Package wurde erstellt.

SQL> create package body probabilities_plsql
  2  as
  3    g_number_of_die_faces number(4)
  4    ;
  5    procedure initialization
  6    --
  7    -- Calculate the number of die faces (6 in case of a traditional die) only once.
  8    --
  9    is
 10    begin
 11      select count(*)
 12        into g_number_of_die_faces
 13        from die
 14      ;
 15    end initialization
 16    ;
 17    function face_value
 18    ( p_face_id in die.face_id%type
 19    ) return die.face_value%type result_cache relies_on (die)
 20    --
 21    -- A lookup function returning the face_value of a given face_id.
 22    -- This function is called multiple times for the same face_id's and
 23    -- is therefore optimized by the result_cache hint.
 24    --
 25    is
 26      l_face_value die.face_value%type;
 27    begin
 28      select face_value
 29        into l_face_value
 30        from die
 31       where face_id = p_face_id
 32      ;
 33      return l_face_value;
 34    end face_value
 35    ;
 36    function probability
 37    ( p_face_id in die.face_id%type
 38    ) return die.probability%type result_cache relies_on (die)
 39    --
 40    -- A lookup function returning the probability of a given face_id.
 41    -- This function is called multiple times for the same face_id's and
 42    -- is therefore optimized by the result_cache hint.
 43    --
 44    is
 45      l_probability die.probability%type;
 46    begin
 47      select probability
 48        into l_probability
 49        from die
 50       where face_id = p_face_id
 51      ;
 52      return l_probability;
 53    end probability
 54    ;
 55    procedure calculate (p_number_of_throws in number)
 56    is
 57      l_die_face_id die.face_id%type;
 58      l_remainder   number(10);
 59      l_sum         probabilities.sum_of_dies%type;
 60      l_probability probabilities.percentage%type
 61      ;
 62      type ta_probabilities is table of probabilities%rowtype index by pls_integer;
 63      a_probabilities ta_probabilities;
 64    begin
 65      --
 66      -- Loop as many times as there are possible combinations of the
 67      -- dies. This number equals: power(,p_number_of_throws).
 68      -- For example: with a traditional die (6 faces) and 3 throws, there
 69      -- are power(6,3) = 216 iterations.
 70      --
 71      for i in 1 .. power(g_number_of_die_faces,p_number_of_throws)
 72      loop
 73        l_remainder := i;
 74        l_sum := 0;
 75        l_probability := 1;
 76        --
 77        -- For each combination, iterate over all throws of each individual die,
 78        -- and calculate the face_id of that die (using l_die_face_id and
 79        -- l_remainder) and use that face_id to calculate the sum of the die
 80        -- face values and the probability.
 81        --
 82        for j in 1 .. p_number_of_throws
 83        loop
 84          l_die_face_id := 1 + mod(l_remainder-1, g_number_of_die_faces);
 85          l_remainder   := ceil((l_remainder-l_die_face_id+1)/g_number_of_die_faces);
 86          l_sum         := l_sum + face_value(l_die_face_id);
 87          l_probability := l_probability * probability(l_die_face_id);
 88        end loop;
 89        --
 90        -- Sum up all the probabilities with the same sum.
 91        --
 92        a_probabilities(l_sum).sum_of_dies := l_sum;
 93        a_probabilities(l_sum).percentage :=
 94          nvl(a_probabilities(l_sum).percentage,0) + l_probability
 95        ;
 96      end loop;
 97      --
 98      -- Bulk insert all calculated probabilities into the table PROBABILIIES.
 99      --
100      forall i in indices of a_probabilities
101        insert into probabilities
102        values a_probabilities(i)
103      ;
104    end calculate
105    ;
106  begin
107    initialization;
108  end probabilities_plsql;
109  /

Package Body wurde erstellt.

Beachte, dass dieser Algorithmus nicht datenintensiv ist und die PL/SQL Variante hier wegen des 11g Result Cache sogar schneller ist, aber darum geht es hier nicht. Es geht natürlich um die Lesbarkeit. Glaubst du wirklich, dass die SQL Variante viel komplexer ist als die PL/SQL Variante? Ich würde gerne dien Meinung dazu hören, ob du zustimmst oder nicht.

oracle@matzberger.de Für Kontakte aller Art, auch Fehlermeldungen zum Tutorial Impressum