You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
77 lines
1.8 KiB
77 lines
1.8 KiB
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;
|