SET SERVEROUTPUT ON;

DROP TABLE emp;
CREATE TABLE emp
( matr NUMBER(10) NOT NULL,
  nom VARCHAR2(50) NOT NULL,
  sal number(7,2),  
  adresse VARCHAR2(96),
  dep NUMBER(10) NOT NULL,
  CONSTRAINT emp_pk PRIMARY KEY (matr)
);

INSERT INTO EMP VALUES (0,'Turing, Alan', 2446.13, 'Manchester, GB', 1);
INSERT INTO EMP VALUES (1,'Dijkstra, Edsger', 1779.88, 'Rotterdam, NL', 1);
INSERT INTO EMP VALUES (2,'Mac Lane, Saunders', 3141.16, 'Chicago, US', 2);

COMMIT;
SELECT * FROM emp;

DROP TABLE DEPT;
CREATE TABLE dept
( id_dep NUMBER(10),
  nom_dep VARCHAR2(32),
  CONSTRAINT dep_pk PRIMARY KEY (id_dep)
);

INSERT INTO DEPT VALUES(1, 'Maths');
INSERT INTO DEPT VALUES(2, 'Computer Science');
INSERT INTO DEPT VALUES(3, 'Biology');

COMMIT;
SELECT * FROM dept;

ALTER TABLE emp
ADD CONSTRAINT fk_dep
FOREIGN KEY (dep)
REFERENCES dept(id_dep);


------------------------------------------------------------------------------------------------------------------------
-- requête d'exemple 
SELECT D.id_dep, D.nom_dep,
  COUNT(matr) AS nb
FROM Dept D LEFT OUTER JOIN Emp E
  ON D.id_dep = E.dep
GROUP BY D.id_dep, D.nom_dep; 
-- NB : on utilise un LEFT OUTER JOIN pour avoir un NULL quand le departement est vide (0 en biology)


------------------------------------------------------------------------------------------------------------------------
-- mauvaise solution : nouvel attribut, calcul de l'état actuel et trigger

ALTER TABLE DEPT
  ADD nb_emps NUMBER DEFAULT 0;
  
SELECT * FROM DEPT;

DECLARE
  v_nb_emps NUMBER;
  CURSOR c_dept IS
  SELECT *
  FROM DEPT; 
BEGIN
  FOR v_dept IN c_dept LOOP
    SELECT COUNT(matr) INTO v_nb_emps
    FROM EMP
    WHERE EMP.dep = v_dept.id_dep;
    IF (v_nb_emps IS NULL) THEN
      v_nb_emps := 0;
    END IF;
    UPDATE DEPT 
      SET nb_emps = v_nb_emps
      WHERE DEPT.id_dep = v_dept.id_dep;
    DBMS_OUTPUT.PUT_LINE('Departement ' ||  v_dept.id_dep || ' : ' || v_nb_emps) ;
  END LOOP;
END;
/

SELECT * FROM DEPT;

CREATE OR REPLACE TRIGGER nb_emps
AFTER INSERT OR UPDATE OR DELETE
  ON EMP
  FOR EACH ROW
BEGIN
  IF INSERTING THEN
    UPDATE DEPT 
      SET nb_emps = nb_emps + 1
      WHERE id_dep = :NEW.dep;
  END IF;
  -- IF UPDATING ...
  -- IF DELETING ...
END;
/

DROP TRIGGER nb_emps;


SELECT * FROM EMP;
SELECT * FROM DEPT;
INSERT INTO EMP VALUES (3,'Tarjan, Robert', 2446.13, 'Princeton, US', 2);
SELECT * FROM EMP;
SELECT * FROM DEPT;

ALTER TABLE DEPT
  DROP COLUMN nb_emps;

------------------------------------------------------------------------------------------------------------------------
-- bonne solution : une vue

CREATE OR REPLACE VIEW V_DEPT_NB AS
SELECT D.id_dep, D.nom_dep,
  COUNT(matr) AS nb
FROM Dept D LEFT OUTER JOIN Emp E
  ON D.id_dep = E.dep
GROUP BY D.id_dep, D.nom_dep; 

SELECT * FROM V_DEPT_NB;

SELECT * FROM user_views WHERE view_name LIKE '%DEPT%';


------------------------------------------------------------------------------------------------------------------------
-- mise à jour des vues
UPDATE V_DEPT_NB
SET nom_dep = 'Mathematics' WHERE id_dep = 1;
-- KO : on ne peut pas mettre à jour une vue qui contient un aggregat 


CREATE OR REPLACE VIEW V_EMP_DEP1 AS
SELECT * FROM EMP
WHERE dep = 1;

SELECT * FROM  V_EMP_DEP1;
UPDATE V_EMP_DEP1 SET sal = '2000' WHERE matr = 0;
-- OK
SELECT * FROM EMP;

------------------------------------------------------------------------------------------------------------------------

DROP VIEW V_DEPT_NB;
DROP VIEW V_EMP_DEP1;
