DB Wiki

User Tools

Site Tools

Trace:
grandnational

Differences

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

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
grandnational [2021/04/25 09:36] – z0hpvkgrandnational [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 ==== ==== Create tables and insert data ====
Line 8: Line 20:
 Create table participants (ID number(2) generated by default on null as identity,  Create table participants (ID number(2) generated by default on null as identity, 
                            Name varchar2(20), primary key(ID),                             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 ('Ian');
 insert into participants (name) values ('Pat'); insert into participants (name) values ('Pat');
Line 88: Line 100:
 FOR a IN C1 FOR a IN C1
 LOOP 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 LOOP;
 END; END;
Line 122: Line 135:
 </code> </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>
grandnational.1619343393.txt.gz · Last modified: 2025/03/08 22:23 (external edit)