-- ALTER TABLE ville Drop Column pourcentagemax;
--ALTER TABLE ville RENAME Column pourcentagemin to pourcent;
-- update ville set POURCENT= 40 where nom_ville='Dijon';  
-- update ville set POURCENT= 100 where NO_VILLE= 8;   
--ALTER TABLE promotion add Colunm pourcent number (4,0);


CREATE OR REPLACE PROCEDURE test(nombre integer) IS
	
	i integer := 1;
	num_ville number(5,0);
	aleat number(5, 0);
	nom varchar(30);
	prenom varchar(30);
--	laville varchar(50);
	date_naissance date;
	num_promo number(5, 0);
	
	CURSOR c_ville IS
		SELECT * from ville
		ORDER BY pourcent;

	enreg c_ville%rowtype;

	CURSOR c_promo IS
		SELECT * from promotion
		ORDER BY pourcent;

	enreg_promo c_promo%rowtype;
	
	
BEGIN
	WHILE nombre >= i LOOP
		nom:=DBMS_RANDOM.STRING('u', 7);
		prenom:=DBMS_RANDOM.STRING('u', 7);
		Select TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2484418,2484418+3640)),'J') into 		date_naissance FROM DUAL;
	
		aleat:= DBMS_RANDOM.VALUE(1, 100);
		OPEN c_ville;
		LOOP
	  		FETCH c_ville INTO enreg;
			EXIT WHEN c_ville %NOTFOUND;
			If (aleat < enreg.pourcent) then 
				num_ville := enreg.no_ville;
			END If;
		END LOOP;
		CLOSE c_ville;

		
		aleat:= DBMS_RANDOM.VALUE(1, 100);
		OPEN c_promo;
		LOOP
	  		FETCH c_promo INTO enreg_promo;
			EXIT WHEN c_promo %NOTFOUND;
			If (aleat < enreg_promo.pourcent) then 
				num_promo := enreg_promo.no_promotion;
			END If;
		END LOOP;
		CLOSE c_promo;

	insert into etudiant values(auto_incr.NEXTVAL, nom, prenom, num_ville, num_promo, date_naissance);
		i:=i+1;
	END LOOP;
END;
/

