-- vytvareni pohledu na propojeni mezi ucitelem a predmety ktere uci.
-- pouziva tabulky z cviceni 5 (http://www.fi.muni.cz/~xsvenda/vyuka/pv003/cv5.sql)
DROP VIEW ucitele_uci;
CREATE VIEW ucitele_uci AS
   SELECT ucitele.jmeno AS ucitel, predmety.jmeno AS predmet FROM predmety, ucitele, uci WHERE
   ucitele.id=uci.kdo AND uci.co=predmety.id;

-- pouziti pohledu (jako 'bezna' tabulka) 
SELECT * FROM ucitele_uci;
SELECT predmet FROM ucitele_uci WHERE ucitel='Matyas';

--
-- PL/SQL procedury
-- 
DROP TABLE lide;
DROP TABLE audit_log;
CREATE TABLE lide (uco NUMBER, sal NUMBER);
CREATE TABLE audit_log(uco NUMBER, text VARCHAR2(50));

-- tvorba opakovane pouzitelne procedury (bude ulozena v databazi)
CREATE OR REPLACE PROCEDURE zvys_vyplatu (uco_zam INTEGER, suma REAL) IS
soucasna REAL;
salary_missing EXCEPTION;
BEGIN
SELECT sal INTO soucasna FROM lide WHERE uco = uco_zam;
IF soucasna IS NULL THEN
RAISE salary_missing;
ELSE
UPDATE lide SET sal = sal + suma WHERE uco = uco_zam;
INSERT INTO audit_log VALUES (uco_zam, 'Vyplata zvysena na ' || TO_CHAR(soucasna + suma));
END IF;

EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO audit_log VALUES (uco_zam, 'UCO neexistuje');
WHEN salary_missing THEN
INSERT INTO audit_log VALUES (uco_zam, 'Vyplata je NULL');
END zvys_vyplatu;
/
-- vypis pripadnych chyb pri vkladani procedury do databaze
show error;

-- provedeme zvyseni vyplaty pro neexistujiciho zamestnance. 
-- dojde k vyvolani vyjimky 'NO_DATA_FOUND'
EXECUTE zvys_vyplatu(1,1000);
SELECT * FROM lide;
SELECT * FROM audit_log;

-- zvyseni vyplaty existujiciho zamestnance (probehne v poradku)
INSERT INTO lide (uco, sal) VALUES (1, 7550); 
EXECUTE zvys_vyplatu(1,1000);
SELECT * FROM lide;
SELECT * FROM audit_log;

-- zmena platu na NULL, aby doslo k vyvolani nasi interni vyjimky 'salary_missing'
UPDATE lide SET sal=NULL where uco=1;
EXECUTE zvys_vyplatu(1,1000);
SELECT * FROM lide;
SELECT * FROM audit_log;

--
-- PL/SQL funkce
-- 
DROP TABLE accounts;
CREATE TABLE accounts(acct_no NUMBER, balance REAL);
INSERT INTO accounts VALUES (101,10234.5);

CREATE OR REPLACE FUNCTION stav_uctu (cislo_uctu INTEGER) RETURN REAL IS
stav REAL;
BEGIN
SELECT balance INTO stav FROM accounts WHERE acct_no = cislo_uctu;
RETURN stav;
END stav_uctu;
/

-- provedeni funkce (zobrazeni navratove hodnoty pomoci funkce select)
SELECT stav_uctu(101) FROM dual;

-- funkce nepristupujici k databazi
CREATE OR REPLACE FUNCTION urok (roky NUMBER, castka NUMBER, sazba NUMBER) 
RETURN NUMBER IS
BEGIN
RETURN castka * POWER((sazba / 100) + 1, roky);
END urok;
/

SELECT urok(10,7550,5) FROM dual;

-- 5) Vyjimky
-- Ne vsechny interni vyjimky jsou pojmenovane (jako napr. ZERO_DIVIDE), proto
-- je muzeme osetrovat v casti OTHERS nebo pojmenovat:
-- PRAGMA EXCEPTION_INIT(exception_name, Oracle_error_number);
-- Zakladni informace lze nalezt napr. na: http://courses.csusm.edu/cs643yo/slides/exceptions.htm