Johann ELSASS

Developer

SQL

My experience with SQL is essentially with the following databases:

MySQL: using the standards SQL functions or multiple inserts for big data. On a network, it seemed that optimal packet size was between 32 Ko and 64Ko.
SQL Server 2005: using standard SQL functions and SQL Server Management Studio.

SQL language is a bit underrated. If you combine it with some scripting features like with PL/SQL, you can get functional applications. Of course, not like under Windows, the interface is just text commands. I had a look at Oracle PL/SQL, which reminds me of Pascal in many aspects, for example declaring the variables before the block of instructions and nested procedures.

Here is for example a code of PL/SQL to compute prime numbers using a table. This is clearly not the most efficient way, as using RAM is of course faster than accessing the disk.

It is composed of two packages. primes_actions provides access to a table containing numbers to be checked, and primes_compute contains the code the do the sieve of Eratosthene.

First create a table in the Oracle database:

create table primes
             (id int not null,
              isprime char(1)
                      check (isprime in ('Y','N'))
                      not null, 
              primary key (id));

Then define a package that interact with this table:

Show code

create or replace package primes_actions is

    subtype t_number is primes.id%type;
    subtype t_isprime is primes.isprime%type;
    type t_number_table is table of t_number;

    function isprime_to_bool(p_isprime t_isprime)
             return boolean;
    function bool_to_isprime(p_bool boolean)
             return t_isprime;
    procedure init_table (p_max_value t_number,
                          p_isprime_bool boolean);
    function can_be_prime (p_prime t_number)
             return boolean;
    function collect_primes
             return t_number_table;
    procedure update_range (p_primes t_number_table,
                            p_areprime_bool boolean);
    
end primes_actions;

It is better to use a carefully defined interface that allows to interact with functions with parameters that are standard types like boolean or user defined types declared only once in this package like t_number. Here is the body of the package:

Show code

create or replace package body primes_actions is

    function bool_to_isprime(p_bool in boolean)
             return t_isprime is
    begin
      if p_bool then
        return 'Y';
      else
        return 'N';
      end if;    
    end bool_to_isprime;
    
    function isprime_to_bool(p_isprime in t_isprime)
             return boolean is
    begin
      if p_isprime = 'Y' then
        return true;
      else
        return false;
      end if;
    end isprime_to_bool;

    procedure init_table (p_max_value in t_number,
                          p_isprime_bool in boolean) is
      v_all t_number_table;    
      v_already t_number;
      v_start t_number;
      v_isprime t_isprime;
    begin
      --convert parameter
      v_isprime := bool_to_isprime(p_isprime_bool);
      
      --slice the existing table
      delete from primes where id > p_max_value;
      update primes set isprime = v_isprime;
      
      --expand the existing table if needed
      select max(id) into v_already from primes;
      if v_already is null then
          v_start := 2;
      else
          v_start := v_already+1;
      end if;
      if v_start <= p_max_value then
          v_all := t_number_table();
          for i in v_start .. p_max_value
          loop
            v_all.extend();
            v_all(v_all.last) := i;
          end loop;
          forall i in v_all.first .. v_all.last
            insert into primes (id,isprime)
                   values (v_all(i),v_isprime);
      end if;
    end init_table;

    function collect_primes
             return t_number_table is
      v_primes   t_number_table;
      v_isprime  t_isprime;
    begin
      v_isprime := bool_to_isprime(true);
      select id
          bulk collect into v_primes
          from primes
          where isprime = v_isprime
          order by id; 
      return v_primes;
    end collect_primes;

    procedure update_range (p_primes in t_number_table,
                            p_areprime_bool in boolean) is
      v_isprime  t_isprime;
    begin
      v_isprime := bool_to_isprime(p_areprime_bool);
      forall n in p_primes.first .. p_primes.last
        update primes set isprime = v_isprime
               where id = p_primes(n);
    end update_range;
            
    function can_be_prime (p_prime t_number)
             return boolean is
      v_isprime  t_isprime;
    begin
      select isprime into v_isprime
             from primes
             where id = p_prime;
      return isprime_to_bool(v_isprime);
    end can_be_prime;

end primes_actions;

Using bulk collect, insert and update make the program faster. However it requires to used the table type. Now let's define a package to do the sieve or Eratosthene:

Show code

create or replace package primes_compute is

    procedure show_primes
              (p_maxvalue in primes_actions.t_number);
    procedure private_sieve_init
              (p_max_value in primes_actions.t_number);    
    procedure private_sieve_run
              (p_max_value in primes_actions.t_number);

end;

Here is the implementation:

Show code

create or replace package body primes_compute is

    procedure private_sieve_init
              (p_max_value in primes_actions.t_number) is
    begin
      --presuppose all numbers are prime
      primes_actions.init_table (p_max_value, true);
    end;

    procedure private_sieve_run
              (p_max_value in primes_actions.t_number) is
      i  primes_actions.t_number;
      
      procedure remove_multiple
                (p_basevalue in primes_actions.t_number) is
        v_multiple  primes_actions.t_number;
        v_exclude   primes_actions.t_number_table;
      begin
          if p_basevalue < 1 then
            return; 
          end if;    

          v_exclude := primes_actions.t_number_table();
          v_multiple := p_basevalue;
          loop
            v_multiple := v_multiple + p_basevalue;
            exit when v_multiple > p_max_value;            
            v_exclude.extend;
            v_exclude(v_exclude.last) := v_multiple;
          end loop;
          primes_actions.update_range(v_exclude, false);
      end;
      
    begin      
      for i in 2 .. p_max_value
      loop
        if primes_actions.can_be_prime(i) then
          remove_multiple(i);
        end if;
      end loop;
    end;
    
    procedure show_primes
              (p_maxvalue in primes_actions.t_number) is
    
      v_timer_starttime  timestamp;
      v_acc       varchar2(2000);

      v_primes    primes_actions.t_number_table;      
      v_curprime  primes_actions.t_number;

      procedure timer_start is
      begin
        v_timer_starttime := SYSTIMESTAMP;
      end;
      
      function timer_elapsed return varchar is
      begin
        return (extract(second from
               (SYSTIMESTAMP - v_timer_starttime))
               * 1000) || ' ms';
      end;
      
      procedure timer_log(p_text varchar) is
      begin
        dbms_output.put_line(timer_elapsed() || ': ' || p_text);
      end;
      
      procedure output_start is
      begin
        v_acc := '';
      end;
      
      procedure output_next(p_element varchar) is
        v_new_length  int;
      begin
        v_new_length := length(v_acc) + length(p_element) + 1;
        if (v_new_length > 80) and
            (length(v_acc) > 0) then
          dbms_output.put_line(v_acc);
          v_acc := p_element || ', ';
        elsif (v_new_length = 80) then
          v_acc := v_acc || p_element || ',';
          dbms_output.put_line(v_acc);
          v_acc := '';          
        else
          v_acc := v_acc || p_element || ', ';
        end if;                
      end;
      
      procedure output_end is
      begin
          if length(v_acc) > 0 then
            dbms_output.put_line(v_acc || ' ...');
          end if;      
      end;
             
    begin
      timer_start();
      
      timer_log('Initialising sieve...');
      private_sieve_init(p_maxvalue);      
      
      timer_log('Computing sieve...');
      private_sieve_run(p_maxvalue);            
      
      timer_log('Done.');
      
      output_start();
      v_primes := primes_actions.collect_primes();
      v_curprime := v_primes.first;
      while v_curprime is not null
      loop
        output_next(v_primes(v_curprime));
        v_curprime := v_primes.next(v_curprime);
      end loop;
      output_end();
    end;
    
end;

Now we just have to run show_primes:

begin primes_compute.show_primes(500); end;

0 ms: Initialising sieve...
30 ms: Computing sieve...
165 ms: Done.
2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47, 53, 59, 61, 67, 71, 73, 
79, 83, 89, 97, 101, 103, 107, 109, 113, 127, 131, 137, 139, 149, 151, 157, 163,
167, 173, 179, 181, 191, 193, 197, 199, 211, 223, 227, 229, 233, 239, 241, 251, 
257, 263, 269, 271, 277, 281, 283, 293, 307, 311, 313, 317, 331, 337, 347, 349, 
353, 359, 367, 373, 379, 383, 389, 397, 401, 409, 419, 421, 431, 433, 439, 443, 
449, 457, 461, 463, 467, 479, 487, 491, 499,  ...

Statement processed.

0.17 seconds

Show comments

Back to homepage