CREATE TABLE lottorad(
nr NUMBER(6));
CREATE TABLE lottofacit(
nr NUMBER(6));
INSERT INTO lottofacit VALUES(3);
INSERT INTO lottofacit VALUES(7);
INSERT INTO lottofacit VALUES(11);
INSERT INTO lottofacit VALUES(35);
INSERT INTO lottofacit VALUES(12);
INSERT INTO lottofacit VALUES(5);
INSERT INTO lottofacit VALUES(24);
COMMIT;

SELECT *
FROM lottofacit;

---------- Package lotto -----------------
CREATE OR REPLACE PACKAGE lotto
AS
    PROCEDURE clear_table;
    PROCEDURE skapaLottoRad;
    FUNCTION skapaNyttLottoNr RETURN NUMBER;
    FUNCTION get_speladRad RETURN TYPE;
    TYPE rc IS REF CURSOR;

END;
/


----- Skapar Package Body -----

CREATE OR REPLACE PACKAGE BODY lotto
AS
    ------------Slumpar heltal (1-35)--------------
    FUNCTION skapaNyttLottoNr
    RETURN NUMBER
    AS
    lottoNr NUMBER(2);
    BEGIN
        SELECT ROUND(DBMS_RANDOM.VALUE(1,35),0)
        INTO lottoNr
        FROM dual;
        RETURN lottoNr;
    END skapaNyttLottoNr;


    ------------Hämtar spelad rad och returnerar en cursor--------------
    FUNCTION get_speladRad
        RETURN lotto.rc
        AS
        x lotto.rc;

        BEGIN
        OPEN x FOR
            SELECT nr
            FROM lottorad;
            RETURN x;
    END get_speladRad;


    ------------Tömmer lottorad-------------------------
    PROCEDURE clear_table
    AS
    v_nr NUMBER;

    BEGIN
        SELECT COUNT(*)
        INTO v_nr
        FROM lottorad;

        IF v_nr != 0
           THEN DELETE FROM lottorad;
        END IF;
    END clear_table;


    ------------Procedur skapaLottoRad------------------
    PROCEDURE skapaLottoRad
    AS
    v_lottoNr lottorad.nr%TYPE;
    v_count NUMBER;
    v_nr NUMBER;
    v_tempNr NUMBER;

    BEGIN
        v_count := 0;

        WHILE v_count < 7
        LOOP
            v_lottoNr := skapaNyttLottoNr;
            SELECT COUNT(nr)
            INTO v_tempNr
            FROM lottorad
            WHERE nr = v_lottoNr;
            IF v_tempNR <= 0 THEN
                INSERT INTO lottorad(nr)
                VALUES(v_lottoNr);

                v_count := v_count + 1;
            END IF;
        END LOOP;
    END;
END lotto;
/

EXEC lotto.clear_table;
EXEC lotto.skapaLottoRad;

SELECT *
FROM lottorad;