DB Wiki

CREATE USER GRANDNATIONAL IDENTIFIED BY horses;
GRANT CREATE SESSION TO GRANDNATIONAL;
GRANT CREATE TABLE TO GRANDNATIONAL;
GRANT CREATE PROCEDURE TO GRANDNATIONAL;
GRANT CREATE VIEW TO GRANDNATIONAL;
GRANT CREATE SEQUENCE TO GRANDNATIONAL;
GRANT UNLIMITED TABLESPACE TO GRANDNATIONAL;

-- Lists the entrants in the sweepstake
DROP TABLE participants;
CREATE TABLE participants (ID NUMBER(2) generated BY DEFAULT ON NULL AS IDENTITY, 
                           Name varchar2(20), PRIMARY KEY(ID), 
		           CONSTRAINT unique_name UNIQUE (name));
INSERT INTO participants (name) VALUES ('Ian');
INSERT INTO participants (name) VALUES ('Pat');
INSERT INTO participants (name) VALUES ('Graham');
INSERT INTO participants (name) VALUES ('Tracy');
INSERT INTO participants (name) VALUES ('Michaela');
INSERT INTO participants (name) VALUES ('Glenn');
 
-- List of horses entered into the Grand National
DROP TABLE horses;
CREATE TABLE horses (HorseNo NUMBER(2), 
                     Name varchar2(40), PRIMARY KEY(HorseNo));
INSERT INTO horses VALUES (1,'Bristol De Mai');
INSERT INTO horses VALUES (2,'Chris Dream');
INSERT INTO horses VALUES (3,'Yala Enki');
INSERT INTO horses VALUES (4,'Ballyoptic');
INSERT INTO horses VALUES (5,'Definitly Red');
INSERT INTO horses VALUES (6,'Lake View Lad');
INSERT INTO horses VALUES (7,'Burrows Saint');
INSERT INTO horses VALUES (8,'Magic Of Light');
INSERT INTO horses VALUES (9,'Acapella Bourgeois');
INSERT INTO horses VALUES (10,'Talkischeap');
INSERT INTO horses VALUES (11,'Tout Est Permis');
INSERT INTO horses VALUES (12,'Anibale Fly');
INSERT INTO horses VALUES (13,'Mister Malarky');
INSERT INTO horses VALUES (14,'Kimberlite Candy');
INSERT INTO horses VALUES (15,'Any Second Now');
INSERT INTO horses VALUES (16,'Balko Des Flos');
INSERT INTO horses VALUES (17,'Alpha Des Obeaux');
INSERT INTO horses VALUES (18,'OK Corral');
INSERT INTO horses VALUES (19,'Takingrisks');
INSERT INTO horses VALUES (20,'Shattered Love');
INSERT INTO horses VALUES (21,'Jett');
INSERT INTO horses VALUES (22,'Lord Du Mesnil');
INSERT INTO horses VALUES (23,'Potters Corner');
INSERT INTO horses VALUES (24,'Class Conti');
INSERT INTO horses VALUES (25,'Milan Native');
INSERT INTO horses VALUES (26,'Discorama');
INSERT INTO horses VALUES (27,'Vieux Lion Rouge');
INSERT INTO horses VALUES (28,'Cloth Cap');
INSERT INTO horses VALUES (29,'Caberet Queen');
INSERT INTO horses VALUES (30,'Minellacelebration');
INSERT INTO horses VALUES (31,'Canelo');
INSERT INTO horses VALUES (32,'The Long Mile');
INSERT INTO horses VALUES (33,'Give Me A Copper');
INSERT INTO horses VALUES (34,'Farclas');
INSERT INTO horses VALUES (35,'Minella Times');
INSERT INTO horses VALUES (36,'Sub Lieutenant');
INSERT INTO horses VALUES (37,'Hogans Height');
INSERT INTO horses VALUES (38,'Double Shuffle');
INSERT INTO horses VALUES (39,'Ami Desbois');
INSERT INTO horses VALUES (40,'Blaklion');
 
-- Lists the entrants and the horses that they got
DROP TABLE sweepstake;
CREATE TABLE sweepstake (PlayerID NUMBER(2), 
                         HorseNo NUMBER(2), 
			 HorseName varchar2(40), PRIMARY KEY (HorseNo));

-- Initialise the SWEEPSTAKE table
CREATE OR REPLACE PROCEDURE init AS
BEGIN
  EXECUTE IMMEDIATE 'delete from sweepstake';
  EXECUTE IMMEDIATE 'insert into sweepstake (HorseNo, HorseName) (select horseno, name from horses)';
END;
/
 
-- Performs one round of the draw.
CREATE OR REPLACE PROCEDURE OneCycle AS
CURSOR C1 IS SELECT PlayerID, PlayerName, horseno, HorseName FROM (
	       SELECT ROW_NUMBER() over (ORDER BY DBMS_RANDOM.VALUE ASC) row_num,
	              HorseNo, HorseName FROM sweepstake WHERE playerid IS NULL),
	      (SELECT id PlayerID, name PlayerName FROM participants)
	     WHERE row_num = PlayerID;
BEGIN
FOR a IN C1
LOOP
  EXECUTE IMMEDIATE 'Update sweepstake set PlayerID = :pid where HorseNo = :horseno' 
                     using a.PlayerID, a.HorseNo;
END LOOP;
END;
/
 
-- Generate the sweepstake data
CREATE OR REPLACE PROCEDURE GenSweepstake AS
CURSOR C1 IS SELECT name, horseno, horsename
             FROM participants, sweepstake
             WHERE id = playerid ORDER BY horseno;
NoParticipants NUMBER(2);
NoHorses       NUMBER(3);
NoCycles       NUMBER(2);
BEGIN
SELECT COUNT(*) INTO NoParticipants FROM participants;
SELECT COUNT(*) INTO NoHorses FROM horses;
NoCycles := CEIL(NoHorses/NoParticipants);
  init;
FOR a IN 1..NoCycles
LOOP
  OneCycle;
END LOOP;
 
DBMS_OUTPUT.put_line(' ');
DBMS_OUTPUT.put_line('PLAYER    NUMBER  HORSE NAME');
DBMS_OUTPUT.put_line('------    ------  ------------------');
FOR b IN C1
LOOP
   DBMS_OUTPUT.put_line(RPAD(b.name, 10) || RPAD(b.horseno, 8) || RPAD(b.horsename, 30));
END LOOP;
END;
/

CREATE OR REPLACE VIEW v_sweepstake AS
       SELECT name, horseno, horsename
       FROM participants, sweepstake
       WHERE id = playerid 
       ORDER BY horseno;
 
CREATE OR REPLACE VIEW v_check AS
       SELECT name, COUNT(name) No_Horses FROM v_sweepstake
       GROUP BY name
       ORDER BY 2 DESC;

SET serveroutput ON
BEGIN
  GenSweepstake();
END;
/