/* Dane są tabele:
STUDENT {IdStudenta Int PK, NrIndeksu Varchar2(10), Rok Int, Imie Varchar2(32), Nazwisko Varchar2(64)}
OCENA {IdOceny Int PK, IdStudenta  FK, Wartosc Int, Przedmiot Varchar2(32).
1.  W bloku PL/SQL przejrzyj wszystkich studentów 2 roku, wypisując średnią każdego z nich oraz kasując studentów (i ich oceny), którzy mają średnią poniżej 2,5. W tabeli nie jest włączone kaskadowe usuwanie.
2.  Napisz wyzwalacz, który:
>Pozwoli na zmianę roku jedynie o 1 w górę,
>nie pozwoli na wpisanie roku wyższego niż 5. */

--ddl

DROP TABLE OCENA;
DROP TABLE STUDENT;
/

CREATE TABLE STUDENT
(
    IdStudenta INT PRIMARY KEY,
    NrIndeksu  VARCHAR2(10) NOT NULL,
    Rok        INT          NOT NULL,
    Imie       VARCHAR2(32) NOT NULL,
    Nazwisko   VARCHAR2(64) NOT NULL
)
/
CREATE TABLE OCENA
(
    IdOceny    INT PRIMARY KEY,
    IdStudenta INT NOT NULL REFERENCES STUDENT,
    Wartosc    INT NOT NULL,
    Przedmiot  VARCHAR2(32)
)
/

--dml

INSERT INTO STUDENT (IDSTUDENTA, NRINDEKSU, ROK, IMIE, NAZWISKO)
VALUES (1, 'S00001', 1, 'Adam', 'Małysz');
INSERT INTO STUDENT (IDSTUDENTA, NRINDEKSU, ROK, IMIE, NAZWISKO)
VALUES (2, 'S00002', 2, 'Mariusz', 'Pudzianowski');
INSERT INTO STUDENT (IDSTUDENTA, NRINDEKSU, ROK, IMIE, NAZWISKO)
VALUES (3, 'S00003', 3, 'Robert', 'Kubica');
INSERT INTO STUDENT (IDSTUDENTA, NRINDEKSU, ROK, IMIE, NAZWISKO)
VALUES (4, 'S00004', 2, 'Zbigniew', 'Stonoga');
INSERT INTO STUDENT (IDSTUDENTA, NRINDEKSU, ROK, IMIE, NAZWISKO)
VALUES (5, 'S00005', 5, 'Leo', 'Messi');
/

INSERT INTO OCENA (IdOceny, IdStudenta, Wartosc, Przedmiot)
VALUES (1, 1, 3, 'Matematyka');
INSERT INTO OCENA (IdOceny, IdStudenta, Wartosc, Przedmiot)
VALUES (2, 1, 2, 'Matematyka');
INSERT INTO OCENA (IdOceny, IdStudenta, Wartosc, Przedmiot)
VALUES (3, 1, 5, 'Matematyka');
INSERT INTO OCENA (IdOceny, IdStudenta, Wartosc, Przedmiot)
VALUES (4, 1, 4, 'Matematyka');
INSERT INTO OCENA (IdOceny, IdStudenta, Wartosc, Przedmiot)
VALUES (5, 2, 3, 'Matematyka');
INSERT INTO OCENA (IdOceny, IdStudenta, Wartosc, Przedmiot)
VALUES (6, 2, 2, 'Matematyka');
INSERT INTO OCENA (IdOceny, IdStudenta, Wartosc, Przedmiot)
VALUES (7, 2, 2, 'Matematyka');
INSERT INTO OCENA (IdOceny, IdStudenta, Wartosc, Przedmiot)
VALUES (8, 2, 2, 'Matematyka');
INSERT INTO OCENA (IdOceny, IdStudenta, Wartosc, Przedmiot)
VALUES (9, 3, 3, 'Matematyka');
INSERT INTO OCENA (IdOceny, IdStudenta, Wartosc, Przedmiot)
VALUES (10, 3, 5, 'Matematyka');
INSERT INTO OCENA (IdOceny, IdStudenta, Wartosc, Przedmiot)
VALUES (11, 4, 4, 'Matematyka');
INSERT INTO OCENA (IdOceny, IdStudenta, Wartosc, Przedmiot)
VALUES (12, 4, 3, 'Matematyka');
INSERT INTO OCENA (IdOceny, IdStudenta, Wartosc, Przedmiot)
VALUES (13, 5, 4, 'Matematyka');

--zad1

DECLARE
    v_student INT;
    v_rok     INT;
    v_srednia NUMBER := 0;
    CURSOR cur_oceny IS SELECT IdStudenta, Rok
                        FROM STUDENT;
BEGIN
    OPEN cur_oceny;
    LOOP
        FETCH cur_oceny INTO v_student, v_rok;
        EXIT WHEN cur_oceny%NOTFOUND;

        SELECT NVL(AVG(Wartosc), 0) INTO v_srednia FROM OCENA WHERE IdStudenta = v_student;
        IF v_rok = 2 THEN
            DBMS_OUTPUT.PUT_LINE('SREDNIA STUDENTA ID ' || v_student || ' WYNOSI ' || v_srednia);
        END IF;
        IF v_srednia < 2.5 THEN
            DELETE FROM OCENA WHERE IdStudenta = v_student;
            DELETE FROM STUDENT WHERE IdStudenta = v_student;
        END IF;
    END LOOP;
    CLOSE cur_oceny;
END;

--zad2

CREATE OR REPLACE TRIGGER year_trigg
    BEFORE INSERT OR UPDATE
    ON STUDENT
    FOR EACH ROW
DECLARE
    v_newYear STUDENT.Rok%TYPE;
BEGIN
    v_newYear := :NEW.Rok;
    IF INSERTING THEN
        IF v_newYear > 5 THEN
            RAISE_APPLICATION_ERROR
                (-20100, 'Nie mozna wstawic roku wiekszego niz 5');
        END IF;
    ELSIF UPDATING THEN
        IF (v_newYear - :old.Rok > 1 OR v_newYear - :old.Rok < 0) THEN
            :NEW.Rok := :old.Rok;
            --DBMS_OUTPUT.PUT_LINE('NIE MOZNA PRZESKOCZYC O WIECEJ NIZ 1 ROK');
            RAISE_APPLICATION_ERROR
                (-20100, 'NIE MOZNA PRZESKOCZYC O WIECEJ NIZ 1 ROK LUB COFNAC SIE');

        ELSIF v_newYear > 5 THEN
            :NEW.Rok := :old.Rok;
            --DBMS_OUTPUT.PUT_LINE('Nie mozna dac roku wiekszego niz 5');
            RAISE_APPLICATION_ERROR
                (-20100, 'NIE MOZNA WSTAWIC ROKU WIEKSZEGO NIZ 5');
        END IF;
    END IF;
END;

UPDATE STUDENT
SET ROK = 6
WHERE IDSTUDENTA = 5;

INSERT INTO STUDENT (IdStudenta, NrIndeksu, Rok, Imie, Nazwisko)
VALUES (6, 's2137', 6, 'Jerzy', 'Delbana');