-- CS3540 PL/SQL Script #1

DROP TABLE TESTTABLE;

CREATE TABLE TESTTABLE ( PKey NUMBER(6),
			 FirstName VARCHAR2(20),
			 LastName VARCHAR2(20),
			 Birthdate DATE,
			 GPA NUMBER(5,3));

DROP SEQUENCE testpkey_seq;			 

CREATE SEQUENCE testpkey_seq START WITH 1 INCREMENT BY 1 cache 20;
	
set serveroutput on
		 
CREATE OR REPLACE PROCEDURE myDataFiller IS
	myFirstName VARCHAR2(20);
	myLastName VARCHAR2(20);
	myBirthdate DATE;
	myGPA NUMBER(5,3);
	myPkey NUMBER(6);	
	TIME1 VARCHAR2(50);
BEGIN
	myFirstName := 'John';
	myLastName := 'Locke';
	myBirthdate := '01-JAN-80';
	myGPA := '3.5';
	

	select 'Begin: ' || to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') 
		Into TIME1
		from dual;
	dbms_output.put_line(TIME1);

	LOOP
	   SELECT testpkey_seq.nextval into myPkey FROM Dual;
	   INSERT INTO TESTTABLE (PKey, FirstName, LastName, Birthdate, GPA) 
			         VALUES (myPkey, myFirstName, myLastName, 
				  myBirthdate, myGPA);
	   EXIT WHEN myPkey > 100000;
	END LOOP;

	select 'End: ' || to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') 
		Into TIME1
		from dual;
	dbms_output.put_line(TIME1);
END;	
/
Execute myDataFiller;