--******************CREATION DES VIEWS
--Vue client
CREATE OR REPLACE VIEW Clients AS
(
((
( SELECT * FROM clients_AM
  WHERE pays
  IN (SELECT NAME FROM AM_COUNTRIES)
)
UNION ALL
( SELECT * FROM clients_edn
  WHERE pays
  IN (SELECT NAME FROM EUR_COUNTRIES WHERE site = 'EURN')
))
UNION ALL
( SELECT * FROM clients_eds
  WHERE pays
  IN (SELECT NAME FROM EUR_COUNTRIES WHERE site = 'EURS')
))
UNION ALL
(SELECT * FROM clients_o)
);

SELECT * FROM am_countries;

SELECT * FROM clients WHERE pays='France';
--Vue stock
CREATE OR REPLACE VIEW Stock AS
(
(((SELECT * FROM stock_AM)
UNION ALL
(SELECT * FROM stock_edn))
UNION ALL
(SELECT * FROM stock_eds))
UNION ALL
(SELECT * FROM stock_o)
);

--Vue commande
CREATE OR REPLACE VIEW Commandes AS
(
(((SELECT * FROM commandes_am)
UNION ALL
(SELECT * FROM commandes_edn))
UNION ALL
(SELECT * FROM commandes_eds))
UNION ALL
(SELECT * FROM commandes_o)
);

--Vue details commande
CREATE OR REPLACE VIEW Details_Commandes AS
(
(((SELECT * FROM det_commandes_am)
UNION ALL
(SELECT * FROM det_commandes_edn))
UNION ALL
(SELECT * FROM det_commandes_eds))
UNION ALL
(SELECT * FROM det_commandes_o)
);

--******************Materialized View
CREATE MATERIALIZED VIEW MVR_employe
REFRESH FAST
NEXT SYSDATE+(1/24)
AS
SELECT *
FROM RemEmployes;

CREATE OR REPLACE SYNONYM Employes FOR MVR_employe;

DROP MATERIALIZED VIEW MVR_employe;

INSERT INTO Employes
VALUES (14,1,'CAROTT','LAPIN', 'ReprÈsentant(e)',  'Mme',  '19/09/58', '03/05/93', 2856,   250);

--******************Materialized View LOG ON
CREATE MATERIALIZED VIEW LOG ON FOURNISSEURS;
GRANT SELECT ON MLOG$_FOURNISSEURS TO mfgonzalezf;

INSERT INTO Clients
VALUES ('AAAAA', 'Paris spÈcialitÈs', 'boulevard Charonne',   'Paris',    '75012', 'France', '01.42.34.22.66', '01.42.34.22.77');

INSERT INTO CLients
VALUES ('BBBBB', 'Paris spÈcialitÈs', 'boulevard Charonne',   'Paris',    '75012', 'Bahamas', '01.42.34.22.66', '01.42.34.22.77');

INSERT INTO CLients
VALUES ('CCCCC', 'Paris spÈcialitÈs', 'boulevard Charonne',   'Paris',    '75012', 'Bahamas', '01.42.34.22.66', '01.42.34.22.77');

SELECT * FROM AM_COUNTRIES;
SELECT * FROM CLIENTS_AM;