Untitled

DECLARE
    v_codclt  Client.codclt%TYPE;
    v_codart  Article.codart%TYPE;
    v_qtecdee Lignecde.qtecdee%TYPE;
    v_count   INTEGER;
    v_qtestck Article.qtestck%TYPE;
    v_prixunit Article.prixunit%TYPE;
    v_numcde   Commande.numcde%TYPE;
    v_mntcde   Commande.mntcde%TYPE;
BEGIN
   
    v_codclt := :Entrez_code_du_client; 
    SELECT COUNT(*)
    INTO v_count
    FROM Client
    WHERE codclt = v_codclt;

    IF v_count = 0 THEN
        DBMS_OUTPUT.PUT_LINE('Le code client saisi n''existe pas.');
        RETURN;
    END IF;
    v_codart := :Entrez_code_article;
    SELECT COUNT(*)
    INTO v_count
    FROM Article
    WHERE codart = v_codart;

    IF v_count = 0 THEN
        DBMS_OUTPUT.PUT_LINE('Le code article saisi n''existe pas.');
        RETURN;
    END IF;

    
    v_qtecdee := :Entrez_quantite_commande;
    SELECT qtestck
    INTO v_qtestck
    FROM Article
    WHERE codart = v_codart;

    IF v_qtecdee-v_qtestck >0  THEN
        DBMS_OUTPUT.PUT_LINE('La quantité commandée est supérieure à la quantité en stock.');
        RETURN;
    END IF;
    DBMS_OUTPUT.PUT_LINE('Toutes les saisies sont valides. La commande peut être passée.');

    select Seq_Cde.NEXTVAL into v_numcde from dual;

    INSERT INTO Commande (numcde, datecde, mntcde, codclt)
    VALUES (v_numcde, SYSDATE, 0, v_codclt);

    INSERT INTO Lignecde (codart, numcde, qtecdee)
    VALUES (v_codart, v_numcde, v_qtecdee);

    v_mntcde := v_prixunit * v_qtecdee;
    UPDATE Commande
    SET mntcde = v_mntcde
    WHERE numcde = v_numcde;

    UPDATE Client
    SET caclt = (SELECT SUM(mntcde)
                 FROM Commande
                 WHERE codclt = v_codclt)
    WHERE codclt = v_codclt;

    DBMS_OUTPUT.PUT_LINE('Commande et lignes insérées avec succès, et montants mis à jour.');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Une erreur s''est produite : ' || SQLERRM);
END;
/