Tárolt eljárás:
CREATE OR REPLACE PROCEDURE hallgato_adatok(neptun_id_param IN VARCHAR2) IS
v_hallgato_id VARCHAR2(50);
v_legjobb_erdemjegy NUMBER;
v_felvett_kredit NUMBER;
BEGIN
— Keresse meg a hallgatót a neptunID alapján
SELECT neptunID, MAX(erdemjegy), SUM(kredit)
INTO v_hallgato_id, v_legjobb_erdemjegy, v_felvett_kredit
FROM leckekonyv
WHERE neptunID = neptun_id_param
GROUP BY neptunID;
— Ha nincs találat, írja ki az üzenetet
IF v_hallgato_id IS NULL THEN
DBMS_OUTPUT.PUT_LINE(‘Nincs ilyen hallgatóról adat.’);
ELSE
— Kiírja a hallgató adatait
DBMS_OUTPUT.PUT_LINE(‘Hallgató azonosítója: ‘ || v_hallgato_id);
DBMS_OUTPUT.PUT_LINE(‘Hallgató legjobb érdemjegye: ‘ || v_legjobb_erdemjegy);
DBMS_OUTPUT.PUT_LINE(‘Hallgató felvett kreditjeinek összege: ‘ || v_felvett_kredit);
END IF;
END hallgato_adatok;
/
— Tesztelési példák
EXEC hallgato_adatok(‘OEB1X1’); — Létező neptunID
EXEC hallgato_adatok(‘OEB0T2’); — Nem létező neptunID
EXEC hallgato_adatok(‘OENULL’); — Nem létező neptunID
Trigger
CREATE OR REPLACE TRIGGER ellenoriz_elofeltetel
BEFORE INSERT ON kurzusok
FOR EACH ROW
DECLARE
v_elofeltetel_kredit NUMBER;
BEGIN
— Ellenőrzi az előfeltétel kreditszámát
SELECT kredit INTO v_elofeltetel_kredit
FROM kurzusok
WHERE kurzusID = :NEW.elofeltetelID;
IF v_elofeltetel_kredit IS NOT NULL AND :NEW.kredit < v_elofeltetel_kredit THEN
— Kiírja az üzenetet, és beállítja az új kreditszámot az előfeltételére
DBMS_OUTPUT.PUT_LINE(‘Az új kurzus kreditszáma egyezzen meg az előfeltételének kreditszámával.’);
:NEW.kredit := v_elofeltetel_kredit;
END IF;
END ellenoriz_elofeltetel;
/
— Tesztelési példák
INSERT INTO kurzusok VALUES (101, ‘Tesi’, TO_DATE(‘2023-08-01’, ‘YYYY-MM-DD’), 2, 18, 2); — kisebb kredit
— Ebben az esetben az új kurzus kreditje egyezik meg az előfeltételének kreditszámával
INSERT INTO kurzusok VALUES (102, ‘Tesi2’, TO_DATE(‘2023-02-02’, ‘YYYY-MM-DD’), 6, 18, 2); — nagyobb kredit
— Ebben az esetben az új kurzus kreditje is egyezik meg az előfeltételének kreditszámával
— Tesztelés ellenőrzésképpen mentési ponttal
SAVEPOINT elofeltetel_ellenorzes;
— Ezt a részt ki is hagyhatod, ha nem akarod visszavonni a módosításokat
ROLLBACK TO SAVEPOINT elofeltetel_ellenorzes;
Lekérdezések
SELECT oktatoID, vnev || ‘ ‘ || knev AS teljes_nev, fizetes, AVG(ertekeles) AS atlag_ertekeles
FROM oktatok o
LEFT JOIN leckekonyv l ON o.oktatoID = l.oktatoID
GROUP BY oktatoID, vnev, knev, fizetes
ORDER BY AVG(ertekeles) DESC, oktatoID;
SELECT oktatoID, vnev || ‘ ‘ || knev AS teljes_nev, AVG(ertekeles) AS atlag_ertekeles, COUNT(l.oktatoID) AS kurzusok_szama
FROM oktatok o
JOIN leckekonyv l ON o.oktatoID = l.oktatoID
GROUP BY oktatoID, vnev, knev
HAVING AVG(ertekeles) > 4.0
ORDER BY COUNT(l.oktatoID) DESC;
SELECT kurzusID, COUNT(ertekeles) AS ertekelesek_szama
FROM leckekonyv
WHERE ertekeles > 4
GROUP BY kurzusID
ORDER BY ertekelesek_szama DESC
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;
SELECT o.oktatoID, o.vnev || ‘ ‘ || o.knev AS teljes_nev, o.fizetes
FROM oktatok o
WHERE o.fizetes IS NOT NULL
ORDER BY o.fizetes DESC
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY
UNION
SELECT o.oktatoID, o.vnev || ‘ ‘ || o.knev AS teljes_nev, o.fizetes
FROM oktatok o
WHERE o.fizetes IS NOT NULL
ORDER BY o.fizetes ASC
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;
SELECT neptunID, AVG(erdemjegy) AS atlag_erdemjegy, COUNT(kurzusID) AS kurzusok_szama
FROM leckekonyv
GROUP BY neptunID
HAVING AVG(erdemjegy) = 5.0
ORDER BY COUNT(kurzusID) DESC
OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;
Szorgalmi
— Felhasználók és szerepkör létrehozása
CREATE USER Gyakorlatv IDENTIFIED BY gyv123;
CREATE USER Eloado