If you prefer to read in German, then you can find a translation here.

On my post Calculating probabilities with N throws of a die, I received a comment by Narendra saying:

I hope you are not serious about your last statement with some comments I'm sure it's not that hard to maintain....:)

But I was serious. However, it's a sentiment I hear a lot: after my presentation Do more with SQL I received similar comments claiming PL/SQL would be easier in some cases. And according to Iggy Fernandez, Steven Feuerstein has said here:

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.

And I don't get it.

Well, I can understand why that would be a first reaction when seeing some undocumented long piece of SQL containing some of the newer SQL constructs. But when thinking a bit longer about the subject, I don't think it's a totally fair reaction. Here's why.

- When faced with a challenging problem, most people tend to resort to the language they are most comfortable with. I know I do. For example, I'm way better with PL/SQL than I am with Java. So when faced with a hard algorithm, I'll always use PL/SQL. And I bet a Java programmer reasons the other way round. So when saying that straight SQL is harder to maintain than PL/SQL, I guess you are really saying that your PL/SQL skills are very good, but your SQL skills are, well, somewhat less than very good. That's no problem at all, since you will still be able to build applications effectively. But I don't think the language itself is to blame, it's the skills of the people talking that language.
- In production code, I see PL/SQL code more often being documented with comments than SQL code. But every piece of production code that isn't straightforward should be documented. Why does SQL code rarely contain comments? Probably because most SQL statements in production are of the basic SELECT ... FROM ... WHERE kind. And when used to not commenting those easy SQL statements, the harder ones are almost automatically lacking comments as well. When you start adding comments for SQL code, as well as for your PL/SQL code, then it will be a reason less for why you may find SQL code harder to grasp.
- In PL/SQL you are able to split up a complex task into many simple tasks. Each task is then performed by a single function or a procedure. And those functions and procedures have clear names, making the code self documenting. This is called modularizing your code and you've probably been taught about this subject in school already. Complex SQL used to be just a giant piece of text lacking this ability, but from Oracle9i onwards, Oracle gave us the WITH-clause (also known as subquery factoring). With this clause you can give a meaningful name to each subpiece of SQL. Thus, the same kind of modularization we achieve with PL/SQL, is possible with SQL since 9i as well.

And so I think SQL is equally readable, just a little more compact. And often faster due to less context switching. And more often correct because it's one read consistent query instead of several queries taking place at different times.

An example using the code from Calculating probabilities with N throws of a die, of how it could look like in production. Difference is that the results of that query are inserted into a table. I made both variants "production like" by documenting them well. First a package using SQL:

rwijk@ORA11GR1> 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 is aangemaakt.

rwijk@ORA11GR1> create 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 is aangemaakt.

And a package doing it the PL/SQL way, using the same idea:

rwijk@ORA11GR1> create 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 is aangemaakt.

rwijk@ORA11GR1> 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 is aangemaakt.

Note that this algorithm is not data intensive and the PL/SQL variant here is actually faster than the SQL variant due to 11g's result cache, but that's not the point here. The point is of course readability. Do you really think the SQL variant is much more complex than the PL/SQL variant? I'd love to hear your thoughts about this subject, whether you agree or not.

## UPDATE

Three nice follow-up blog entries:

by Chen Shapira

by Laurent Schneider

by H.Tonguç Yılmaz