#!/usr/bin/perl

use strict;
use warnings;

use Devel::DProf;

use Benchmark;
use Getopt::Long;
use Pod::Usage;
use DBI;
use DBD::Oracle qw(:ora_types);

my %foo = (
  lnum1 => 123456789,
  lnum2 => 123456789,
  lnum3 => 123456789,
  lnum4 => 123456789,
);

my %sths = ();
my $dbh = undef;

##################################################################
# Insert foo using stored procedure (PL/SQL)
sub insert_foo_ora_sp {
##################################################################
  my $fooref = shift;

  # ------ prepare function call unless in cache
  if (!defined($sths{insert_foo_ora_sp})) {
    my $prepstr =
      q{BEGIN :retval := pkg_foo.func_insert_foo}.
      q{(:lnum1,:lnum2,:lnum3,:lnum4); END;};
    $sths{insert_foo_ora_sp} = $dbh->prepare ($prepstr);
  }
  if (!defined($sths{insert_foo_ora_sp})) {
    die(q{sth_prepare failed!});
  }

  my $retval = undef;
  $sths{insert_foo_ora_sp}->bind_param_inout(":retval", \$retval, 32);
  $sths{insert_foo_ora_sp}->bind_param(q{:} . $_, $fooref->{$_}) for (keys %{$fooref});

  # ------ execute
  my $rv = $sths{insert_foo_ora_sp}->execute();
  if ($rv != 1) {
    die(qq{sth_execute returned '$rv'});
  }

  return $retval;
}

##################################################################
# Insert foo using sql (using bound sql)
sub insert_foo_ora_sql {
##################################################################
  my $fooref = shift;

  if (!defined($sths{get_next_foo_id_sql})) {
    my $prepstr = qq{SELECT FOO_ID_SEQ.NEXTVAL FROM DUAL};
    $sths{get_next_foo_id_sql} = $dbh->prepare($prepstr);
  }
  if (!defined($sths{get_next_foo_id_sql})) {
    die(q{sth_prepare failed!});
  }

  my $rv = $sths{get_next_foo_id_sql}->execute();
  die(q{sth_execute failed:} . $!) unless $rv;

  my @arr = $sths{get_next_foo_id_sql}->fetchrow_array();


  push @arr, $foo{lnum1}, $foo{lnum2}, $foo{lnum3}, $foo{lnum4};

  if (!$sths{insert_foo_ora_sql}) {
    my $sql = "insert into foo (foo_id,lnum1,lnum2,lnum3,lnum4) values (?,?,?,?,?)";
    $sths{insert_foo_ora_sql} = $dbh->prepare ($sql);
    die(q{sth_prepare failed!}) unless $sths{insert_foo_ora_sql};
  }

  $rv = $sths{insert_foo_ora_sql}->execute(@arr);
  die(q{sth_execute failed:} . $!) unless $rv;

  return $arr[0]; # return foo_id
}

##################################################################
# Get foogroup using PL/SQL stored procedure
sub get_foo_ora_sp {
##################################################################
  my $foo_id = shift;

  if (!defined($sths{get_foo_sql_sp})) {
    my $prepstr = qq{BEGIN pkg_foo.proc_get_foo (:pi_foo_id, :pio_foo ); END;};
    $sths{get_foo_sql_sp} = $dbh->prepare($prepstr);
  }
  if (!defined($sths{get_foo_sql_sp})) {
    die(q{sth_prepare failed!});
  }

  my $retvalsth = undef;
  $sths{get_foo_sql_sp}->bind_param(q{:pi_foo_id}, $foo_id);
  $sths{get_foo_sql_sp}->bind_param_inout(":pio_foo", \$retvalsth, 0, { ora_type => ORA_RSET } );

  my $rv = $sths{get_foo_sql_sp}->execute();
  if (!$rv) {
    die(qq{sth_execute returned '$rv'});
  }

  my $number_of_rows = 0;
  $number_of_rows++ while ( my @row = $retvalsth->fetchrow_array );

  return $number_of_rows;
}


##################################################################
# Get foo using sql
sub get_foo_ora_sql {
##################################################################
  my $foo_id = shift;

  if (!$sths{get_foo_ora_sql_foo}) {
    my $sql = "SELECT foo_id,lnum1,lnum2,lnum3,lnum4 FROM foo WHERE foo_id = ?";
    $sths{get_foo_ora_sql_foo} = $dbh->prepare ($sql);
    die(q{sth_prepare failed!}) unless $sths{get_foo_ora_sql_foo};
  }

  my $rv = $sths{get_foo_ora_sql_foo}->execute($foo_id);
  die(q{sth_execute failed!}) unless $rv;

  my $number_of_rows = 0;
  $number_of_rows++ while (my @row = $sths{get_foo_ora_sql_foo}->fetchrow_array());

  return $number_of_rows;
}

##################################################################
sub update_foo_vars {
##################################################################
  foreach my $key (keys %foo) {
    $foo{$key}++;
  }
}

# ------ main
my $N = 1e2;
my $dbistr  = q{DBI:Oracle:};
my $dbiuser = q{scott};
my $dbipass = q{tiger};

GetOptions("N|n=i" => \$N) or pod2usage(2);

$dbh = DBI->connect($dbistr, $dbiuser, $dbipass);
die(q{connection failed!}) if (!defined($dbh));

timethese($N,
          {
           insert_and_select_foo_ora_sp => sub {
             update_foo_vars();
             my $foo_id = insert_foo_ora_sp(\%foo);
             get_foo_ora_sp($foo_id);
           },
           insert_and_select_foo_ora_sql => sub {
             update_foo_vars();
             my $foo_id = insert_foo_ora_sql(\%foo);
             get_foo_ora_sql($foo_id);
           }
          }
         );

__END__