tache 3
REM 1) passer les commandes AT en ECT après 24 H
CREATE OR REPLACE PROCEDURE passerATenECT
IS
CURSOR mon_curseur IS
SELECT IdO
FROM oorder
WHERE OrderState = 'AT'
AND floor(((sysdate-OrderDate)*24*60*60)/3600) > 24;
AT_ECT mon_curseur%ROWTYPE;
BEGIN
OPEN mon_curseur;
FETCH mon_curseur INTO AT_ECT;
WHILE mon_curseur%FOUND
LOOP
FETCH mon_curseur INTO AT_ECT;
UPDATE Oorder
SET OrderState = 'ECT'
WHERE IdO = AT_ECT.IdO;
StockManager(AT_ECT.IdO);
END LOOP;
CLOSE mon_curseur;
END;
/
REM 1.5) rajouter une colonne OrderDateECT dans la table Order
ALTER TABLE Oorder
ADD OrderDateECT Date;
REM 2) Processus StockManager : passer les commandes ECT en T si stock suffisant
REM sinon les met en attente de reappro (et genere une demande de reappro)<-simple affichage
REM 3) Créer la table SUPPLY
DROP TABLE Supply CASCADE CONSTRAINTS;
CREATE TABLE Supply (tk number, appro sys.XMLTYPE);
DROP SEQUENCE seq_supply;
CREATE SEQUENCE seq_supply start with 1 INCREMENT BY 1;
INSERT INTO Supply(tk,appro) VALUES (seq_supply.nextval, sys.XMLType.createXML(
'<?xml version="1.0"?>
<product>
<order>123</order>
<idProduct>1</idProduct>
<quantity>12</quantity>
</product>'));
CREATE INDEX proc_idx ON Supply(appro)
INDEXTYPE IS ctxsys.context
parameters('FILTER ctxsys.null_filter SECTION GROUP ctxsys.path_section_group');
REM 4) Processus SupplyManager : mettre à jour le stock d'apres la table SUPPLY
REM et traiter les commandes ECT en attente de reappro concernees
CREATE OR REPLACE TRIGGER SupplyManager
AFTER INSERT ON Supply
FOR EACH ROW
DECLARE
res_order oorder.IdO%TYPE :=0;
res_product product.IdProd%TYPE :=0;
res_qt product.StockQuantity%TYPE :=0;
complet INTEGER :=1;
CURSOR mon_curseur IS SELECT reserve FROM orderline WHERE IdO = res_order;
ligne_order mon_curseur%ROWTYPE;
BEGIN
SELECT s.appro.extract('/product/order/text()').getStringVal() INTO res_order FROM supply s WHERE tk = :new.tk;
SELECT s.appro.extract('/product/idProduct/text()').getStringVal() INTO res_product FROM supply s WHERE tk = :new.tk;
SELECT s.appro.extract('/product/quantity/text()').getStringVal() INTO res_qt FROM supply s WHERE tk = :new.tk;
UPDATE OrderLine
SET reserve = 1
WHERE IdO = res_order
AND IdProduct = res_product;
complet := 1;
OPEN mon_curseur;
FETCH mon_curseur INTO ligne_order;
WHILE mon_curseur%FOUND
LOOP
IF ligne_order.reserve = 0 THEN
complet := 0;
END IF;
FETCH mon_curseur INTO ligne_order;
END LOOP;
CLOSE mon_curseur;
IF complet = 1 THEN
UPDATE Oorder
SET OrderState = 'T'
WHERE IdO = res_order;
END IF;
END;
/
REM 5) Procédure de création de job
VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno, 'passerATenECT;', SYSDATE, 'SYSDATE + 1/(24*60*60)');
commit;
END;
/
commit;