CV4 DB2

CREATE TABLE cv4_trpaslici_plat(id_trpaslika NUMBER(2,0),plat NUMBER(8,2));
/

CREATE OR REPLACE
PROCEDURE cv4_NASTAV_PLATY AS

  CURSOR c_trpaslici IS
    SELECT
      id_trpaslika,
      vyska,
      SUM(skutecnost)
    FROM
      A_O_SNEHURCE.trpaslici
      JOIN A_O_SNEHURCE.tezby USING(id_trpaslika)
    WHERE
      den BETWEEN TO_DATE('5.2.2012','DD.MM.YYYY') AND TO_DATE('25.2.2012','DD.MM.YYYY')
    GROUP BY
      id_trpaslika, vyska;

  i_id_trpaslika A_O_SNEHURCE.trpaslici.id_trpaslika%TYPE;
  i_vyska A_O_SNEHURCE.trpaslici.vyska%TYPE;
  i_vytezeno NUMBER(6,0);

  i_vydaje NUMBER(8,2) DEFAULT 0;

BEGIN

  DELETE FROM cv4_trpaslici_plat;

  OPEN c_trpaslici;
  LOOP
    FETCH c_trpaslici INTO i_id_trpaslika, i_vyska, i_vytezeno;
    EXIT WHEN c_trpaslici%NOTFOUND;

    IF i_vyska < 110 THEN
      i_vydaje := i_vydaje + (i_vytezeno*1.25) + 30;
      INSERT INTO cv4_trpaslici_plat(id_trpaslika,plat) VALUES (i_id_trpaslika, (i_vytezeno*1.25) + 30);
    ELSE
      i_vydaje := i_vydaje + (i_vytezeno*1.25);
      INSERT INTO cv4_trpaslici_plat(id_trpaslika,plat) VALUES (i_id_trpaslika, (i_vytezeno*1.25));
    END IF;

  END LOOP;
  CLOSE c_trpaslici;

  COMMIT;

  DBMS_OUTPUT.put_line('Výdaje: ' || i_vydaje);

END cv4_NASTAV_PLATY;
/

--příklad III:
CREATE TABLE cv4_trpaslici_vlastnosti(jmeno VARCHAR2(20), vlastnosti VARCHAR2(100));
/

CREATE OR REPLACE
PROCEDURE cv4_TRPASLIK_VLASTNOSTI AS

  i_vlastnosti VARCHAR2(100);

BEGIN

  DELETE FROM cv4_trpaslici_vlastnosti;

  FOR c_trp IN (SELECT id_trpaslika, jmeno FROM A_O_SNEHURCE.trpaslici)
  LOOP

    i_vlastnosti := NULL;

    FOR c_vlastnosti IN (SELECT vlastnost FROM A_O_SNEHURCE.POZNATKY JOIN A_O_SNEHURCE.VLASTNOSTI USING(ID_VLASTNOSTI) WHERE ID_TRPASLIKA = c_trp.id_trpaslika)
    LOOP
      i_vlastnosti := i_vlastnosti || c_vlastnosti.vlastnost || ';';
    END LOOP;

    INSERT INTO cv4_trpaslici_vlastnosti(jmeno,vlastnosti) VALUES (c_trp.jmeno,i_vlastnosti);

  END LOOP;

  COMMIT;

END cv4_TRPASLIK_VLASTNOSTI;
/

CREATE OR REPLACE PROCEDURE cv4_TRPASLICI_ZEBRICEK
(
  OD IN VARCHAR2
, DO IN VARCHAR2
) AS
  i NUMBER := 0;
BEGIN

  DBMS_OUTPUT.put_line('Výpis za období ' || od || ' - ' || DO || ':');

  FOR X IN (
    SELECT
      jmeno, SUM(skutecnost) sum_skut
    FROM
      A_O_SNEHURCE.trpaslici
      JOIN A_O_SNEHURCE.tezby USING(id_trpaslika)
    WHERE
      den >= TO_DATE(od,'DD.MM.YYYY')
      AND den <= TO_DATE(DO,'DD.MM.YYYY')
    GROUP BY
      jmeno
    ORDER BY 2 DESC
  )
  LOOP
    i := i + 1;

    DBMS_OUTPUT.put_line(i || '. ' || x.jmeno);

    -- vypisují se jen první tři
    EXIT WHEN i >= 3;
  END LOOP;
END cv4_TRPASLICI_ZEBRICEK;
/

CREATE OR REPLACE
PROCEDURE cv4_delete_script(p_schema VARCHAR2) AS

  CURSOR c_script IS
    SELECT
      'drop table '||owner||'.'||table_name||' cascade constraints;'
    FROM
      all_tables
    WHERE
      UPPER(owner) = UPPER(p_schema)
    ORDER BY
      table_name;

  i_script_row VARCHAR2(500);

BEGIN

  OPEN c_script;
  LOOP
    FETCH c_script INTO i_script_row;
    EXIT WHEN c_script%NOTFOUND;

    DBMS_OUTPUT.put_line(i_script_row);

  END LOOP;
  CLOSE c_script;

END cv4_delete_script;
/

CREATE OR REPLACE
FUNCTION cv4_POMER_PLAT
(
  P_MZDA IN NUMBER
, P_ODDELENI_ID IN NUMBER DEFAULT NULL
) RETURN VARCHAR2
AS
  i_vysledek NUMBER;
BEGIN

  SELECT
    p_mzda/(SUM(MZDA)/COUNT(*)) * 100
  INTO
    i_vysledek
  FROM
    A_HR.zamestnanci
  WHERE
    ODDELENI_ID = NVL(P_ODDELENI_ID,ODDELENI_ID);

  RETURN ROUND(i_vysledek,2) || ' %';

EXCEPTION
    WHEN OTHERS THEN
      RETURN 'Chyba výpočtu.';
END cv4_POMER_PLAT;
/


-- cv4_2:

CREATE OR REPLACE
FUNCTION cv4_vekova_skupina(p_datum DATE) RETURN VARCHAR2
AS

  i_vek NUMBER;

BEGIN

  i_vek := (SYSDATE - p_datum) / 365;

  CASE
    WHEN i_vek >= 0 AND i_vek < 18 THEN
      RETURN 'Dítě';
    WHEN i_vek >= 18 AND i_vek < 65 THEN
      RETURN 'Dospělý';
    WHEN i_vek >= 65 THEN
      RETURN 'Důchodce';
    ELSE
      RETURN 'Neznámá';
  END CASE;


END cv4_vekova_skupina;
/