--
-- vytvoreni a naplneni tabulek pro vazbu mezi uciteli a predmety
--
DROP TABLE ucitele;
CREATE TABLE ucitele (id NUMBER PRIMARY KEY, jmeno VARCHAR2(20));
INSERT INTO ucitele VALUES (1,'Matyas');
INSERT INTO ucitele VALUES (2,'Riha');
INSERT INTO ucitele VALUES (3,'Svenda');

DROP TABLE predmety;
CREATE TABLE predmety (id NUMBER PRIMARY KEY, jmeno VARCHAR2(20));
INSERT INTO predmety VALUES (1,'Architektura RDBS');
INSERT INTO predmety VALUES (2,'Projekt z DBS');
INSERT INTO predmety VALUES (3,'Autentizace');
INSERT INTO predmety VALUES (4,'Zonglovani');

DROP TABLE uci;
CREATE TABLE uci (kdo NUMBER, co NUMBER);
INSERT INTO uci VALUES (2,1);
INSERT INTO uci VALUES (2,3);
INSERT INTO uci VALUES (1,3);

DROP TABLE logs;
CREATE TABLE logs(logline VARCHAR2(50));

--
-- kursor, ktery nacte bez pozuiti cyklu prvni tri zaznamy a ulozi od specialni tabulky
-- 
DROP TABLE ucitele_jmena;
CREATE TABLE ucitele_jmena (jmeno VARCHAR2(20));

DECLARE
CURSOR pr1 IS SELECT jmeno FROM ucitele;
name1 ucitele.jmeno%TYPE;
name2 ucitele.jmeno%TYPE;
name3 ucitele.jmeno%TYPE;
BEGIN
OPEN pr1;
FETCH pr1 INTO name1; -- ziskam prvni radek
FETCH pr1 INTO name2; -- ziskam druhy radek
FETCH pr1 INTO name3; -- ziskam treti radek

INSERT INTO ucitele_jmena VALUES (name1);
INSERT INTO ucitele_jmena VALUES (name2);
INSERT INTO ucitele_jmena VALUES (name3);

CLOSE pr1;
END;
/
SELECT * FROM ucitele_jmena; 

--
-- kurzor se vstupnimi parametry 
-- pouziti attributu %NOTFOUND pro ukonceni nekonecneho cyklu nacitani (kdyz neni nalezen dalsi zaznam)
-- pouziti attributu %ISOPEN pro detekci stavu kurzoru
DELETE FROM ucitele_jmena;

DECLARE
CURSOR pr2(id_low NUMBER, id_high NUMBER) IS SELECT jmeno FROM ucitele WHERE ucitele.id BETWEEN id_low AND id_high;
name ucitele.jmeno%TYPE;

BEGIN
OPEN pr2(1, 2); -- parametry se zadavaji pri otevreni kurzoru
IF pr2%ISOPEN THEN INSERT INTO logs(logline) VALUES ('pr2 je otevren'); END IF;
LOOP
    FETCH pr2 INTO name;  
    EXIT WHEN pr2%NOTFOUND;
    INSERT INTO ucitele_jmena VALUES (name);
END LOOP;
CLOSE pr2;

IF NOT pr2%ISOPEN THEN INSERT INTO logs(logline) VALUES ('pr2 je uz uzavren'); END IF;

END;
/
SELECT * FROM ucitele_jmena; 
SELECT * FROM logs;

--
-- funkce vyuzivajici kurzor na zjisteni poctu predmetu, ktere vyucuje ucitel se zadanym id 
-- 
CREATE OR REPLACE FUNCTION pocet_uci(id NUMBER) RETURN NUMBER AS 
  CURSOR pr3 IS SELECT * FROM uci;
  pocet_predmetu NUMBER := 0;
  BEGIN
  FOR uci_rec IN pr3    -- FOR cyklus nacita do promenne typu %ROWTYPE
  LOOP
    IF uci_rec.kdo = id THEN pocet_predmetu := pocet_predmetu + 1;
    END IF;
  END LOOP;
  
  RETURN pocet_predmetu;
END;
/
SHOW ERROR;

SELECT pocet_uci(2) AS FROM dual; 


--
-- vyuziti hromadneho nacteni vsech zaznamu z tabulky pomoci BULK COLLECT
-- vyuziti %ROWCOUNT pro explicitni kurzor
-- 
DECLARE
TYPE jmenoTab IS TABLE OF ucitele.jmeno%TYPE;   -- definice typu vysledne tabulky  
names jmenoTab;
pocet NUMBER;
CURSOR pr4 IS SELECT jmeno FROM ucitele; 
BEGIN
OPEN pr4;
FETCH pr4 BULK COLLECT INTO names;    -- hromadne nacteni jmen

-- rozhodovani dle poctu nactenych zaznamu pomoci attributu %ROWCOUNT 
IF (pr4%ROWCOUNT > 2) THEN
    -- pouze ulozeni hlaseni, ze bylo nacteno prilis mnoho zaznamu  
    pocet := pr4%ROWCOUNT;
    INSERT INTO ucitele_jmena(jmeno) VALUES ('moc ucitelu:' || TO_CHAR(pocet));
ELSE
    -- ulozeni nactenych dat do tabulky
    FORALL i IN names.first .. names.last INSERT INTO ucitele_jmena(jmeno) VALUES (names(i));
END IF;    

CLOSE pr4;
END;
/
SHOW ERROR;
SELECT * FROM ucitele_jmena;

SELECT COUNT(*) Pocet_ucitelu FROM ucitele_jmena;
-- implicitni kursor po operaci DELETE
DECLARE
pocet NUMBER;    
u_jmeno CONSTANT VARCHAR2(20) := 'Neexistujici';
BEGIN
    DELETE FROM ucitele_jmena WHERE jmeno = u_jmeno;
    IF (SQL%FOUND) THEN
        INSERT INTO logs(logline) VALUES ('ucitel ' || u_jmeno || ' smazan');
    ELSE 
        INSERT INTO logs(logline) VALUES ('ucitel ' || u_jmeno || ' nebyl nalezen');
    END IF;
    
    DELETE FROM ucitele_jmena; 
    pocet := SQL%ROWCOUNT;
    INSERT INTO logs(logline) VALUES ('Celkem smazano ' || TO_CHAR(pocet) || ' ucitelu'); 
END;
/
SHOW ERROR;

SELECT * FROM logs;