set echo off;
set verify off;
set feed off;

DELETE FROM PRODUITFOURN WHERE NUMPRODUIT = 'P0002';
DELETE FROM PRODUITFOURN WHERE NUMPRODUIT = 'P0004';

DROP TABLE TproduitAvFourn PURGE;
DROP TABLE TproduitNoFourn PURGE;

CREATE TABLE TproduitAvFourn(
    NOMFOURN    VARCHAR2(100),
    NUMPRODUIT  CHAR(5),
    DESIGNATION VARCHAR2(100),
    STOCK       NUMBER,
    PRIXV       NUMBER(10, 2),
    CODERAYON   CHAR(5)
);

CREATE TABLE TproduitNoFourn(
    NUMPRODUIT  CHAR(5),
    DESIGNATION VARCHAR2(100),
    STOCK       NUMBER,
    PRIXV       NUMBER(10, 2),
    CODERAYON   CHAR(5)
);

DECLARE
    dNumProd CHAR(5);
    dTest NUMBER;
    dDesign varchar2(100);
    dStock NUMBER;
    dPrix NUMBER(10,2);
    dCodeR CHAR(5);
    dNomF varchar2(100);
    dListeF varchar2(1000);

    CURSOR r IS SELECT * FROM PRODUIT;
    CURSOR p IS SELECT f.NOM FROM FOURNISSEUR f, PRODUITFOURN pf
    WHERE dNumProd = pf.NUMPRODUIT AND f.REFFOURN = pf.REFFOURN;

BEGIN
    OPEN r;
    FETCH r INTO dNumProd, dDesign, dStock, dPrix, dCodeR;

    WHILE r%FOUND LOOP
        SELECT count(NUMPRODUIT) INTO dTest FROM PRODUITFOURN
            WHERE NUMPRODUIT = dNumProd;

        IF dTest != 0 THEN
            OPEN p;
            FETCH p INTO dNomF;
            WHILE p%FOUND LOOP
                dListeF := dListeF||' '||dNomF;
                FETCH p INTO dNomF;
            END LOOP;
            CLOSE p;
            INSERT INTO TproduitAvFourn VALUES (dListeF, dNumProd,dDesign,dStock,dPrix,dCodeR);
            dListeF := '';
        ELSE
            INSERT INTO TproduitNoFourn VALUES (dNumProd,dDesign,dStock,dPrix,dCodeR);
        END IF;

        FETCH r INTO dNumProd, dDesign, dStock, dPrix, dCodeR;
    END LOOP;
    CLOSE r;
END;
.
/

SELECT * FROM TproduitAvFourn;
SELECT * FROM TproduitNoFourn;

set feed on;
set verify on;
set echo on;