BD - REPÀS: Procedures

-- 1)

CREATE TYPE empl AS
( nom char(30),
  sou integer);

CREATE OR REPLACE FUNCTION empl_departament(numdept integer) RETURNS SETOF empl AS $$
DECLARE
  e empl;
  missatge VARCHAR(50);
  quants INTEGER;
  count INTEGER;

BEGIN

  count = 0;

  FOR e IN SELECT nom_empl, sou FROM empleats em WHERE em.num_dpt = numdept AND em.ciutat_empl = 'SITGES'
     LOOP
        count = count + 1;
        RETURN NEXT e;
     END LOOP;

  IF count = 0 THEN
        SELECT texte INTO missatge FROM missatgesExcepcions WHERE num=1;
        RAISE EXCEPTION '%', missatge;
  END IF;

  RETURN;

  EXCEPTION
     WHEN raise_exception THEN
    RAISE EXCEPTION '%', missatge;
     WHEN OTHERS THEN
        SELECT texte INTO missatge FROM missatgesExcepcions WHERE num=2;
        RAISE EXCEPTION '%', missatge;

END;
$$LANGUAGE plpgsql;

-- 2)

CREATE OR REPLACE FUNCTION eliminar_dept(numdept INTEGER) RETURNS void AS $$
DECLARE
   missatge VARCHAR(50);

BEGIN

     IF EXISTS (SELECT * FROM empleats em WHERE em.num_dpt = numdept) THEN
      SELECT texte INTO missatge FROM missatgesExcepcions WHERE num=1;
      RAISE EXCEPTION '%',missatge;
     END IF;

     DELETE FROM departaments WHERE num_dpt = numdept;

     IF NOT FOUND THEN
       SELECT texte INTO missatge FROM missatgesExcepcions WHERE num=2;
           RAISE EXCEPTION '%',missatge;
     END IF;

EXCEPTION
   WHEN raise_exception THEN
           RAISE EXCEPTION '%',SQLERRM;
   WHEN OTHERS THEN
           SELECT texte INTO missatge FROM missatgesExcepcions WHERE num=3;
           RAISE EXCEPTION '%',missatge;

END;
$$LANGUAGE plpgsql;

-- 3)

CREATE TYPE dades_treb AS(
    t_dni CHAR(8),
    t_nom_treb CHAR(30),
    t_sou_base REAL,
    t_plus_sou REAL,
    t_matricula CHAR(10)
);

CREATE OR REPLACE FUNCTION llistat_treb(dni_min CHAR(8), dni_max CHAR(8)) RETURNS SETOF dades_treb AS $$
DECLARE
    missatge VARCHAR(50);
    wd dades_treb;
    t treballadors;
    l CHAR(10);
BEGIN
    IF NOT EXISTS (SELECT * FROM treballadors treb WHERE treb.dni >= dni_min AND treb.dni <= dni_max) THEN
    SELECT texte INTO missatge FROM missatgesExcepcions WHERE num = 1;
    RAISE EXCEPTION '%', missatge;
    END IF;
    FOR t IN SELECT * FROM treballadors treb WHERE treb.dni >= dni_min AND treb.dni <= dni_max
    LOOP
        SELECT t.dni, t.nom, t.sou_base, t.plus INTO wd.t_dni, wd.t_nom_treb, wd.t_sou_base, wd.t_plus_sou;
        SELECT NULL INTO wd.t_matricula;
        IF 5 <= (SELECT COUNT(*) FROM lloguers_actius la WHERE wd.t_dni = la.dni) THEN
            FOR l IN SELECT matricula FROM lloguers_actius la WHERE wd.t_dni = la.dni
            LOOP
                SELECT l INTO wd.t_matricula;
                RETURN NEXT wd;
            END LOOP;

        ELSE RETURN NEXT wd;

        END IF;

    END LOOP;

    EXCEPTION
    WHEN raise_exception THEN
        RAISE EXCEPTION '%', missatge;
    WHEN others THEN
        SELECT texte INTO missatge FROM missatgesExcepcions WHERE num = 2;
        RAISE EXCEPTION '%', missatge;
END;
$$LANGUAGE plpgsql;
-- 4)

CREATE OR REPLACE FUNCTION assignar_individual(nom CHAR(10), esport CHAR(10)) RETURNS void AS $$
DECLARE
    missatge VARCHAR(50);
    nhomes INTEGER;
    ndones INTEGER;
BEGIN
    IF EXISTS (SELECT * FROM socisclubs WHERE nclub = esport AND nsoci = nom) THEN
        SELECT texte INTO missatge FROM missatgesExcepcions WHERE num = 3;
        RAISE EXCEPTION '%', missatge;
    END IF;
    IF NOT EXISTS (SELECT * FROM socis WHERE nom = nsoci) OR NOT EXISTS (SELECT * FROM clubs WHERE nclub = esport) THEN
        SELECT texte INTO missatge FROM missatgesExcepcions WHERE num = 4;
        RAISE EXCEPTION '%', missatge;
    END IF;
    IF 10 = (SELECT COUNT(*) FROM socisclubs WHERE nclub = esport) THEN
        SELECT texte INTO missatge FROM missatgesExcepcions WHERE num = 1;
        RAISE EXCEPTION '%', missatge;
    END IF;
    nhomes = (SELECT COUNT(*) FROM socisclubs sc, socis s WHERE sc.nclub = esport
                                AND sc.nsoci = s.nsoci
                                AND s.sexe = 'M');
    ndones = (SELECT COUNT(*) FROM socisclubs sc, socis s WHERE sc.nclub = esport
                                AND sc.nsoci = s.nsoci
                                AND s.sexe = 'F');
    IF nhomes = ndones THEN
        IF 'M' = (SELECT sexe FROM socis s WHERE s.nsoci = nom) THEN
            SELECT texte INTO missatge FROM missatgesExcepcions WHERE num = 2;
            RAISE EXCEPTION '%', missatge;
        END IF;
    END IF;

    --Insert if there's no fail
    INSERT INTO socisclubs VALUES(nom, esport);
    IF 6 = (SELECT COUNT(*) FROM socisclubs sc WHERE esport = sc.nclub) THEN
        INSERT INTO clubs_amb_mes_de_5_socis VALUES(esport);
    END IF;
    --

    EXCEPTION
        WHEN raise_exception THEN
            RAISE EXCEPTION '%', missatge;
        WHEN OTHERS THEN
            SELECT texte INTO missatge FROM missatgesExcepcions WHERE num = 6;
            RAISE EXCEPTION '%', missatge;

END;
$$LANGUAGE plpgsql;

-- JosepRivaille