CREATE TABLE ALL_POSSIBLE_FIXTURES AS  
WITH X(TEAM) AS ( 
SELECT 'AAA' FROM DUAL UNION ALL 
SELECT 'BBB' FROM DUAL UNION ALL 
SELECT 'CCC' FROM DUAL UNION ALL 
SELECT 'DDD' FROM DUAL UNION ALL 
SELECT 'EEE' FROM DUAL UNION ALL 
SELECT 'FFF' FROM DUAL UNION ALL 
SELECT 'GGG' FROM DUAL UNION ALL 
SELECT 'HHH' FROM DUAL UNION ALL 
SELECT 'III' FROM DUAL UNION ALL 
SELECT 'JJJ' FROM DUAL UNION ALL 
SELECT 'KKK' FROM DUAL UNION ALL 
SELECT 'LLL' FROM DUAL UNION ALL 
SELECT 'MMM' FROM DUAL UNION ALL 
SELECT 'NNN' FROM DUAL UNION ALL 
SELECT 'OOO' FROM DUAL UNION ALL 
SELECT 'PPP' FROM DUAL UNION ALL 
SELECT 'QQQ' FROM DUAL UNION ALL 
SELECT 'RRR' FROM DUAL 
)  
SELECT  
  X1.TEAM AS HOME_TEAM, 
  X2.TEAM AS AWAY_TEAM  
FROM X X1  
CROSS JOIN X X2  
WHERE X1.TEAM <> X2.TEAM 

CREATE TABLE FIXTURE  
(	"HOME_TEAM" VARCHAR2(2000 BYTE),  
	"AWAY_TEAM" VARCHAR2(2000 BYTE),  
	"ROUND" NUMBER 
)

DECLARE   
  v_cnt NUMBER;   
   
PROCEDURE insertGames IS   
   
BEGIN   
  dbms_output.enable();   
  FOR ROUND_NO IN 1 .. 17 LOOP   
    FOR MATCH_ID IN 1 .. 9 LOOP   
      INSERT INTO FIXTURE(HOME_TEAM, AWAY_TEAM, ROUND)   
   
      SELECT HOME_TEAM, AWAY_TEAM, ROUND_NO AS ROUND FROM (   
        SELECT HOME_TEAM, AWAY_TEAM, DBMS_RANDOM.VALUE AS RND FROM ALL_POSSIBLE_FIXTURES   
        WHERE   
          -- this match cannot be in this round   
          (HOME_TEAM||AWAY_TEAM NOT IN (SELECT HOME_TEAM||AWAY_TEAM FROM FIXTURE WHERE ROUND = ROUND_NO)) AND   
          (AWAY_TEAM||HOME_TEAM NOT IN (SELECT AWAY_TEAM||HOME_TEAM FROM FIXTURE WHERE ROUND = ROUND_NO)) AND   
          -- the teams cannot already be playing each other in this configuration   
          (HOME_TEAM||AWAY_TEAM NOT IN (SELECT HOME_TEAM||AWAY_TEAM FROM FIXTURE)) AND   
          (AWAY_TEAM||HOME_TEAM NOT IN (SELECT AWAY_TEAM||HOME_TEAM FROM FIXTURE)) AND   
          -- the teams cannot have had two home games in the last two rounds (***** NB. This doesnt appear to be working *****)   
          (HOME_TEAM NOT IN (SELECT HOME_TEAM FROM FIXTURE WHERE ROUND > ROUND_NO-2 GROUP BY HOME_TEAM HAVING COUNT(*) > 1)) AND   
          (AWAY_TEAM NOT IN (SELECT AWAY_TEAM FROM FIXTURE WHERE ROUND > ROUND_NO-2 GROUP BY AWAY_TEAM HAVING COUNT(*) > 1)) AND   
          -- these teams cannot be scheduled to play more than 11 games home or away   
          (HOME_TEAM NOT IN (SELECT HOME_TEAM FROM FIXTURE GROUP BY HOME_TEAM HAVING COUNT(*) > 9)) AND   
          (AWAY_TEAM NOT IN (SELECT AWAY_TEAM FROM FIXTURE GROUP BY AWAY_TEAM HAVING COUNT(*) > 9)) AND   
          -- these teams cannot already be in this round, either home or away   
          (HOME_TEAM NOT IN (SELECT HOME_TEAM FROM FIXTURE WHERE ROUND = ROUND_NO)) AND   
          (AWAY_TEAM NOT IN (SELECT AWAY_TEAM FROM FIXTURE WHERE ROUND = ROUND_NO)) AND   
          (HOME_TEAM NOT IN (SELECT AWAY_TEAM FROM FIXTURE WHERE ROUND = ROUND_NO)) AND   
          (AWAY_TEAM NOT IN (SELECT HOME_TEAM FROM FIXTURE WHERE ROUND = ROUND_NO))   
          ORDER BY 3   
      ) WHERE ROWNUM = 1;   
      COMMIT;   
    END LOOP;   
  END LOOP;   
END;   
   
BEGIN   
  SELECT COUNT(*) INTO v_cnt FROM FIXTURE;   
  -- If we have reached the end and there is not 17 * 9 games (153), then dump it and start again.   
  WHILE v_CNT < 153 LOOP   
    SELECT COUNT(*) INTO v_cnt FROM FIXTURE;   
    DBMS_OUTPUT.PUT_LINE(v_CNT);   
    IF v_CNT <> 153 THEN   
      EXECUTE IMMEDIATE 'TRUNCATE TABLE FIXTURE';   
      insertGames;   
    END IF;   
  END LOOP;   
END; 

SELECT * FROM FIXTURE WHERE HOME_TEAM = 'AAA' 
UNION ALL 
SELECT * FROM FIXTURE WHERE AWAY_TEAM = 'AAA' 
ORDER BY 3