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

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

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

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

...
Table created.
1 row created.
...

--
-- kursor, ktery nacte bez pozuiti cyklu prvni tri zaznamy a ulozi od specialni tabulky
-- 
SQL> DROP TABLE ucitele_jmena;
SQL> CREATE TABLE ucitele_jmena (jmeno VARCHAR2(20));
SQL> 
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;
/
PL/SQL procedure successfully completed.

SQL> SELECT * FROM ucitele_jmena; 

JMENO
--------------------
Matyas
Riha
Svenda

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

3 rows deleted.

SQL>
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;
/

PL/SQL procedure successfully completed.

SQL> SELECT * FROM ucitele_jmena; 

JMENO
--------------------
Matyas
Riha

SQL> SELECT * FROM logs;

LOGLINE
--------------------------------------------------
pr2 je otevren
pr2 je uz uzavren

--
-- funkce vyuzivajici kurzor na zjisteni poctu predmetu, ktere vyucuje ucitel se zadanym id 
--
SQL> 
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;
/

Function created.

SQL> SHOW ERROR;
SQL> SELECT pocet_uci(2) AS FROM dual;

POCET_UCI(2)
------------
           2

--
-- vyuziti hromadneho nacteni vsech zaznamu z tabulky pomoci BULK COLLECT
-- vyuziti %ROWCOUNT pro explicitni kurzor
--
SQL> 
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;
/

PL/SQL procedure successfully completed.

SQL> SHOW ERROR;
SQL> SELECT * FROM ucitele_jmena;

JMENO
--------------------
Matyas
Riha
moc ucitelu:3

SQL> SELECT COUNT(*) Pocet_ucitelu FROM ucitele_jmena;

POCET_UCITELU
-------------
            3

-- implicitni kursor po operaci DELETE
SQL> 
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;
/

PL/SQL procedure successfully completed.

SQL> SHOW ERROR;
SQL> SELECT * FROM logs;

LOGLINE
--------------------------------------------------
pr2 je otevren
pr2 je uz uzavren
ucitel Neexistujici nebyl nalezen
Celkem smazano 3 ucitelu