-- Procedure inserting data without using bind variables.

CREATE OR REPLACE PROCEDURE no_bv_inserts AS
  productid NUMBER(6);
  description VARCHAR2(30);

  start_time PLS_INTEGER :=0;
  stop_time PLS_INTEGER :=0;
  total_time PLS_INTEGER :=0;
BEGIN
  description := 'some description';

  start_time := DBMS_UTILITY.GET_TIME;
  FOR i IN 1 .. 10000
  LOOP
    productid := 300000 + i;
    EXECUTE IMMEDIATE 'INSERT INTO PRODUCT VALUES (' || productid || ', ''' || description || ''')';
  END LOOP;
 
  COMMIT;

  stop_time := DBMS_UTILITY.GET_TIME;
  total_time := (stop_time - start_time) * 10;
  DBMS_OUTPUT.PUT_LINE('Execution time in ms: ' || total_time);
END;
/


-- Procedure inserting data using bind variables

CREATE OR REPLACE PROCEDURE bv_inserts AS
  productid NUMBER(6);
  description VARCHAR2(30);

  start_time PLS_INTEGER :=0;
  stop_time PLS_INTEGER :=0;
  total_time PLS_INTEGER :=0;
BEGIN
  description := 'some description';

  start_time := DBMS_UTILITY.GET_TIME;
  FOR i IN 1 .. 10000
  LOOP
    productid := 300000 + i;
    EXECUTE IMMEDIATE 'INSERT INTO PRODUCT VALUES (:prodid, :describ)' USING productid, description;
  END LOOP;

  COMMIT;
 
  stop_time := DBMS_UTILITY.GET_TIME;
  total_time := (stop_time - start_time) * 10;
  DBMS_OUTPUT.PUT_LINE('Execution time in ms: ' || total_time);
END;
/


-- Procedure selecting data without using bind variables

CREATE OR REPLACE PROCEDURE NO_BV_SELECTS AS
  TYPE rc IS REF CURSOR;
  cur rc;
  
  start_time PLS_INTEGER :=0;
  stop_time PLS_INTEGER :=0;
  total_time PLS_INTEGER :=0;
BEGIN
  start_time := DBMS_UTILITY.GET_TIME;
  FOR i IN 1 .. 10000
  LOOP
    OPEN cur FOR
      'SELECT ename FROM emp WHERE empno = ' || to_char(i);
    CLOSE cur;
  END LOOP;
  
  stop_time := DBMS_UTILITY.GET_TIME;
  total_time := (stop_time - start_time) * 10;
  DBMS_OUTPUT.PUT_LINE('Execution time in ms: ' || total_time);
END;
/


-- Procedure selecting data using bind variables

CREATE OR REPLACE PROCEDURE "BV_SELECTS" AS
  TYPE rc IS REF CURSOR;
  cur rc;
  
  start_time PLS_INTEGER :=0;
  stop_time PLS_INTEGER :=0;
  total_time PLS_INTEGER :=0;
BEGIN
  start_time := DBMS_UTILITY.GET_TIME;
  FOR i IN 1 .. 10000
  LOOP
    OPEN cur FOR
      'SELECT ename FROM emp WHERE empno = :x' using i;
    CLOSE cur;
  END LOOP;
  
  stop_time := DBMS_UTILITY.GET_TIME;
  total_time := (stop_time - start_time) * 10;
  DBMS_OUTPUT.PUT_LINE('Execution time in ms: ' || total_time);
END;
/



-- Tables needed for concurrency test

DROP TABLE job_parameters;
CREATE TABLE job_parameters 
(
  jobid NUMBER PRIMARY KEY,
  iterations NUMBER,
  table_index NUMBER
);


DROP TABLE test_results;
CREATE TABLE test_results
(
  jobid NUMBER,
  job_no NUMBER,
  iterations NUMBER,
  bind_variables CHAR(3),
  execution_time NUMBER
);


-- Procedures needed for concurrency test

CREATE OR REPLACE PROCEDURE NO_BV_CONCURRENCY(job_id NUMBER, jobs_no NUMBER) AS
  jp_rec job_parameters%ROWTYPE;
  
  start_time PLS_INTEGER :=0;
  stop_time PLS_INTEGER :=0;
  total_time PLS_INTEGER :=0;
BEGIN
  SELECT * INTO jp_rec FROM job_parameters WHERE jobid = job_id;
  
  start_time := DBMS_UTILITY.GET_TIME;
  FOR i IN 1 .. jp_rec.iterations
  LOOP
    EXECUTE IMMEDIATE 'INSERT INTO t' || jp_rec.table_index || ' VALUES (' || i || ')';
    COMMIT;
  END LOOP;
  stop_time := DBMS_UTILITY.GET_TIME;
  
  total_time := (stop_time - start_time) * 10;
  INSERT INTO test_results VALUES (job_id, jobs_no, jp_rec.iterations, 'NO', total_time);
  DELETE FROM job_parameters WHERE jobid = job_id;
  COMMIT;
END;
/


CREATE OR REPLACE PROCEDURE BV_CONCURRENCY(job_id NUMBER, jobs_no NUMBER) AS
  jp_rec job_parameters%ROWTYPE;
  
  start_time PLS_INTEGER :=0;
  stop_time PLS_INTEGER :=0;
  total_time PLS_INTEGER :=0;
BEGIN
  SELECT * INTO jp_rec FROM job_parameters WHERE jobid = job_id;
  
  start_time := DBMS_UTILITY.GET_TIME;
  FOR i IN 1 .. jp_rec.iterations
  LOOP
    EXECUTE IMMEDIATE 'INSERT INTO t' || jp_rec.table_index || ' VALUES (:x)' USING i;
    COMMIT;
  END LOOP;
  stop_time := DBMS_UTILITY.GET_TIME;
  
  total_time := (stop_time - start_time) * 10;
  INSERT INTO test_results VALUES (job_id, jobs_no, jp_rec.iterations, 'YES', total_time);
  DELETE FROM job_parameters WHERE jobid = job_id;
  COMMIT;
END;
/


CREATE OR REPLACE PROCEDURE CONCURRENCY_SIMULATION 
  (procedure_name VARCHAR2, jobs NUMBER, iterations NUMBER) 
  AUTHID current_user
AS
  job_id NUMBER;
  counter NUMBER;
BEGIN
  FOR i IN 1 .. jobs
  LOOP
    BEGIN
      EXECUTE IMMEDIATE 'DROP TABLE T' || i;
    EXCEPTION
      WHEN OTHERS THEN NULL;
    END;
    EXECUTE IMMEDIATE 'CREATE TABLE T' || i || '(X NUMBER)';
  END LOOP;
  
  FOR i IN 1 .. jobs
  LOOP
    DBMS_JOB.SUBMIT(job_id, procedure_name || '(JOB, ' || jobs || ');');
    INSERT INTO job_parameters VALUES (job_id, iterations, i);
  END LOOP;
  
  COMMIT;
  
  LOOP
    DBMS_LOCK.SLEEP(5);
    SELECT COUNT(*) INTO counter FROM job_parameters;
    EXIT WHEN (counter = 0);
  END LOOP;

  FOR i IN 1 .. jobs
  LOOP
    BEGIN
      EXECUTE IMMEDIATE 'DROP TABLE T' || i;
    EXCEPTION
      WHEN OTHERS THEN NULL;
    END;
  END LOOP;
END;
/


