Untitled

-- Utwórz w procedurę zwracająca dane studentów (Imię, Nazwisko, Miasto, Numer
-- indeksu), których rok rekrutacji będzie podawany w parametrze procedury


CREATE OR REPLACE PROCEDURE Students (p_Rok INT)
    IS
    CURSOR StudentZroku IS
        SELECT Imie, Nazwisko, NrIndeksu
        FROM Osoba O
                 JOIN Student S ON O.IdOsoba = S.IdOsoba
        WHERE EXTRACT(YEAR FROM DataRekrutacji) = p_Rok;

    v_Student StudentZroku%ROWTYPE;
BEGIN
    OPEN StudentZroku;
    LOOP
        FETCH StudentZroku INTO v_Student;
        EXIT WHEN StudentZroku%NOTFOUND;
        dbms_output.put_line(v_Student.Imie || ' ' || v_Student.Nazwisko);
    END LOOP;
    CLOSE StudentZroku;
END;
Set Serveroutput on;
BEGIN
    Students(2013);
end;

-- Utwórz procedurę, która będzie „przenosiła” zapisane w bazie danych osoby z miasta,
-- którego nazwa jest podana w parametrze procedury do innego miasta, też podanego
-- (nazwa) w parametrze procedury. W wyniku działania procedury proszę też wyświetlić
-- komunikat z informacją, ile osób zostało przeniesionych i pomiędzy jakimi miastami.


CREATE TABLE Miasto (
                        IdMiasto INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
                        Miasto VARCHAR2(32) NOT NULL
);

-- Dodanie kilku miast
INSERT INTO Miasto (Miasto) VALUES ('Warszawa');
INSERT INTO Miasto (Miasto) VALUES ('Kraków');
INSERT INTO Miasto (Miasto) VALUES ('Poznań');
INSERT INTO Miasto (Miasto) VALUES ('Łódź');

ALTER TABLE Osoba ADD IdMiasto INT;

ALTER TABLE Osoba
    ADD CONSTRAINT fk_Miasto FOREIGN KEY (IdMiasto) REFERENCES Miasto(IdMiasto);

CREATE OR REPLACE PROCEDURE TRANSFER_PEOPLE (
    v_zMiasta IN VARCHAR2,
    v_doMiasta IN VARCHAR2
)
    IS
    -- Deklaracja zmiennych
    v_z NUMBER;
    v_do NUMBER;
    v_counter NUMBER := 0;  -- Inicjalizacja licznika na zero

    -- Kursor po osobach z określonego miasta
    CURSOR c_iterator IS
        SELECT IdOsoba FROM Osoba WHERE IdMiasto = v_z;

BEGIN
    -- Pobranie ID miasta źródłowego
    SELECT IdMiasto
    INTO v_z
    FROM Miasto
    WHERE Miasto = v_zMiasta;

    -- Pobranie ID miasta docelowego
    SELECT IdMiasto
    INTO v_do
    FROM Miasto
    WHERE Miasto = v_doMiasta;

    -- Sprawdzenie, czy miasto źródłowe i docelowe są różne
    IF v_z = v_do THEN
        DBMS_OUTPUT.PUT_LINE('Miasto źródłowe i docelowe są identyczne. Brak potrzeby transferu.');
        RETURN;
    END IF;

    -- Iteracja po osobach z danego miasta i aktualizacja
    FOR osoba_rec IN c_iterator LOOP
            UPDATE Osoba
            SET IdMiasto = v_do
            WHERE IdOsoba = osoba_rec.IdOsoba;

            -- Zwiększenie licznika tylko jeśli zmieniono rekord
            IF SQL%ROWCOUNT > 0 THEN
                v_counter := v_counter + 1;
            END IF;

        END LOOP;

    -- Komunikat końcowy
    DBMS_OUTPUT.PUT_LINE(v_counter || ' osoby zostały przeniesione z ' || v_zMiasta || ' do ' || v_doMiasta);

    -- Zatwierdzenie transakcji
    COMMIT;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Jedno z miast nie istnieje!');
        ROLLBACK;
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Wystąpił nieoczekiwany błąd: ' || SQLERRM);
        ROLLBACK;
END;
/

BEGIN
    TRANSFER_PEOPLE('Warszawa', 'Kraków');
END;
/

-- Utwórz procedurę służącą do dopisywania nowego przedmiotu do bazy. Procedura będzie
-- otrzymywała w parametrach nazwę i symbol przedmiotu. W procedurze należy sprawdzić,
-- czy przedmiot o danej nazwie lub symbolu istnieje. Jeżeli nie, należy go dopisać. Na
-- zakończenie należy wypisać komunikat z informacją o wykonaniu (lub nie) operacji.

CREATE OR REPLACE PROCEDURE ADD_SUBJECT(
    NAZWA IN VARCHAR2,
    SYMBOL IN VARCHAR2
)
    IS
    found NUMBER := 0; -- 0 = false, 1 = true
    max_current_id NUMBER;

BEGIN
    -- Sprawdzenie czy przedmiot już istnieje
    SELECT COUNT(*)
    INTO found
    FROM PRZEDMIOT
    WHERE PRZEDMIOT = NAZWA OR SYMBOL = SYMBOL;

    -- Jeśli znaleziono przedmiot
    IF found > 0 THEN
        DBMS_OUTPUT.PUT_LINE('Przedmiot ' || NAZWA || ' już istnieje w bazie danych.');
    ELSE
        -- Znalezienie najwyższego ID przedmiotu
        SELECT NVL(MAX(IDPRZEDMIOT), 0)
        INTO max_current_id
        FROM PRZEDMIOT;

        -- Dodanie nowego przedmiotu
        INSERT INTO PRZEDMIOT(IDPRZEDMIOT, PRZEDMIOT, SYMBOL)
        VALUES(max_current_id + 1, NAZWA, SYMBOL);

        DBMS_OUTPUT.PUT_LINE('Dodano przedmiot ' || NAZWA || ' do bazy danych.');
        COMMIT;
    END IF;

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Wystąpił błąd: ' || SQLERRM);
        ROLLBACK;
END;
/

-- Utwórz procedurę dopisującą nowego dydaktyka do bazy. Imię, nazwisko, płeć i nazwa
-- stopnia naukowego będą podawane w parametrach procedury.
--
-- W procedurze sprawdź istnienie w bazie stopnia naukowego o podanej w parametrze nazwie, oraz rekordu
-- zawierającego dane kandydata (imię, nazwisko, stopień).
--
-- Jeżeli taki dydaktyk już jest w bazie odnotowany, nie dopisuj go.
-- Jeżeli stopień o podanej nazwie nie jest w bazie
-- odnotowany, wstaw NULL w rekordzie dydaktyka.
-- Nowo dopisanego dydaktyka zrób podwładnym profesora Cezarego Czosnka.
--
-- Procedurę należy zakończyć komunikatem informującym o wykonanej operacji.
-- W PL/SQL utwórz i wykorzystaj sekwencję do realizacji wartości klucza głównego w tabelach OSOBA i DYDAKTYK.

CREATE SEQUENCE przedmiot_seq
    START WITH 1      -- Pierwsza wartość wygenerowana przez sekwencję
    INCREMENT BY 1    -- Każda kolejna wartość będzie zwiększana o 1
    NOCACHE           -- Nie buforuje wartości (można pominąć)
    NOCYCLE;          -- Nie powtarza wartości po osiągnięciu maksimum



CREATE OR REPLACE PROCEDURE ADD_TEACHER(
    IMIE IN VARCHAR2,
    NAZWISKO IN VARCHAR2,
    PLEC IN CHAR,
    STOPIEN_NAZWA IN VARCHAR2
)
    IS
    -- Zmienna do przechowywania ID osoby
    id_osoba NUMBER;
    przelozony NUMBER;
    stopien_exists NUMBER;
    id_katedra NUMBER;
    found_teacher NUMBER := 0;

BEGIN
    -- Sprawdzenie, czy dana osoba już istnieje w bazie
    SELECT COUNT(*)
    INTO found_teacher
    FROM OSOBA O
             JOIN DYDAKTYK D ON O.IDOSOBA = D.IDOSOBA
    WHERE O.IMIE = IMIE AND O.NAZWISKO = NAZWISKO;

    -- Jeśli dydaktyk już istnieje, zakończ procedurę
    IF found_teacher > 0 THEN
        DBMS_OUTPUT.PUT_LINE('Dydaktyk o podanym imieniu i nazwisku już istnieje w bazie.');
        RETURN;
    END IF;

    -- Pobranie ID stopnia naukowego, jeśli istnieje
    BEGIN
        SELECT IDSTOPIEN
        INTO stopien_exists
        FROM STOPNIETYTULY
        WHERE STOPIEN = STOPIEN_NAZWA;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            stopien_exists := NULL;
    END;

    -- Pobranie ID przełożonego (profesor Cezary Czosnek)
    BEGIN
        SELECT IDOSOBA
        INTO przelozony
        FROM OSOBA
        WHERE IMIE = 'Cezary' AND NAZWISKO = 'Czosnek';
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('Przełożony nie został znaleziony.');
            RETURN;
    END;

    -- Poprawione pobieranie ID katedry z jednoznacznymi aliasami
    BEGIN
        SELECT K.IDKATEDRA
        INTO id_katedra
        FROM KATEDRA K
                 JOIN DYDAKTYK D ON K.IDKATEDRA = D.IDKATEDRA
        WHERE D.IDOSOBA = przelozony;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            id_katedra := NULL;
    END;

    -- Dodanie nowego rekordu w tabeli OSOBA
    INSERT INTO OSOBA (IDOSOBA, IMIE, NAZWISKO, PLEC)
    VALUES (przedmiot_seq.NEXTVAL, IMIE, NAZWISKO, PLEC);

    -- Przypisanie osoby do tabeli DYDAKTYK
    INSERT INTO DYDAKTYK (IDOSOBA, IDSTOPIEN, PODLEGA, IDKATEDRA)
    VALUES (przedmiot_seq.CURRVAL, stopien_exists, przelozony, id_katedra);

    DBMS_OUTPUT.PUT_LINE('Dydaktyk ' || IMIE || ' ' || NAZWISKO || ' został dodany.');
    COMMIT;

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Wystąpił błąd: ' || SQLERRM);
        ROLLBACK;
END;
/