create or replace type integer_return_array is varray(25) of integer
/

create or replace PACKAGE swingbench AS
  function storedprocedure1(min_sleep integer, max_sleep integer) return integer_return_array;
  function storedprocedure2(min_sleep integer, max_sleep integer) return integer_return_array;
  function storedprocedure3(min_sleep integer, max_sleep integer) return integer_return_array;
  function storedprocedure4(min_sleep integer, max_sleep integer) return integer_return_array;
  function storedprocedure5(min_sleep integer, max_sleep integer) return integer_return_array;
  function storedprocedure6(min_sleep integer, max_sleep integer) return integer_return_array;
END;
/

create or replace PACKAGE BODY swingbench AS
  SELECT_STATEMENTS   integer := 1;
  INSERT_STATEMENTS   integer := 2;
  UPDATE_STATEMENTS   integer := 3;
  DELETE_STATEMENTS   integer := 4;
  COMMIT_STATEMENTS   integer := 5;
  ROLLBACK_STATEMENTS integer := 6;
  dml_array integer_return_array := integer_return_array();
  function from_mills_to_tens(value integer) return float is
    real_value float := 0;
    begin
      real_value := value/1000;
      return real_value;
      exception
        when zero_divide then
          real_value := 0;
          return real_value;
  end from_mills_to_tens;
  procedure sleep(min_sleep integer, max_sleep integer) is
    begin
      if (((max_sleep - min_sleep) > 0) AND (min_sleep < max_sleep)) then
        dbms_lock.sleep(dbms_random.value(from_mills_to_tens(max_sleep), from_mills_to_tens(min_sleep)));
     end if;
  end sleep;
  procedure init_dml_array is
    begin
      dml_array := integer_return_array();
      for i in 1..7 loop
        dml_array.extend;
        dml_array(i) := 0;
      end loop;
  end init_dml_array;
  procedure increment_selects(num_selects integer) is
    begin
      dml_array(SELECT_STATEMENTS) := dml_array(SELECT_STATEMENTS) + num_selects;
  end increment_selects;
  procedure increment_inserts(num_inserts integer) is
    begin
      dml_array(INSERT_STATEMENTS) := dml_array(INSERT_STATEMENTS) + num_inserts;
  end increment_inserts;
  procedure increment_updates(num_updates integer) is
    begin
      dml_array(UPDATE_STATEMENTS) := dml_array(UPDATE_STATEMENTS) + num_updates;
  end increment_updates;
  procedure increment_deletes(num_deletes integer) is
    begin
      dml_array(DELETE_STATEMENTS) := dml_array(DELETE_STATEMENTS) + num_deletes;
  end increment_deletes;
  procedure increment_commits(num_commits integer) is
    begin
      dml_array(COMMIT_STATEMENTS) := dml_array(COMMIT_STATEMENTS) + num_commits;
  end increment_commits;
  procedure increment_rollbacks(num_rollbacks integer) is
    begin
      dml_array(ROLLBACK_STATEMENTS) := dml_array(ROLLBACK_STATEMENTS) + num_rollbacks;
  end increment_rollbacks;
  function storedprocedure1(min_sleep integer, max_sleep integer) return integer_return_array is
  total_login integer:=0;
   begin
      init_dml_array();
      select count(1) into total_login from a;
     increment_selects(1);
      return dml_array;
      sleep(min_sleep, max_sleep);
  end storedprocedure1;
  function storedprocedure2(min_sleep integer, max_sleep integer) return integer_return_array is
    begin
      init_dml_array();
      execute immediate 'insert into a values(1);';
      increment_inserts(1);
      return dml_array;
      sleep(min_sleep, max_sleep);
  end storedprocedure2;
  function storedprocedure3(min_sleep integer, max_sleep integer) return integer_return_array is
    begin
      init_dml_array();
      execute immediate 'update a set a=2;';
      increment_updates(1);
      return dml_array;
      sleep(min_sleep, max_sleep);
  end storedprocedure3;
  function storedprocedure4(min_sleep integer, max_sleep integer) return integer_return_array is
    begin
      init_dml_array();

      execute immediate 'delete from a;';
      increment_deletes(1);
      return dml_array;
      sleep(min_sleep, max_sleep);
  end storedprocedure4;
  function storedprocedure5(min_sleep integer, max_sleep integer) return integer_return_array is
    begin
      init_dml_array();
      sleep(min_sleep, max_sleep);
      return dml_array;
  end storedprocedure5;
  function storedprocedure6(min_sleep integer, max_sleep integer) return integer_return_array is
    begin
      init_dml_array();
      sleep(min_sleep, max_sleep);
      return dml_array;
  end storedprocedure6;
END;
/