DECLARE
  v_promenna NUMBER(3);
BEGIN
  v_promenna := 77;
END; 
/ 


DECLARE 
 v_promenna1 NUMBER(3) NOT NULL DEFAULT 88;
BEGIN
  DBMS_OUTPUT.PUT_LINE('v_promenna1 = '|| v_promenna1);
END; 
/ 



SET SERVEROUT ON SIZE 10000

DECLARE 
 v_promenna NUMBER(3) := 77;
BEGIN
  DBMS_OUTPUT.PUT_LINE('v_promnn = '|| v_promenna);
END; 
/ 



DECLARE
  v_Text VARCHAR2 (50);
BEGIN
  v_Text := 'Jede, jede mainka, kou se j z komnka';
  DBMS_OUTPUT.PUT_LINE('v_Text = '|| v_Text);
END;


DECLARE
  v_DatumCas DATE;
BEGIN
  v_DatumCas := TO_DATE('25.12.2006 13:30:22', 'dd.mm.YYYY, HH24:MI:SS');
  DBMS_OUTPUT.PUT_LINE('v_DatumCas = '|| v_DatumCas);
END;
/ 



DECLARE
  v_Text VARCHAR2 (50);
  v_Text2 v_Text%TYPE;
  v_Job2 emp.job%TYPE;
BEGIN
  v_Text2 := 'Jede, jede mainka, kou se j z komnka';
  v_Job2  := 'PRESIDENT';
END;
/



DECLARE
  v_vnejsi NUMBER(3) := 77;
BEGIN
   -- vnoen blok
      DECLARE
        v_vnitrni NUMBER(3) := 88;
      BEGIN
        DBMS_OUTPUT.PUT_LINE('v_vnj = '|| v_vnejsi);
        DBMS_OUTPUT.PUT_LINE('v_ vnitn = '|| v_vnitrni);
        v_vnejsi := v_vnitrni; 
        DBMS_OUTPUT.PUT_LINE('v_vnj (po zmn) = '|| v_vnejsi);
      END; 
END; 
/




DECLARE
--promnn deklarovan ve vnjm bloku 
  v_vnejsi NUMBER(3) := 77;  
BEGIN
    --zatek vnoenho bloku
      DECLARE
      --promnn deklarovan ve vnoenm bloku
        v_vnitrni NUMBER(3) := 88;
      BEGIN
        DBMS_OUTPUT.PUT_LINE('v_vnj = '|| v_vnejsi);
        DBMS_OUTPUT.PUT_LINE('v_ vnitn = '|| v_vnitrni);
      --piazen hodnoty vnj promnn do vnitn promnn 
        v_vnejsi := v_vnitrni; 
        DBMS_OUTPUT.PUT_LINE('v_vnj (po zmn) = '|| v_vnejsi);
      END; 
      --konec vnoenho bloku
END;
/




SET SERVEROUT ON SIZE 10000
DECLARE
  v_Jmeno emp.ename%TYPE;
  v_Job emp.job%TYPE;
BEGIN
  SELECT ename, job INTO v_Jmeno, v_Job FROM emp WHERE empno = 7934;
--vpis hodnot promnnch 
  DBMS_OUTPUT.PUT_LINE('v_Jmno = '|| v_Jmeno);
  DBMS_OUTPUT.PUT_LINE('v_Job = '|| v_Job);
END;
/





SET SERVEROUT ON SIZE 10000
DECLARE
  v_Jmeno emp.ename%TYPE;
  v_Job emp.job%TYPE;
BEGIN
  SELECT ename, job INTO v_Jmeno, v_Job FROM emp WHERE empno = 7934;
-- vpis hodnot promnnch
  DBMS_OUTPUT.PUT_LINE('v_Jmno = '|| v_Jmeno);
  DBMS_OUTPUT.PUT_LINE('v_Job = '|| v_Job);
EXCEPTION
--oeten vjimky NO_DATA_FOUND
  WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Data jsme nenali');
--oeten vjimky TOO_MANY_ROWS
  WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('Pli mnoho dat');
END;
/



SET SERVEROUT ON SIZE 10000
DECLARE
  v_vek NUMBER(3) := 17;
  BEGIN
    IF v_vek < 18 THEN
    DBMS_OUTPUT.PUT_LINE('Zamstnance nen mon zamstnat pro nzk vk.');    
  END IF;
END; 
/



SET SERVEROUT ON SIZE 10000
DECLARE
  v_cisloA NUMBER(3) := 22;
  v_cisloB NUMBER(3) := 33;
  v_maximum NUMBER(3);
  BEGIN
    IF v_cisloA > v_cisloB THEN
      v_maximum := v_cisloA;
    ELSE
      v_maximum := v_cisloB;
    DBMS_OUTPUT.PUT_LINE('Maximum = '|| v_maximum);    
  END IF;
END; 
/



SET SERVEROUT ON SIZE 10000
DECLARE
  v_cisloA NUMBER(3) := 22;
  v_cisloB NUMBER(3) := 33;
  v_cisloC NUMBER(3) := 11;
  v_minimum NUMBER(3);
  BEGIN
    IF v_cisloA < v_cisloB AND v_cisloA < v_cisloC  
      THEN v_minimum := v_cisloA; END IF;
    IF v_cisloB < v_cisloA AND v_cisloB < v_cisloC  
      THEN v_minimum := v_cisloB; END IF;
    IF v_cisloC < v_cisloA AND v_cisloC < v_cisloB  
      THEN v_minimum := v_cisloC; END IF;
    DBMS_OUTPUT.PUT_LINE('Minimum = '|| v_minimum);    

END;
/




SET SERVEROUT ON SIZE 10000
DECLARE
  v_znamka NUMBER(1) := 2;
BEGIN
  IF v_znamka = 1 THEN
     dbms_output.put_line('Vborn');
  ELSIF v_znamka = 2 THEN
     dbms_output.put_line('Chvalitebn');
  ELSIF v_znamka = 3 THEN
     dbms_output.put_line('Dobr');
  ELSIF v_znamka = 4 THEN
     dbms_output. put_line('Dostaten');
  ELSIF v_znamka = 5 THEN
     dbms_output.put_line('Nedostaten');
  ELSE
     dbms_output.put_line('Znmka mimo klasifikan stupnici');
  END IF;
END; 
/






SET SERVEROUT ON SIZE 10000
DECLARE
  v_znamka NUMBER(1) := 1;
BEGIN
  CASE v_znamka
    WHEN 1 THEN dbms_output.put_line('Vborn');
    WHEN 2 THEN dbms_output.put_line('Chvalitebn');
    WHEN 3 THEN dbms_output.put_line('Dobr');
    WHEN 4 THEN dbms_output.put_line('Dostaten');
    WHEN 5 THEN dbms_output.put_line('Nedostaten ');
    ELSE dbms_output.put_line('Znmka mimo klasifikan stupnici');
  END CASE;
END; 
/



SET SERVEROUT ON SIZE 10000
DECLARE
  v_body NUMBER(3) := 44;
BEGIN
  CASE 
    WHEN v_body BETWEEN 91 AND 100 THEN dbms_output.put_line('Vborn');
    WHEN v_body BETWEEN 71 AND 90 THEN dbms_output.put_line('Chvalitebn');
    WHEN v_body BETWEEN 51 AND 70 THEN dbms_output.put_line('Dobr');
    WHEN v_body BETWEEN 31 AND 50 THEN dbms_output.put_line('Dostaten');
    WHEN v_body BETWEEN 0 AND 30 THEN dbms_output.put_line('Nedostaten');
    ELSE dbms_output.put_line('Znmka mimo klasifikan stupnici');
  END CASE;
END; 
/








SET SERVEROUT ON SIZE 10000
DECLARE
  v_pocet NUMBER(3) := 0;
BEGIN
  LOOP 
    v_pocet := v_pocet +1;
    IF v_pocet >= 100 THEN
       EXIT;
    END IF;      
  END LOOP;
  dbms_output.put_line('v_poet = '||v_pocet);
END; 
/





SET SERVEROUT ON SIZE 10000
DECLARE
  v_citac NUMBER(7);
BEGIN
  FOR v_pocitadlo IN 1..3 LOOP
      v_citac := 5* v_pocitadlo;
      dbms_output.put_line('v_ta = '||v_citac);
  END LOOP;
END; 
/



SET SERVEROUT ON SIZE 10000
DECLARE
  v_pocet NUMBER(7);
BEGIN
  SELECT COUNT(*) INTO v_pocet FROM emp; 
  FOR v_citac IN 1..v_pocet LOOP
      dbms_output.put_line('v_ta = '||v_citac);
  END LOOP;
END; 
/




SET SERVEROUT ON SIZE 10000
DECLARE
 CURSOR k1 IS
 SELECT empno, ename, job FROM emp;
BEGIN
  OPEN k1;
    dbms_output.put_line('ROWCOUNT = '||k1%ROWCOUNT);
  CLOSE k1;
END;
/





SET SERVEROUT ON SIZE 10000
DECLARE
 v_empno  emp.empno%TYPE;
 v_ename  emp.ename%TYPE;
 v_job    emp.job%TYPE;
 
 CURSOR k1 IS
 SELECT empno, ename, job FROM emp;
BEGIN
  OPEN k1;
    FETCH k1 INTO v_empno, v_ename, v_job;
    dbms_output.put_line('ROWCOUNT = '||k1%ROWCOUNT);
  CLOSE k1;
END;
/



SET SERVEROUT ON SIZE 10000
DECLARE
 v_empno  emp.empno%TYPE;
 v_ename  emp.ename%TYPE;
 v_job    emp.job%TYPE;
 
 CURSOR k1 IS
 SELECT empno, ename, job FROM emp;
BEGIN
  OPEN k1;
    LOOP
      FETCH k1 INTO v_empno, v_ename, v_job;
      dbms_output.put_line(v_empno||', '||v_ename||', '||v_job);
      EXIT WHEN k1%NOTFOUND;
    END LOOP; 
  CLOSE k1;
END;
/




SET SERVEROUT ON SIZE 10000
DECLARE
 rec_emp emp%ROWTYPE;
 
 CURSOR k1 IS
 SELECT empno, ename, job FROM emp;
BEGIN
  FOR rec_emp IN k1 LOOP
   dbms_output.put_line(rec_emp.empno||', '|| rec_emp.ename||', '|| rec_emp.job);
  END LOOP; 
END;


SET SERVEROUT ON SIZE 10000
DECLARE
rec_emp emp%ROWTYPE;
BEGIN
  FOR rec_emp IN (SELECT empno, ename, job FROM emp) LOOP
   dbms_output.put_line(rec_emp.empno||', '|| rec_emp.ename||', '|| rec_emp.job);
  END LOOP; 
END;
/




SET SERVEROUT ON SIZE 10000
DECLARE
 rec_emp emp%ROWTYPE;
 CURSOR k1 (v_job VARCHAR2) IS
 SELECT empno, ename, job FROM emp WHERE job = v_job;
BEGIN
  FOR rec_emp IN k1('MANAGER') LOOP
   dbms_output.put_line(rec_emp.ename||', '|| rec_emp.job);
  END LOOP; 
END;
/






SET SERVEROUT ON SIZE 10000
DECLARE
 rec_emp emp%ROWTYPE;
 CURSOR k1 (v_job VARCHAR2) IS
 SELECT empno, ename, job FROM emp WHERE job = v_job;
BEGIN
   dbms_output.put_line('Manaei');
   FOR rec_emp IN k1('MANAGER') LOOP
     dbms_output.put_line(rec_emp.ename||', '|| rec_emp.job);
  END LOOP; 

   dbms_output.put_line('ednci');
   FOR rec_emp IN k1('CLERK') LOOP
     dbms_output.put_line(rec_emp.ename||', '|| rec_emp.job);
  END LOOP; 
END;
/



SET SERVEROUT ON SIZE 10000
DECLARE
 rec_vanoce vanoce%ROWTYPE;

BEGIN
  FOR rec_vanoce IN (SELECT * FROM vanoce) LOOP
   dbms_output.put_line(rec_vanoce.jmeno||', '|| rec_vanoce.pocet_deti||
   ', '|| 3000/rec_vanoce.pocet_deti);
  END LOOP; 
END;
/




SET SERVEROUT ON SIZE 10000
DECLARE
 rec_vanoce vanoce%ROWTYPE;
 v_jmeno vanoce.jmeno%TYPE;

BEGIN
  FOR rec_vanoce IN (SELECT * FROM vanoce) LOOP
   v_jmeno := rec_vanoce.jmeno; 
   dbms_output.put_line(rec_vanoce.jmeno||', '|| rec_vanoce.pocet_deti||
                        ', '|| 3000/rec_vanoce.pocet_deti);
  END LOOP; 
EXCEPTION
WHEN ZERO_DIVIDE THEN 
  dbms_output.put_line(v_jmeno||', 1, 3000');
END;
/




SET SERVEROUT ON SIZE 10000
DECLARE
 rec_vanoce vanoce%ROWTYPE;

BEGIN
  FOR rec_vanoce IN (SELECT * FROM vanoce) LOOP
   IF rec_vanoce.pocet_deti = 0 THEN
     dbms_output.put_line(rec_vanoce.jmeno||', 1, 3000 ');
   ELSE
   dbms_output.put_line(rec_vanoce.jmeno||', '|| rec_vanoce.pocet_deti||
                        ', '|| 3000/rec_vanoce.pocet_deti);
   END IF;
  END LOOP; 
EXCEPTION
WHEN ZERO_DIVIDE THEN 
  dbms_output.put_line('Chyba  vyskytlo se dlen nulou');
END;
/




SET SERVEROUT ON SIZE 10000
DECLARE
  v_vysledek NUMBER(9,2);
BEGIN
  v_vysledek := 5/0;
EXCEPTION
  WHEN OTHERS THEN 
  dbms_output.put_line(' !!! Chyba !!!');
  dbms_output.put_line('Kd chyby: '|| SQLCODE );
  dbms_output.put_line('Popis chyby: '|| SQLERRM );
END;
/


SET SERVEROUT ON SIZE 100000
BEGIN
  FOR v_citac IN -20..100 LOOP
    IF (SQLERRM(v_citac) NOT LIKE '%non-ORACLE exception%') AND 
       (SQLERRM(v_citac) NOT LIKE '%Message%not found%') THEN
       dbms_output.put_line('SQLERRM('||v_citac||'): '|| SQLERRM(v_citac));
    END IF;
  END LOOP;
END;
/



SET SERVEROUT ON SIZE 10000
DECLARE
 PRILIS_MNOHO_TRPASLIKU EXCEPTION;  --deklarace vjimky
 v_pocet_trpasliku NUMBER(2); 
 
BEGIN
   v_pocet_trpasliku := 7;
   IF v_pocet_trpasliku > 7  THEN
     RAISE PRILIS_MNOHO_TRPASLIKU;
   END IF;

EXCEPTION WHEN PRILIS_MNOHO_TRPASLIKU THEN 
  dbms_output.put_line('Trpaslk me bt maximln sedm');
END;
/



SET SERVEROUT ON SIZE 10000
DECLARE
 PRILIS_MNOHO_TRPASLIKU EXCEPTION;  --deklarace vjimky
 v_pocet_trpasliku NUMBER(2); 
 
BEGIN
   v_pocet_trpasliku := 9;
   IF v_pocet_trpasliku > 7  THEN
     RAISE PRILIS_MNOHO_TRPASLIKU;
   END IF;

EXCEPTION WHEN PRILIS_MNOHO_TRPASLIKU THEN 
  dbms_output.put_line('Trpaslk me bt maximln sedm');
  dbms_output.put_line('Kd chyby: '|| SQLCODE );
  dbms_output.put_line('Popis chyby: '|| SQLERRM );
END;
/



SET SERVEROUT ON SIZE 10000
DECLARE
 PRILIS_MNOHO_TRPASLIKU EXCEPTION;  --deklarace vlastn vjimky
 v_pocet_trpasliku NUMBER(2); 
 v_cislo_chyby protokol_chyb.cislo_chyby%TYPE;
 
BEGIN
   v_pocet_trpasliku := 9;  --pekroen potu trpaslk
   - v_pocet_trpasliku := 9/0;  --dlen nulou
   IF v_pocet_trpasliku > 7  THEN
     RAISE PRILIS_MNOHO_TRPASLIKU;
   END IF;


EXCEPTION 
  WHEN PRILIS_MNOHO_TRPASLIKU THEN 
    dbms_output.put_line('Trpaslk me bt maximln sedm');
    v_cislo_chyby := SQLCODE;
    INSERT INTO protokol_chyb VALUES (SYSDATE, v_cislo_chyby, 'Mnoho trpaslk');

  WHEN OTHERS THEN 
    dbms_output.put_line('!!Chyba!!');
    v_cislo_chyby := SQLCODE;
    INSERT INTO protokol_chyb VALUES (SYSDATE, v_cislo_chyby, '-');
END;
/



SET SERVEROUT ON SIZE 10000
DECLARE
rec_protokol protokol_chyb %ROWTYPE;
BEGIN
  FOR rec_protokol IN (SELECT * FROM protokol_chyb) LOOP
   dbms_output.put_line(rec_protokol.kdy||'> '|| SQLERRM(rec_protokol.cislo_chyby));
  END LOOP; 
END;
/



CREATE OR REPLACE PROCEDURE mzdy_zvyseni (procento IN NUMBER) AS 
   BEGIN
      UPDATE pracovnici SET mzda = mzda * (100 + procento)/100; 
   END;


CREATE OR REPLACE FUNCTION f_delilka (delenec IN NUMBER, delitel IN NUMBER) 
  RETURN number
  AS 
  vysledek NUMBER;
  BEGIN
    vysledek := delenec/delitel;
    RETURN vysledek;    
  EXCEPTION
    WHEN ZERO_DIVIDE THEN 
    dbms_output.put_line('CHYBA  dlen nulou');
    RETURN NULL; 
  END;




SET SERVEROUT ON SIZE 10000
BEGIN
  dbms_output.put_line('Vsledek dlen> '|| f_delilka(12,4));
END;   



CREATE OR REPLACE PACKAGE BODY cvicny_balicek AS
  PROCEDURE mzdy_zvyseni (procento IN NUMBER) AS 
   BEGIN
      UPDATE pracovnici SET mzda = mzda * (100 + procento)/100; 
   END;

  FUNCTION f_delilka (delenec IN NUMBER, delitel IN NUMBER) 
    RETURN number AS vysledek NUMBER;
  BEGIN
    vysledek := delenec/delitel;
    RETURN vysledek;    
  EXCEPTION
    WHEN ZERO_DIVIDE THEN 
    dbms_output.put_line('CHYBA  dlen nulou');
    RETURN NULL; 
  END;
END cvicny_balicek ;



CREATE OR REPLACE TRIGGER potvrzeni 
AFTER INSERT ON pracovnici
FOR EACH ROW
    BEGIN
        dbms_output.put_line('Zznam byl spn pidn ');
    END;


