tests

CREATE OR REPLACE PROCEDURE AddOrder
        (PIdclient IN oorder.IdClient%type,
        POrderDate IN oorder.OrderDate%type) IS
    BEGIN
        INSERT INTO oorder (IdO, IdClient, OrderDate, OrderState)
        VALUES (Seq_oorder.nextval, PIdclient, POrderDate, 'AT') ;
    END;
/

EXECUTE AddOrder(1, to_date('1998/05/31:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'))

EXECUTE AddOrder(1, to_date('2009/04/23:03:03:00PM', 'yyyy/mm/dd:hh:mi:ssam'))

EXECUTE AddOrder(1, to_date('2009/04/22:03:43:00PM', 'yyyy/mm/dd:hh:mi:ssam'))

EXECUTE AddOrder(1, to_date('2009/04/22:03:44:00PM', 'yyyy/mm/dd:hh:mi:ssam'))

EXECUTE AddOrder(1, to_date('2009/04/21:03:44:00PM', 'yyyy/mm/dd:hh:mi:ssam'))

EXECUTE AddOrder(2, to_date('2009/04/22:12:44:00AM', 'yyyy/mm/dd:hh:mi:ssam'))

EXECUTE AddOrder(2, to_date('2009/04/22:03:00:00PM', 'yyyy/mm/dd:hh:mi:ssam'))

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;
           END LOOP;
           CLOSE mon_curseur;
    END;
/

EXECUTE passerATenECT

EXECUTE AddOrderLine(8, 1, 12)

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>8</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');