Untitled

CREATE OR REPLACE
FUNCTION ZK_HR_BONUS(P_ID_ZAMESTNANCE NUMBER) RETURN NUMBER AS
provize NUMBER;
bonus NUMBER;
mzda NUMBER;
roky NUMBER;
region VARCHAR(500);
jeTam NUMBER;

CURSOR k1 IS SELECT ROUND((sysdate-datum_nastup)/365.25,0) roky, mzda, provize_procent, region_nazev FROM a_hr.zamestnanci
join a_hr.oddeleni ON a_hr.oddeleni.oddeleni_id = a_hr.zamestnanci.oddeleni_id
join a_hr.adresy ON a_hr.adresy.adresa_id = a_hr.oddeleni.adresa_id
join a_hr.zeme ON a_hr.zeme.zeme_id = a_hr.adresy.zeme_id
join a_hr.regiony ON a_hr.regiony.region_id = a_hr.zeme.region_id
WHERE zamestnanec_id = P_ID_ZAMESTNANCE;

CURSOR k2 IS SELECT COUNT(*) FROM A_HR.zamestnanci WHERE zamestnanec_id = P_ID_ZAMESTNANCE;

BEGIN

OPEN k2;
FETCH k2 INTO jeTam;

IF(jeTam = 0) THEN
raise_application_error(-20001,'ID osoby neni v pozadovanem rozmezi');
ELSE

  OPEN k1;
      FETCH k1 INTO roky, mzda, provize, region;
      IF(roky > 13 OR provize != NULL OR region = 'Europe') THEN
        RETURN NULL;
      END IF;

      RETURN ROUND((mzda/10) * roky);
  CLOSE k1;

END IF;

END ZK_HR_BONUS;


SELECT ZK_HR_BONUS(100) FROM dual;