--  Mega_rdb_c.sqlmod
--
--  SQL Module for use with the Mega Zillionare example application.
--
----------------------
--  Header info
----------------------
MODULE MEGA_RDB_C
LANGUAGE C
PARAMETER COLONS
----------------------
--  Declare curors and aliases here
----------------------

DECLARE     ALIAS FILENAME MEGA_DB_RDB

DECLARE MOST_DRAW_CURSOR READ ONLY CURSOR FOR
    SELECT ELM_NO, HIT_COUNT, SINCE_LAST, PCT_HITS, AVE_BTWN
    FROM DRAW_STATS
    ORDER BY HIT_COUNT DESC
    LIMIT TO 10 ROWS


DECLARE MOST_MEGA_CURSOR READ ONLY CURSOR FOR
    SELECT ELM_NO, HIT_COUNT, SINCE_LAST, PCT_HITS, AVE_BTWN
    FROM MEGA_STATS
    ORDER BY HIT_COUNT DESC
    LIMIT TO 10 ROWS

DECLARE ALL_DRAW_ROWS_CURSOR READ ONLY CURSOR FOR
    SELECT DRAW_DT, NO_1, NO_2, NO_3, NO_4, NO_5, MEGA_NO
    FROM DRAWING_DATA
    ORDER BY DRAW_DT ASC

DECLARE DUE_DRAW_CURSOR READ ONLY CURSOR FOR
    SELECT ELM_NO, HIT_COUNT, SINCE_LAST, PCT_HITS, AVE_BTWN
    FROM DRAW_STATS
    WHERE SINCE_LAST > AVE_BTWN
    ORDER BY SINCE_LAST DESC


DECLARE DUE_MEGA_CURSOR READ ONLY CURSOR FOR
    SELECT ELM_NO, HIT_COUNT, SINCE_LAST, PCT_HITS, AVE_BTWN
    FROM MEGA_STATS
    WHERE SINCE_LAST > AVE_BTWN
    ORDER BY SINCE_LAST DESC
    
----------------------
--  Procedures
----------------------
PROCEDURE COMMIT_MEGA
    SQLCODE;

    COMMIT;

PROCEDURE ROLLBACK_MEGA
    SQLCODE;

    ROLLBACK;

--
--  Procedures to delete all rows from tables
--        
PROCEDURE DELETE_ALL_DRAW_RECS
    SQLCODE;

    DELETE FROM DRAWING_DATA;


PROCEDURE DELETE_ALL_MEGA_STATS
    SQLCODE;

    DELETE FROM MEGA_STATS;


PROCEDURE DELETE_ALL_DRAW_STATS
    SQLCODE;

    DELETE FROM DRAW_STATS;

--
--  Procedures to open cursors
--
PROCEDURE OPEN_MOST_DRAW
    SQLCODE;

    OPEN MOST_DRAW_CURSOR;

PROCEDURE OPEN_MOST_MEGA
    SQLCODE;

    OPEN MOST_MEGA_CURSOR;

PROCEDURE OPEN_ALL_DRAW
    SQLCODE;

    OPEN ALL_DRAW_ROWS_CURSOR;

PROCEDURE OPEN_DUE_DRAW
    SQLCODE;

    OPEN DUE_DRAW_CURSOR;

PROCEDURE OPEN_DUE_MEGA
    SQLCODE;

    OPEN DUE_MEGA_CURSOR;
    
--
--  Procedures to fetch rows from cursor
--
PROCEDURE FETCH_MOST_DRAW
    SQLCODE
    :P_ELM_NO       TINYINT
    :P_HIT_COUNT    INTEGER
    :P_SINCE_LAST   INTEGER
    :P_PCT_HITS     DOUBLE PRECISION
    :P_AVE_BTWN     DOUBLE PRECISION;

    FETCH MOST_DRAW_CURSOR INTO :P_ELM_NO, :P_HIT_COUNT, :P_SINCE_LAST,
    :P_PCT_HITS, :P_AVE_BTWN;

PROCEDURE FETCH_MOST_MEGA
    SQLCODE
    :P_ELM_NO       TINYINT
    :P_HIT_COUNT    INTEGER
    :P_SINCE_LAST   INTEGER
    :P_PCT_HITS     DOUBLE PRECISION
    :P_AVE_BTWN     DOUBLE PRECISION;

    FETCH MOST_MEGA_CURSOR INTO :P_ELM_NO, :P_HIT_COUNT, :P_SINCE_LAST,
    :P_PCT_HITS, :P_AVE_BTWN;


PROCEDURE FETCH_ALL_DRAW
    SQLCODE
    :P_DRAW_DT      DATE VMS
    :P_NO_1         TINYINT
    :P_NO_2         TINYINT
    :P_NO_3         TINYINT
    :P_NO_4         TINYINT
    :P_NO_5         TINYINT
    :P_MEGA_NO      TINYINT;

    FETCH ALL_DRAW_ROWS_CURSOR INTO :P_DRAW_DT, :P_NO_1, :P_NO_2, :P_NO_3,
    :P_NO_4, :P_NO_5, :P_MEGA_NO;


PROCEDURE FETCH_DUE_DRAW
    SQLCODE
    :P_ELM_NO       TINYINT
    :P_HIT_COUNT    INTEGER
    :P_SINCE_LAST   INTEGER
    :P_PCT_HITS     DOUBLE PRECISION
    :P_AVE_BTWN     DOUBLE PRECISION;

    FETCH DUE_DRAW_CURSOR INTO :P_ELM_NO, :P_HIT_COUNT, :P_SINCE_LAST,
    :P_PCT_HITS, :P_AVE_BTWN;


PROCEDURE FETCH_DUE_MEGA
    SQLCODE
    :P_ELM_NO       TINYINT
    :P_HIT_COUNT    INTEGER
    :P_SINCE_LAST   INTEGER
    :P_PCT_HITS     DOUBLE PRECISION
    :P_AVE_BTWN     DOUBLE PRECISION;

    FETCH DUE_MEGA_CURSOR INTO :P_ELM_NO, :P_HIT_COUNT, :P_SINCE_LAST,
    :P_PCT_HITS, :P_AVE_BTWN;

--
--  Counting procedures
--
PROCEDURE COUNT_DRAW
    SQLCODE
    :P_COUNT    INTEGER;

    SELECT COUNT(*) INTO :P_COUNT FROM DRAWING_DATA;

--
--  Procedures to close cursors
--
PROCEDURE CLOSE_MOST_DRAW
    SQLCODE;

    CLOSE MOST_DRAW_CURSOR;

PROCEDURE CLOSE_MOST_MEGA
    SQLCODE;

    CLOSE MOST_MEGA_CURSOR;

PROCEDURE CLOSE_ALL_DRAW
    SQLCODE;

    CLOSE ALL_DRAW_ROWS_CURSOR;

PROCEDURE CLOSE_DUE_DRAW
    SQLCODE;

    CLOSE DUE_DRAW_CURSOR;

PROCEDURE CLOSE_DUE_MEGA
    SQLCODE;

    CLOSE DUE_MEGA_CURSOR;
--
--  Single row insert, find, and delete procedures
--
PROCEDURE INSERT_DRAW_REC
    SQLCODE
    :P_DRAW_DT      DATE VMS
    :P_NO_1         TINYINT
    :P_NO_2         TINYINT
    :P_NO_3         TINYINT
    :P_NO_4         TINYINT
    :P_NO_5         TINYINT
    :P_MEGA_NO      TINYINT;

    INSERT INTO DRAWING_DATA( DRAW_DT, NO_1, NO_2, NO_3, NO_4, NO_5, MEGA_NO)
    VALUES (:P_DRAW_DT, :P_NO_1, :P_NO_2, :P_NO_3, :P_NO_4, :P_NO_5,
    :P_MEGA_NO);

PROCEDURE INSERT_MEGA_STATS
    SQLCODE
    :P_ELM_NO       TINYINT
    :P_HIT_COUNT    INTEGER
    :P_LAST_DRAW_NO INTEGER
    :P_SINCE_LAST   INTEGER
    :P_CURR_SEQ     INTEGER
    :P_LONGEST_SEQ  INTEGER
    :P_PCT_HITS     DOUBLE PRECISION
    :P_MAX_BTWN     INTEGER
    :P_AVE_BTWN     DOUBLE PRECISION;

    INSERT INTO MEGA_STATS( ELM_NO, HIT_COUNT, LAST_DRAW_NO, SINCE_LAST,
    CURR_SEQ, LONGEST_SEQ, PCT_HITS, MAX_BTWN, AVE_BTWN)
    VALUES (:P_ELM_NO, :P_HIT_COUNT, :P_LAST_DRAW_NO, :P_SINCE_LAST,
    :P_CURR_SEQ, :P_LONGEST_SEQ, :P_PCT_HITS, :P_MAX_BTWN, :P_AVE_BTWN);


PROCEDURE INSERT_DRAW_STATS
    SQLCODE
    :P_ELM_NO       TINYINT
    :P_HIT_COUNT    INTEGER
    :P_LAST_DRAW_NO INTEGER
    :P_SINCE_LAST   INTEGER
    :P_CURR_SEQ     INTEGER
    :P_LONGEST_SEQ  INTEGER
    :P_PCT_HITS     DOUBLE PRECISION
    :P_MAX_BTWN     INTEGER
    :P_AVE_BTWN     DOUBLE PRECISION;

    INSERT INTO DRAW_STATS( ELM_NO, HIT_COUNT, LAST_DRAW_NO, SINCE_LAST,
    CURR_SEQ, LONGEST_SEQ, PCT_HITS, MAX_BTWN, AVE_BTWN)
    VALUES (:P_ELM_NO, :P_HIT_COUNT, :P_LAST_DRAW_NO, :P_SINCE_LAST,
    :P_CURR_SEQ, :P_LONGEST_SEQ, :P_PCT_HITS, :P_MAX_BTWN, :P_AVE_BTWN);
    
PROCEDURE FIND_DRAW_REC
    SQLCODE
    :P_DRAW_DT      DATE VMS
    :P_NO_1         TINYINT
    :P_NO_2         TINYINT
    :P_NO_3         TINYINT
    :P_NO_4         TINYINT
    :P_NO_5         TINYINT
    :P_MEGA_NO      TINYINT;

    SELECT DRAW_DT, NO_1, NO_2, NO_3, NO_4, NO_5, MEGA_NO
    INTO :P_DRAW_DT, :P_NO_1, :P_NO_2, :P_NO_3, :P_NO_4, :P_NO_5, :P_MEGA_NO
    FROM DRAWING_DATA
    WHERE DRAW_DT >= :P_DRAW_DT
    LIMIT TO 1 ROW;


PROCEDURE UPDATE_DRAW_REC
    SQLCODE
    :P_DRAW_DT      DATE VMS
    :P_NO_1         TINYINT
    :P_NO_2         TINYINT
    :P_NO_3         TINYINT
    :P_NO_4         TINYINT
    :P_NO_5         TINYINT
    :P_MEGA_NO      TINYINT;

    UPDATE DRAWING_DATA
    SET NO_1 = :P_NO_1,
        NO_2 = :P_NO_2,
        NO_3 = :P_NO_3,
        NO_4 = :P_NO_4,
        NO_5 = :P_NO_5,
        MEGA_NO = :P_MEGA_NO
    WHERE DRAW_DT = :P_DRAW_DT;

PROCEDURE DELETE_SINGLE_DRAW_REC
    SQLCODE
    :P_DRAW_DT  DATE VMS;

    DELETE FROM DRAWING_DATA
    WHERE DRAW_DT = :P_DRAW_DT;