Differences

This shows you the differences between two versions of the page.

--- grandnational [2021/04/25 09:27] – created z0hpvk
+++ grandnational [2025/03/08 22:24] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
-<code>
+===== Grand National Sweepstake (Oracle) =====
+==== Create User ====
+<code sql>
+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;
+</code>
+==== Create tables and insert data ====
+<code sql>
 -- 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));
+		           constraint unique_name unique (name));
 insert into participants (name) values ('Ian');
 insert into participants (name) values ('Pat');
@@ Line 11: / Line 27: @@
 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));
+</code>
+==== Create procedures ====
+<code plsql>
+-- 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;
+/
+</code>
+==== Create Views ====
+<code sql>
+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;
+</code>
+==== Generate Sweepstake ====
+<code plsql>
+set serveroutput on
+BEGIN
+  GenSweepstake();
+END;
+/
 </code>