Differences

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

--- grandnational [2021/04/25 09:30] – z0hpvk
+++ grandnational [2025/03/08 22:24] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
-===== Grand National Sweepstake =====
+===== 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>
@@ Line 6: / Line 20: @@
 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 13: / Line 27: @@
 insert into participants (name) values ('Michaela');
 insert into participants (name) values ('Glenn');
-</code>
-<code sql>
 -- List of horses entered into the Grand National
 Drop table horses;
@@ Line 60: / Line 72: @@
 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>