-- Create buffer pools, table spaces for the table

CREATE BUFFERPOOL DATA IMMEDIATE  SIZE 50 PAGESIZE 16 K
/

CREATE BUFFERPOOL IDX  IMMEDIATE  SIZE 50 PAGESIZE 16 K
/

CREATE TABLESPACE DATA PAGESIZE 16 K BUFFERPOOL DATA                 
/

CREATE TABLESPACE IDX  PAGESIZE 16 K BUFFERPOOL  IDX                 
/


-- Create table  MONITOR_TAB

CREATE TABLE MONITOR_TAB
(
   MON_ID    INTEGER NOT NULL
   GENERATED ALWAYS AS IDENTITY
   (START WITH 100000,
      INCREMENT BY 5
   ),
   SSN            CHAR(11)      NOT NULL,
   FIRST_NAME     VARCHAR(20)   NOT NULL,
   LAST_NAME      VARCHAR(20)   NOT NULL,
   JOB_CODE       CHAR(4)       NOT NULL,
   DEPT           SMALLINT      NOT NULL,
   SALARY         DECIMAL(12,2) NOT NULL,
   DOB            DATE          NOT NULL WITH DEFAULT,
   CONSTRAINT     PK_STRAWMAN   PRIMARY KEY (MON_ID)
) IN DATA INDEX IN IDX 
/

SET SERVEROUTPUT ON
/

-- Notice: This creates a PL/SQL Procedure MONITOR_SP

CREATE OR REPLACE PROCEDURE MONITOR_SP 
(
   v_total OUT INTEGER
)
AS
   v_rows INTEGER  := 0;
BEGIN   
    -- INSERT 25000 rows in the MONITOR_TAB table
    DBMS_OUTPUT.PUT_LINE('Before inserting data in MONITOR_TAB table');
    INSERT INTO MONITOR_TAB (SSN,FIRST_NAME,LAST_NAME,job_code,dept,salary,DOB)
    WITH temp1 (s1,r1,r2,r3,r4) AS
	(VALUES (0
	,RAND(2)
	,RAND()+(RAND()/1E5)
	,RAND()* RAND()
	,RAND()* RAND()* RAND())
	UNION ALL
	SELECT s1 + 1
	,RAND()
	,RAND()+(RAND()/1E5)
	,RAND()* RAND()
	,RAND()* RAND()* RAND()
	FROM temp1
	WHERE s1 < 24999
	)
    SELECT SUBSTR(DIGITS(INT(r2*988+10)),8) ||'-'||
	       SUBSTR(DIGITS(INT(r1*88+10)),9) || '-' ||
	       TRANSLATE(SUBSTR(DIGITS(s1),7),'9873450126','0123456789'),
	       CHR(INT(r1*26+65))|| CHR(INT(r2*26+97))|| CHR(INT(r3*26+97))||CHR(INT(r4*26+97))|| 
               CHR(INT(r3*10+97))|| CHR(INT(r3*11+97)),
	       CHR(INT(r2*26+65))||TRANSLATE(CHAR(INT(r2*1E7)),'aaeeiibmty','0123456789'),       
	       CASE
	         WHEN INT(r4*9) > 7 THEN 'MGR'
	         WHEN INT(r4*9) > 5 THEN 'SUPR'
	         WHEN INT(r4*9) > 3 THEN 'PGMR'
	         WHEN INT(R4*9) > 1 THEN 'SEC'
	         ELSE 'WKR'
	       END,
	       INT(r3*98+1),
	       DECIMAL(r4*99999,7,2),
	       DATE('1930-01-01') + INT(50-(r4*50)) YEARS + INT(r4*11) MONTHS + INT(r4*27) DAYS
   FROM temp1;

   v_rows := SQL%ROWCOUNT;
   v_total := v_rows; 
   DBMS_OUTPUT.PUT_LINE('After inserting data in MONITOR_TAB table # of rows inserted = ' || v_rows);

   UPDATE MONITOR_TAB SET salary = 1.1 * salary where salary < 30000;
   v_rows := SQL%ROWCOUNT;
   v_total := v_total + v_rows;
   DBMS_OUTPUT.PUT_LINE('10% salary increase affected ' || v_rows || ' people');

   UPDATE MONITOR_TAB SET salary = 1.2 * salary where salary between 30001 and 40000;
   v_rows := SQL%ROWCOUNT;
   v_total := v_total + v_rows;
   DBMS_OUTPUT.PUT_LINE('20% salary increase affected ' || v_rows || ' people');

   UPDATE MONITOR_TAB SET salary = 1.3 * salary where salary between 40001 and 50000;	
   v_rows := SQL%ROWCOUNT;
   v_total := v_total + v_rows;
   DBMS_OUTPUT.PUT_LINE('30% salary increase affected ' || v_rows || ' people');

   UPDATE MONITOR_TAB SET salary = 1.5 * salary where salary < 50001;
   v_rows := SQL%ROWCOUNT;
   v_total := v_total + v_rows;
   DBMS_OUTPUT.PUT_LINE('50% salary increase affected ' || v_rows || ' people');

EXCEPTION
   WHEN OTHERS THEN
      -- RAISE_APPLICATION_ERROR(-20000,'Error inserting/updating in table MONITOR_TAB');
     DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
     DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);

END;
/


-- Call the stored procedure MONITOR_SP

CALL MONITOR_SP(?)
/

COMMIT
/