create table probabilities(
     sum_of_dies  number
    ,percentage   number
    )
/

create or replace package probabilities_sql
as
  --
  -- The procedure "calculate" calculates all probabilities with
  -- p_number_of_dies throws of a die. The results of the calculation
  -- are inserted into the table probabilities.
  --
  procedure calculate (p_number_of_throws in number)
  ;
end probabilities_sql;
/

create or replace package body probabilities_sql
as
  procedure calculate (p_number_of_throws in number)
  is
  begin
    insert into probabilities
    ( sum_of_dies
    , percentage
    )
    with number_of_die_faces as (select count(*) cnt from die)
    , all_probabilities as
    ( select sum_value
           , prob
           , i
        from --
             -- Generate as many rows as there are possible combinations of the
             -- dies. This equals: power(,p_number_of_throws).
             -- For example: with a traditional die (6 faces) and 3 throws, there
             -- are power(6,3) = 216 rows with a l-value running from 1 until 216.
             --
             ( select level l
                 from number_of_die_faces
              connect by level <= power(cnt,p_number_of_throws)
             )
           , number_of_die_faces
       model
             --
             -- A reference model to be able to quickly lookup the face_value
             -- and probability when provided a face_id
             --
             reference r on (select face_id, face_value, probability from die)
               dimension by (face_id)
               measures (face_value,probability)
             main m
               --
               -- Each combination is in a different partition.
               -- Which means it is easy to parallellize if necessary.
               --
               partition by (l rn, cnt)
               dimension by (0 i)
               measures (0 die_face_id, 0 sum_value, 1 prob, l remainder)
             --
             -- Iterate as many times as there are throws of the die.
             --
             rules iterate (1000) until (iteration_number+1=p_number_of_throws)
             --
             -- For each throw of the die, calculate the face_id, remainder, the
             -- sum and probability. For the sum and probability, the reference
             -- model is used as a lookup. Each iteration overwrites the previous
             -- one.
             --
             ( die_face_id[0] = 1 + mod(remainder[0]-1,cv(cnt))
             , remainder[0]   = ceil((remainder[0] - die_face_id[0] + 1) / cv(cnt))
             , sum_value[0]   = sum_value[0] + face_value[die_face_id[0]]
             , prob[0]        = prob[0] * probability[die_face_id[0]]
             )
    )
    --
    -- All probabilities of each possible combination are now calculated.
    -- Now, sum them all up per sum of all face_values.
    --
    select sum_value
         , sum(prob)
      from all_probabilities
     group by sum_value
    ;
  end calculate;
end probabilities_sql;
/

create or replace package probabilities_plsql
as
  --
  -- The procedure "calculate" calculates all probabilities with
  -- p_number_of_dies throws of a die. The results of the calculation
  -- are inserted into the table probabilities.
  --
  procedure calculate (p_number_of_throws in number)
  ;
end probabilities_plsql;
/

create package body probabilities_plsql
as
  g_number_of_die_faces number(4)
  ;
  procedure initialization
  --
  -- Calculate the number of die faces (6 in case of a traditional die) only once.
  --
  is
  begin
    select count(*)
      into g_number_of_die_faces
      from die
    ;
  end initialization
  ;
  function face_value
  ( p_face_id in die.face_id%type
  ) return die.face_value%type result_cache relies_on (die)
  --
  -- A lookup function returning the face_value of a given face_id.
  -- This function is called multiple times for the same face_id's and
  -- is therefore optimized by the result_cache hint.
  --
  is
    l_face_value die.face_value%type;
  begin
    select face_value
      into l_face_value
      from die
     where face_id = p_face_id
    ;
    return l_face_value;
  end face_value
  ;
  function probability
  ( p_face_id in die.face_id%type
  ) return die.probability%type result_cache relies_on (die)
  --
  -- A lookup function returning the probability of a given face_id.
  -- This function is called multiple times for the same face_id's and
  -- is therefore optimized by the result_cache hint.
  --
  is
    l_probability die.probability%type;
  begin
    select probability
      into l_probability
      from die
     where face_id = p_face_id
    ;
    return l_probability;
  end probability
  ;
  procedure calculate (p_number_of_throws in number)
  is
    l_die_face_id die.face_id%type;
    l_remainder   number(10);
    l_sum         probabilities.sum_of_dies%type;
    l_probability probabilities.percentage%type
    ;
    type ta_probabilities is table of probabilities%rowtype index by pls_integer;
    a_probabilities ta_probabilities;
  begin
    --
    -- Loop as many times as there are possible combinations of the
    -- dies. This number equals: power(,p_number_of_throws).
    -- For example: with a traditional die (6 faces) and 3 throws, there
    -- are power(6,3) = 216 iterations.
    --
    for i in 1 .. power(g_number_of_die_faces,p_number_of_throws)
    loop
      l_remainder := i;
      l_sum := 0;
      l_probability := 1;
      --
      -- For each combination, iterate over all throws of each individual die,
      -- and calculate the face_id of that die (using l_die_face_id and
      -- l_remainder) and use that face_id to calculate the sum of the die
      -- face values and the probability.
      --
      for j in 1 .. p_number_of_throws
      loop
        l_die_face_id := 1 + mod(l_remainder-1, g_number_of_die_faces);
        l_remainder   := ceil((l_remainder-l_die_face_id+1)/g_number_of_die_faces);
        l_sum         := l_sum + face_value(l_die_face_id);
        l_probability := l_probability * probability(l_die_face_id);
      end loop;
      --
      -- Sum up all the probabilities with the same sum.
      --
      a_probabilities(l_sum).sum_of_dies := l_sum;
      a_probabilities(l_sum).percentage :=
        nvl(a_probabilities(l_sum).percentage,0) + l_probability
      ;
    end loop;
    --
    -- Bulk insert all calculated probabilities into the table PROBABILIIES.
    --
    forall i in indices of a_probabilities
      insert into probabilities
      values a_probabilities(i)
    ;
  end calculate
  ;
begin
  initialization;
end probabilities_plsql;
/