Differences

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

--- grandnational [2021/04/25 09:33] – 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 ====
@@ Line 8: / 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 81: / Line 93: @@
 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,
+	       select row_number() over (order by dbms_random.value ASC) row_num,
-		   HorseNo, HorseName from sweepstake where playerid is null),
+	              HorseNo, HorseName from sweepstake where playerid is null),
-							   (
+	      (select id PlayerID, name PlayerName from participants)
-			   select id PlayerID, name PlayerName from participants)
+	     where row_num = PlayerID;
-		  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;
+  EXECUTE IMMEDIATE 'Update sweepstake set PlayerID = :pid where HorseNo = :horseno'
+                     using a.PlayerID, a.HorseNo;
 END LOOP;
 END;
@@ Line 123: / Line 135: @@
 </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>