-- KURSORY
Kurzory pouzivame pro zpracovani vysledku prikazu SELECT
(typicky po jednotlivych radcich).

1) Deklarace kurzoru:

Nejprve je nutne kursor deklarovat v casti declare. Tim se vsak prikaz SELECT
jeste nevykonava.

Syntaxe deklarace kursoru:
 CURSOR cursor_name [(parameter[, parameter]...)][RETURN return_type] IS select_statement;

Parametr vypada:
 cursor_parameter_name [IN] datatype [{:= | DEFAULT} expression]

Priklad:
SQL> DECLARE CURSOR pr1 IS SELECT empno, ename, job FROM emp WHERE deptno = 20;

SQL> DECLARE CURSOR pr2 IS SELECT empno, ename, job, sal FROM emp WHERE sal > 2000;

SQL> DECLARE CURSOR pr3 RETURN dept%ROWTYPE IS SELECT * FROM dept WHERE deptno = 10;

2) Otevreni kursoru
 
DECLARE
CURSOR pr1 IS SELECT ename, job FROM emp WHERE sal < 3000;
...
BEGIN
OPEN pr1;
...
END;

S parametry:
 
DECLARE
emp_name emp.ename%TYPE;
salary emp.sal%TYPE;
CURSOR pr1 (name VARCHAR2, salary NUMBER) IS SELECT ...
...
OPEN pr1(emp_name, 3000);
OPEN pr1(?NOVAK?, 1500);
OPEN pr1(emp_name, salary);

3) Ziskani dat

a) Prikaz FETCH
 
FETCH jmeno_kursoru INTO promenna,... ;

Pr:
SQL> FETCH pr1 INTO my_empno, my_ename, my_deptno;
 
DECLARE
CURSOR pr1 IS SELECT ename FROM emp;
name1 emp.ename%TYPE;
name2 emp.ename%TYPE;
name3 emp.ename%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
...
CLOSE pr1;
END;
 
LOOP
  FETCH pr1 INTO my_record;
  EXIT WHEN pr1%NOTFOUND;
  --  zpracovani dat
END LOOP;

b) FOR ...

kursorovy FOR cyklus
 
FOR promenna IN kursor LOOP
-- zpracovani dat
END LOOP;

Automaticky deklaruje promennou, otevre kursor, provadi fetch a zavre kursor.
 
DECLARE
CURSOR pr1 IS
SELECT ename, sal, hiredate, deptno FROM emp;
...
BEGIN
FOR emp_rec IN pr1 LOOP
...
salary_total := salary_total + emp_rec.sal;
END LOOP;

SQL> 
DECLARE
result temp.col1%TYPE;
CURSOR c1 IS
SELECT n1, n2, n3 FROM data_table WHERE exper_num = 1;
BEGIN
FOR c1_rec IN c1 LOOP
/* calculate and store the results */
result := c1_rec.n2 / (c1_rec.n1 + c1_rec.n3);
INSERT INTO temp VALUES (result, NULL, NULL);
END LOOP;
COMMIT;
END;

c) Prikaz BULK COLLECT:

Prikaz FETCH ziska vzdy jen jeden radek vysledku. Pro ziskani vsech radku
vysledku muzeme vyuzit prikaz BULK COLLECT (vyzaduje promennou typu tabulka).
 
DECLARE
TYPE NumTab IS TABLE OF emp.empno%TYPE;
TYPE NameTab IS TABLE OF emp.ename%TYPE;
nums NumTab;
names NameTab;
CURSOR pr1 IS SELECT empno, ename FROM emp WHERE job = ?CLERK?;
BEGIN
OPEN pr1;
FETCH pr1 BULK COLLECT INTO nums, names;
...
CLOSE pr1;
END;

3) Zavreni kursoru

prikaz CLOSE

SQL> CLOSE pr1;

4) Aliasy pro sloupce neodpovidajici atributum tabulky

Pokud v selectu vyuziji nejen primo jmeno sloupce, ale nejaky vyraz musim
si ho pojmenovat abych se na nej mohl odkazovat.
 
CURSOR pr1 IS
SELECT empno, sal+NVL(comm,0) wages, job FROM ...

IF emp_rec.wages < 1000 THEN ...

5)  Atributy %FOUND, %ISOPEN, %NOTFOUND, %ROWCOUNT

%FOUND - posledni FETCH vratil nejaka data
%NOTFOUND - posledni FETCH jiz nevratil zadna data
%ISOPEN - kursor je otevren
%ROWCOUNT - pocet radku vysledku

             %FOUND    %ISOPEN %NOTFOUND %ROWCOUNT
OPEN 
before       exception FALSE   exception exception 
after        NULL      TRUE    NULL      0

First FETCH 
before       NULL      TRUE    NULL      0
after        TRUE      TRUE    FALSE     1

Next FETCH(es) 
before       TRUE      TRUE    FALSE     1
after        TRUE      TRUE    FALSE     data dependent

Last FETCH 
before       TRUE      TRUE    FALSE     data dependent
after        FALSE     TRUE    TRUE      data dependent

CLOSE 
before       FALSE     TRUE    TRUE      data dependent
after        exception FALSE   exception exception

Tez u implicitnich kursoru:
 
DELETE FROM emp WHERE empno = my_empno;
IF SQL%FOUND THEN -- prikaz delete byl uspesny
 INSERT INTO new_emp VALUES (my_empno, my_ename, ...);

DELETE FROM emp WHERE ...
IF SQL%ROWCOUNT > 10 THEN -- pokud jsem smazal vic nez 10 radku ...
...
END IF;

Priklady:

Mam tabulku lide:
SQL> CREATE TABLE lide (UCO NUMBER, Jmeno VARCHAR2(20), Prijmeni VARCHAR2(20), st NUMBER, zam NUMBER);

Ukol cislo 1: Vytvorte kursor pro radky z tabulky lide, nactete prvni radek 
              vysledku a ten 3x vlozte do pomocne tabulky pom.
	   
SQL> CREATE TABLE pom (uco NUMBER);

Reseni:
SQL> 
SECLARE
CURSOR My_cur IS SELECT * FROM lide;
prom lide%ROWTYPE;

BEGIN
  OPEN My_cur;
  FETCH My_cur INTO prom;
  INSERT INTO pom VALUES (prom.UCO);
  INSERT INTO pom VALUES (prom.UCO);
  INSERT INTO pom VALUES (prom.UCO);
  CLOSE My_cur;
END;
/

Ukol2: Vytvorte tabulky zamestnanci a studenti a pomoci kursoru naplnte
       podle tabulky lide data do tabulek studenti a zamestnanci.
SQL> CREATE TABLE studenti (UCO NUMBER, Jmeno VARCHAR2(20), Prijmeni VARCHAR2(20));
SQL> CREATE TABLE zamestnanci(UCO NUMBER, Jmeno VARCHAR2(20), Prijmeni VARCHAR2(20));

Reseni:
SQL> 
DECLARE
CURSOR My_cur IS SELECT * FROM lide;
prom lide%ROWTYPE;

BEGIN
  FOR prom IN My_cur LOOP
  IF prom.ST = 1 THEN
    INSERT INTO studenti VALUES (prom.UCO, prom.jmeno, prom.prijmeni);
  END IF;
  IF prom.ZAM = 1 THEN
    INSERT INTO zamestnanci VALUES (prom.UCO, prom.jmeno, prom.prijmeni);
  END IF;
  END LOOP;
END;
/