--
-- VIEW
-- 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) 
SQL>  DROP VIEW ucitele_uci; 
SQL>  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;

View created.

-- pouziti pohledu (jako 'bezna' tabulka) 
SQL>  SELECT * FROM ucitele_uci;

UCITEL               PREDMET
-------------------- --------------------
Riha                 Architektura RDBS
Riha                 Autentizace
Matyas               Autentizace

SQL>  SELECT predmet FROM ucitele_uci WHERE ucitel='Matyas';

PREDMET
--------------------
Autentizace


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

-- tvorba opakovane pouzitelne procedury (bude ulozena v databazi)
SQL>  
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; 
/

Procedure created.

-- vypis pripadnych chyb pri vkladani procedury do databaze 
SQL> SHOW error;

No errors.

-- provedeme zvyseni vyplaty pro neexistujiciho zamestnance. 
-- dojde k vyvolani vyjimky 'NO_DATA_FOUND' a tim k zapisu do tabulky audit_log 
SQL>  EXECUTE zvys_vyplatu(1,1000); 

PL/SQL procedure successfully completed.

SQL>  SELECT * FROM lide; 

no rows selected

SQL>  SELECT * FROM audit_log;

       UCO TEXT
---------- --------------------------------------------------
         1 UCO neexistuje

-- zvyseni vyplaty existujiciho zamestnance (probehne v poradku)
SQL>  INSERT INTO lide (uco, sal) VALUES (1, 7550); 

1 row created.

SQL>  EXECUTE zvys_vyplatu(1,1000); 

PL/SQL procedure successfully completed.

SQL>  SELECT * FROM lide; 

       UCO        SAL
---------- ----------
         1       8550

SQL>  SELECT * FROM audit_log;

       UCO TEXT
---------- --------------------------------------------------
         1 UCO neexistuje
         1 Vyplata zvysena na 8550

-- zmena platu na NULL, aby doslo k vyvolani nasi interni vyjimky 'salary_missing' 
SQL>  UPDATE lide SET sal=NULL where uco=1; 

1 row updated.

SQL>  EXECUTE zvys_vyplatu(1,1000); 

PL/SQL procedure successfully completed.

SQL>  SELECT * FROM lide; 

       UCO        SAL
---------- ----------
         1

SQL>  SELECT * FROM audit_log;

       UCO TEXT
---------- --------------------------------------------------
         1 UCO neexistuje
         1 Vyplata zvysena na 8550
         1 Vyplata je NULL


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

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


Function created.

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

STAV_UCTU(101)
--------------
       10234.5

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

Function created.

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

UROK(10,7550,5)
---------------
     12298.1544