CREATE OR REPLACE FUNCTION kdo_to_je(p_kraj VARCHAR2, p_otazka NUMBER, p_odpoved VARCHAR2) RETURN VARCHAR2
AS
  v_id osoby2.osoby_id%TYPE;
  v_prijem osoby2.prijem%TYPE;
BEGIN
   SELECT osoby_id, prijem INTO v_id, v_prijem 
    FROM osoby2 JOIN kraje USING(kraje_id) JOIN odpovedi USING(osoby_id) JOIN moznosti USING(moznosti_id) 
    WHERE kraje.nazev=p_kraj AND otazky_id=p_otazka AND text=p_odpoved;
    RETURN  v_id || ', ' || v_prijem;
EXCEPTION 
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;
  WHEN TOO_MANY_ROWS THEN
    RETURN 'příliš mnoho řádků';
END;

CREATE OR REPLACE PROCEDURE smaz_osobu (p_prijem IN NUMBER)
IS
  e_spatny_prijem EXCEPTION;
BEGIN
  DELETE osoby WHERE prijem=p_prijem;
  IF SQL%NOTFOUND THEN 
    RAISE e_spatny_prijem;
  ELSE
    dbms_output.put_line('smazano celkem: ' || SQL%ROWCOUNT || ' radku.');
  END IF;
EXCEPTION 
  WHEN  e_spatny_prijem THEN
    dbms_output.put_line('Zadaný příjem neexistuje');  
END;



CREATE OR REPLACE PROCEDURE uprav_kraj (p_id IN NUMBER, p_nazev VARCHAR2)
IS
BEGIN
  UPDATE kraje SET nazev=p_nazev WHERE kraje_id=p_id;
  IF SQL%NOTFOUND THEN 
    RAISE_APPLICATION_ERROR(-20917,'Pokus o úpravu neexistujícího kraje.') ;
  ELSE
    dbms_output.put_line('Upraveno.');
  END IF;
END;


CREATE OR REPLACE PROCEDURE uprav_kraj (p_id IN NUMBER, p_nazev VARCHAR2)
IS
  e_spatny_kraj EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_spatny_kraj, -20917);
BEGIN
  UPDATE kraje SET nazev=p_nazev WHERE kraje_id=p_id;
  IF SQL%NOTFOUND THEN 
    RAISE_APPLICATION_ERROR(-20917,'Pokus o úpravu neexistujícího kraje.') ;
  ELSE
    dbms_output.put_line('Upraveno.');
  END IF;
  EXCEPTION 
  WHEN  e_spatny_kraj THEN
    dbms_output.put_line('Kraj neexistuje');  
END;



CREATE OR REPLACE TRIGGER BU_osoby_prijem BEFORE UPDATE OF prijem ON osoby
FOR EACH ROW
BEGIN
IF (:NEW.prijem > :OLD.prijem * 1.2) OR (:NEW.prijem < :OLD.prijem * 0.8) THEN
  RAISE_APPLICATION_ERROR(-20179,'Změna příjmu o více než 20 %.') ;
END IF;
END; 



CREATE OR REPLACE TRIGGER prijmy BEFORE UPDATE OF prijem ON osoby
FOR EACH ROW
BEGIN
IF (:NEW.prijem > :OLD.prijem * 1.2) THEN
   :NEW.prijem:= :OLD.prijem * 1.2;
ELSIF (:NEW.prijem < :OLD.prijem * 0.8) THEN
   :NEW.prijem:= :OLD.prijem * 0.8;  
END IF;
END;




CREATE OR REPLACE VIEW moje_osoby (osoba, kraj, povolani, pohlavi, vek, prijem) AS 
SELECT osoby_id, kraje.nazev, povolani.nazev, pohlavi, vek, prijem 
FROM osoby NATURAL JOIN kraje JOIN povolani USING (povolani_id) 

CREATE OR REPLACE TRIGGER II_moje_osoby INSTEAD OF INSERT ON moje_osoby
FOR EACH ROW
DECLARE
  v_kraje_id kraje.kraje_id%TYPE;
  v_povolani_id povolani.povolani_id%TYPE;
  v_poc PLS_INTEGER;
BEGIN
  SELECT count(*) INTO v_poc FROM kraje WHERE nazev= :NEW.kraj;
  IF v_poc=0 THEN
     RAISE_APPLICATION_ERROR(-20309,'Neexistující kraj.') ;
  ELSE
    SELECT kraje_id INTO v_kraje_id FROM kraje WHERE nazev= :NEW.kraj;
  END IF;
  SELECT count(*) INTO v_poc FROM povolani WHERE nazev= :NEW.povolani;
  IF v_poc=0 THEN
     SELECT max(povolani_id) INTO v_povolani_id FROM povolani;
     v_povolani_id:=v_povolani_id+1;
     INSERT INTO povolani VALUES (v_povolani_id,:NEW.povolani);
  ELSE
    SELECT povolani_id INTO v_povolani_id FROM povolani WHERE nazev= :NEW.povolani;
  END IF; 
  INSERT INTO osoby (osoby_id, kraje_id, povolani_id, pohlavi, vek, prijem) 
     VALUES (:NEW.osoba, v_kraje_id, v_povolani_id, :NEW.pohlavi, :NEW.vek, :NEW.prijem);
END;




CREATE OR REPLACE PROCEDURE uprav_osoby (p_id NUMBER, p_sloupec VARCHAR2, p_hodnota VARCHAR2)
IS
  v_poc PLS_INTEGER;
  v_prikaz VARCHAR2(500);
BEGIN
  SELECT count(*) INTO v_poc FROM all_tab_columns WHERE upper(column_name)= upper(p_sloupec) AND upper(table_name)='OSOBY';
  IF v_poc=0 THEN
    DBMS_OUTPUT.PUT_LINE('takový sloupec neexistuje');
  ELSE
    v_prikaz := 'UPDATE osoby SET '|| p_sloupec|| '= '''|| p_hodnota || ''' WHERE osoby_id='||p_id;
    EXECUTE IMMEDIATE v_prikaz;
  END IF;
  EXCEPTION 
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLCODE|| ' ' || SQLERRM(SQLCODE));
END;


CREATE OR REPLACE PROCEDURE uprav_osoby (p_rsloupec VARCHAR2, p_rhodnota VARCHAR2, p_nsloupec VARCHAR2, p_nhodnota VARCHAR2)
IS
  v_poc PLS_INTEGER;
  v_prikaz VARCHAR2(500);
BEGIN
  SELECT count(*) INTO v_poc FROM all_tab_columns WHERE upper(column_name)= upper(p_rsloupec) AND upper(table_name)='OSOBY';
  IF v_poc=0 THEN
    DBMS_OUTPUT.PUT_LINE('restrikcni sloupec '|| p_rsloupec || ' neexistuje');
  ELSE
    SELECT count(*) INTO v_poc FROM all_tab_columns WHERE upper(column_name)= upper(p_nsloupec) AND upper(table_name)='OSOBY';
    IF v_poc=0 THEN
      DBMS_OUTPUT.PUT_LINE('upravovany sloupec '|| p_nsloupec || ' neexistuje');
    ELSE
      v_prikaz := 'UPDATE osoby SET '|| p_nsloupec|| '= '''|| p_nhodnota || ''' WHERE '|| p_rsloupec ||'='|| '''' || p_rhodnota || '''';
      EXECUTE IMMEDIATE v_prikaz;
      DBMS_OUTPUT.PUT_LINE('celkem bylo upraveno '|| SQL%ROWCOUNT || ' zaznamu');
    END IF;
  END IF;
  EXCEPTION 
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLCODE|| ' ' || SQLERRM(SQLCODE));
END;

BEGIN
uprav_osoby ('vek', '25', 'vek', '260');
END;